Нормализация БД: 1НФ, 2НФ, 3НФ с примерами

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

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

Что такое нормализация — простыми словами

Представьте Excel-выгрузку заказов интернет-магазина: в одной строке — заказ, клиент, товар, менеджер, который его оформил. Удобно смотреть глазами, но попробуйте посчитать, сколько раз купили конкретный товар, или поменять телефон клиента, у которого 20 заказов. Придётся искать и править данные в десятках мест — и велик риск где-то ошибиться.

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

Сквозной пример: заказы интернет-магазина

Вот кусок такой Excel-выгрузки — три заказа, семь строк (в заказе может быть несколько товаров):

Уже видно избыточность: «Иванов А.С.» встречается трижды, «Клавиатура Logitech» с ценой 2 500 — дважды, «Смирнова О. / Продажи» — трижды. Дальше на этой же таблице разберём, почему это не просто некрасиво, а опасно, а затем приведём её к 3НФ.

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

Аномалия обновления. Отдел Смирновой переименовали в «Продажи и маркетинг». Правильный запрос — обновить все её строки:

UPDATE raw_orders SET department = 'Продажи и маркетинг' WHERE manager = 'Смирнова О.';

Но если кто-то забудет WHERE для всех совпадений и обновит только заказ 1, получим противоречие в одной и той же таблице:

SELECT DISTINCT manager, department FROM raw_orders WHERE manager = 'Смирнова О.';
-- Смирнова О. | Продажи и маркетинг   (заказ 1 обновили)
-- Смирнова О. | Продажи               (заказ 2 забыли)

Теперь неизвестно, в каком отделе Смирнова на самом деле — данные врут сами себе. Чем больше у менеджера заказов, тем выше шанс такой ошибки.

Аномалия вставки. В компанию наняли нового менеджера Волкова П. в отдел «Маркетинг». Записать этот факт нельзя — в таблице нет строки без заказа, а у Волкова заказов пока ноль. Факт «Волков работает в Маркетинге» просто негде хранить, пока он не продаст хоть что-то.

Аномалия удаления. У Петровой Е.В. всего один заказ (order_id = 2). Стандартная операция очистки просроченных заказов:

DELETE FROM raw_orders WHERE order_id = 2;

— и вместе с заказом бесследно исчезает сам факт, что Петрова вообще была клиентом. Информация о клиенте оказалась «прицеплена» к информации о заказе, хотя это разные сущности.

Все три аномалии — следствие одной причины: в таблице намешаны факты о разных сущностях (клиент, товар, менеджер, заказ), и нормализация — это способ их разделить.

Функциональная зависимость: понятие, без которого 2НФ и 3НФ не понять

Прежде чем идти дальше, нужно одно определение — на нём держатся все следующие формы.

Функциональная зависимость X → Y означает: зная значение X, вы всегда можете однозначно назвать Y. Если одному и тому же X в разных строках соответствуют разные Y — зависимости нет.

В нашей таблице:

• order_id → manager — у заказа один менеджер (в обеих строках заказа 1 менеджер один и тот же).
• manager → department — у менеджера один отдел.
• product → price — у товара одна цена (в реальной БД зависимость строится от product_id, а не от названия — у двух разных товаров название теоретически может совпасть, а вот числовой ID — никогда. В таблице выше вместо product_id для читаемости показано название, но помните: определяет значение именно ID).

Из этого вытекают два вида зависимостей, которые и запрещают 2НФ и 3НФ:

• Частичная зависимость — есть только когда первичный ключ составной (из нескольких столбцов), и какой-то столбец зависит не от всего ключа, а только от его части.
• Транзитивная зависимость — цепочка X → Y → Z, где Z зависит от X не напрямую, а через Y. Пример из наших данных: order_id → manager → department — отдел зависит от заказа только потому, что зависит от менеджера, который этот заказ обслужил.

Теперь применим это к таблице.

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

Простыми словами: каждая ячейка содержит ровно одно значение. Никаких списков через запятую, никаких phone1, phone2, phone3.

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

Добавим в пример то, что почти всегда ломает 1НФ на практике, — телефоны клиента:

