← Все статьи

Нормализация и денормализация на практике

· 7 мин чтения

Нормальные формы без академизма: какие аномалии они устраняют, как довести модель до третьей нормальной формы на живом примере, когда и как осознанно денормализовать ради производительности и как не потерять при этом согласованность данных.

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

Зачем нормализовать: аномалии

Представим, что заказы хранятся в одной таблице, как в выгрузке из Excel:

номердатаклиенттелефон клиентатоварысумма
100101.10Иванов И.И.+7 900 111-22-33Ноутбук x1, Мышь x281 000
100202.10Иванов И.И.+7 900 111-22-33Монитор x125 000
100302.10Петров П.П.+7 900 444-55-66Мышь x11 500

С такой структурой возникают три классические аномалии:

  • Аномалия изменения. Иванов сменил телефон — нужно обновить все его заказы. Обновили не все — в базе два телефона, и непонятно, какой верный.
  • Аномалия вставки. Нельзя завести клиента, пока он ничего не заказал: ему просто негде храниться.
  • Аномалия удаления. Удалили единственный заказ Петрова — потеряли сведения о самом Петрове.

Плюс поле «товары» нельзя нормально обработать запросом: посчитать, сколько мышей продано за месяц, можно только разбором строки.

Суть нормализации: каждый факт хранится в одном месте. Тогда его нельзя изменить «не везде».

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

Первая нормальная форма (1НФ)

Правило: в каждой ячейке — одно атомарное значение, нет повторяющихся групп.

Нарушение — поле «товары» со списком внутри. Также нарушением считаются колонки вида телефон1, телефон2, телефон3.

Исправление: вынести позиции в отдельные строки — каждая строка описывает одну позицию заказа.

номердатаклиенттелефонтоварцена товаракол-во
100101.10Иванов И.И.+7 900 111-22-33Ноутбук78 0001
100101.10Иванов И.И.+7 900 111-22-33Мышь1 5002

«Атомарность» относительна задаче. ФИО одной строкой — это 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»). О трёх уровнях моделирования данных — в статье «Концептуальная, логическая и физическая модель».