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

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

Правила разбиения записаны в виде нормальных форм: первая (1НФ), вторая (2НФ), третья (3НФ), нормальная форма Бойса-Кодда (НФБК), 4НФ и 5НФ. Рабочие таблицы мы советуем доводить до 3НФ или НФБК: формы выше нужны редко.

Откуда взялась нормализация

Термин ввёл Эдгар Кодд в статье 1970 года о модели реляционных баз данных. Нормализацией там названа процедура, которая убирает вложенные таблицы и раскладывает их по отдельным таблицам. Саму таблицу в теории называют отношением. В 1971 году Кодд описал вторую и третью нормальные формы, и исходную процедуру стали называть первой нормальной формой. НФБК появилась в 1974 году, 4НФ и 5НФ в 1977 и 1979 годах.

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

Примеры проверены на Python 3.14 с модулем sqlite3 (SQLite 3.50). Сохраните скрипт ниже как run.py, блоки SQL подряд в файл shop.sql и запустите python run.py shop.sql. База данных живёт в памяти.

import sqlite3
import sys

conn = sqlite3.connect(":memory:", autocommit=True)
buf = ""
for line in open(sys.argv[1], encoding="utf-8"):
    buf += line
    if sqlite3.complete_statement(buf):
        try:
            for row in conn.execute(buf):
                print("|".join(str(v) for v in row))
        except sqlite3.Error as e:
            print(f"{type(e).__name__}: {e}")
        buf = ""

Если будете подставлять в SQL данные пользователя, передавайте их параметрами (?), а не склейкой строк: иначе получите SQL-инъекцию.

Допустим, на этапе проектирования заказы положили в одну таблицу, а товары перечислили в одной ячейке: «Кружка x2, Блокнот x1». Поиск LIKE '%Ручка%' по такому столбцу зацепит и ручку-роллер, а посчитать проданные штуки запросом нельзя: количество спрятано внутри строки.

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

Таблица находится в 1НФ, если в каждой ячейке хранится одно значение: без списков, массивов и вложенных таблиц. Атомарность зависит от задачи: если приложение ищет по фамилии, фамилию хранят отдельным столбцом.

Приводим данные к 1НФ: каждая позиция заказа получает отдельную строку, а строку однозначно определяет составной ключ (order_id, product).

CREATE TABLE sales (
    order_id INTEGER NOT NULL,
    product  TEXT    NOT NULL,
    qty      INTEGER NOT NULL,
    email    TEXT,
    city     TEXT,
    price    INTEGER,
    PRIMARY KEY (order_id, product)
);
INSERT INTO sales VALUES
    (101, 'Кружка',       2, 'anna@mail.ru', 'Казань', 450),
    (101, 'Блокнот',      1, 'anna@mail.ru', 'Казань', 200),
    (102, 'Блокнот',      3, 'ivan@mail.ru', 'Пермь',  200),
    (102, 'Ручка-роллер', 1, 'ivan@mail.ru', 'Пермь',  150),
    (103, 'Ручка',        5, 'anna@mail.ru', 'Казань',  40);

NOT NULL у столбцов ключа здесь нужен: в SQLite столбец PRIMARY KEY принимает NULL, если он не объявлен NOT NULL и не INTEGER PRIMARY KEY, а таблица не STRICT и не WITHOUT ROWID. По стандарту SQL первичный ключ NULL запрещает.

Аномалии вставки, обновления и удаления

Теперь продажи считает обычный GROUP BY, но email и город Анны повторяются в трёх строках. Из-за такой избыточности данных возникают три аномалии. Покажем их внутри транзакции и откатим, чтобы данные пригодились дальше. Здесь откат намеренный, а если откатывать транзакцию нужно при ошибке, в SQL Server для этого есть TRY CATCH.

BEGIN;
UPDATE sales SET city = 'Самара' WHERE order_id = 103;
SELECT DISTINCT email, city FROM sales WHERE email = 'anna@mail.ru';
DELETE FROM sales WHERE order_id = 102;
SELECT COUNT(*) FROM sales WHERE email = 'ivan@mail.ru' OR product = 'Ручка-роллер';
ROLLBACK;
INSERT INTO sales (product, price) VALUES ('Пенал', 300);

