ER-диаграммы и схема БД

Вы умеете нормализовать схему и расставлять связи. Следующий вызов — донести схему до коллег: разработчиков, аналитиков, менеджеров. Таблица SQL-команд плохо читается неподготовленным человеком. ER-диаграмма (Entity-Relationship diagram, диаграмма «сущность-связь») — графическое изображение схемы базы данных, которое читается без знания SQL.

Что такое ER-диаграмма

ER-модель предложил Питер Чен в 1976 году. Диаграмма показывает:

Сущности (Entities) — объекты предметной области: клиент, заказ, товар
Атрибуты (Attributes) — свойства сущностей: имя клиента, сумма заказа
Связи (Relationships) — отношения между сущностями: клиент делает заказ

[Клиент] ────делает───< [Заказ] >────содержит────< [Позиция заказа] >────ссылается─── [Товар]

В реляционных базах сущности соответствуют таблицам, атрибуты — колонкам, связи — внешним ключам.

Нотации ER-диаграмм

Существует несколько нотаций для изображения связей. Самая распространённая в современных инструментах — нотация Crow's Foot («воронья лапа»):

  |──      = обязательно, один (ровно один)
  O──      = необязательно (ноль или один)
  |──<     = один-ко-многим (обязательно)
  O──<     = один-ко-многим (необязательно)
  >──<     = многие-ко-многим

Пример диаграммы интернет-магазина в псевдо-Crow's Foot:

CUSTOMERS ──|────|< ORDERS ──|────|< ORDER_ITEMS >|────|── PRODUCTS
                                                   |
                                              |────|── CATEGORIES

«Воронья лапа» (<) означает «многие», вертикальная черта (|) — «один», кружок (O) — «ноль (необязательно)».

ERD в текстовом формате: Mermaid

Для документации часто используют текстовые диаграммы. Mermaid — язык разметки, который GitHub рендерит в диаграммы прямо в Markdown:

erDiagram
    CUSTOMERS {
        int id PK
        string name
        string email
    }
    ORDERS {
        int id PK
        int customer_id FK
        decimal total
        string status
    }
    CUSTOMERS ||--o{ ORDERS : "делает"

||--o{ читается: один (обязательно) к нулю или многим. Это нотация Crow's Foot в текстовом виде. GitHub, GitLab и многие wiki-системы рендерят такие диаграммы автоматически.

Полная схема магазина в Mermaid ERD

erDiagram
    CATEGORIES {
        int id PK
        string name
        int parent_id FK
    }
    PRODUCTS {
        int id PK
        string title
        int category_id FK
        decimal price
    }
    CUSTOMERS {
        int id PK
        string name
        string email
    }
    ORDERS {
        int id PK
        int customer_id FK
        string status
        timestamp created_at
    }
    ORDER_ITEMS {
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }

    CATEGORIES ||--o{ CATEGORIES : "parent"
    CATEGORIES ||--o{ PRODUCTS : "категория"
    CUSTOMERS ||--o{ ORDERS : "делает"
    ORDERS ||--|{ ORDER_ITEMS : "содержит"
    PRODUCTS ||--|{ ORDER_ITEMS : "в заказе"

Такая диаграмма живёт рядом с кодом в README.md и обновляется при изменении схемы.

Инструменты для ER-диаграмм

Бесплатные онлайн:

dbdiagram.io — простой язык DBML, экспорт в SQL
drawio — универсальный, есть шаблоны для ERD
QuickDBD — быстрый текстовый ввод

Встроенные в СУБД-инструменты:

DBeaver — reverse engineer ERD из существующей схемы
pgAdmin — просмотр зависимостей таблиц

Для разработчиков:

Mermaid (в Markdown файлах)
PlantUML

Особенно полезен reverse engineering — автоматическое построение ER-диаграммы из существующей базы. DBeaver умеет это: открываете базу, выбираете таблицы — получаете диаграмму.

Чтение существующей схемы в PostgreSQL

Когда нет диаграммы, можно изучить схему через SQL:

-- Все таблицы и их колонки
SELECT t.table_name, c.column_name, c.data_type, c.is_nullable
FROM information_schema.tables t
JOIN information_schema.columns c ON c.table_name = t.table_name
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
ORDER BY t.table_name, c.ordinal_position;

-- Все внешние ключи (связи между таблицами)
SELECT
    tc.table_name AS child_table,
    kcu.column_name AS fk_column,
    ccu.table_name AS parent_table,
    ccu.column_name AS parent_column,
    rc.delete_rule AS on_delete
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints rc
    ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Второй запрос даёт полный список связей: какая таблица ссылается на какую, через какую колонку, и что происходит при удалении.

Концептуальная, логическая и физическая модели

В профессиональном моделировании данных различают три уровня:

Концептуальная модель (Conceptual Data Model):

Самый высокий уровень абстракции
Для бизнес-аналитиков и стейкхолдеров
Только сущности и связи, без деталей реализации
Пример: «Клиент делает Заказы, Заказы содержат Товары»

Логическая модель (Logical Data Model):

Добавляет атрибуты, типы данных, ключи
Независима от конкретной СУБД
Для архитекторов и ведущих разработчиков
Пример: Customer(id, name, email), Order(id, date, total, customer_id)

Физическая модель (Physical Data Model):

Конкретная реализация для выбранной СУБД
Включает типы данных (SERIAL, TIMESTAMPTZ), индексы, ограничения, партиционирование
Для разработчиков и DBA
Пример: CREATE TABLE customers (id SERIAL PRIMARY KEY, ...)

Концептуальная: [Клиент] ──делает──< [Заказ]
Логическая:     Customer(id, name) ──1:N──< Order(id, customer_id, total)
Физическая:     CREATE TABLE customers (...); CREATE TABLE orders (...);

В небольших проектах эти уровни часто объединяют. Понимание слоёв помогает при работе в команде: дизайнер рисует концептуальную, архитектор уточняет логическую, разработчик реализует физическую.

Ключи на диаграмме

В ER-диаграммах первичные ключи обозначают подчёркиванием имени атрибута. В Mermaid — пометкой PK:

erDiagram
    PRODUCTS {
        int id PK
        int category_id FK
        string title
        decimal price
        boolean in_stock
    }
    CATEGORIES {
        int id PK
        string name
        int parent_id FK "nullable"
    }
    CATEGORIES ||--o{ PRODUCTS : "в категории"
    CATEGORIES ||--o{ CATEGORIES : "подкатегория"

FK помечает внешние ключи. Самоссылающийся CATEGORIES ||--o{ CATEGORIES — рекурсивная связь «категория содержит подкатегории».

Проектирование «сверху вниз» vs «снизу вверх»

Два подхода к созданию схемы:

«Сверху вниз» (Top-Down): начинать с концептуальной ER-диаграммы:

Выделить сущности из требований
Определить атрибуты каждой сущности
Определить связи
Перевести в физическую схему SQL

«Снизу вверх» (Bottom-Up): начинать с данных:

Собрать все данные, которые нужно хранить
Нормализовать (1NF → 2NF → 3NF)
Расставить FK по связям
Построить ER-диаграмму как документацию

На практике используют оба подхода: начинают с концептуальной диаграммы (top-down), уточняют при нормализации (bottom-up).

Из диаграммы в SQL: пример

Концептуальная диаграмма блога:

[USER] ──пишет──< [POST] ──получает──< [COMMENT] >──пишет── [USER]

Физическая схема SQL:

CREATE TABLE users (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username   VARCHAR(50) UNIQUE NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id  BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    published  BOOLEAN DEFAULT FALSE
);

CREATE TABLE comments (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id    BIGINT NOT NULL REFERENCES posts(id)  ON DELETE CASCADE,
    author_id  BIGINT NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    content    TEXT NOT NULL
);

Диаграмма → SQL = прямая трансляция. Каждая сущность — таблица, каждая связь ──< — FK в таблице «многие».

ER-диаграмма как живая документация

Диаграмма полезна, только если она актуальна. Практические советы:

Хранить рядом с кодом: файл docs/er-diagram.md с Mermaid — обновляется в той же PR, что и миграция схемы. Ревьюер видит изменение диаграммы рядом с изменением SQL.

Генерировать из схемы: для существующих проектов — использовать DBeaver или dbdocs.io для автоматической генерации. Это правда о том, что есть в базе, а не о том, что планировалось.

Не рисовать все детали: большие схемы (50+ таблиц) на одной диаграмме нечитаемы. Разбейте на домены: «Пользователи», «Заказы», «Каталог товаров» — отдельные диаграммы для каждого домена.

Нотация для аудитории: для бизнеса — только прямоугольники и стрелки без атрибутов. Для разработчиков — полная Crow's Foot нотация с атрибутами и типами.

Краткий итог

ER-диаграмма — графическое изображение схемы: сущности, атрибуты, связи
Нотация Crow's Foot: ||──o{ = один к нулю-или-многим; ||──|{ = один к одному-или-многим
Mermaid erDiagram — текстовый формат, рендерится в GitHub/GitLab
Reverse engineering в DBeaver строит диаграмму из существующей схемы
information_schema в PostgreSQL — программный доступ к схеме
Top-down: концепция → SQL; Bottom-up: данные → нормализация → диаграмма

Что дальше

Вы умеете проектировать и документировать схемы. Последний урок модуля — антипаттерны: типичные ошибки проектирования, которые делают базу сложной в поддержке.

ER-диаграммы и схема БД