Нормализация базы данных: 1НФ, 2НФ, 3НФ и когда их нарушать

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

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

Представьте, что вы ведёте учёт заказов в Excel. В одной ячейке написали «Книга, Ручка, Тетрадь» — кажется удобно. Но теперь попробуйте найти все заказы с ручкой или посчитать, сколько ручек продали за месяц. Не получается — данные «слиплись».

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

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

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

Без нормализации возникают три типа аномалий:

• Аномалия вставки — нельзя добавить данные без добавления других данных (нельзя добавить курс без студента).
• Аномалия обновления — одно логическое изменение требует правки множества строк (изменение адреса клиента в 100 заказах).
• Аномалия удаления — удаление одних данных уничтожает другие (удаляем последний заказ клиента → теряем данные о клиенте).

-- Пример аномалии обновления: без нормализации правим 100 строк
UPDATE orders SET customer_address = 'Новый адрес' WHERE customer_id = 42;

-- С нормализацией: правим одну строку в таблице clients
UPDATE clients SET address = 'Новый адрес' WHERE id = 42;

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

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

Требования:

1. Все значения атомарны — нет списков или множеств в одном поле.
2. Нет повторяющихся групп столбцов.
3. Есть первичный ключ.

Нарушение 1НФ:

┌──────────┬──────────┬────────────────────────┐
│ order_id │ customer │ products               │
├──────────┼──────────┼────────────────────────┤
│ 1        │ Иванов   │ Книга, Ручка, Тетрадь  │
│ 2        │ Петров   │ Ноутбук                │
└──────────┴──────────┴────────────────────────┘

Поле products содержит список — это нарушение атомарности. Другой типичный случай — столбцы phone1, phone2, phone3 (повторяющаяся группа).

После 1НФ — выносим каждый товар в отдельную строку:

┌──────────┬──────────┐     ┌──────────┬──────────┐
│ orders   │          │     │ order_items          │
├──────────┼──────────┤     ├──────────┬───────────┤
│ order_id │ customer │     │ order_id │ product   │
├──────────┼──────────┤     ├──────────┼───────────┤
│ 1        │ Иванов   │────>│ 1        │ Книга     │
│ 2        │ Петров   │     │ 1        │ Ручка     │
└──────────┴──────────┘     │ 1        │ Тетрадь   │
                            │ 2        │ Ноутбук   │
                            └──────────┴───────────┘

Исправление:

CREATE TABLE order_items (
  order_id   INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity   INT,
  PRIMARY KEY (order_id, product_id)
);

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

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

Требования:

1. Выполнены условия 1НФ.
2. Каждый неключевой атрибут полностью зависит от всего первичного ключа (нет частичных зависимостей).

Важно для начинающих: частичная зависимость возможна только при составном первичном ключе (состоящем из нескольких столбцов). Если ключ — одна колонка, таблица автоматически в 2НФ.

Нарушение 2НФ:

┌──────────┬────────────┬──────────┬──────────────┬───────────────┐
│ order_id │ product_id │ quantity │ product_name │ product_price │
├──────────┼────────────┼──────────┼──────────────┼───────────────┤
│ 1        │ 42         │ 2        │ Ноутбук      │ 89990         │
│ 1        │ 15         │ 5        │ Ручка        │ 120           │
│ 2        │ 42         │ 1        │ Ноутбук      │ 89990         │
└──────────┴────────────┴──────────┴──────────────┴───────────────┘

Первичный ключ — (order_id, product_id). Но product_name и product_price зависят только от product_id, а не от пары. Итог: название «Ноутбук» хранится в двух строках — если изменится цена, придётся обновлять все строки.

Исправление:

-- Продукты выносим в отдельную таблицу
CREATE TABLE products (
  product_id    SERIAL PRIMARY KEY,
  product_name  TEXT    NOT NULL,
  product_price NUMERIC NOT NULL
);

-- В order_items остаётся только то, что относится к конкретной строке заказа
CREATE TABLE order_items (
  order_id   INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

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

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

Требования:

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

Транзитивная зависимость — когда: ключ → поле A → поле B. То есть поле B зависит не напрямую от ключа, а через поле A.

Нарушение 3НФ:

┌─────────────┬─────────┬───────────────┬─────────────────┬───────────────────┐
│ employee_id │ name    │ department_id │ department_name │ department_budget │
├─────────────┼─────────┼───────────────┼─────────────────┼───────────────────┤
│ 1           │ Иванов  │ 10            │ Разработка      │ 5 000 000         │
│ 2           │ Петров  │ 10            │ Разработка      │ 5 000 000         │
│ 3           │ Сидоров │ 20            │ Маркетинг       │ 2 000 000         │
└─────────────┴─────────┴───────────────┴─────────────────┴───────────────────┘

Цепочка: employee_id → department_id → department_name, department_budget. Название и бюджет отдела зависят от department_id, а не напрямую от сотрудника. Если переименовать отдел — придётся обновлять все строки сотрудников.

Исправление:

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

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

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

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

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

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

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

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

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

-- Денормализованное поле для быстрого чтения
ALTER TABLE products ADD COLUMN avg_rating NUMERIC(3,2) DEFAULT 0;
ALTER TABLE products ADD COLUMN reviews_count INT DEFAULT 0;

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

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

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

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

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

Типичные вопросы на собеседованиях по этой теме:

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

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