anna@mail.ru|Казань
anna@mail.ru|Самара
0
IntegrityError: NOT NULL constraint failed: sales.order_id

Аномалия обновления (её ещё называют аномалией изменения): Анна переехала, город поправили только в последнем заказе, и у одного клиента стало два города. Аномалия удаления: Иван отменил заказ 102, и вместе с ним из базы исчезли сам Иван и сведения о ручке-роллере. Аномалия вставки: новый пенал некуда записать, пока его никто не заказал, потому что номер заказа входит в ключ.

Функциональные зависимости и ключи

Функциональная зависимость A → B означает, что значение A однозначно определяет значение B. Левую часть называют детерминантом. В нашей таблице email → city и order_id → email.

Потенциальный ключ: минимальный набор атрибутов, который однозначно определяет строку таблицы. Один из потенциальных ключей назначают первичным (primary key), а любой набор, в котором есть ключ, называют суперключом. Атрибут из хотя бы одного потенциального ключа называют ключевым, остальные неключевыми.

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

SELECT 'product -> price', COUNT(*) FROM (
    SELECT product FROM sales GROUP BY product HAVING COUNT(DISTINCT price) > 1);
SELECT 'email -> city', COUNT(*) FROM (
    SELECT email FROM sales GROUP BY email HAVING COUNT(DISTINCT city) > 1);
SELECT 'order_id -> qty', COUNT(*) FROM (
    SELECT order_id FROM sales GROUP BY order_id HAVING COUNT(DISTINCT qty) > 1);

product -> price|0
email -> city|0
order_id -> qty|2

Количество от номера заказа не зависит: в двух заказах у разных товаров оно разное, и qty зависит только от всего ключа (order_id, product). Для product -> price запрос вернул ноль, но зависимости нет: в sales записана цена продажи, и со скидкой тот же блокнот продадут дешевле. Ноль получился случайно: строк всего пять.

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

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

В sales ключ составной, и email с городом зависят от его части order_id: это нарушение 2НФ, их выносим в orders. Цена продажи зависит от пары целиком и остаётся в позиции заказа items. Товары выносим в справочник products, и у него появляется новый факт предметной области: цена каталога. Пока каждый товар продан по одной цене, начальную цену каталога можно взять из продаж.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name  TEXT    NOT NULL UNIQUE,
    price INTEGER NOT NULL
);
CREATE TABLE orders (id INTEGER PRIMARY KEY, email TEXT NOT NULL, city TEXT);
CREATE TABLE items (
    order_id   INTEGER NOT NULL REFERENCES orders (id),
    product_id INTEGER NOT NULL REFERENCES products (product_id),
    qty   INTEGER NOT NULL,
    price INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
BEGIN;
INSERT INTO sales VALUES (104, 'Блокнот', 1, 'ivan@mail.ru', 'Пермь', 180);
INSERT INTO products (name, price) SELECT DISTINCT product, price FROM sales;
ROLLBACK;
INSERT INTO products (name, price)
    SELECT DISTINCT product, price FROM sales ORDER BY product;
INSERT INTO orders SELECT DISTINCT order_id, email, city FROM sales;
INSERT INTO items
    SELECT l.order_id, p.product_id, l.qty, l.price
    FROM sales l JOIN products p ON p.name = l.product;
SELECT * FROM products;

IntegrityError: UNIQUE constraint failed: products.name
1|Блокнот|200
2|Кружка|450
3|Ручка|40
4|Ручка-роллер|150

Первая попытка показывает, где ломается сбор цены каталога из продаж: блокнот продали ещё раз за 180, SELECT DISTINCT дал два «Блокнота» с разными ценами, и вставка упала на UNIQUE. Тогда цену каталога задают руками. В products лежит текущая цена каталога, в items цена на момент продажи. Поднимете цену блокнота до 250, и сумма старого заказа 101 меняться не должна. Если убрать цену из позиций как «зависящую от товара», настоящий магазин потеряет историю продаж.

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

Таблица находится в 3НФ, если она во 2НФ и ни один неключевой атрибут не зависит от ключа транзитивно, через другой неключевой атрибут. Коротко правило звучит так: неключевой столбец сообщает факт о ключе, обо всём ключе и ни о чём, кроме ключа.

В orders цепочка id → email → city: город относится к клиенту, и у Анны он записан дважды. Если же city означает город доставки, это факт заказа и ему место в orders.

Выносим клиентов в таблицу customers, а заполненную orders меняем как рабочую базу: добавляем внешний ключ, заполняем, удаляем лишние столбцы.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    city  TEXT
);
INSERT INTO customers (email, city)
    SELECT DISTINCT email, city FROM orders;
