Нормализация: 1НФ, 2НФ, 3НФ

Как навести порядок в таблице, чтобы данные не врали сами себе

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

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

Проблема: одна большая таблица

Допустим, школа ведёт записи о том, кто на какой кружок ходит. Всё хранится в одной таблице enrollments:

Вроде бы всё понятно, но эта таблица скрывает три классические аномалии:

• Аномалия вставки. Открыли новый кружок "Рисование", но на него пока никто не записался. Куда вписать кружок и его преподавателя? Строки без ученика быть не может — кружок просто негде хранить.
• Аномалия обновления. Сидоров сменил телефон. Его номер записан в нескольких строках, и если поправить не все, база будет одновременно утверждать два разных номера.
• Аномалия удаления. Боря ушёл из школы, мы удаляем его строку — и вместе с ней теряем единственное упоминание того, что Сидоров вообще ведёт шахматы (если бы он вёл только их).

Корень всех бед один: один и тот же факт хранится во многих местах. Нормализация лечит именно это.

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

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

В нашей таблице ячейка club у Ани содержит сразу два кружка через запятую, а teacher — двух преподавателей. Это нарушает 1НФ: по такому столбцу невозможно нормально фильтровать и соединять. Чтобы привести к 1НФ, разносим списки по отдельным строкам — одна строка = один факт "ученик ходит на кружок":

Теперь значения атомарны. Первичным ключом такой таблицы становится пара (student_id, club) — именно она однозначно определяет строку. Это составной ключ, и он нам пригодится в следующем шаге.

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

2НФ: таблица уже в 1НФ, и каждый неключевой столбец зависит от всего составного ключа, а не от его части.

Посмотрим, от чего зависят неключевые столбцы при ключе (student_id, club):

Имя ученика определяется одним student_id — кружок тут ни при чём. А преподаватель определяется только кружком. Это и есть частичные зависимости: столбцы привязаны к части ключа. Разбиваем таблицу на три, выделив независимые сущности:

Заметьте: теперь новый кружок "Рисование" можно добавить в clubs даже без единого ученика — аномалия вставки исчезла.

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

3НФ: таблица в 2НФ, и неключевые столбцы зависят только от ключа, а не друг от друга (нет цепочки "ключ → столбец A → столбец B").

Взглянем на таблицу clubs с ключом club. Телефон teacher_phone на самом деле зависит не от кружка, а от преподавателя: club → teacher → teacher_phone. Это транзитивная зависимость. Из-за неё, если один преподаватель ведёт два кружка, его телефон снова дублируется — и возвращается аномалия обновления. Выносим преподавателя в отдельную таблицу:

Теперь телефон преподавателя записан ровно один раз. Сменился номер — правим одну строку, и вся база сразу актуальна.

Смотрим нормализованную схему в деле

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

CREATE TABLE students (
  student_id   INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL
);
CREATE TABLE teachers (
  teacher       TEXT PRIMARY KEY,
  teacher_phone TEXT NOT NULL
);
CREATE TABLE clubs (
  club    TEXT PRIMARY KEY,
  teacher TEXT REFERENCES teachers(teacher)
);
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(student_id),
  club       TEXT REFERENCES clubs(club),
  PRIMARY KEY (student_id, club)
);

INSERT INTO students VALUES (1,'Аня Котова'),(2,'Боря Лосев'),(3,'Вера Зуева');
INSERT INTO teachers VALUES ('Петров','111-22'),('Сидоров','333-44');
INSERT INTO clubs VALUES ('Робототехника','Петров'),('Шахматы','Сидоров');
INSERT INTO enrollments VALUES (1,'Робототехника'),(1,'Шахматы'),(2,'Шахматы'),(3,'Робототехника');

SELECT s.student_name, e.club, c.teacher, t.teacher_phone
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN clubs    c ON c.club       = e.club
JOIN teachers t ON t.teacher    = c.teacher
ORDER BY s.student_name, e.club;

В результате мы видим те же связи, что и в исходной "свалочной" таблице, но каждый факт (имя ученика, ведущий кружка, телефон преподавателя) хранится в одном месте. Попробуйте поменять телефон Сидорова одной строкой UPDATE teachers SET teacher_phone='999-00' WHERE teacher='Сидоров'; — изменение мгновенно отразится во всех его кружках.

Частые ошибки

• Хранить списки в одной ячейке ("Шахматы, Робототехника") — это нарушение 1НФ; такие данные мучительно фильтровать и соединять.
• Путать порядок форм. Сначала 1НФ (атомарность), потом 2НФ (частичные зависимости имеют смысл только при составном ключе), и лишь затем 3НФ (транзитивные зависимости).
• Нормализовать до бесконечности. Для учебных и большинства рабочих задач 3НФ — достаточная и удобная цель; разбивать таблицы ещё мельче без причины не нужно.

Коротко

• Денормализованная таблица порождает повтор данных и три аномалии: вставки, обновления и удаления.
• 1НФ — атомарные значения, никаких списков и повторяющихся групп в ячейках.
• 2НФ — 1НФ плюс отсутствие частичной зависимости от части составного ключа.
• 3НФ — 2НФ плюс отсутствие транзитивных зависимостей (столбец не зависит от другого неключевого столбца).
• Итог нормализации: каждый факт хранится ровно один раз, а полную картину собирают через соединения (JOIN).