Урок 06.01: ER-диаграммы (Сущности и связи)

Цель урока

Научиться строить ER-диаграммы (Entity-Relationship Diagram) для моделирования данных на трёх уровнях абстракции: концептуальном (бизнес-модель), логическом и физическом (таблицы SQL). Понять разницу между ERD и UML Class Diagram, освоить нотацию «Crow's Foot» («Лапка вороны»), научиться выделять сущности, атрибуты и связи из требований, а также глубоко разобраться с трансляцией N:M-связей и выбором между суррогатными и натуральными ключами.

Ключевые понятия

Термин	Определение
Entity (Сущность)	Объект предметной области, о котором мы храним данные (прямоугольник)
Attribute (Атрибут)	Свойство сущности (колонка в таблице)
Relationship (Связь)	Логическое соединение между сущностями (линия)
Primary Key (Первичный ключ)	Атрибут или набор атрибутов, однозначно идентифицирующий запись
Composite Key (Составной ключ)	Первичный ключ, состоящий из двух и более атрибутов
Foreign Key (Внешний ключ)	Атрибут, ссылающийся на Primary Key другой сущности
Surrogate Key (Суррогатный ключ)	Искусственный ключ (автоинкремент/UUID), не имеющий бизнес-смысла
Natural Key (Натуральный ключ)	Ключ, взятый из предметной области (ISBN, VIN, email)
Cardinality (Кардинальность)	Количество экземпляров одной сущности, связанных с другой
Crow's Foot Notation	Нотация «лапка вороны» (троеточие на конце линии для «много»)
Chen Notation	Классическая нотация Питера Чена (ромб для связи)
IE (Information Engineering) Notation	Нотация с «лапкой» (Crow's Foot), наиболее популярная

1. Три уровня моделирования данных

Одна из главных компетенций аналитика — умение переключаться между уровнями абстракции при проектировании данных. Одна и та же предметная область выглядит по-разному на разных уровнях.

1.1. Концептуальная модель (Conceptual / Business Data Model)

Также известна как: бизнес-модель данных, семантическая модель, доменная модель.

Назначение: Описать данные языком бизнеса — без технических деталей, без типов, без ключей. Показать, о чём бизнес говорит, и как эти понятия связаны.

Кто создаёт: Бизнес-аналитик совместно с заказчиком (Subject Matter Expert).

Кто читает: Заказчик, бизнес-пользователь, Product Owner, инвесторы.

Пример формулировки:

«Наш бизнес оперирует понятиями: Клиент, Заказ, Товар, Склад. Клиент делает Заказ. В Заказе может быть несколько Товаров. Товары хранятся на Складах.»

Как выглядит:

┌──────────┐          ┌──────────┐          ┌──────────┐
│  Клиент  │──────────│  Заказ   │──────────│  Товар   │
└──────────┘          └──────────┘          └──────────┘
                          │
                          │
                     ┌────┴────┐
                     │  Склад  │
                     └─────────┘

Характеристики концептуальной модели:

Параметр	Значение
Атрибуты	❌ Не указываются (или только ключевые слова)
Типы данных	❌ Не указываются
Первичные ключи	❌ Не указываются
Foreign Keys	❌ Не указываются
Связи	✅ Только типы: «имеет», «состоит из», «относится к»
Степень детализации	Низкая
Инструменты	Бумага/доска, Miro, Gliffy, простые блок-схемы

Когда нужна: В начале проекта, на этапе Discovery, для согласования терминологии с заказчиком. Помогает обнаружить расхождения в понимании предметной области.

Правило хорошей концептуальной модели: Если заказчик (не IT-специалист) смотрит на неё и говорит «Да, всё верно, это наш бизнес» — модель удалась.

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

Назначение: Описать структуру данных независимо от конкретной СУБД — какие сущности, какие атрибуты, какие связи, но без учёта физической реализации (индексы, партиционирование, типы СУБД).

Кто создаёт: Системный аналитик, Data Architect.

Кто читает: Разработчики, архитекторы, DBA.

Пример формулировки:

«Сущность Заказ имеет атрибуты: номер (строка), дата создания (дата/время), статус (справочник). Клиент и Заказ связаны 1:N (один клиент — много заказов).»

Характеристики логической модели:

Параметр	Значение
Атрибуты	✅ Полный список
Типы данных	✅ Обобщённые (VARCHAR, INTEGER, DATE — без привязки к СУБД)
Первичные ключи	✅ Указаны (PK)
Foreign Keys	✅ Указаны (FK)
Связи	✅ С кардинальностью (1:1, 1:N, N:M)
Нормализация	✅ Применена (до 3НФ)
Инструменты	Draw.io, dbdiagram.io, Lucidchart, PlantUML, ERwin

Когда нужна: После утверждения концептуальной модели, перед физическим проектированием. Является артефактом для передачи в разработку.

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

Назначение: Описать конкретные SQL-таблицы для выбранной СУБД (PostgreSQL, MySQL, MSSQL, Oracle), включая типы данных, индексы, constraints, партиции, триггеры.

Кто создаёт: DBA, Backend-разработчик, Data Engineer.

Кто читает: DBA, разработчики, DevOps.

Пример формулировки:

CREATE TABLE "order" (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id     UUID NOT NULL REFERENCES client(id),
    order_date    TIMESTAMPTZ NOT NULL DEFAULT now(),
    status        VARCHAR(20) NOT NULL DEFAULT 'draft',
    total_amount  NUMERIC(12,2) NOT NULL DEFAULT 0
);
CREATE INDEX idx_order_client_id ON "order"(client_id);

Характеристики физической модели:

Параметр	Значение
Атрибуты	✅ С точными именами колонок
Типы данных	✅ Привязаны к СУБД (VARCHAR(n), TIMESTAMPTZ, NUMERIC(12,2))
Первичные ключи	✅ С указанием стратегии (SERIAL, UUID, sequence)
Foreign Keys	✅ С правилами ON DELETE / ON UPDATE
Индексы	✅ Включены
Constraints	✅ CHECK, UNIQUE, EXCLUDE
Партиционирование	✅ Если применимо
Инструменты	SQL DDL, DBeaver (ERD-модуль), DataGrip, Liquibase, Flyway

1.4. Сравнительная таблица трёх уровней

Критерий	Концептуальная	Логическая	Физическая
Аудитория	Заказчик, бизнес	Аналитик, архитектор	DBA, разработчик
Технические детали	Нет	Частично	Полностью
Типы данных	Нет	Обобщённые	Конкретные СУБД
Ключи	Нет	PK, FK	PK, FK, INDEX, UNIQUE
Нормализация	Нет	3НФ	3НФ + денормализация
Индексы	Нет	Нет	Да
Длительность жизни	Первые 2 недели проекта	Весь проект	Весь проект
Инструменты	Miro, доска	dbdiagram, Draw.io	DBeaver, SQL DDL

Правило для аналитика:

Начинайте с концептуальной (говорите с бизнесом на его языке), переходите к логической (структурируйте данные), завершайте физической (для разработки). Не прыгайте сразу в SQL, не выяснив бизнес-термины.

2. Суть ER-диаграммы и её место в моделировании

ER-диаграмма (Entity-Relationship Diagram) — это визуальная модель данных предметной области. Она показывает:

Сущности — о чём мы храним информацию
Атрибуты — что именно мы храним
Связи — как сущности связаны между собой

Простая аналогия: ER-диаграмма — это меню ресторана. Есть категории (сущности): «Напитки», «Салаты», «Горячее». У каждой — атрибуты (название, цена, вес). Связи: «Салат входит в состав Комбо-обеда».

Расположение в стеке артефактов:

Требования (User Stories) 
    ↓
Use Case / BPMN (процессы)
    ↓
Концептуальная ERD (сущности бизнеса) ← ВЫ ЗДЕСЬ
    ↓
Логическая ERD (сущности + атрибуты + связи)
    ↓
Физическая ERD (таблицы + типы + ключи)
    ↓
DDL (CREATE TABLE SQL)

2.1. ERD vs UML Class Diagram

Аспект	ER-диаграмма (ERD)	UML Class Diagram
Фокус	Данные (таблицы, колонки)	Объекты (классы, методы)
Методы/поведение	❌ Нет	✅ Да
Атрибуты	✅ (типы, ключи)	✅ (типы, модификаторы доступа)
Связи	Только ассоциации	Ассоциация, агрегация, композиция, наследование, зависимость
Первичный ключ	✅ Обязательно	⚠️ Опционально (может быть id)
Foreign Key	✅ Явно указан	❌ Не указывается (используется ссылка на объект)
Для кого	DBA, разработчик БД	ООП-разработчик, архитектор
Трансляция в SQL	Прямая (таблица → CREATE TABLE)	Требует ORM-маппинга (JPA, Hibernate)

На практике: Если вы проектируете базу данных — используйте ERD. Если систему в целом (ООП-код + БД) — начните с Class Diagram, а из него сгенерируйте ERD или используйте ORM-маппинг.

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

3.1. Нотация «Crow's Foot» (IE-нотация)

Наиболее популярная нотация в современных инструментах (Draw.io, Lucidchart, dbdiagram.io).

┌────────────┐              ┌──────────────┐
│  Сущность   │              │   Сущность    │
├────────────┤              ├──────────────┤
│ id (PK)    │1          0..*│ id (PK)      │
│ атрибут 1  │──────────────│ атрибут 1    │
│ атрибут 2  │   связь      │ атрибут 2    │
└────────────┘              └──────────────┘

Символы на концах линии:

Символ	Название	Значение	Обозначение
`╪`	Две черты	Ровно один	1
`╪◯`	Две черты + кружок	Ноль или один	0..1
`─◯`	Линия + кружок	Ноль или более	0..*
`─<`	Линия + лапка	Один или более	1..*
`─╪`	Линия + одна черта	Ровно один (альтернатива)	1

Как читать «лапку» (Crow's Foot):

Возьмём связь Проект → Задача:

Project ╪─────────────< Task
         1          0..*

Читается слева направо: «Один проект содержит ноль или более задач».

Читается справа налево: «Каждая задача относится ровно к одному проекту».

3.2. Нотация Чена (Chen)

Классическая нотация (Питер Чен, 1976):

  ┌──────────┐        ┌──────────┐        ┌──────────┐
  │ Сущность  │────────│  Связь   │────────│ Сущность  │
  └──────────┘        └──────────┘        └──────────┘

Особенности:

Сущность — прямоугольник
Связь — ромб (с глаголом внутри)
Атрибуты — овалы, соединённые с сущностью
Первичные ключи подчёркнуты

Где используется: В академических курсах, старых учебниках. В коммерческих проектах — редко, но полезно знать для чтения legacy-документации.

3.3. Нотация IDEF1X

Используется в государственных и военных проектах США (стандарт FIPS 184). Отличается строгими правилами и более сложной нотацией. В коммерческой разработке — редко.

4. Элементы ER-диаграммы (глубокий разбор)

4.1. Сущность (Entity)

Прямоугольник с именем (существительное в единственном числе).

Правильно	Неправильно	Почему
`Заказ`	`Заказы` (множественное)	Таблица хранит много записей, но сущность — тип, а не коллекция
`Пользователь`	`Юзеры` (смесь языков)	Должен быть единый язык модели (русский или английский)
`КатегорияТовара`	`ProductCategoryNameType`	Имя — существительное, не нужно перечислять атрибуты в имени

Сильная сущность (Strong / Independent Entity)

Может существовать самостоятельно, имеет свой Primary Key. Не зависит от других сущностей.

Примеры: Пользователь, Товар, Проект — они могут существовать без связи с чем-либо.

Слабая сущность (Weak / Dependent Entity)

Не может существовать без родительской сущности. Её Primary Key обязательно включает Foreign Key родителя.

Пример слабой сущности:

ПозицияЗаказа не может существовать без Заказ. Если удалить заказ — позиции удаляются автоматически.
PK = (order_id, line_number), где order_id — FK на Заказ, line_number — порядковый номер позиции внутри заказа.

Признаки слабой сущности:

Не имеет смысла без родителя (нет заказа → нет позиций)
PK содержит FK родителя
Обычно имеет relationship «Whole-Part» (целое-часть)

Ассоциативная сущность (Associative Entity)

Возникает при трансляции связи N:M. Является одновременно:

Сущностью (может иметь свои атрибуты, FK на обе родительские сущности)
Связью (реализует N:M)

Пример: Зачисление (Enrollment) — связь Студент : Курс (N:M). Ассоциативная сущность Зачисление может иметь атрибут дата_зачисления, оценка.

4.2. Атрибуты (Attributes)

Тип атрибута	Нотация	Пример в SQL
Простой	Имя колонки	`name VARCHAR(255)`
Ключевой (PK)	Подчёркнут или (PK)	`id UUID PRIMARY KEY`
Внешний ключ (FK)	(FK) после имени	`project_id UUID REFERENCES project(id)`
Обязательный	NOT NULL	`email VARCHAR(255) NOT NULL`
Опциональный	NULL (подразумевается)	`avatar_url VARCHAR(500)` (NULL по умолчанию)
Уникальный	(UQ)	`email VARCHAR(255) UNIQUE`
Многозначный	Овал с двойной линией в Chen	«Телефоны» — несколько номеров у одного пользователя. Лучше вынести в отдельную сущность `Телефон`

Правило для аналитика:

Если у атрибута может быть более одного значения — это не атрибут, а отдельная сущность. Никогда не храните несколько телефонов в одной колонке через запятую — это нарушение 1НФ.

4.3. Связи (Relationships)

Тип связи	Нотация Crow's Foot	Значение	Реализация в SQL	Пример
One-to-One (1:1)	`╪—╪`	Одной записи A соответствует ровно одна запись B	FK на любую сторону с UNIQUE	User ↔ Passport
One-to-Many (1:N)	`╪—<`	Одной записи A соответствует много записей B	FK на стороне «много» (N)	Category → Product
Many-to-Many (N:M)	`<—<`	Много A соответствует много B	Промежуточная таблица	Student ↔ Course

5. Превращение N:M в промежуточную таблицу: молекулярный разбор

Это одна из самых важных тем при проектировании БД. Рассмотрим её на атомарном уровне — от бизнес-требования до SQL DDL.

5.1. Почему N:M не существует в реляционной БД?

Реляционная модель (Эдгар Кодд, 1970) оперирует таблицами (отношениями) и связями через значения (Foreign Keys). В таблице нельзя хранить «список ссылок» в одной колонке — это нарушает 1НФ.

Ограничение реляционной модели:

В таблице Student одна колонка может содержать ровно одно значение (атомарность)
Если студент записан на 5 курсов — мы не можем поместить 5 course_id в одну колонку

Решение: Создать третью таблицу (ассоциативную, junction table), каждая строка которой представляет один факт связи.

5.2. Пошаговая трансформация

Шаг 1. Исходная бизнес-ситуация

«Студент записывается на курсы. Один студент может посещать много курсов. Один курс могут посещать много студентов.»

Тип связи: Студент N:M Курс.

Шаг 2. Концептуальная модель

┌──────────┐     N:M     ┌──────────┐
│ Студент   │◄──────────►│  Курс    │
└──────────┘             └──────────┘

Шаг 3. Выделяем ассоциативную сущность

Задаём себе вопросы:

Вопрос	Ответ	Вывод
Может ли студент существовать без курсов?	Да	Сильная сущность
Может ли курс существовать без студентов?	Да	Сильная сущность
Что представляет собой факт «студент записан на курс»?	Запись в журнале	Нужна новая сущность
Есть ли у этой записи собственные атрибуты?	Дата записи, оценка, статус	Да → полноценная сущность

Вводим ассоциативную сущность «Зачисление» (Enrollment).

Шаг 4. Связи после трансформации

Студент 1 ────< Зачисление >──── 1 Курс
               Зачисление
            ┌──────────────┐
            │ id (PK)      │ или (student_id, course_id) [PK]
            │ student_id   │ → Студент
            │ course_id    │ → Курс
            │ enrolled_at  │
            │ grade        │
            └──────────────┘

Исходная N:M разбилась на две связи 1:N:

Студент (1) → Зачисление (N): один студент — много записей о зачислении
Курс (1) → Зачисление (N): один курс — много записей о зачислении

Шаг 5. Выбор первичного ключа ассоциативной сущности

Здесь возникает принципиальный выбор: составной (Composite) ключ или суррогатный (Surrogate).

Вариант A: Составной первичный ключ (Composite PK)

CREATE TABLE enrollment (
    student_id  UUID NOT NULL,
    course_id   UUID NOT NULL,
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    grade       NUMERIC(3,1),
    status      VARCHAR(20) NOT NULL DEFAULT 'active',

    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES course(id)  ON DELETE CASCADE
);

Плюсы: Гарантирует уникальность пары (студент, курс) на уровне БД — один студент не может быть дважды записан на один курс без дополнительной логики.

Минусы:

Если нужен FK на enrollment из другой таблицы — придётся таскать два поля (student_id, course_id) как внешний ключ
Более громоздкие JOIN-запросы
Сложнее при репликации/шардировании

Вариант B: Суррогатный PK + UNIQUE-constraint

CREATE TABLE enrollment (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    student_id  UUID NOT NULL,
    course_id   UUID NOT NULL,
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    grade       NUMERIC(3,1),
    status      VARCHAR(20) NOT NULL DEFAULT 'active',

    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES course(id)  ON DELETE CASCADE,
    UNIQUE (student_id, course_id)
);

Плюсы:

Единый ключ id — удобно для FK из других таблиц
Распространённая практика в ORM (Hibernate, Entity Framework)
Проще при миграциях и шардировании

Минусы:

Занимает больше места (UUID + нужен ещё индекс для UNIQUE-constraint)
Возможна ситуация, когда UNIQUE-constraint забыли — и появятся дубликаты

Рекомендация аналитика:

Ситуация	Рекомендуемый ключ
Простая N:M без доп. атрибутов (чистый link)	Составной PK
N:M с доп. атрибутами (дата, статус, роль)	Суррогатный PK + UNIQUE
Проект использует ORM (JPA, Hibernate)	Суррогатный PK + UNIQUE
Высокие требования к производительности	Суррогатный PK (меньше размер индекса при FK)
Нужна строгая гарантия отсутствия дубликатов	Составной PK (надёжнее, чем UNIQUE-constraint)

Шаг 6. Полный SQL-пример с данными

-- Создание таблиц
CREATE TABLE student (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    full_name  VARCHAR(255) NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    enrolled_at DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE course (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       VARCHAR(255) NOT NULL,
    code        VARCHAR(20) UNIQUE NOT NULL,
    credits     SMALLINT NOT NULL CHECK (credits > 0)
);

-- Вариант A: составной ключ
CREATE TABLE enrollment_composite (
    student_id  UUID NOT NULL,
    course_id   UUID NOT NULL,
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    grade       NUMERIC(3,1),

    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id)  REFERENCES course(id)
);

-- Вставка данных
INSERT INTO student (id, full_name, email) VALUES
    ('a1b2c3d4-...', 'Иван Петров', 'ivan@example.com'),
    ('e5f6g7h8-...', 'Мария Сидорова', 'maria@example.com');

INSERT INTO course (id, title, code, credits) VALUES
    ('x1y2z3w4-...', 'Базы данных', 'DB-101', 4),
    ('u5v6w7x8-...', 'Алгоритмы', 'ALG-201', 3);

-- Запись студента на курс (вариант A)
INSERT INTO enrollment_composite (student_id, course_id) VALUES
    ('a1b2c3d4-...', 'x1y2z3w4-...'),
    ('a1b2c3d4-...', 'u5v6w7x8-...'),
    ('e5f6g7h8-...', 'x1y2z3w4-...');

-- Попытка повторной записи (вызовет ошибку — нарушение PK):
INSERT INTO enrollment_composite (student_id, course_id) VALUES
    ('a1b2c3d4-...', 'x1y2z3w4-...');
-- ОШИБКА: duplicate key value violates unique constraint...

Шаг 7. Визуализация структуры «до и после»

До (концептуальная модель):

Student ———< Course   (N:M)

После (физическая модель):

┌──────────────┐     ┌──────────────────┐     ┌──────────────┐
│   Student    │     │   Enrollment     │     │   Course     │
├──────────────┤     ├──────────────────┤     ├──────────────┤
│ id (PK)      │1   N│ student_id (FK)  │N   1│ id (PK)      │
│ full_name    │─────│ course_id (FK)   │─────│ title        │
│ email        │     │ enrolled_at      │     │ code (UQ)    │
└──────────────┘     │ grade            │     │ credits      │
                     │==================│     └──────────────┘
                     │ Composite PK:    │
                     │ (student_id,     │
                     │  course_id)      │
                     └──────────────────┘

5.3. Частные случаи N:M

Самосвязь N:M (рекурсивная)

Ситуация: Товары в интернет-магазине — «Похожие товары». Товар A похож на товар B, и наоборот.

CREATE TABLE product_similar (
    product_id   UUID NOT NULL REFERENCES product(id),
    similar_to_id UUID NOT NULL REFERENCES product(id),
    similarity_score NUMERIC(3,2) NOT NULL DEFAULT 0.5,

    PRIMARY KEY (product_id, similar_to_id),
    CHECK (product_id <> similar_to_id)
);

Тернарная N:M (три сущности)

Ситуация: «Студент записывается на курс в определённом семестре».

CREATE TABLE enrollment_ternary (
    student_id UUID NOT NULL REFERENCES student(id),
    course_id   UUID NOT NULL REFERENCES course(id),
    semester_id UUID NOT NULL REFERENCES semester(id),

    PRIMARY KEY (student_id, course_id, semester_id),
    -- один студент может быть записан на один курс
    -- в одном семестре только один раз
);

6. Суррогатный ключ (Surrogate Key) vs Натуральный ключ (Natural Key)

6.1. Определения

Натуральный ключ (Natural Key) — ключ, который уже существует в предметной области и имеет бизнес-смысл.

Примеры натуральных ключей:

Сущность	Натуральный ключ	Почему это natural?
Книга	ISBN (10 или 13 цифр)	Международный стандарт, уникален для каждой книги
Автомобиль	VIN (17 символов)	Уникальный идентификатор ТС
Сотрудник	Табельный номер	Присваивается в компании
Страна	ISO-код (RU, US, DE)	Международный стандарт
Email	Адрес email	Уникален в пределах системы
Налоговый номер	ИНН / SSN	Государственный реестр

Суррогатный ключ (Surrogate Key) — искусственный ключ, созданный исключительно для нужд базы данных, не имеющий бизнес-смысла.

Примеры суррогатных ключей:

Тип	Значение	Пример
Автоинкремент (SERIAL)	1, 2, 3, 4, ...	`id SERIAL PRIMARY KEY`
UUID	128-битный идентификатор	`550e8400-e29b-41d4-a716-446655440000`
Sequence	Настраиваемая последовательность	`NEXTVAL('user_seq')`
Snowflake (Twitter)	64-битный ID по времени + шард	17014118346046923173

6.2. Сравнение: таблица всех аргументов

Критерий	Суррогатный ключ (id SERIAL/UUID)	Натуральный ключ (ISBN, Email, VIN)
Бизнес-смысл	❌ Нет (просто число/строка)	✅ Есть, понятен человеку
Уникальность	✅ Гарантирована системой	⚠️ Требуется верификация (кто-то мог ошибиться)
Стабильность	✅ Никогда не меняется	⚠️ Может измениться (email поменяли, ISBN переиздали)
Производительность JOIN	✅ INT/UUID — быстро	⚠️ VARCHAR — медленнее, большой размер индекса
Размер индекса	4–16 байт	От 20 до 100+ байт
Человекочитаемость	❌ id=472 — ни о чём не говорит	✅ «ivan@example.com» — понятно
Миграция данных	✅ Легко (id уникален всегда)	⚠️ Сложно (нужно проверять уникальность в новой системе)
Шардирование	⚠️ UUID — проблема с упорядоченностью	⚠️ Зависит от формата
Безопасность	⚠️ SERIAL — легко угадать следующий id	⚠️ Email виден в URL
Работа с ORM	✅ Идеально (Hibernate требует id)	⚠️ Требует настройки @NaturalId

6.3. Когда использовать каждый тип

Используйте натуральный ключ, когда:

Ключ гарантированно стабилен и уникален во внешнем мире
- ✅ ИНН организации — не меняется, уникален
- ✅ VIN автомобиля
- ✅ ISBN книги
- ❌ Email — может поменяться (человек сменил провайдера)
Ключ имеет строгий мировой стандарт
- ✅ ISO-коды стран (RU, US)
- ✅ IBAN (международный номер банковского счёта)
Система является источником правды (Source of Truth) для этого ключа
- Например, государственный реестр транспортных средств
Ключ небольшой по размеру и стабильного формата
- 10–20 символов, не TEXT

Используйте суррогатный ключ, когда:

Нет подходящего натурального ключа (почти всегда в новых системах)
- ✅ Пользователь — не гарантирован уникальный email
- ✅ Заказ — номер заказа может сбрасываться в новом году
Ключ может измениться в будущем
- Email пользователя, название компании, номер телефона
Натуральный ключ слишком длинный или составной
- Композитный ключ из 4 полей — крайне неудобно для FK
Система использует ORM-фреймворк
- Hibernate, Entity Framework, Prisma — требуют простой id
Есть требования к security (не показывать бизнес-данные в URL)
- /order/7483 — злоумышленник может перебирать заказы
- /order/a1b2c3d4-... — угадать UUID практически невозможно

6.4. Стратегия для аналитика: гибридный подход

Лучшая практика — использовать оба ключа:

CREATE TABLE customer (
    -- Суррогатный (для системы)
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Натуральный (для бизнеса)
    email         VARCHAR(255) UNIQUE NOT NULL,
    tax_id        VARCHAR(12) UNIQUE,         -- ИНН
    external_code VARCHAR(50) UNIQUE,          -- Код из внешней ERP
    ...
);

Что получаем:

✅ Все FK внутри системы ссылаются на id (быстро, компактно, стабильно)
✅ Бизнес-логика использует email / tax_id / external_code для поиска
✅ Если email изменится — достаточно обновить одну строку, FK не сломаются

Исключение: ассоциативные таблицы N:M без доп. атрибутов

Иногда составной натуральный ключ — лучшее решение:

CREATE TABLE book_category (
    book_id     UUID NOT NULL REFERENCES book(id),
    category_id UUID NOT REFERENCES category(id),
    PRIMARY KEY (book_id, category_id)
);

Здесь составной PK выполняет двойную функцию: и уникальность гарантирует, и ключом является. Суррогатный id был бы избыточен.

6.5. Антипаттерны ключей

Антипаттерн	Пример	Проблема
Email как PK	`PRIMARY KEY (email)`	Email изменился — нужно каскадно обновлять все FK
Телефон как PK	`PRIMARY KEY (phone)`	Несколько пользователей с одним телефоном (семья)
Serial без UNIQUE constraint	`id SERIAL PK`, нет UNIQUE на email	Дубликаты email возможны
UUID v4 как PK без упорядочивания	`id UUID DEFAULT gen_random_uuid()`	Фрагментация индекса B-Tree
Текстовый PK (VARCHAR(500))	`PRIMARY KEY (description)`	Огромный индекс, медленные JOIN
Составной PK из 5+ полей	`PK (a, b, c, d, e)`	Ужасно для FK, медленные JOIN

6.6. Рекомендация: алгоритм выбора ключа для новой сущности

flowchart TD
    A[Новая сущность] --> B{Есть стабильный<br>натуральный ключ?}
    B -->|Да| C{Ключ короткий<br>и не изменится?}
    C -->|Да| D[Используйте NATURAL KEY<br>как PRIMARY KEY]
    C -->|Нет| E[Используйте SURROGATE KEY<br>как PK + UNIQUE на natural key]
    B -->|Нет| E
    E --> F{Какая СУБД?}
    F -->|PostgreSQL| G[UUID v7 или SERIAL]
    F -->|MySQL| H[BIGINT AUTO_INCREMENT]
    F -->|Распределённая система| I[UUID v4 или Snowflake]

Золотое правило: Natural key — для идентификации «снаружи», Surrogate key — для связи «внутри» (FK, JOIN, индексы).

7. Пример: ER-диаграмма для системы управления задачами (Task Manager)

7.1. Концептуальная модель (бизнес-уровень)

┌──────────┐     ┌──────────┐     ┌──────────┐
│  User   │────│  Project │────│  Task    │
└──────────┘     └──────────┘     └────┬─────┘
                                       │
                                  ┌────┴─────┐
                                  │ Comment  │
                                  └──────────┘

Бизнес-термины:

Пользователь — человек, работающий в системе
Проект — контейнер для задач
Задача — единица работы
Комментарий — обсуждение задачи
Метка (Label) — тег для группировки задач (не показана на концептуальной)

7.2. Логическая модель (уровень аналитика)

┌──────────────┐         ┌──────────────────────┐
│     User     │         │       Project        │
├──────────────┤         ├──────────────────────┤
│ id (PK)      │1      N*│ id (PK)              │
│ email (UQ)   │─────────│ name                  │
│ full_name    │  owner  │ description           │
│ role         │         │ created_at           │
│ created_at   │         └───────────┬───────────┘
└──────────────┘                     │1
       │1                            │
       │                             │
       │┌────────────────────────┐   │
       ││    ProjectMember       │   │
       │├────────────────────────┤   │
       └│ user_id (PK)(FK)      │   │
        │ project_id (PK)(FK)   │───┘
        │ role_in_project       │
        │ joined_at             │
        └───────────────────────┘

┌──────────────────────────────┐
│            Task              │
├──────────────────────────────┤
│ id (PK)                      │
│ project_id (FK)              │────── Project
│ title                        │
│ description                  │
│ status: VARCHAR(20)          │
│ priority: VARCHAR(10)        │
│ author_id (FK)               │────── User (автор)
│ assignee_id (FK) │NULL       │────── User (исполнитель)
│ created_at                   │
│ deadline │NULL               │
└──────────────────────────────┘
        │1
        │
   ┌────┴──────────┐       ┌──────────────────────┐
   │   Comment      │       │     TaskLabel        │
   ├───────────────┤       ├──────────────────────┤
   │ id (PK)       │       │ task_id (PK)(FK)     │
   │ task_id (FK)  │       │ label_id (PK)(FK)    │
   │ author_id (FK)│       └──────────┬───────────┘
   │ text          │                  │
   │ created_at    │             ┌────┴──────────┐
   └───────────────┘             │   Label       │
                                 ├──────────────┤
                                 │ id (PK)      │
                                 │ name (UQ)    │
                                 │ color        │
                                 └───────────────┘

7.3. Сводная таблица связей

Связь	Тип	Пояснение	PK на стороне «много»
User → Project (owner)	1:N	Пользователь владеет N проектами	FK `owner_id` в Project
User → ProjectMember	1:N	Пользователь может быть участником N проектов	FK `user_id` в ProjectMember
User → Task (author)	1:N	Пользователь — автор N задач	FK `author_id` в Task
User → Task (assignee)	1:N	Пользователь — исполнитель N задач	FK `assignee_id` в Task (NULL)
User → Comment	1:N	Пользователь написал N комментариев	FK `author_id` в Comment
Project → ProjectMember	1:N	Проект имеет N участников	FK `project_id` в ProjectMember
Project → Task	1:N	Проект содержит N задач	FK `project_id` в Task
Task → Comment	1:N	Задача имеет N комментариев	FK `task_id` в Comment
Task → Label	N:M	Задача имеет N меток, метка у N задач	Через TaskLabel (composite PK)

7.4. Ключевые решения по ключам в этой модели

Сущность	Тип PK	Обоснование
User	UUID (суррогат)	Email — natural key, но может измениться
Project	UUID (суррогат)	Нет natural key
Task	UUID (суррогат)	Нет natural key
Comment	UUID (суррогат)	Нет natural key
Label	UUID (суррогат)	name — natural key, но может измениться
ProjectMember	Composite PK (user_id, project_id)	Уникальность пары — бизнес-правило
TaskLabel	Composite PK (task_id, label_id)	Уникальность пары — бизнес-правило

8. Как строить ER-диаграмму: пошаговый метод

Шаг 1. Выделите сущности из требований

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

Пример из требований к Task Manager:

«Пользователь создаёт задачу в проекте. К задаче можно добавить комментарий и прикрепить метку. Пользователь может быть автором или исполнителем задачи. В проекте может быть несколько участников.»

→ Сущности: User, Task, Project, Comment, Label, ProjectMember

Фильтр: Не все существительные — сущности. «Задача» может быть сущностью, а «добавление» — действием (не сущность).

Шаг 2. Постройте концептуальную модель (на доске/в Miro)

Нарисуйте прямоугольники-сущности
Соедините линиями с подписями-глаголами
Не думайте о ключах и типах — только о бизнес-понятиях
Покажите заказчику и спросите: «Всё ли так? Ничего не пропущено?»

Шаг 3. Определите атрибуты для каждой сущности

Вопросы для каждого атрибута:

Имя — отражает суть? (не data1, info)
Тип — что будет хранить? (строка, число, дата, флаг)
Обязательность — может ли быть NULL?
Уникальность — не может ли повториться?
Изменяемость — может ли значение поменяться?

Пример:

Атрибут	Тип	Обязательный	Уникальный	Изменяемый
id	UUID	✅	✅ (PK)	❌
email	VARCHAR(255)	✅	✅	❓ (крайне редко)
full_name	VARCHAR(255)	✅	❌	✅
role	VARCHAR(20)	✅	❌	✅
created_at	TIMESTAMPTZ	✅	❌	❌

Шаг 4. Определите связи между сущностями

Алгоритм для пары сущностей A и B:

Возьмите одну запись A. Сколько записей B может быть с ней связано?
Возьмите одну запись B. Сколько записей A может быть с ней связано?
Результат — пара кардинальностей (1:1, 1:N, N:M).

Пример:

Вопрос	Ответ
Сколько проектов может создать один пользователь?	Много
Сколько пользователей может создать один проект?	Один
Итог: User → Project	1:N

Шаг 5. Определите Foreign Keys

Правило:

Тип связи	Куда ставить FK
1:N	На сторону «много» (N) — таблица B получает FK на PK таблицы A
1:1	На любую сторону, обычно ту, которая «подчинённая» (например, в UserProfile — FK на User)
N:M	Создать промежуточную таблицу с FK на обе исходные таблицы

Шаг 6. Выберите стратегию ключей

Используйте алгоритм выбора ключа из раздела 6.6.

Шаг 7. Нарисуйте диаграмму

Используйте Draw.io, dbdiagram.io, Lucidchart или PlantUML.

9. ER-диаграммы в PlantUML

PlantUML поддерживает ER-диаграммы через синтаксис entity и relationship.

@startuml
' Настройка: нотация Crow's Foot
!define table(x) class x << (T,#FFAAAA) >>

entity "User" as user {
  + id: UUID [PK]
  --
  email: VARCHAR(255) [UQ]
  name: VARCHAR(255)
  role: VARCHAR(50)
  created_at: TIMESTAMP
}

entity "Project" as project {
  + id: UUID [PK]
  --
  name: VARCHAR(255)
  description: TEXT
}

entity "Task" as task {
  + id: UUID [PK]
  --
  project_id: UUID [FK]
  title: VARCHAR(255)
  status: VARCHAR(20)
  priority: VARCHAR(10)
  author_id: UUID [FK]
  assignee_id: UUID [FK]
}

' Связи (Crow's Foot)
user ||--o{ task : author
user ||--o{ task : assignee
project ||--o{ task : contains
task ||--o{ comment : has
task }o--o{ label : tagged
@enduml

Результат: ER-диаграмма в нотации Crow's Foot с корректными кардинальностями.

10. Распространённые ошибки и антипаттерны

Ошибка 1: Атрибут как сущность

⚠️ Неправильно	✅ Правильно
Отдельная сущность «НазваниеПроекта»	Атрибут `name` в сущности `Project`

Правило: Если у «атрибута» нет своих атрибутов и он не может существовать независимо — он не сущность.

Ошибка 2: Много сущностей для одного явления

Пример: Товар, НаименованиеТовара, ОписаниеТовара, ЦенаТовара — всё это одна сущность Товар с атрибутами name, description, price.

Ошибка 3: Many-to-Many без промежуточной таблицы

При трансляции в SQL N:M обязательно превращается в промежуточную таблицу. Лучше сразу показать её на ER-диаграмме, а не прятать за «ромбом».

Ошибка 4: Неправильная кардинальность

Частая ошибка: Пользователь 1 — * Заказ вместо Пользователь 1 — 0..* Заказ. Уточняйте: «Может ли быть 0?» Если пользователь может зарегистрироваться, но не сделать ни одного заказа — кардинальность 0..*, а не 1..*.

Ошибка 5: Нет Primary Key

Каждая сущность должна иметь PK. Даже слабая сущность — хотя бы композитный PK (FK родителя + уникальный номер внутри родителя).

Ошибка 6: Email или телефон как PRIMARY KEY

-- НЕПРАВИЛЬНО
CREATE TABLE user (
    email VARCHAR(255) PRIMARY KEY,  -- email изменился → всё сломалось
    ...
);

-- ПРАВИЛЬНО
CREATE TABLE "user" (
    id    UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,  -- natural key как UNIQUE
    ...
);

Ошибка 7: Использование SERIAL (автоинкремент) без понимания последствий

-- ПРОБЛЕМА: при слиянии баз данных будут конфликты id
CREATE TABLE "order" (
    id SERIAL PRIMARY KEY,
    ...
);

-- РЕШЕНИЕ для распределённых систем:
CREATE TABLE "order" (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

Ошибка 8: Композитный PK из слишком многих полей

-- ПРОБЛЕМА: FK на эту таблицу будет содержать 4 поля
CREATE TABLE some_link (
    a_id INT,
    b_id INT,
    c_id INT,
    d_id INT,
    PRIMARY KEY (a_id, b_id, c_id, d_id)
);

-- ЛУЧШЕ: если это не business rule, а техническая уникальность — использовать
-- суррогатный PK + UNIQUE-constraint
CREATE TABLE some_link (
    id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    a_id INT,
    b_id INT,
    c_id INT,
    d_id INT,
    UNIQUE (a_id, b_id, c_id, d_id)
);

11. Чек-лист: Good Enough ERD

№	Критерий	Уровень модели	Самопроверка
1	Каждая сущность — существительное в единственном числе	Логический	Да / Нет
2	У каждой сущности есть Primary Key	Логический	Да / Нет
3	Foreign Keys явно отмечены	Логический	Да / Нет
4	Кардинальность указана на каждом конце связи	Логический	Да / Нет
5	Many-to-Many имеет промежуточную сущность	Логический	Да / Нет
6	Weak entities отмечены (зависимые от родителя)	Логический	Да / Нет
7	Атрибуты имеют обобщённые типы (VARCHAR, INT, DATE)	Логический	Да / Нет
8	Выбран тип ключа (суррогат vs натуральный) с обоснованием	Логический	Да / Нет
9	Атрибуты не дублируются между сущностями (нормализация)	Логический	Да / Нет
10	Концептуальная модель согласована с заказчиком	Концептуальный	Да / Нет
11	Типы данных привязаны к конкретной СУБД	Физический	Да / Нет
12	Определены индексы (PK, FK, уникальные)	Физический	Да / Нет
13	Диаграмма не перегружена (≤20 сущностей на одной)	Любой	Да / Нет

12. Вопросы для самопроверки

Базовый уровень

В чём разница между сильной и слабой сущностью? Приведите пример.
Какие три типа связей (по кардинальности) бывают? Как они обозначаются в Crow's Foot?
Как N:M-связь транслируется в реляционную таблицу? Пошагово.
Чем ERD отличается от UML Class Diagram? (минимум 3 отличия)
Что такое Foreign Key и куда его добавляют при связи 1:N?
Какие три нотации ER-диаграмм вы знаете? Какая наиболее популярна?

Продвинутый уровень

Студент спроектировал ERD, где атрибут «телефон» — многозначный (список телефонов в одной колонке). Почему это плохо? Как исправить?
В чём отличие концептуальной, логической и физической моделей данных? Приведите пример одной и той же сущности на всех трёх уровнях.
Когда вы выберете составной (composite) PK для ассоциативной сущности, а когда — суррогатный PK + UNIQUE-constraint? Аргументируйте.
Разберите ситуацию: email — натуральный ключ для пользователя. В чём риски? Какой альтернативный подход предложите?
Что такое «самосвязь N:M»? Приведите пример из реальной жизни.
Какие проблемы возникают при использовании SERIAL (автоинкремент) как PK в распределённой системе? Как их решить?

13. Практическое задание

Задание 1. Постройте ER-диаграмму (концептуальный + логический уровень)

Кейс: Система онлайн-библиотеки.

Требования:

Библиотека хранит книги (название, автор, ISBN, год издания, количество экземпляров)
Каждая книга относится к одной или нескольким категориям (жанрам)
Читатель регистрируется в системе (ФИО, email, телефон, дата рождения)
Читатель может взять книгу в аренду (дата выдачи, дата возврата, статус: на руках / возвращена / просрочена)
За одну выдачу можно взять несколько книг (до 5)
Библиотекарь выдаёт и принимает книги

Что нужно сделать:

Часть А (концептуальная модель):

Нарисуйте ER-диаграмму на уровне бизнес-понятий (только сущности с именами и связи)
Подпишите типы связей словами (не кардинальностями)

Часть Б (логическая модель):

Добавьте атрибуты к каждой сущности (минимум 3 атрибута на сущность, включая PK)
Укажите кардинальность (Crow's Foot)
Покажите все Foreign Keys

Рекомендуемый набор сущностей:

Book (Книга)
Category (Категория/жанр)
Author (Автор)
Reader (Читатель)
Librarian (Библиотекарь)
Rental (Аренда/выдача)
RentalItem (Позиция аренды — конкретная книга в конкретной выдаче)
BookCategory (связка книга-категория)
BookAuthor (связка книга-автор)

Задание 2. Проектирование ключей

Для каждой сущности из Задания 1 определите:

Сущность	Тип PK (суррогат / натуральный / составной)	Обоснование (2–3 предложения)
Book	?	?
Reader	?	?
Rental	?	?
RentalItem	?	?
BookCategory	?	?

Задание 3. SQL DDL (физический уровень)

Напишите SQL DDL (CREATE TABLE) для следующих сущностей:

Book (id, title, isbn, year, total_copies, available_copies)
Author (id, name, bio)
BookAuthor (промежуточная — продумайте: составной PK или суррогат?)
Rental (id, reader_id, librarian_id, rental_date, due_date, status)

Требования к DDL:

PRIMARY KEY, FOREIGN KEY, NOT NULL, DEFAULT
Выбор типа для id (SERIAL / UUID / BIGINT) с кратким обоснованием в комментарии
CHECK-constraint для статуса (ENUM или CHECK)
UNIQUE-constraint для ISBN

Задание 4. Анализ и исправление ошибок

Дана ER-диаграмма (текстовое описание). Найдите ошибки и исправьте.

Book (id, title, author, isbn, year)
    │1
    │
Category (id, name)

Вопросы:

Какая здесь связь между Book и Category и почему это неправильно?
Какая связь должна быть на самом деле?
Перерисуйте корректную модель (текстом или схемой).

Дополнительно: Какая ещё ошибка есть в этой схеме, кроме кардинальности? (Подсказка: посмотрите на атрибут author в Book)

14. Дополнительные материалы

Книга: Томас Коннолли, Каролин Бегг — «Базы данных: проектирование, реализация и сопровождение. Теория и практика», главы 2–5
Книга: Мартин Клеппман — «Высоконагруженные приложения», глава 5 (репликация, шардирование, ключи)
Книга: Стив Хоберман — «Data Modeling Made Simple», главы по суррогатным и натуральным ключам
Инструмент: dbdiagram.io — онлайн-редактор ER-диаграмм
Инструмент: PlantUML ER-Diagram — plantuml.com/er-diagram
Шпаргалка: «Crow's Foot Notation Cheat Sheet» — карта символов кардинальности
Статья: «Entity-Relationship Diagram Symbols and Notation» на lucidchart.com
Статья: «Surrogate vs Natural Keys: A Guide» — martinfowler.com (поиск по теме)
Практика: Спроектировать ERD для своего pet-проекта, начиная с концептуальной модели на бумаге

Урок 06.01: ER-диаграммы (Сущности и связи)

Цель урока

Ключевые понятия

1. Три уровня моделирования данных

1.1. Концептуальная модель (Conceptual / Business Data Model)

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

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

1.4. Сравнительная таблица трёх уровней

2. Суть ER-диаграммы и её место в моделировании

2.1. ERD vs UML Class Diagram

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

3.1. Нотация «Crow's Foot» (IE-нотация)

3.2. Нотация Чена (Chen)

3.3. Нотация IDEF1X

4. Элементы ER-диаграммы (глубокий разбор)

4.1. Сущность (Entity)

Сильная сущность (Strong / Independent Entity)

Слабая сущность (Weak / Dependent Entity)

Ассоциативная сущность (Associative Entity)

4.2. Атрибуты (Attributes)

4.3. Связи (Relationships)

5. Превращение N:M в промежуточную таблицу: молекулярный разбор

5.1. Почему N:M не существует в реляционной БД?

5.2. Пошаговая трансформация

Шаг 1. Исходная бизнес-ситуация

Шаг 2. Концептуальная модель

Шаг 3. Выделяем ассоциативную сущность

Шаг 4. Связи после трансформации

Шаг 5. Выбор первичного ключа ассоциативной сущности

Шаг 6. Полный SQL-пример с данными

Шаг 7. Визуализация структуры «до и после»

5.3. Частные случаи N:M

Самосвязь N:M (рекурсивная)

Тернарная N:M (три сущности)

6. Суррогатный ключ (Surrogate Key) vs Натуральный ключ (Natural Key)

6.1. Определения

6.2. Сравнение: таблица всех аргументов

6.3. Когда использовать каждый тип

6.4. Стратегия для аналитика: гибридный подход

6.5. Антипаттерны ключей

6.6. Рекомендация: алгоритм выбора ключа для новой сущности

7. Пример: ER-диаграмма для системы управления задачами (Task Manager)

7.1. Концептуальная модель (бизнес-уровень)

7.2. Логическая модель (уровень аналитика)

7.3. Сводная таблица связей

7.4. Ключевые решения по ключам в этой модели

8. Как строить ER-диаграмму: пошаговый метод

Шаг 1. Выделите сущности из требований

Шаг 2. Постройте концептуальную модель (на доске/в Miro)

Шаг 3. Определите атрибуты для каждой сущности

Шаг 4. Определите связи между сущностями

Шаг 5. Определите Foreign Keys

Шаг 6. Выберите стратегию ключей

Шаг 7. Нарисуйте диаграмму

9. ER-диаграммы в PlantUML

10. Распространённые ошибки и антипаттерны

Ошибка 1: Атрибут как сущность

Ошибка 2: Много сущностей для одного явления

Ошибка 3: Many-to-Many без промежуточной таблицы

Ошибка 4: Неправильная кардинальность

Ошибка 5: Нет Primary Key

Ошибка 6: Email или телефон как PRIMARY KEY

Ошибка 7: Использование SERIAL (автоинкремент) без понимания последствий

Ошибка 8: Композитный PK из слишком многих полей

11. Чек-лист: Good Enough ERD

12. Вопросы для самопроверки

Базовый уровень

Продвинутый уровень

13. Практическое задание

Задание 1. Постройте ER-диаграмму (концептуальный + логический уровень)

Задание 2. Проектирование ключей

Задание 3. SQL DDL (физический уровень)

Задание 4. Анализ и исправление ошибок

14. Дополнительные материалы

📚 Материалы модуля

🖼️ Схема и инфографика

🎬 Видео-лекция

📄 Дополнительные материалы (PDF)