ER-модели и нормализация: урок по Системный анализ

Модель данных отвечает на вопрос «что система хранит и как это связано». Её ошибки дороже всего: код переписать проще, чем мигрировать миллионы строк.

Три уровня модели

Концептуальная: сущности и связи языком бизнеса, без атрибутов или с главными. «Клиент делает Заказ, Заказ содержит Товары».
Логическая: все атрибуты, ключи, кардинальности, нормализация. Не привязана к СУБД.
Физическая: таблицы конкретной СУБД, типы (numeric(12,2), timestamptz), индексы, партиционирование.

ER-диаграмма

Сущность: то, о чём храним данные (Клиент, Заказ).
Атрибут: свойство (email, дата).
Связь и её кардинальность: 1:1, 1:N, M:N.
Обязательность: может ли заказ существовать без клиента.

Нотация «вороньи лапки» (crow's foot) самая распространённая: лапка = «много», черта = «один», кружок = «ноль».

Связи в таблицах

1:N: внешний ключ на стороне «многих». orders.customer_id → customers.id.
M:N: отдельная таблица связи. Студенты и курсы: enrollments(student_id, course_id, enrolled_at). В таблицу связи часто переезжают атрибуты самой связи (дата записи, оценка).
1:1: внешний ключ с UNIQUE. Используют, чтобы вынести редкие или чувствительные данные (паспорт клиента отдельно).

Ключи

Первичный ключ (PK): уникально и не NULL.
Естественный ключ: из предметной области (ИНН, номер телефона). Риск: меняется или оказывается неуникальным.
Суррогатный: bigint или UUID без смысла. Стандарт для PK; естественный ключ закрепляют UNIQUE.
Составной: из нескольких колонок, типичен для таблиц связи.
Внешний ключ (FK): ссылочная целостность, ON DELETE задаёт поведение (RESTRICT, CASCADE, SET NULL).

Нормальные формы

1НФ: значения атомарны, нет повторяющихся групп. Колонка phones = '8900..., 8911...' нарушает 1НФ.
2НФ: 1НФ + каждый неключевой атрибут зависит от всего составного ключа. В order_items(order_id, product_id, qty, product_title) название товара зависит только от product_id: нарушение.
3НФ: 2НФ + нет транзитивных зависимостей (неключевой атрибут зависит от другого неключевого). employees(id, dept_id, dept_name): dept_name зависит от dept_id.
НФБК (Бойса-Кодда): каждый детерминант является потенциальным ключом. Строже 3НФ, на практике редкость.

Короткая формула 3НФ: «каждый неключевой атрибут зависит от ключа, от всего ключа и ни от чего, кроме ключа».

Аномалии, от которых спасает нормализация

Вставки: нельзя добавить отдел без сотрудника.
Обновления: название отдела хранится в 300 строках, обновили 299.
Удаления: удалили последнего сотрудника, потеряли отдел.

Денормализация

Сознательно дублируют данные ради скорости чтения: счётчик комментариев в посте, сумма заказа в orders. Цена: нужно поддерживать согласованность (триггер, событие, пересчёт). Важный частный случай: исторические данные. Цена товара в строке заказа это не дублирование, а факт «по какой цене продали», он не должен меняться при смене прайса.

Словарь данных

Таблица для каждой сущности: атрибут, тип, обязательность, допустимые значения, источник, пример. Без него разработчик и тестировщик угадывают.

Частые ошибки

Деньги в float (надо numeric или целые копейки).
Статус свободным текстом вместо справочника или ограничения CHECK.
Время без часового пояса в распределённой системе.
M:N «через запятую» в одной колонке.
Удаление «физически», когда бизнесу нужна история (используют мягкое удаление или архив).

Что спросят на собесе

Объясни 1НФ, 2НФ и 3НФ на примере.
Как реализовать связь многие ко многим?
Суррогатный или естественный ключ: что выберешь и почему?
Когда денормализация оправдана?
Спроектируй модель данных для записи к врачу.

ER-модель и нормализация