Нормализация базы данных: 1НФ, 2НФ, 3НФ простыми словами

Коротко

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

• 1НФ: в каждой ячейке одно значение, никаких списков через запятую и повторяющихся колонок payment_1, payment_2.
• 2НФ: каждая неключевая колонка зависит от всего составного ключа, а не от его части.
• 3НФ: неключевые колонки не зависят друг от друга, только от ключа.
• НФБК (нормальная форма Бойса — Кодда) — усиленная 3НФ: всё, что однозначно определяет другие колонки, должно быть ключом.
• Рабочие базы продуктов обычно держат в 3НФ. Аналитические витрины часто сознательно денормализуют ради скорости и простоты, и расплачиваются риском двойного счёта.

Зачем нужна нормализация: три аномалии

Когда один факт записан в нескольких строках, любое изменение надо повторить во всех копиях. Теория баз данных называет такие проблемы аномалиями и выделяет три вида. Посмотрим на них в таблице, которую финансовый отдел собрал из учебной базы SQL-курса: оплаты пользователей 8 и 21 вместе с их страной, каналом и тарифом.

• Аномалия обновления. Страна пользователя хранится в каждой его строке. Стоит поправить одну и забыть про остальные — у пользователя становится две страны, и выручка по странам делится между ними.
• Аномалия вставки. Нельзя записать пользователя, который ещё не платил: строка таблицы — это оплата. В учебной базе 4613 пользователей и только 912 платящих, то есть 3701 человек в такой таблице отсутствует. Посчитать конверсию в оплату по ней невозможно.
• Аномалия удаления. Если удалить оплату как тестовую или возвращённую, вместе с ней пропадают страна и канал пользователя, хотя сам пользователь никуда не делся.

Первая нормальная форма (1НФ): одно значение в ячейке

Колонка payments со списком дат нарушает первое правило: в ячейке лежит не значение, а несколько. Такой список нельзя нормально отфильтровать, посчитать или соединить с другой таблицей: чтобы найти оплаты за июль, придётся искать подстроку, а сумма оплат превращается в разбор текста.

Приведение к 1НФ — это разворот списка в строки: одна оплата — одна строка. Та же ошибка встречается в виде колонок payment_1, payment_2, payment_3. В учебной базе у 290 пользователей больше одной оплаты, максимум три. Сегодня трёх колонок хватает, завтра кто-то заплатит в четвёртый раз, и схему придётся менять.

После разворота ключом строки становится пара (user_id, paid_at): по ней однозначно находится одна оплата.

Вторая нормальная форма (2НФ): зависимость от всего ключа

Таблица в 1НФ стала удобнее для запросов, но дублирование выросло: страна пользователя 8 теперь записана трижды. Вторая нормальная форма требует, чтобы каждая неключевая колонка зависела от всего ключа целиком. Ключ здесь составной — (user_id, paid_at). Страна и канал определяются одним user_id, дата оплаты на них никак не влияет. Это частичная зависимость, и она нарушает 2НФ.

Лечение — вынести такие колонки в отдельную таблицу, где ключ — только user_id. Получаются две таблицы: users со страной и каналом и payments с тем, что относится к конкретной оплате. Аномалии обновления и вставки уходят: страна хранится в одной строке, а пользователь существует независимо от того, платил ли он.

Проверка на 2НФ нужна только таблицам с составным ключом. Если ключ — одна колонка, например суррогатный payment_id, частично зависеть от него нельзя, и таблица в 1НФ автоматически оказывается во 2НФ. Это не значит, что проблем нет: они просто переезжают в третью форму.

Третья нормальная форма (3НФ): никаких зависимостей между неключевыми колонками

В payments осталась колонка plan_price. Она зависит не от оплаты, а от тарифа: у тарифа pro цена 29, у basic 19. Получается цепочка «оплата → тариф → цена», которую называют транзитивной зависимостью. Третья нормальная форма её запрещает: неключевая колонка должна зависеть от ключа напрямую, а не через другую неключевую колонку.

Если маркетинг поднимет цену тарифа pro, придётся переписать все строки оплат с этим тарифом, а заодно исказить историю. Поэтому цену выносят в справочник plans (plan, price), а в оплате оставляют только ссылку на тариф.

Здесь есть тонкость, на которой спотыкаются новички. Цена тарифа в справочнике и сумма, которую человек заплатил, — разные факты. Цена — свойство тарифа сегодня. Сумма — свойство конкретной оплаты в тот день, и после подорожания она не должна меняться. Поэтому в нормализованной схеме у оплаты остаётся своя колонка amount. В учебной базе у каждого тарифа сейчас одна сумма во всех оплатах, но хранить её отдельно всё равно правильно: это не дубль справочника, а историческая запись.

В той же учебной базе есть и честное отступление от 3НФ: таблица subscriptions хранит monthly_price рядом с plan, и у каждого плана там ровно одна цена. Так упростили учебную схему, чтобы в запросах было меньше соединений. В продуктовой базе такую колонку стоит держать под проверкой.

Что такое нормальная форма Бойса — Кодда

НФБК закрывает редкий случай, который 3НФ пропускает. Правило звучит так: если значение одной колонки однозначно определяет другую, первая должна быть ключом таблицы.

