1NF: атомарность и первичный ключ

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

1NF задаёт минимальные требования к тому, чтобы таблицу вообще можно было считать корректным реляционным отношением. В этом уроке мы разберём три составляющих 1NF — атомарность значений, отсутствие repeating groups и наличие первичного ключа — и доведём конкретную «грязную» таблицу до 1NF.

Три требования первой нормальной формы

Таблица находится в 1NF, если выполняются три условия:

1. Атомарность значений. В каждой ячейке — одно неделимое значение. Никаких списков, массивов, наборов, вложенных структур в одной ячейке.
2. Отсутствие repeating groups. Нет повторяющихся групп столбцов вида phone1, phone2, phone3 — то есть нескольких столбцов под однотипные значения одного атрибута.
3. Наличие первичного ключа. У таблицы есть primary key — набор атрибутов, уникально и однозначно идентифицирующий каждую строку.

Первые два требования — про то, чтобы значение атрибута было одно и простое. Третье — про то, чтобы строки были различимы. Разберём каждое.

Атомарность: одно значение в ячейке

Самое частое нарушение 1NF — список значений, втиснутый в одну ячейку. Посмотрите на таблицу заказов:

ORDERS (нарушение 1NF)
order_id | customer | products                  | qtys
---------+----------+---------------------------+--------
1001     | Анна     | Клавиатура, Мышь, Коврик  | 2,1,3
1002     | Иван     | Монитор                   | 1

Столбец products хранит список товаров через запятую, qtys — список количеств. Это нарушение атомарности. Чем оно плохо конкретно:

• Поиск превращается в разбор строки. Запрос «все заказы с товаром Мышь» нельзя написать как WHERE product = 'Мышь'. Приходится использовать LIKE '%Мышь%' — а это найдёт ещё и «Мышь беспроводная», и сломается, если товар назвать «Коврик для мыши». СУБД не может применить индекс к подстроке внутри списка.
• Связь между списками неявная. То, что «Мышь» в products соответствует «1» в qtys, держится только на совпадении позиции. Стоит спискам разъехаться по длине — данные молча портятся, и СУБД этого не заметит.
• Агрегация невозможна штатно. Посчитать суммарное проданное количество товара нельзя обычным SUM — числа спрятаны внутри текстовой строки.
• Целостность не проверить. Нельзя поставить foreign key на товар: FK ссылается на значение столбца, а не на фрагмент строки внутри него.

Лекарство — одна строка на одно атомарное значение. Список разворачивается «вширь по строкам»:

ORDER_LINES (атомарно)
order_id | product     | qty
---------+-------------+----
1001     | Клавиатура  | 2
1001     | Мышь        | 1
1001     | Коврик      | 3
1002     | Монитор     | 1

Теперь WHERE product = 'Мышь' работает и использует индекс, связь товара со своим количеством явная (они в одной строке), SUM(qty) считается штатно, на product можно повесить foreign key.

Repeating groups: повторяющиеся столбцы

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

CUSTOMERS (нарушение 1NF: repeating group)
customer_id | name | phone1      | phone2      | phone3
------------+------+-------------+-------------+-------------
1           | Анна | 8-900-11-11 | 8-900-22-22 | NULL
2           | Иван | 8-901-33-33 | NULL        | NULL

phone1, phone2, phone3 — это repeating group: один атрибут «телефон» размазан по трём столбцам. Проблемы того же рода, что и со списком, плюс свои:

• Жёсткий лимит. Структура вмещает максимум три телефона. Появился четвёртый — нужно менять схему таблицы (ALTER TABLE ADD COLUMN phone4), а это правка определения таблицы, а не вставка данных.
• Разреженность и NULL. У большинства строк телефон один — значит phone2 и phone3 почти всегда NULL. Столбцы простаивают, занимая место.
• Запросы ветвятся. «Найти покупателя по номеру телефона» вместо одного предиката требует WHERE phone1 = ? OR phone2 = ? OR phone3 = ?. Добавили phone4 — придётся править все такие запросы.

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

CUSTOMERS                      CUSTOMER_PHONES
customer_id | name             customer_id | phone
------------+------            ------------+-------------
1           | Анна             1           | 8-900-11-11
2           | Иван             1           | 8-900-22-22
                               2           | 8-901-33-33

Теперь телефонов может быть сколько угодно без изменения схемы, NULL-ов нет, поиск по номеру — один предикат WHERE phone = ? с индексом. Связь «1:N покупатель — телефоны» выражена правильным реляционным способом: отдельной таблицей с внешним ключом.

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

