Нормализация: 1NF-3NF-BCNF, денормализация — когда оправдана?

#Проблема: ненормализованная таблица

Аномалии:

• Обновления: Ann сменила телефон — нужно менять во всех её заказах; пропустили один — данные противоречат.
• Вставки: нельзя добавить клиента без заказа.
• Удаления: удалили единственный заказ Bob — потеряли и информацию о Bob.
• products — список в ячейке: нельзя посчитать продажи товара SQL-запросом без парсинга строк.

#Нормальные формы по шагам

#1NF — атомарность

• Каждая ячейка — одно значение (не список, не «Phone x1, Case x2»).
• Нет повторяющихся столбцов (phone1, phone2, phone3).
• Строки различимы по ключу.

#2NF — полная зависимость от ключа

Актуальна для составных ключей. В order_lines(order_id, product_id, qty, order_date, product_name):

• qty зависит от (order_id, product_id) — полностью. ✓
• order_date зависит только от order_id — часть ключа. ✗ → в таблицу orders.
• product_name зависит только от product_id. ✗ → в таблицу products.

#3NF — нет транзитивных зависимостей

orders(id, customer_id, customer_phone): id → customer_id → customer_phone. Телефон зависит от клиента, а не от заказа → в таблицу customers.

Мнемоника: каждый неключевой атрибут зависит от ключа, от всего ключа и ни от чего, кроме ключа.

#BCNF

3NF допускает редкий случай: зависимость, где детерминанта — не ключ, но зависимый атрибут — часть ключа. Пример: (студент, предмет, преподаватель), каждый преподаватель ведёт один предмет: преподаватель → предмет, но преподаватель — не ключ. BCNF требует разложить на (студент, преподаватель) и (преподаватель, предмет). На практике 3NF и BCNF обычно совпадают.

Существуют 4NF (многозначные зависимости) и 5NF, но в прикладной разработке до них доходят редко.

#Итоговая схема

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

Нормализованная схема оптимальна для записи и целостности, но чтение может требовать много JOIN-ов и агрегатов. Денормализация — осознанный компромисс:

#Типичные ошибки

• Преждевременная денормализация «для скорости» без измерений — получаем аномалии без выигрыша.
• Чрезмерная нормализация справочников-однодневок, из-за которой простой экран требует 12 JOIN-ов.
• Хранение списков через запятую (tags = "a,b,c") — вместо связующей таблицы или массива/JSON с индексом.
• Путать исторический снапшот (цена в заказе) с дублированием.
• EAV (Entity-Attribute-Value) «для гибкости» — теряется типизация и производительность; современная альтернатива — JSON/JSONB с индексами.

#Вопросы на засыпку

Нарушает ли массив или JSONB в PostgreSQL первую нормальную форму?

Формально — да (значение не атомарно). Практически — это осознанный выбор, если элементы не нужно связывать с другими таблицами, а СУБД умеет их индексировать (GIN) и запрашивать. Для связей «многие-ко-многим» с внешними ключами лучше связующая таблица.

Что такое функциональная зависимость?

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

Почему в DWH используют денормализованные схемы?

Аналитические запросы читают огромные объёмы и агрегируют; JOIN-ы нормализованной схемы дороги. Схема «звезда» (таблица фактов + денормализованные измерения) и колоночные хранилища оптимизированы под чтение, а данные загружаются пакетно, без конкурентных обновлений.

Как поддерживать денормализованный счётчик без гонок?

Атомарным обновлением в той же транзакции: UPDATE posts SET likes_count = likes_count + 1 WHERE id = @id — без чтения значения в приложение. Или асинхронно (событие + периодический пересчёт для исправления расхождений).

#Итог

Нормализация (обычно до 3NF) убирает дублирование и аномалии: один факт — одно место. Денормализация — инструмент оптимизации чтения, который применяют после измерений и всегда вместе с механизмом поддержания согласованности. Исторические снапшоты данных — это не дублирование, а отдельные факты.