Пример из жизни аналитической команды. Таблица закреплений хранит клиента, продуктовое направление и аналитика: (client, area, analyst). У клиента по каждому направлению один аналитик, так что ключ — (client, area). Но каждый аналитик ведёт только одно направление, значит analyst определяет area, а ключом не является. Формально 3НФ выполнена, а аномалия осталась: если аналитик переходит в другое направление, нужно переписать все строки с его клиентами. По НФБК таблицу делят на analysts (analyst, area) и assignments (client, analyst).

Дальше существуют 4НФ и 5НФ, но в рабочих схемах продуктов до них доходят редко. Для аналитика важнее уверенно видеть нарушения первых трёх форм: именно они порождают дубли и расхождения в отчётах.

Как выглядит нормализованная база на практике

Учебная база SQL-курса устроена именно так. users хранит по строке на пользователя: дату регистрации, канал, страну, устройство. events — по строке на действие, payments — по строке на оплату. Таблицы связаны колонкой user_id: в users это первичный ключ, в events и payments — ссылка на него, то есть внешний ключ.

Нормализованные таблицы не мешают считать метрики, просто данные собираются соединением. Выручка по странам — это payments, к которому через user_id подтянута страна из users. Результат по учебной базе: Россия — 520 платящих и 17 469 выручки, Казахстан — 195 и 6 477, Армения — 110 и 3 476, Беларусь — 80 и 2 916, у семи платящих страна не указана, на них приходится 301. Вместе ровно 30 639 — столько же, сколько сумма по таблице оплат без соединения. Так и проверяют, что JOIN не потерял и не размножил строки.

Целостность связей тоже проверяется запросом. В учебной базе нет ни одной оплаты и ни одного события с user_id, которого нет в users. В продуктовой базе за этим следит внешний ключ, в хранилище данных — чаще всего только такие проверки.

select
  u.country,
  count(distinct p.user_id) as payers,
  count(*)                  as payments,
  sum(p.amount)             as revenue
from payments as p
join users as u on u.user_id = p.user_id
group by u.country
order by revenue desc;

-- оплаты без пользователя: 0
select count(*)
from payments as p
left join users as u on u.user_id = p.user_id
where u.user_id is null;

Почему в аналитике данные денормализуют

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

Поэтому в хранилищах данных строят витрины: широкие таблицы, где к каждой оплате уже приклеены канал, страна, тариф и месяц регистрации. Популярна схема «звезда»: таблица фактов с числами и несколько справочников-измерений вокруг неё. Колоночные базы вроде ClickHouse и DuckDB читают из широкой таблицы только нужные колонки, так что лишние поля почти ничего не стоят.

Денормализация — осознанное решение, а не отказ от правил. Её делают поверх нормализованного источника, пересобирают автоматически и не правят руками. Если страна пользователя поменялась, витрину пересчитывают из users, а не ищут и правят строки в ней самой.

Как денормализация приводит к двойному счёту

Главный риск широких таблиц — зерно. Зерно — это то, чему соответствует одна строка: оплата, пользователь, пользователь-день, событие. Пока витрина построена по оплатам, sum(amount) даёт выручку. Стоит приклеить к оплатам события, и строка превращается в пару «оплата × событие».

На учебной базе соединение payments с events по user_id даёт 11 667 строк вместо 1251. sum(amount) по такой таблице — 284 913 вместо 30 639, в 9,3 раза больше. Хуже того, завышение неравномерно: у каждого канала своя активность, поэтому искажается и сравнение каналов. Referral в неправильном отчёте выглядит почти в семь с половиной раз сильнее paid_search, а на самом деле — примерно в 3,8 раза.

Правило простое: сначала агрегируйте каждую таблицу до общего зерна, потом соединяйте. Если нужно показать и выручку, и активность по каналам, посчитайте выручку по пользователю из payments, события по пользователю из events и только после этого соединяйте с users. Подробный разбор — в статье про JOIN без потерь и дублей.

Как понять, что таблица плохо нормализована

Несколько признаков, которые видно без теории. В ячейках встречаются списки через запятую или JSON-массивы, которые приходится разбирать в каждом запросе. Есть колонки с номерами: phone_1, phone_2, payment_3. Одно и то же значение, например название тарифа или страна, повторяется в тысячах строк, и в нём попадаются опечатки: «Россия», «россия», «RU».

Самая полезная проверка — запрос на функциональную зависимость. Если вы подозреваете, что цена определяется тарифом, посчитайте count(distinct price) по каждому тарифу. Больше одного значения — либо зависимости нет и цена действительно разная, либо в данных ошибка. На учебной базе у каждого тарифа ровно одна цена и в subscriptions, и в payments.

Проверьте на учебной базе

Откройте песочницу SQL-курса и соберите широкую таблицу: оплаты с каналом и страной пользователя. Убедитесь, что сумма amount в ней равна сумме по payments. Затем добавьте соединение с events и найдите, во сколько раз выросла выручка для каждой страны. После этого перепишите запрос так, чтобы события сначала агрегировались по пользователю, и сравните результат с исходной суммой.