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

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

Заодно станет видно, откуда взялись users и orders, на которых стоит весь остальной справочник по SQL. Они не придуманы, они получаются из одной таблицы за три шага.

Плоская таблица, с которой всё начинается

Так выглядит выгрузка из таблицы заказов, которую вели в Excel и перенесли в базу как есть.

orders_flat

Читается легко, отчёт собирается без единого JOIN. Проблемы начинаются, когда данные приходится менять.

Что именно ломается

Три поломки называют аномалиями. Каждая воспроизводится на этих шести строках.

Аномалия обновления. Аня переехала в Казань. Правим её заказ:

UPDATE orders_flat SET city = 'Казань', warehouse = 'Склад КЗН' WHERE order_id = 11;

SELECT customer, city, count(*) AS rows FROM orders_flat WHERE customer = 'Аня' GROUP BY 1, 2;

База теперь утверждает, что Аня живёт в двух городах одновременно. Ошибки не было, UPDATE отработал ровно так, как написан. Просто город клиента лежит в трёх строках, а поправили одну. На шести строках это заметно, на двух миллионах — нет.

Аномалия удаления. У Глеба один заказ, и его отменили:

DELETE FROM orders_flat WHERE order_id = 14;

Вместе с заказом исчез сам Глеб: что он существует и живёт в Казани, знала только эта строка. Удаляли заказ, потеряли клиента.

Аномалия вставки. Обратная беда. Вера зарегистрировалась, но пока ничего не купила. Записать её некуда: строка в этой таблице обязана иметь order_id, amount и products. Клиент без заказа в такой схеме не представим.

Отдельно портит жизнь список в ячейке. Кто заказывал мышь?

SELECT order_id, products FROM orders_flat WHERE products LIKE '%мыши%';

В заказе 15 мыши нет, там коврик. Поиск подстрокой не различает товар и часть названия другого товара, а в русском языке склонения делают промахи в обе стороны: LIKE '%мышь%' заказ 15 не найдёт, LIKE '%мыши%' найдёт лишний. Посчитать, сколько ковриков продано, нельзя вовсе.

Первая нормальная форма: одно значение в ячейке

1НФ требует, чтобы в ячейке лежало одно значение, а не список. Разворачиваем products в строки:

CREATE TABLE order_items_flat AS
SELECT order_id, trim(p) AS product, customer, city, warehouse, amount, status, created_at
FROM orders_flat, unnest(string_to_array(products, ',')) AS p;

ALTER TABLE order_items_flat ADD PRIMARY KEY (order_id, product);

Шесть строк стали восемью, и вопрос про мышь наконец отвечается точно:

SELECT order_id FROM order_items_flat WHERE product = 'мышь';

Возвращает 10 и 14, без коврика. Появилась и агрегация, которой раньше не было: GROUP BY product честно считает товары. Ключ теперь составной, (order_id, product): один только order_id перестал быть уникальным.

Вторая нормальная форма: убрать зависимость от части ключа

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

Смотрим на таблицу выше. customer, city, amount, status, created_at не зависят от product вообще. Заказ 10 сделан Аней на 900 рублей независимо от того, смотрим мы на строку с мышью или с ковриком. Зависимость идёт от order_id, то есть от половины ключа. Это и есть частичная зависимость, и она объясняет, почему сумма заказа продублировалась в двух строках.

Разносим:

CREATE TABLE order_items AS SELECT DISTINCT order_id, product FROM order_items_flat;
CREATE TABLE orders_2nf AS
SELECT DISTINCT order_id, customer, city, warehouse, amount, status, created_at FROM order_items_flat;

Сумма заказа снова написана один раз. Товары уехали в свою таблицу и больше ничего за собой не тянут.

Третья нормальная форма: убрать зависимость между неключевыми

3НФ запрещает транзитивную зависимость: неключевая колонка не должна зависеть от другой неключевой.

В orders_2nf таких цепочек две. Склад определяется городом: любой заказ из Казани обслуживает «Склад КЗН», и к самому заказу это отношения не имеет. А город определяется клиентом: Аня живёт в Москве, и в её заказе город просто переписан. Обе цепочки идут через промежуточное звено, а не напрямую от ключа.

Разрываем каждую собственной таблицей:

CREATE TABLE cities (city text PRIMARY KEY, warehouse text NOT NULL);

CREATE TABLE users (
  id   int PRIMARY KEY,
  name text NOT NULL,
  city text NOT NULL REFERENCES cities(city)
);

CREATE TABLE orders (
  id         int  PRIMARY KEY,
  user_id    int  NOT NULL REFERENCES users(id),
  amount     int  NOT NULL,
  status     text NOT NULL,
  created_at date NOT NULL
);

Это ровно те users и orders, на которых построены все остальные разборы справочника, от JOIN до оконных функций. И Вера появилась: клиента теперь можно завести без единого заказа.

Что изменилось после разбиения

Все три аномалии закрылись, причём закрылись механически, а не договорённостью.

Переезд Ани стал одной строкой в users, и противоречивого состояния больше не существует. Удаление заказа Глеба больше не стирает Глеба. Вера записана без заказа.

Внешние ключи теперь не дают развалить схему руками:

INSERT INTO orders VALUES (99, 42, 100, 'paid', '2026-06-25');

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL:  Key (user_id)=(42) is not present in table "users".

DELETE FROM cities WHERE city = 'Казань';