Поле phones хранит список — нельзя написать WHERE phone = '+7 900 444-55-66' и найти заказ 1, база не умеет заглядывать внутрь строки-списка. То же самое с колонками phone1, phone2, phone3 — это повторяющаяся группа, просто растянутая по горизонтали вместо запятых.

Исправление — телефон превращается в отдельную сущность «один клиент — много телефонов»:

CREATE TABLE customer_phones (
  customer_id INT REFERENCES customers(id),
  phone       TEXT NOT NULL,
  PRIMARY KEY (customer_id, phone)
);

Теперь SELECT * FROM customer_phones WHERE phone = '+7 900 444-55-66' работает как обычный поиск. Наша основная таблица заказов уже была в 1НФ (в ней нет списков), поэтому идём дальше.

Вторая нормальная форма (2НФ)

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

Требования: выполнена 1НФ, нет частичных зависимостей.

Для начинающих: частичная зависимость в принципе невозможна при ключе из одной колонки — тогда 2НФ выполняется автоматически. Она проверяется только когда ключ составной.

Вернёмся к основной таблице. Одна строка заказа описывает пару «заказ + товар», значит естественный первичный ключ здесь — (order_id, product). Проверим по нему каждый столбец:

• price зависит только от product (Клавиатура стоит 2 500 в обеих строках, где встречается) — не зависит от order_id. Частичная зависимость.
• customer, manager, department зависят только от order_id (не меняются между двумя товарами заказа 1) — не зависят от product. Тоже частичная зависимость, просто с другой стороны ключа.
• qty — единственный столбец, который реально описывает пару «этот товар в этом заказе»: зависит от всего ключа целиком. Он и остаётся.

Исправление — то, что зависит только от части ключа, выносим в отдельные таблицы:

-- Зависит только от order_id
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer TEXT NOT NULL,
  manager  TEXT NOT NULL,
  department TEXT NOT NULL
);

-- Зависит только от product_id
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name TEXT NOT NULL,
  price NUMERIC NOT NULL
);

-- Зависит от обеих частей ключа сразу
CREATE TABLE order_items (
  order_id   INT REFERENCES orders(order_id),
  product_id INT REFERENCES products(product_id),
  qty        INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Цена товара теперь хранится в одном месте — обновили products, и она верна во всех заказах сразу. Обратите внимание: у новой таблицы orders первичный ключ — одна колонка (order_id), значит по правилу выше она автоматически в 2НФ. Но это не значит, что она в 3НФ — там ещё осталась та самая аномалия обновления с отделом Смирновой.

Третья нормальная форма (3НФ)

Простыми словами: столбцы зависят от ключа, только от ключа и ничего, кроме ключа. Если A определяет B, а B определяет C — B и C не место в этой таблице.

Требования: выполнена 2НФ, нет транзитивных зависимостей.

Смотрим на таблицу orders, полученную на прошлом шаге:

Ключ здесь один столбец (order_id), поэтому 2НФ формально выполнена. Но вспомните цепочку из раздела про функциональные зависимости: order_id → manager → department. Отдел не является фактом о заказе — это факт о менеджере, который просто «протёк» в таблицу заказов через транзитивную зависимость. Отсюда и была аномалия обновления из начала статьи: чтобы поменять отдел Смирновой, приходится лезть в каждую её строку заказа, вместо того чтобы поменять одну запись в одной таблице.

Исправление — разрываем цепочку, вынося менеджера и отдел в отдельные таблицы:

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  department_name TEXT NOT NULL,
  budget NUMERIC
);

CREATE TABLE employees (
  employee_id   SERIAL PRIMARY KEY,
  employee_name TEXT NOT NULL,
  department_id INT REFERENCES departments(department_id)
);

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  employee_id INT REFERENCES employees(employee_id)
);

Теперь смена отдела Смирновой — это UPDATE employees SET department_id = ... WHERE employee_name = 'Смирнова О.', одна строка, без шанса на противоречие. Ровно та же логика применима и к клиенту: если бы в orders остался, скажем, customer_city, он зависел бы от заказа только транзитивно — через клиента (order_id → customer_id → customer_city), поэтому город тоже переезжает в таблицу customers, а не остаётся в orders.

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

