Перейти к основному содержимому

Нормализация данных

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ
Разработчику Аналитику Архитектору

Нормализация - убрать дубли и аномалии в таблицах​

Повтор одних и тех же фактов в каждой строке заказа усложняет правки. Нормализация хранит каждый факт в одном месте; связи — через ключи.

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

Перед главой: Реляционная модель, ER. Расширенный разбор и чек-лист проектировщика — Проектирование баз данных. После: Ограничения, JOIN.

Функциональные зависимости​

Определение: атрибут B функционально зависит от A (A → B), если каждому значению A соответствует ровно одно значение B.

ТипПример
Тривиальная{сотрудник, отдел} → сотрудник
ПолнаяB зависит от всего составного ключа, не от его части
ЧастичнаяB зависит только от части составного ключа
ТранзитивнаяA → B, B → C, при этом B не является ключом

Отношение с простым (не составным) первичным ключом автоматически находится во 2НФ: частичных зависимостей от части ключа быть не может.

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

Нормализация идёт ступенями: каждая следующая форма опирается на предыдущую и убирает свой класс избыточности. На практике проектирования SQL-схем для OLTP обычно достаточно 3НФ или НФБК; 4НФ полезна знать, когда в одной таблице смешаны независимые списки значений.

ФормаЧто устраняетТипичный приём
1НФПовторяющиеся группы, неатомарные ячейкиОдно значение в ячейке; "широкие" столбцы product1, product2 → отдельные строки
2НФЧастичные зависимости от части составного ключаРазделить заголовок сущности и строки состава
3НФТранзитивные зависимости между неключевыми полямиВынести справочник, на который ссылается FK
НФБКЗависимости, где левая часть — не суперключДополнительная декомпозиция при нескольких кандидатных ключах
4НФНезависимые многозначные зависимостиДве таблицы "один ко многим" вместо декартова произведения

Ниже — один сквозной домен (заказы интернет-магазина), на котором видно переход от "сырой" таблицы к 3НФ. Примеры НФБК и 4НФ — отдельные типовые случаи.

1НФ — первая нормальная форма​

Критерий: каждый атрибут атомарен — в ячейке одно значение домена; нет повторяющихся групп столбцов (телефон1, телефон2, product1, product2) и списков "через запятую" вместо отдельных строк.

До (нарушение 1НФ) — в одной строке заказа несколько товаров в разных столбцах:

order_idcustomer_namecustomer_emailproduct1product2product3
5001Ивановivan@mail.ruКроссовкиКепкаЧасы

После (1НФ) — каждый товар заказа в своей строке; данные клиента повторяются, пока не пройдём 2НФ и 3НФ:

order_idcustomer_namecustomer_emailproductorder_date
5001Ивановivan@mail.ruКроссовки2025-03-01
5001Ивановivan@mail.ruКепка2025-03-01
5001Ивановivan@mail.ruЧасы2025-03-01

2НФ — вторая нормальная форма​

Критерий: выполнена 1НФ, и каждый неключевой атрибут полностью зависит от всего первичного ключа. Актуально при составном ключе — поля, зависящие только от order_id, нельзя держать в таблице с ключом (order_id, product).

До (нарушение 2НФ) — ключ (order_id, product), а customer_name, customer_email, order_date определяются только order_id:

order_idproductcustomer_namecustomer_emailorder_date
5001КроссовкиИвановivan@mail.ru2025-03-01
5001КепкаИвановivan@mail.ru2025-03-01

После (2НФ) — заголовок заказа и позиции разделены:

orders — всё, что зависит только от order_id:

order_idcustomer_namecustomer_emailorder_date
5001Ивановivan@mail.ru2025-03-01

order_items — связь заказа с товаром:

order_idproduct
5001Кроссовки
5001Кепка

Отношение с простым (не составным) первичным ключом автоматически во 2НФ: частичных зависимостей от "части ключа" быть не может.

3НФ — третья нормальная форма​

Критерий: выполнена 2НФ, и неключевые атрибуты нетранзитивно зависят от ключа — то есть не зависят от других неключевых полей.

До (нарушение 3НФ) — в orders поле region определяется через customer_email (или имя клиента), а не напрямую через order_id:

order_idcustomer_emailorder_dateregion
5001ivan@mail.ru2025-03-01Центр
5002ivan@mail.ru2025-03-05Центр

Цепочка: order_id → customer_email → region. Смена региона клиента потребует правки всех его заказов.

После (3НФ):

customers — факты о клиенте хранятся один раз:

customer_emailcustomer_nameregion
ivan@mail.ruИвановЦентр

orders — ссылка на клиента и дата заказа:

order_idcustomer_emailorder_date
5001ivan@mail.ru2025-03-01
5002ivan@mail.ru2025-03-05

Другой частый случай: employee_id → department_id → department_head в одной таблице сотрудников → отдельная таблица departments.

НФБК — нормальная форма Бойса–Кодда​

Критерий: усиление 3НФ — каждая нетривиальная функциональная зависимость X → Y имеет X в роли суперключа (кандидата в ключ). 3НФ допускает редкие случаи, когда зависимость идёт от неключевого атрибута, который входит в другой кандидатный ключ.

До (нарушение НФБК) — расписание: преподаватель ведёт курс только в одной аудитории; при этом instructor → room_number, хотя instructor сам по себе не является ключом:

course_idroom_numberinstructor
CS101A-201Петров
CS102B-105Сидоров

Кандидатные ключи могут быть (course_id, room_number) и (course_id, instructor), но зависимость instructor → room_number создаёт избыточность и аномалии при смене аудитории преподавателя.

