Нормализация
Предпосылки: реляционная модель — аномалии, первичный ключ, внешний ключ.
← Реляционная модель | SELECT и фильтрация →
В реляционной модели мы увидели, что плоская таблица порождает аномалии, и разделение на несколько связанных таблиц их устраняет. Но как далеко разделять? Какой принцип определяет, какие столбцы должны быть вместе, а какие — в отдельной таблице? Ответ даёт теория нормальных форм. Её фундамент — понятие функциональной зависимости.
Функциональная зависимость
Интернет-магазин ведёт таблицу заказов, где каждая строка — позиция в заказе:
order_id | product_id | customer_id | customer_name | customer_city | product_name | price | quantity
---------+------------+-------------+---------------+---------------+--------------+-------+---------
1 | 101 | 1 | Alice | New York | Книга | 500 | 1
1 | 102 | 1 | Alice | New York | Ручка | 100 | 2
2 | 103 | 2 | Bob | London | Тетрадь | 200 | 1
3 | 101 | 1 | Alice | New York | Книга | 500 | 3
3 | 104 | 1 | Alice | New York | Ластик | 50 | 1
В этой таблице есть закономерности. Если две строки совпадают по product_id, они всегда совпадают по product_name и price: зная идентификатор товара, можно однозначно определить его название и цену. Это и есть функциональная зависимость (functional dependency): product_id → product_name, price. Читается: «product_id функционально определяет product_name и price».
Формально: атрибут B функционально зависит от атрибута A (записывается A → B), если в любом допустимом состоянии таблицы не существует двух строк с одинаковым значением A и разными значениями B. Левая часть зависимости — детерминант (determinant, от лат. determinare — «определять»), правая — зависимый атрибут.
Какие зависимости есть в таблице выше? product_id → product_name, price — товар определяет название и цену. order_id → customer_id, customer_name, customer_city — заказ определяет клиента. customer_id → customer_name, customer_city — идентификатор клиента определяет имя и город. (order_id, product_id) → quantity — позиция в заказе определяет количество. Последняя зависимость означает, что (order_id, product_id) — составной ключ таблицы: комбинация этих двух столбцов уникальна для каждой строки.
Заметим цепочку: order_id → customer_id → customer_name. Заказ определяет клиента, клиент определяет имя — значит, заказ определяет имя. Это транзитивная зависимость (transitive dependency): A → B и B → C, следовательно A → C. Транзитивные зависимости — источник аномалий: имя клиента хранится в каждом заказе этого клиента, и при изменении имени нужно обновить все строки.
Каждая нормальная форма запрещает определённый вид функциональной зависимости — и тем самым устраняет определённый вид аномалии.
Первая нормальная форма (1НФ)
Допустим, для компактности менеджер хранит товары заказа в одной ячейке:
order_id | customer_name | items
---------+---------------+-------------------------------
1 | Alice | Книга:500, Ручка:100
2 | Bob | Тетрадь:200
3 | Alice | Книга:500, Ластик:50
Три строки вместо пяти. Но попробуем ответить на вопрос «сколько раз заказывали Книгу?». Нужно разобрать каждую строку items, разделить по запятой, проверить каждый элемент. SQL не умеет работать с подстроками внутри ячейки как с отдельными значениями — нет ни фильтрации по отдельному товару, ни подсчёта, ни обновления цены одного товара без перезаписи всей строки.
Первая нормальная форма (1NF, first normal form) требует, чтобы каждая ячейка содержала одно атомарное значение. «Атомарное» означает неделимое с точки зрения СУБД: целое число, строка текста, дата — но не список значений внутри одного поля.
Решение: каждый товар — отдельная строка. Таблица из начала раздела о функциональных зависимостях уже удовлетворяет 1НФ — каждая ячейка содержит одно значение, каждый товар в заказе — отдельная строка с собственными столбцами product_id, product_name, price, quantity. Теперь «сколько раз заказывали Книгу» — это WHERE product_name = 'Книга'. Однако таблица по-прежнему полна дублирования: «Alice, New York» повторяется четыре раза, «Книга, 500» — дважды.
Вторая нормальная форма (2НФ)
Составной ключ таблицы — (order_id, product_id). Посмотрим на зависимости неключевых атрибутов. product_id → product_name, price — название и цена зависят только от product_id, то есть от части ключа. order_id → customer_id, customer_name, customer_city — данные клиента зависят только от order_id, тоже от части ключа. И только (order_id, product_id) → quantity — количество зависит от всего ключа.
Первые две зависимости — частичные (partial dependency): неключевой атрибут зависит не от всего составного ключа, а от его части. Название товара «Книга» записано в каждой строке, где product_id = 101. Если цена книги изменится с 500 на 600, нужно обновить все такие строки — аномалия обновления.
Вторая нормальная форма (2NF) требует: 1НФ + каждый неключевой атрибут зависит от всего ключа, а не от его части. Решение: вынести частичные зависимости в отдельные таблицы.
orders products
order_id | customer_id | ... product_id | product_name | price
---------+-------------+---- -----------+--------------+------
1 | 1 | 101 | Книга | 500
2 | 2 | 102 | Ручка | 100
3 | 1 | 103 | Тетрадь | 200
104 | Ластик | 50
order_items
order_id | product_id | quantity
---------+------------+---------
1 | 101 | 1
1 | 102 | 2
2 | 103 | 1
3 | 101 | 3
3 | 104 | 1
Цена книги теперь хранится в одном месте — в таблице products. Обновление — одна строка.
Суррогатные ключи и 2НФ на практике. ORM-ы по умолчанию создают таблицы с суррогатным первичным ключом — одностолбцовым автоинкрементом (id serial PRIMARY KEY). Если первичный ключ состоит из одного столбца, частичных зависимостей быть не может — любой неключевой атрибут зависит от «всего» ключа, потому что ключ неделим. Нарушения 2НФ возникают только при составных ключах, а они в современных приложениях встречаются редко.
Третья нормальная форма (3НФ)
После приведения к 2НФ таблица orders выглядит так:
orders
order_id | customer_id | customer_name | customer_city
---------+-------------+---------------+--------------
1 | 1 | Alice | New York
2 | 2 | Bob | London
3 | 1 | Alice | New York
Первичный ключ — order_id. Все неключевые атрибуты зависят от всего ключа (ключ одностолбцовый — частичных зависимостей нет, 2НФ выполнена). Но customer_name и customer_city зависят от order_id не напрямую, а через customer_id:
order_id --> customer_id --> customer_name
--> customer_city
Это транзитивная зависимость: order_id → customer_id → customer_name. Имя клиента определяется его идентификатором, а не заказом. Следствие: «Alice» записана в строках 1 и 3. Alice меняет фамилию — нужно найти и обновить все её заказы. Пропустили один — в базе два разных имени для одного клиента.
Третья нормальная форма (3NF) требует: 2НФ + неключевые атрибуты не зависят друг от друга (нет транзитивных зависимостей через неключевые атрибуты). Решение: вынести клиентов в отдельную таблицу.
orders customers
order_id | customer_id customer_id | customer_name | customer_city
---------+------------ ------------+---------------+--------------
1 | 1 1 | Alice | New York
2 | 2 2 | Bob | London
3 | 1
Имя клиента хранится один раз. Обновление — одна строка в customers.
Итого после нормализации до 3НФ — четыре таблицы: customers, orders, products, order_items. Каждый факт записан ровно один раз. Каждая таблица описывает одну сущность.
Нормальная форма Бойса-Кодда (БКНФ)
3НФ запрещает транзитивные зависимости через неключевые атрибуты. Но есть редкий случай, когда детерминант — не кандидатный ключ, а зависимость формально не транзитивна.
До сих пор в таблицах был один первичный ключ. Но в таблице может быть несколько наборов столбцов, каждый из которых однозначно идентифицирует строку. Каждый такой набор — кандидатный ключ (candidate key, англ. «ключ-кандидат»); один из них выбирается как первичный. Когда кандидатных ключей несколько и они перекрываются, 3НФ оказывается недостаточной.
Пример: университет ведёт расписание. Каждый преподаватель ведёт ровно один предмет, но предмет могут вести несколько преподавателей. Студент по каждому предмету учится у одного преподавателя.
student | subject | teacher
-----------+------------+-----------
Alice | Математика | Иванов
Bob | Математика | Петрова
Alice | Физика | Сидоров
Bob | Физика | Сидоров
Кандидатные ключи: {student, subject} (однозначно определяет преподавателя) и {student, teacher} (преподаватель ведёт один предмет — значит, пара «студент + преподаватель» определяет предмет). Оба ключа составные и перекрываются по student.
Зависимость teacher → subject (Иванов всегда ведёт Математику) означает: детерминант teacher не является кандидатным ключом — одного teacher недостаточно, чтобы определить всю строку (неизвестно, какой студент). Таблица удовлетворяет 3НФ (нет транзитивных зависимостей через неключевые атрибуты — и subject, и teacher входят в кандидатные ключи), но содержит аномалию: если Иванов начнёт вести Статистику вместо Математики, нужно обновить все строки с Ивановым.
Нормальная форма Бойса-Кодда (BCNF, Boyce-Codd normal form) требует: каждый детерминант в таблице — кандидатный ключ. Это строже 3НФ: любая нетривиальная зависимость A → B допустима только если A — кандидатный ключ или его надмножество.
На практике 3НФ и БКНФ почти всегда совпадают. Расхождение возникает только при нескольких перекрывающихся составных кандидатных ключах — конструкция, которая в реальных схемах встречается крайне редко.
Четвёртая нормальная форма (4НФ)
Формы от 1НФ до БКНФ устраняют все проблемы, связанные с функциональными зависимостями. Но бывает зависимость другого рода — многозначная (multivalued dependency, MVD).
Компания ведёт таблицу навыков и языков сотрудников. Навыки и языки — независимые факты: знание Java не связано со знанием французского.
employee | skill | language
---------+--------+---------
Alice | Java | English
Alice | Java | French
Alice | Python | English
Alice | Python | French
Bob | Go | English
Alice знает два языка программирования и говорит на двух языках — 4 строки (декартово произведение 2 × 2). Добавить третий язык — нужно вставить ещё 2 строки (по одной на каждый навык). Забыть одну комбинацию — и данные выглядят так, будто Alice знает Python только на English. Записывается employee →→ skill и employee →→ language: для каждого сотрудника множество навыков не зависит от множества языков.
Четвёртая нормальная форма (4NF) требует: БКНФ + нет нетривиальных многозначных зависимостей (кроме тех, что следуют из суперключей). Решение: разделить на две таблицы.
employee_skills employee_languages
employee | skill employee | language
---------+-------- ---------+---------
Alice | Java Alice | English
Alice | Python Alice | French
Bob | Go Bob | English
Теперь добавить язык — одна строка в employee_languages, без влияния на навыки.
На практике нарушения 4НФ встречаются редко: ситуация возникает только когда два независимых отношения «многие-ко-многим» складывают в одну таблицу. Любой разработчик интуитивно создаст отдельные junction-таблицы — employee_skills и employee_languages — не задумываясь о теории.
Пятая нормальная форма (5НФ) и выше
Ещё более редкий случай: таблица не разложима на две без потери информации, но разложима на три. Это зависимость соединения (join dependency).
Пример: таблица supplier_part_project фиксирует, что поставщик поставляет деталь для проекта. Amazon поставляет Процессоры. Amazon участвует в проекте Atlas. Процессоры используются в Atlas. Но из этих трёх бинарных фактов не следует, что именно Amazon поставляет Процессоры для Atlas — это самостоятельный тернарный факт. Попытка разложить таблицу на три пары (поставщик-деталь, поставщик-проект, деталь-проект) и восстановить JOIN-ом создаст ложные комбинации.
Пятая нормальная форма (5NF, project-join normal form) требует: 4НФ + нет нетривиальных зависимостей соединения, не выводимых из кандидатных ключей. На практике ситуации, нарушающие 5НФ, но не 4НФ, исключительно редки — их приходится конструировать специально.
За 5НФ существует шестая нормальная форма (6NF): каждая таблица содержит ключ и не более одного дополнительного атрибута. 6НФ используется в теории темпоральных баз данных, где каждый атрибут может иметь свою историю изменений. На практике она означала бы десятки таблиц вместо одной — никто не проектирует схемы в 6НФ.
Иерархия форм образует цепочку строгих включений: 1НФ ⊃ 2НФ ⊃ 3НФ ⊃ БКНФ ⊃ 4НФ ⊃ 5НФ ⊃ 6НФ. Каждая следующая запрещает больше видов зависимостей. На практике целью является 3НФ/БКНФ — они устраняют подавляющее большинство аномалий. Старшие формы полезны как диагностический инструмент: если в схеме возникают необъяснимые аномалии при корректной БКНФ, стоит проверить многозначные зависимости (4НФ).
Цена нормализации
Нормализация устраняет аномалии, но платит за это структурой запросов. В исходной плоской таблице вся информация о заказе — в одной строке:
SELECT * FROM flat_orders WHERE order_id = 1;Один запрос, одна таблица. После нормализации до 3НФ та же информация разнесена по четырём таблицам:
SELECT o.order_id, c.customer_name, c.customer_city,
p.product_name, p.price, oi.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id = 1;Три JOIN вместо нуля. Каждый JOIN — поиск соответствия между таблицами. На малых масштабах разница незаметна: при наличии индексов на внешних ключах каждый JOIN — быстрый поиск по индексу, несколько микросекунд. Проблема проявляется в двух ситуациях.
Нагрузка на чтение в разы выше записи. Типичное веб-приложение — 90–95% операций это чтение. Нормализация оптимизирует запись (каждый факт обновляется в одном месте), но усложняет чтение (данные собираются из нескольких таблиц). Чем выше доля чтения, тем чаще платишь за JOIN и тем реже получаешь выгоду от единственной точки обновления.
Данные распределены по нескольким серверам. JOIN внутри одного сервера — дешёвая операция. JOIN между серверами (distributed join) — сетевой запрос с задержкой на порядки выше. Шардирование делает многотабличные запросы значительно дороже.
На практике
Большинство приложений работают в 3НФ не потому, что разработчики сознательно нормализуют схему, а потому, что здравый смысл ведёт к тому же результату. «Клиенты — отдельная таблица, товары — отдельная» — это и есть 3НФ. ORM-ы создают суррогатные ключи, что автоматически исключает нарушения 2НФ.
Осознанные нарушения нормальных форм встречаются в двух формах.
JSON-столбцы нарушают 1НФ. Заказ хранит поле metadata jsonb с произвольной структурой: {"gift_wrap": true, "note": "Happy birthday"}. Эти данные не фильтруются и не агрегируются как обычные столбцы — ради гибкости жертвуют атомарностью. PostgreSQL частично компенсирует это JSONB-операторами и GIN-индексами, но это обходной путь, а не полноценная работа с атрибутами.
Кешированные поля нарушают 3НФ. Таблица orders хранит total_price, хотя его можно вычислить через SUM(quantity * price) из order_items и products. Зависимость order_id → total_price транзитивна — total_price определяется содержимым заказа. Выгода — избежать JOIN и агрегацию при каждом отображении заказа. Цена — ответственность за синхронизацию: при изменении позиций заказа total_price нужно пересчитать. Аналогичный паттерн — counter_cache в Rails: comments_count в таблице posts вместо COUNT(*) при каждом запросе.
Денормализация как архитектурное решение
Когда цена JOIN-ов становится ощутимой — не на уровне одного запроса, а на уровне системы — решение лежит не в «добавить столбец», а в перестройке архитектуры. Конкретная стратегия зависит от профиля нагрузки, паттерна доступа и допустимой задержки в согласованности данных:
- Профили чтения и записи определяют, какая сторона tradeoff важнее: если запись редка, оптимизация чтения через денормализацию оправдана
- Выбор хранилища — разные хранилища для разных паттернов доступа: OLTP-база для транзакций, аналитическое хранилище с денормализованными таблицами для отчётов
- CQRS — нормализованная модель для записи, денормализованная проекция для чтения, ценой eventual consistency
Все эти стратегии принимают нормализованную схему как отправную точку. Нормализация — не конечное состояние, а фундамент: сначала данные организуются без аномалий, а затем, если измеримая нагрузка требует, строятся денормализованные представления поверх нормализованного источника.
Sources
- E.F. Codd, 1970, A Relational Model of Data for Large Shared Data Banks. Communications of the ACM. https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
- E.F. Codd, 1974, Recent Investigations into Relational Data Base Systems. IBM Research Report RJ1385.
- C.J. Date, 2003, An Introduction to Database Systems (8th ed.). Addison-Wesley — главы о нормальных формах и функциональных зависимостях.
- W. Kent, 1983, A Simple Guide to Five Normal Forms in Relational Database Theory. Communications of the ACM. https://www.bkent.net/Doc/simple5.htm
- PostgreSQL Documentation (v16). https://www.postgresql.org/docs/16/