ERROR:  update or delete on table "cities" violates foreign key constraint "users_city_fkey" on table "users"
DETAIL:  Key (city)=(Казань) is still referenced from table "users".

Исходный плоский вид никуда не делся, он собирается запросом:

SELECT o.id, u.name, u.city, c.warehouse, o.amount, o.status
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN cities c ON c.city = u.city
ORDER BY o.id;

Вот зачем в SQL нужны соединения. JOIN — не усложнение ради красоты схемы, а способ вернуть то, что нормализация разложила по местам.

Нормальная форма Бойса-Кодда

БКНФ (BCNF) ужесточает третью: левая часть каждой зависимости обязана быть суперключом. Разница вылезает редко, только когда в таблице несколько перекрывающихся ключей-кандидатов.

Классический пример: таблица (студент, предмет, преподаватель), где преподаватель ведёт ровно один предмет, а у предмета преподавателей несколько. Ключ здесь (студент, предмет), неключевая колонка одна, транзитивных зависимостей нет — 3НФ соблюдена. Но зависимость преподаватель → предмет есть, а преподаватель суперключом не является. Уберите последнего студента у преподавателя, и информация о том, какой предмет он ведёт, исчезнет.

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

Денормализация: когда возвращаться назад

Утверждение «нормализация замедляет запросы» звучит на каждом втором собеседовании. Оно верно наполовину, и половину видно только на объёме. Я собрал в PostgreSQL 16.11 две схемы одних и тех же данных: 2 млн заказов и 50 тыс. клиентов нормализованно, и ту же выборку одной плоской таблицей.

Аналитический запрос, сумма оплаченных заказов по Москве:

Плоская выигрывает примерно треть. Хеш-соединение с таблицей клиентов не бесплатно, и на полном скане это видно. Ровно поэтому витрины для отчётов собирают денормализованными.

Теперь обратная задача, клиент сменил город:

Разница в 230 раз, и она не про скорость. Плоская схема правит сорок копий одного факта, а любая из них может остаться непоправленной — та самая аномалия обновления, уже в проде.

С местом на диске всё оказалось интереснее, чем пишут в учебниках. Плоская таблица заняла 148 МБ, нормализованная пара с двумя индексами — 163 МБ. Экономия появляется, когда дублируется что-то длинное: адрес, описание, название компании. Дублирующееся user_777 не окупает даже индекс по внешнему ключу.

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

На чём ловят на собеседовании

«Третья нормальная форма — это когда три таблицы». Номер формы не имеет отношения к числу таблиц. Наш пример дал четыре таблицы на третьей форме, а можно построить схему из двадцати таблиц, которая не дотягивает и до первой.

«2НФ есть всегда, если есть первичный ключ». Почти. Если ключ состоит из одной колонки, частичной зависимости просто неоткуда взяться, и 2НФ выполняется автоматически. Проверять её имеет смысл только при составном ключе. На этом обычно и валятся: пересказывают определение, но не могут сказать, когда оно вообще применимо.

«1НФ запрещает массивы, поэтому в PostgreSQL нельзя text[] и jsonb». Тип данных ни при чём. Вопрос в том, обращаешься ли ты к частям значения по отдельности. Если по массиву нужен поиск, группировка и соединение — это скрытая таблица, и её надо развернуть. Если значение всегда читается и пишется целиком, как настройки виджета, jsonb с GIN-индексом уместен и никакую форму не нарушает.

«Нормализация — это про экономию места». Была про место в семидесятых, когда диск стоил дорого. Сейчас она про непротиворечивость: один факт в одном месте нельзя поправить наполовину. Замер выше показывает, что места она может и не сэкономить вовсе.

«Денормализуем сразу, так быстрее». Быстрее на чтении и медленнее на записи, а расплата приходит не производительностью, а расхождением данных. Сначала нормальная схема, потом витрина под конкретный медленный отчёт, и только по замеру.

Частые вопросы

Как определить нормальную форму по готовой таблице

По трём вопросам подряд. Есть ли в ячейке список или несколько значений — если да, форма нулевая. Составной ли ключ, и зависит ли хоть одна колонка только от его части — если да, застряли на первой. Зависит ли неключевая колонка от другой неключевой — если да, застряли на второй. Прошли все три, перед вами 3НФ.

Нужно ли всегда доводить до 3НФ

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

Чем 3НФ отличается от БКНФ

3НФ разрешает зависимость, если её правая часть входит в какой-нибудь ключ-кандидат. БКНФ такого послабления не даёт и требует, чтобы слева всегда стоял суперключ. Разница проявляется только при нескольких перекрывающихся ключах-кандидатах, поэтому в реальных схемах 3НФ и БКНФ чаще всего совпадают.

Замедлит ли JOIN отчёты после нормализации

На точечных запросах нет: соединение по индексированному внешнему ключу стоит доли миллисекунды. На полных сканах да, и в замере выше это стоило 15 мс на 2 млн строк. Если такой отчёт крутится раз в минуту, собирайте под него отдельную витрину, а основную схему не трогайте.

Где потренироваться

В пути «SQL для аналитиков» на Koddo схема users и orders из этой статьи используется во всех паках: задачи написаны на нормализованных таблицах, поэтому почти каждая начинается с соединения. Разобрать сам механизм соединения удобнее в статье про JOIN, а случай «клиент есть, заказов нет» — в разборе LEFT JOIN, он ровно про Веру. Готовые задачи с решениями собраны в задачах по SQL.