После (НФБК) — разнести "кто ведёт" и "где и когда идёт занятие":

course_idinstructor
CS101Петров
course_idroom_numbertime_slot
CS101A-201Пн 10:00

Для большинства бизнес-приложений целевая схема — 3НФ или НФБК; дальше идут формы для узких теоретических случаев.

4НФ — четвёртая нормальная форма​

Критерий: выполнена НФБК, и в таблице нет независимых многозначных зависимостей для одной сущности. Если у сотрудника несколько навыков и несколько проектов, и эти списки не связаны друг с другом, хранить их в одной таблице (employee_id, skill, project) приводит к декартову произведению строк.

До (нарушение 4НФ) — у E001 два навыка и два проекта → четыре строки вместо двух+двух:

employee_idskillproject
E001JavaProjectX
E001JavaProjectY
E001PythonProjectX
E001PythonProjectY

Строки Java + ProjectY и Python + ProjectX могут не соответствовать реальности — это артефакт смешения независимых списков.

После (4НФ):

employee_skills:

employee_idskill
E001Java
E001Python

employee_projects:

employee_idproject
E001ProjectX
E001ProjectY

5НФ (форма соединения) на практике встречается ещё реже — при декомпозиции по зависимостям соединения; для типового OLTP достаточно знать, что она существует.

Аномалии обновления​

АномалияПроявление
ВставкиНельзя добавить отдел без сотрудника, если отдел хранится только в строке сотрудника
УдаленияУдаление последнего сотрудника стирает сведения об отделе
МодификацииСмена названия отдела требует обновления многих строк

Нормализация разбивает данные так, чтобы каждый факт хранился в одном месте.

Практика — декомпозиция "Заказы"​

Исходная денормализованная таблица (ключ — (order_id, product_code)):

Заказы (номер_заказа, дата, id_клиента, фио_клиента, телефон, город,
        код_товара, наименование_товара, категория, цена, количество)

Зависимости:

  • order_id → date, client_id
  • client_id → full_name, phone, city
  • product_code → name, category, price
  • (order_id, product_code) → quantity

Итоговая схема в 3НФ (PostgreSQL: SERIAL, CURRENT_DATE; в MySQL — AUTO_INCREMENT):

Код ITЗагрузка примера кода…

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

Определение: сознательное нарушение нормальных форм ради скорости отчётов, меньшего числа JOIN, витрин или кэша в документе. В семи стратегиях масштабирования БД и в девяти рычагах производительности это приём на уровне схемы — рядом с индексами и материализованными представлениями.

Нужен явный источник правды и синхронизация копий — триггеры, job пересборки, ETL, события (CQRS). Дублирование полей в одной "плоской" таблице (клиент + товар + заказ в одной строке) убирает соединения при чтении, но усложняет обновление — правки должны затрагивать все копии согласованно.

Типичные сценарии:

  • аналитические запросы с десятками соединений;
  • высоконагруженное чтение в OLTP;
  • исторические срезы в хранилище данных.

Чек-лист моделирования данных​

Перед CREATE TABLE в проде пройдите схему по пунктам ниже. Полный цикл "концепция → DDL" — в Проектировании баз данных; ER-термины — Entity Relationship.

Имена и соглашения​

  • Имена таблиц и столбцов короткие и единообразные (лимит длины в целевой СУБД и в ORM).
  • Таблицы — множественное число или единый стиль команды (orders, не смешивать с Order).
  • Внешние ключи читаются без схемы: customer_id → customers.id.

Сущности​

  • Каждая сущность — существительное предметной области, не отчёт и не экран UI.
  • Слабые сущности (строка заказа, оценка на курсе) имеют ключ через родителя или составной PK.
  • Связь M:N разбита на таблицу-связку с собственным PK.

Атрибуты​

  • Ячейки атомарны (1НФ): нет списков через запятую в одном столбце.
  • NULL только там, где "неизвестно" или "не применимо", а не "забыли заполнить".
  • Бизнес-коды, которые должны быть уникальны, помечены UNIQUE, даже при суррогатном PK.

Связи​

  • У каждой связи ясны обе стороны и кардинальность (1:1, 1:N, M:N).
  • Для FK заданы политики ON DELETE / ON UPDATE (не дефолт "как получится").
  • Нет лишней дублирующей связи между теми же сущностями без разной семантики.

ER-модель и процессы​

  • Модель проверена на типовые операции — вставка, обновление, удаление без обхода ограничений.
  • Решено, где хранить историю (отдельные таблицы версий, valid_from / valid_to, архив).
  • Избыточность данных осознанна: либо 3НФ, либо денормализация с планом синхронизации — ниже.
  • Выполнено правило минимальных данных: всё нужное есть, лишнего нет.
После чек-листа

Прогоните нормальные формы на ключевых таблицах (1НФ–3НФ) и сверьте индексы под ожидаемые WHERE / JOIN — см. внутреннее устройство БД и рычаги производительности.

Контрольные вопросы​

  1. Почему таблица с простым первичным ключом автоматически во 2НФ?
  2. Приведите пример транзитивной зависимости вне иерархии "сущность — подчинённая сущность".
  3. Чем нарушение 4НФ отличается от нарушения 3НФ? (подсказка: независимые списки значений.)
  4. Когда денормализация допустима без роста аномалий модификации?
  5. Почему 3НФ не устраняет все избыточности? (подсказка: НФ Бойса–Кодда.)

См. также​

Содержание