BCNF — усиленная версия 3НФ для более редкого случая: каждая функциональная зависимость X → Y должна иметь X суперключом. Большинство таблиц, приведённых к 3НФ, автоматически оказываются и в BCNF — этот кейс проявляется только при составных ключах с пересекающимися зависимостями.

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

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

Пройдя все шаги, получаем полную нормализованную схему интернет-магазина:

CREATE TABLE customers (
  customer_id   SERIAL PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city          TEXT
);

CREATE TABLE customer_phones (
  customer_id INT REFERENCES customers(customer_id),
  phone       TEXT NOT NULL,
  PRIMARY KEY (customer_id, phone)
);

CREATE TABLE departments (
  department_id   SERIAL PRIMARY KEY,
  department_name TEXT NOT NULL,
  budget          NUMERIC
);

CREATE TABLE employees (
  employee_id   SERIAL PRIMARY KEY,
  employee_name TEXT NOT NULL,
  department_id INT REFERENCES departments(department_id)
);

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  product_name TEXT NOT NULL,
  price        NUMERIC NOT NULL
);

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  customer_id INT REFERENCES customers(customer_id),
  employee_id INT REFERENCES employees(employee_id)
);

CREATE TABLE order_items (
  order_id   INT REFERENCES orders(order_id),
  product_id INT REFERENCES products(product_id),
  qty        INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Каждый факт теперь живёт ровно в одном месте: цена товара — в products, отдел менеджера — в departments, телефон клиента — в customer_phones. Все три аномалии из начала статьи в этой схеме физически невозможны.

Когда денормализация оправдана

Нормализация — не самоцель. Есть случаи, когда денормализация улучшает производительность без значимых потерь целостности:

1. Аналитические таблицы и хранилища данных. OLAP-запросы агрегируют миллионы строк, множество JOIN'ов убивают производительность. В Data Warehouse применяют схему «звезда» — денормализованные таблицы фактов и измерений.

2. Вычисленные агрегаты. Если каждый запрос к странице товара пересчитывает средний рейтинг из миллиона отзывов, есть смысл хранить avg_rating прямо в products и обновлять при добавлении отзыва:

ALTER TABLE products ADD COLUMN avg_rating NUMERIC(3,2) DEFAULT 0;
ALTER TABLE products ADD COLUMN reviews_count INT DEFAULT 0;

3. Исторические снимки. В order_items стоит хранить unit_price на момент покупки, а не только ссылку на текущую цену в products — иначе при изменении цены товара исторические отчёты о прошлых продажах станут неверными задним числом.

4. Поля для поиска и сортировки. Если часто нужно сортировать клиентов по полному имени, дешевле хранить full_name денормализованно, чем на каждый запрос конкатенировать first_name || ' ' || last_name.

Практическое правило

Проектируйте нормализованно (3НФ). Денормализуйте точечно — когда профилировщик показал реальную проблему с производительностью, а не «на всякий случай».

Шпаргалка по нормальным формам

Потренируйтесь

Вот сырая таблица успеваемости — определите, какие нормальные формы она нарушает, и разбейте на таблицы:

CREATE TABLE courses (
  course_id   SERIAL PRIMARY KEY,
  course_name TEXT NOT NULL,
  teacher_id  INT REFERENCES teachers(teacher_id)
);

CREATE TABLE teachers (
  teacher_id   SERIAL PRIMARY KEY,
  teacher_name TEXT NOT NULL,
  room         TEXT NOT NULL
);

CREATE TABLE enrollments (
  student_id INT REFERENCES students(student_id),
  course_id  INT REFERENCES courses(course_id),
  grade      INT,
  PRIMARY KEY (student_id, course_id)
);

Вопросы для самопроверки

1. В чём разница между 2НФ и 3НФ?
2. Может ли таблица быть в 1НФ, но не в 2НФ? При каком условии?
3. Что такое транзитивная зависимость — приведите пример из этой статьи.
4. Почему product_id → price, а не price → product_id?
5. Когда вы сознательно нарушите нормальные формы?

Проверьте свои знания проектирования на практических задачах в нашем тренажёре SQL.