Третье требование — у таблицы есть первичный ключ. Без PK строки неразличимы, и таблица допускает полные дубликаты:

ORDER_LINES (нет PK — допускает дубликаты)
order_id | product     | qty
---------+-------------+----
1001     | Мышь        | 1
1001     | Мышь        | 1     <- полный дубль; это ошибка или вторая позиция?

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

Первичный ключ устраняет неоднозначность. Он должен быть уникален, не содержать NULL ни в одном атрибуте и быть стабильным. Для ORDER_LINES естественный ключ — пара {order_id, product}: в одном заказе товар фигурирует один раз, а количество хранится в qty. С таким составным PK СУБД сама запретит дубликаты — повторная вставка {1001, Мышь} будет отклонена.

CREATE TABLE order_lines (
    order_id  INT  NOT NULL,
    product   TEXT NOT NULL,
    qty       INT  NOT NULL,
    PRIMARY KEY (order_id, product)
);

Если естественного ключа нет или он громоздкий, вводят surrogate key — искусственный идентификатор (обычно автоинкрементный integer). Что выбирать — natural или surrogate — тема отдельного урока про ключи; для 1NF важно одно: первичный ключ обязан существовать.

Почему нарушения 1NF так дорого обходятся

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

Разберём по слоям, что отказывает при нарушении атомарности:

• Реляционная алгебра. Операции выборки, проекции, соединения определены над атомарными значениями. Список в ячейке выводит таблицу за рамки модели — операторы к ней применять некорректно.
• Индексы. B-tree индексирует значение столбца целиком. Подстроку внутри списка он не индексирует — поиск по элементу списка обречён на полное сканирование.
• Ссылочная целостность. Foreign key ссылается на значение столбца. На элемент списка внутри ячейки FK поставить нельзя — связи между такими данными СУБД не контролирует.
• Ограничения. CHECK, UNIQUE рассчитаны на значение столбца, а не на содержимое списка. Проверить «каждый товар в списке существует» штатным ограничением невозможно.
• Агрегация и типизация. Список товаров и количеств хранится как текст; СУБД видит строку, а не числа и не сущности, — SUM, COUNT, сравнения по элементам не работают.

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

нарушена 1NF (список в ячейке)
   -> отказывает поиск по индексу
   -> отказывает foreign key на элемент
   -> отказывают CHECK / UNIQUE по элементам
   -> отказывает штатная агрегация
   => таблица перестаёт быть реляционным отношением

Сводим всё вместе

Возьмём таблицу, нарушающую 1NF по всем трём пунктам сразу:

ENROLLMENTS (нарушение 1NF)
student      | courses_taken              | grade1 | grade2 | grade3
-------------+----------------------------+--------+--------+-------
Анна Петрова | Математика, Физика, Химия  | 5      | 4      | 5
Иван Сидоров | История                    | 4      | NULL   | NULL

Дефекты: courses_taken — список в ячейке; grade1..grade3 — repeating group; первичного ключа нет, имя студента не идентификатор (тёзки неразличимы). Приводим к 1NF — разворачиваем список и группу оценок в строки, добавляем ключ:

ENROLLMENTS (1NF)
student_id | course      | grade        PK = {student_id, course}
-----------+-------------+------
1          | Математика  | 5
1          | Физика      | 4
1          | Химия       | 5
2          | История     | 4

Одно значение в ячейке, никаких пронумерованных столбцов, составной первичный ключ {student_id, course}. Таблица в 1NF. При этом она ещё далека от идеала: имя студента (если бы оно осталось) дублировалось бы в каждой строке его курсов — это уже задача для 2NF, и ею мы займёмся в следующем уроке.

Попробуй сам

Дана таблица библиотеки, нарушающая 1NF:

LOANS
reader_name  | book_titles                  | author1   | author2
-------------+------------------------------+-----------+----------
Анна П.      | Чистый код, Рефакторинг      | Мартин    | Фаулер
Иван С.      | SQL за 10 минут              | Форта     | NULL

Выполните:

1. Найдите все три нарушения 1NF в этой таблице и назовите каждое своим термином (неатомарное значение / repeating group / отсутствие PK).
2. Приведите таблицу к 1NF. Решите, какой первичный ключ выбрать, и обоснуйте, почему reader_name плохой кандидат в PK.
3. Объясните на конкретном примере запроса, почему до приведения к 1NF нельзя было нормально выполнить поиск «все выдачи книг автора Фаулер».