ALTER TABLE orders ADD COLUMN customer_id INTEGER REFERENCES customers (customer_id);
UPDATE orders SET customer_id =
    (SELECT customer_id FROM customers c WHERE c.email = orders.email);
ALTER TABLE orders DROP COLUMN email;
ALTER TABLE orders DROP COLUMN city;
SELECT * FROM orders;

101|1
102|2
103|1

Как проверить разложение таблицы

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

CREATE VIEW joined AS
    SELECT o.id, p.name, i.qty, c.email, c.city, i.price
    FROM items i
    JOIN orders o    ON o.id = i.order_id
    JOIN customers c ON c.customer_id = o.customer_id
    JOIN products p  ON p.product_id = i.product_id;
SELECT COUNT(*) FROM (SELECT * FROM sales EXCEPT SELECT * FROM joined);
SELECT COUNT(*) FROM (SELECT * FROM joined EXCEPT SELECT * FROM sales);

0
0

Повторим две операции, которые раньше ломали данные:

UPDATE customers SET city = 'Самара' WHERE email = 'anna@mail.ru';
DELETE FROM items WHERE order_id = 102;
DELETE FROM orders WHERE id = 102;
SELECT email, city FROM customers;

anna@mail.ru|Самара
ivan@mail.ru|Пермь

Город Анны поменялся одной строкой, а Иван пережил отмену заказа. Новый товар теперь записывается в products, где номера заказа нет.

Ссылочную целостность держат внешние ключи, но в SQLite они по умолчанию выключены, и включать их нужно в каждом соединении. Без этого база данных молча примет позицию несуществующего заказа:

INSERT INTO items VALUES (999, 1, 1, 200);
SELECT COUNT(*) FROM items WHERE order_id = 999;
DELETE FROM items WHERE order_id = 999;
PRAGMA foreign_keys = ON;
INSERT INTO items VALUES (999, 1, 1, 200);

1
IntegrityError: FOREIGN KEY constraint failed

На рабочей базе добавляется страховка: резервная копия, новые таблицы рядом со старой, перенос через INSERT ... SELECT DISTINCT, сверка через EXCEPT, переключение кода и только потом DROP COLUMN. На грязных данных перенос остановится сам: если у одного заказа в строках записаны разные города, SELECT DISTINCT даст две строки с одним номером, и INSERT в orders упадёт с UNIQUE constraint failed: orders.id.

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

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

Пример: каждый преподаватель ведёт один предмет, а по каждому предмету студент ходит к одному преподавателю. Потенциальных ключей два, (student, subject) и (student, teacher), все атрибуты ключевые, и 3НФ выполнена. Но зависимость teacher → subject есть, а teacher не суперключ.

Таблица enrollment (student, subject, teacher) с ключом (student, subject) примет строку, по которой один преподаватель ведёт два предмета: ключ этого не запрещает. Нового преподавателя без студентов в неё тоже не записать. Разложение по НФБК даёт две таблицы:

