Нормализацию обычно проходят в институте как набор определений с функциональными зависимостями и забывают сразу после экзамена. А зря: нормальные формы — это не теория ради теории, а сжатый опыт того, какие ошибки в структуре данных приводят к противоречивым данным. Денормализация — обратная сторона той же медали: осознанное отступление от правил ради скорости. Разберём обе темы на практике, без формул, но с пониманием, что и зачем мы делаем.
Зачем нормализовать: аномалии
Представим, что заказы хранятся в одной таблице, как в выгрузке из Excel:
| номер | дата | клиент | телефон клиента | товары | сумма |
|---|---|---|---|---|---|
| 1001 | 01.10 | Иванов И.И. | +7 900 111-22-33 | Ноутбук x1, Мышь x2 | 81 000 |
| 1002 | 02.10 | Иванов И.И. | +7 900 111-22-33 | Монитор x1 | 25 000 |
| 1003 | 02.10 | Петров П.П. | +7 900 444-55-66 | Мышь x1 | 1 500 |
С такой структурой возникают три классические аномалии:
- Аномалия изменения. Иванов сменил телефон — нужно обновить все его заказы. Обновили не все — в базе два телефона, и непонятно, какой верный.
- Аномалия вставки. Нельзя завести клиента, пока он ничего не заказал: ему просто негде храниться.
- Аномалия удаления. Удалили единственный заказ Петрова — потеряли сведения о самом Петрове.
Плюс поле «товары» нельзя нормально обработать запросом: посчитать, сколько мышей продано за месяц, можно только разбором строки.
Суть нормализации: каждый факт хранится в одном месте. Тогда его нельзя изменить «не везде».
Нормальные формы по шагам
Первая нормальная форма (1НФ)
Правило: в каждой ячейке — одно атомарное значение, нет повторяющихся групп.
Нарушение — поле «товары» со списком внутри. Также нарушением считаются колонки вида телефон1, телефон2, телефон3.
Исправление: вынести позиции в отдельные строки — каждая строка описывает одну позицию заказа.
| номер | дата | клиент | телефон | товар | цена товара | кол-во |
|---|---|---|---|---|---|---|
| 1001 | 01.10 | Иванов И.И. | +7 900 111-22-33 | Ноутбук | 78 000 | 1 |
| 1001 | 01.10 | Иванов И.И. | +7 900 111-22-33 | Мышь | 1 500 | 2 |
«Атомарность» относительна задаче. ФИО одной строкой — это 1НФ, если система никогда не ищет по фамилии отдельно. Адрес одной строкой — 1НФ для отправки писем, но не для логистики, которой нужен регион.
Вторая нормальная форма (2НФ)
Правило: 1НФ, и каждый неключевой атрибут зависит от всего ключа, а не от его части.
Ключ таблицы выше — пара (номер заказа, товар). Но дата и клиент зависят только от номера заказа, а цена товара — только от товара. Это частичные зависимости.
Исправление: разделить на три таблицы:
- Заказы (номер, дата, клиент, телефон);
- Товары (товар, цена);
- Позиции (номер заказа, товар, количество).
2НФ актуальна только для таблиц с составным ключом. Если ключ — один суррогатный id, таблица автоматически во 2НФ, но это не значит, что проблема исчезла: она просто маскируется, и её поймает 3НФ.
Третья нормальная форма (3НФ)
Правило: 2НФ, и нет транзитивных зависимостей — неключевые атрибуты не зависят от других неключевых атрибутов.
В таблице «Заказы» телефон зависит от клиента, а клиент — от номера заказа. Телефон зависит от ключа транзитивно, через клиента.
Исправление: вынести клиентов:
- Клиенты (id, ФИО, телефон);
- Заказы (номер, дата, клиент_id);
- Товары (id, наименование, цена);
- Позиции (номер заказа, товар_id, количество).
Классическая мнемоника: каждый неключевой атрибут должен зависеть «от ключа, от всего ключа и ни от чего, кроме ключа».
Формы выше третьей
- Нормальная форма Бойса — Кодда (НФБК) — усиление 3НФ для случаев с несколькими пересекающимися потенциальными ключами. На практике 3НФ и НФБК почти всегда совпадают.
- 4НФ — устраняет независимые многозначные зависимости: если у сотрудника есть набор навыков и набор языков, их нельзя хранить в одной таблице «сотрудник — навык — язык», иначе получится декартово произведение.
- 5НФ и 6НФ — встречаются в специальных задачах (6НФ — в хранилищах с полной историей, например в подходе Anchor Modeling).
Для операционных систем ориентир — 3НФ. Её достаточно, чтобы избавиться от практически всех аномалий.
Ловушка: цена в позиции заказа
После нормализации цена хранится только в таблице товаров. Но цена меняется, и если позиция заказа ссылается на товар без своей цены, все старые заказы «подорожают» вместе с прайсом. Это не нарушение нормализации, а ошибка моделирования: цена на момент заказа — отдельный факт, отличный от текущей цены товара. Поэтому в позиции заказа хранят свою цену, и это не денормализация, а корректная модель.
Тот же принцип действует для адреса доставки (адрес на момент заказа, а не текущий адрес клиента), ставки налога, курса валюты, условий тарифа. Перед тем как «нормализовать» атрибут в справочник, стоит спросить: это текущее значение или значение на момент события?
Денормализация: когда и зачем
Денормализация — осознанное добавление избыточности ради производительности или простоты чтения. Ключевое слово — осознанное: это решение принимается после нормализованной модели, а не вместо неё.
Типичные причины:
- Тяжёлые соединения на частых запросах. Список заказов с именем клиента и суммой показывается тысячи раз в минуту, и каждый раз собирать сумму из позиций дорого.
- Агрегаты. Количество комментариев к статье, остаток на счёте, рейтинг товара — считать их на лету по миллионам строк долго.
- Отчёты и аналитика. Хранилища данных строятся на денормализованных моделях (звезда, снежинка), потому что оптимизированы под чтение больших объёмов, а не под изменение.
- Распределённые системы. Сервис хранит копию нужных ему данных из другого сервиса, чтобы не обращаться к нему синхронно.
- Документные БД. В MongoDB или в колонке
jsonbпринято хранить вместе то, что читается вместе.
Приёмы денормализации
| Приём | Пример | Чем платим |
|---|---|---|
| Хранимое вычисляемое поле | orders.total — сумма позиций | нужно пересчитывать при изменении позиций |
| Копия атрибута | orders.customer_name для списка заказов | расхождение при переименовании клиента |
| Счётчик | articles.comments_count | конкуренция при частых обновлениях |
| Вложенные данные | позиции как JSON-массив внутри заказа | сложнее запросы по позициям и ограничения целостности |
| Материализованное представление | витрина продаж по дням | данные отстают до обновления представления |
| Отдельная модель чтения (CQRS) | поисковый индекс каталога в Elasticsearch | итоговая согласованность, ещё одна система |
Как денормализовать безопасно
- Определить источник истины. Для каждой избыточной копии должно быть ясно, откуда она берётся и что главнее при расхождении.
- Выбрать механизм синхронизации. Обновление в той же транзакции, триггер, обновление по событию, периодический пересчёт. От выбора зависит, насколько копия может отставать.
- Описать допустимую задержку. Сумма заказа должна быть точной всегда, счётчик лайков может отставать на минуту.
- Предусмотреть сверку. Регламентная проверка, что копии совпадают с источником, и процедура исправления.
- Пометить в модели. Комментарий у поля: «денормализация: сумма позиций, пересчитывается при изменении позиций».
- Сначала измерить. Денормализацию без замеров часто делают ради проблемы, которой нет. Индекс или правильный запрос нередко решают задачу без избыточности.
OLTP и OLAP: разные цели — разные модели
Операционные системы (OLTP) обрабатывают множество мелких транзакций: создать заказ, изменить статус. Для них важна целостность, и модель близка к 3НФ.
Аналитические системы (OLAP, хранилища данных) читают большие объёмы для отчётов. Для них используют схему «звезда»: в центре таблица фактов (продажи: дата, товар, магазин, количество, сумма), вокруг — денормализованные таблицы измерений (товар с категорией, брендом и поставщиком в одной таблице). Избыточность здесь норма, потому что данные загружаются регламентно и почти не изменяются.
Ошибка — пытаться обслуживать оба сценария одной моделью: либо отчёты тормозят на нормализованной схеме, либо транзакции ломают денормализованную.
Что аналитику описать
- логическую модель в 3НФ — как отправную точку;
- факты «на момент события» — цены, адреса, ставки, которые копируются в документ осознанно;
- каждую денормализацию: источник истины, способ синхронизации, допустимое отставание;
- требования к производительности ключевых запросов — чтобы денормализация была обоснована цифрами;
- отдельно — модель отчётности, если отчёты строятся не из операционной базы.
Типичные ошибки
- Таблица-простыня «как в Excel» — все аномалии сразу.
- Нормализация до абсурда — отдельный справочник для каждого значения, двадцать соединений на простой экран.
- Справочник вместо факта на момент события — старые документы меняются задним числом.
- Денормализация без источника истины — две копии расходятся, и никто не знает, какой верить.
- EAV «на всякий случай» — универсальная таблица «сущность — атрибут — значение» вместо модели: гибко, но без типов, ограничений и с тяжёлыми запросами.
Итог
Нормализация — способ хранить каждый факт в одном месте и тем самым исключить противоречия. Для операционных систем ориентир — третья нормальная форма. Денормализация — сознательное отступление от неё ради производительности, у которого всегда должны быть источник истины, механизм синхронизации и измеренная причина. А отдельно от обоих стоит помнить про факты «на момент события»: копировать цену в заказ — не денормализация, а правильная модель.
Модель удобно проверять и показывать команде в редакторе DBML — ER-диаграмма строится по тексту автоматически (см. гайд «ER-диаграмма онлайн»). Если модель уже есть в базе, её можно восстановить из DDL (гайд «SQL в DBML»). О трёх уровнях моделирования данных — в статье «Концептуальная, логическая и физическая модель».