CREATE TABLE teachers (teacher TEXT NOT NULL PRIMARY KEY, subject TEXT NOT NULL);
CREATE TABLE student_teacher (
    student TEXT NOT NULL,
    teacher TEXT NOT NULL REFERENCES teachers (teacher),
    PRIMARY KEY (student, teacher)
);
INSERT INTO teachers VALUES
    ('Ковалёв', 'Базы данных'), ('Зуева', 'Алгоритмы'), ('Ершов', 'Базы данных');
INSERT INTO student_teacher VALUES
    ('Оля', 'Ковалёв'), ('Петя', 'Ковалёв'), ('Петя', 'Зуева'), ('Петя', 'Ершов');
SELECT s.student, t.subject, COUNT(*) FROM student_teacher s
    JOIN teachers t ON t.teacher = s.teacher
    GROUP BY s.student, t.subject HAVING COUNT(*) > 1;

Петя|Базы данных|2

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

4НФ и 5НФ

Четвёртая нормальная форма касается многозначных зависимостей. Пример без кода: навыки сотрудника и его языки лежат в одной таблице (employee, skill, language) и друг от друга не зависят. Для двух навыков и двух языков придётся хранить все четыре сочетания. Ключ такой таблицы состоит из всех трёх столбцов, а многозначная зависимость employee ↠ skill начинается с employee, а он не суперключ. 4НФ требует, чтобы левая часть каждой нетривиальной многозначной зависимости была суперключом, поэтому таблицу делят на (employee, skill) и (employee, language).

Пятая форма требует, чтобы каждая нетривиальная зависимость соединения следовала из потенциальных ключей. Таблица в 4НФ нарушает 5НФ лишь в редких случаях.

Денормализация: когда дублирование оправдано

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

ALTER TABLE orders ADD COLUMN total INTEGER;
UPDATE orders SET total =
    (SELECT SUM(qty * price) FROM items i WHERE i.order_id = orders.id);
UPDATE items SET qty = 4 WHERE order_id = 101 AND product_id = 1;
SELECT o.id, o.total, SUM(i.qty * i.price) FROM orders o
    JOIN items i ON i.order_id = o.id GROUP BY o.id;

101|1100|1700
103|200|200

Количество блокнотов в заказе 101 поменяли, а сохранённая сумма осталась старой: 1100 против настоящих 1700. Синхронизацию поручают базе, например триггером:

CREATE TRIGGER items_total AFTER UPDATE ON items
BEGIN
    UPDATE orders SET total =
        (SELECT SUM(qty * price) FROM items WHERE order_id = NEW.order_id)
    WHERE id = NEW.order_id;
END;
UPDATE items SET qty = 2 WHERE order_id = 101 AND product_id = 1;
SELECT id, total FROM orders;

101|1300
103|200

Этот триггер ловит только UPDATE, для вставки и удаления позиций нужны ещё два. В PostgreSQL для отчётов есть материализованные представления: результат запроса хранится как таблица, но данные в нём не всегда свежие, их обновляют командой REFRESH MATERIALIZED VIEW. Дальше по этому пути модель для чтения отделяют от модели записи совсем: так устроен паттерн CQRS.

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

Как нормализация влияет на скорость запросов

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

EXPLAIN QUERY PLAN SELECT qty FROM items WHERE product_id = 2;
CREATE INDEX idx_items_product ON items (product_id);
EXPLAIN QUERY PLAN SELECT qty FROM items WHERE product_id = 2;

2|0|216|SCAN items
3|0|62|SEARCH items USING INDEX idx_items_product (product_id=?)

SCAN в последнем столбце означает перебор всей таблицы позиций: индекс первичного ключа начинается с order_id, и искать по product_id он не помогает. После CREATE INDEX план меняется на SEARCH, база данных сразу находит нужные строки. Первые три числа служебные и зависят от версии.

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

Обязательно ли доводить таблицы до третьей нормальной формы?

Для таблиц, в которые пишет приложение, да: третья нормальная форма убирает аномалии вставки, обновления и удаления из примера с заказами. Аномалии вроде таблицы enrollment, где у таблицы несколько перекрывающихся ключей, снимает только НФБК.

Как понять, что таблица уже в 3НФ?

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