Раздел 4 — Нормализация и целостность данных

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

Возьмём таблицу поставок, в которую свалили всё сразу:

Первичный ключ здесь — пара (sup_id, prod_id): один поставщик поставляет один товар в определённом количестве. Из этого рождаются три беды.

Формальный инструмент нормализации — функциональная зависимость.

Зависимости выводятся не из данных, а из смысла предметной области. Если сегодня в таблице у каждого поставщика один город — это ещё не значит, что зависимость существует; нужно знать, что так устроена реальность.

Выпишем зависимости нашей таблицы:

sup_id            → sup_name, city, city_status
city              → city_status
prod_id           → prod_name
(sup_id, prod_id) → qty

Это условие вы уже знаете из раздела 2 как свойство отношения. Нарушают его двумя способами.

Способ первый — список в ячейке:

Способ второй — повторяющиеся группы столбцов:

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

Решение в обоих случаях одно — вынести многозначный атрибут:

CREATE TABLE clients (
    id   integer      PRIMARY KEY,
    name varchar(100) NOT NULL
);

CREATE TABLE client_phones (
    client_id integer     NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    phone     varchar(20) NOT NULL,
    PRIMARY KEY (client_id, phone)
);

2НФ имеет смысл только при составном первичном ключе. Если ключ простой, частичных зависимостей быть не может, и отношение автоматически во 2НФ.

В нашей таблице поставок ключ составной — (sup_id, prod_id). Находим частичные зависимости:

sup_id  → sup_name, city, city_status   ← зависит от части ключа
prod_id → prod_name                     ← зависит от части ключа
(sup_id, prod_id) → qty                 ← полная зависимость, всё в порядке

Каждый детерминант становится ключом новой таблицы:

-- Факты о поставщике
CREATE TABLE suppliers (
    sup_id      integer      PRIMARY KEY,
    sup_name    varchar(100) NOT NULL,
    city        varchar(100) NOT NULL,
    city_status integer      NOT NULL   -- транзитивная зависимость, уберём в 3НФ
);

-- Факты о товаре
CREATE TABLE products (
    prod_id   integer      PRIMARY KEY,
    prod_name varchar(200) NOT NULL
);

-- Факт о поставке — то, что действительно зависит от пары
CREATE TABLE supplies (
    sup_id  integer NOT NULL REFERENCES suppliers(sup_id),
    prod_id integer NOT NULL REFERENCES products(prod_id),
    qty     integer NOT NULL CHECK (qty > 0),
    PRIMARY KEY (sup_id, prod_id)
);

Аномалия вставки исчезла: нового поставщика теперь можно записать в suppliers до первой поставки. Аномалия удаления тоже: удаление поставки не трогает данные о поставщике.

В таблице suppliers осталась цепочка:

sup_id → city → city_status

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

Выделяем город в справочник:

CREATE TABLE cities (
    city   varchar(100) PRIMARY KEY,
    status integer      NOT NULL
);

CREATE TABLE suppliers (
    sup_id   integer      PRIMARY KEY,
    sup_name varchar(100) NOT NULL,
    city     varchar(100) NOT NULL REFERENCES cities(city)
);

Теперь статус Москвы хранится ровно в одном месте.

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

Различие проявляется редко — только когда у отношения несколько перекрывающихся потенциальных ключей. Классический пример: запись студентов на консультации.

Правила предметной области:

Отношение КОНСУЛЬТАЦИЯ(студент, дисциплина, преподаватель) имеет зависимости:

(студент, дисциплина) → преподаватель     ← потенциальный ключ
преподаватель         → дисциплина        ← детерминант, но НЕ ключ

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

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

CREATE TABLE teachers (
    teacher   varchar(100) PRIMARY KEY,
    subject   varchar(100) NOT NULL
);

CREATE TABLE consultations (
    student varchar(100) NOT NULL,
    teacher varchar(100) NOT NULL REFERENCES teachers(teacher),
    PRIMARY KEY (student, teacher)
);

Существуют также 4НФ (устраняет многозначные зависимости) и 5НФ (зависимости соединения). На практике встречаются редко; знать достаточно, что они есть и решают проблемы независимых многозначных фактов в одном отношении.

Нормализация борется с избыточностью, но платит за это соединениями: чем больше таблиц, тем больше JOIN в каждом запросе. Иногда цену признают слишком высокой и сознательно возвращают дублирование. Это называется денормализацией.

Целостность данных — соответствие содержимого базы правилам предметной области. Различают четыре вида.

Соберём весь арсенал на одном примере — таблице бронирования переговорных комнат.

CREATE TABLE bookings (
    id         integer     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id    integer     NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,
    user_id    integer     NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    starts_at  timestamptz NOT NULL,
    ends_at    timestamptz NOT NULL,
    purpose    varchar(200),
    status     varchar(20)  NOT NULL DEFAULT 'active',

    -- доменная целостность: статус из списка
    CONSTRAINT bookings_status_valid
        CHECK (status IN ('active', 'cancelled', 'completed')),

    -- бизнес-правило: бронь не может кончиться раньше, чем началась
    CONSTRAINT bookings_period_valid
        CHECK (ends_at > starts_at),

    -- бизнес-правило: бронь не длиннее 8 часов
    CONSTRAINT bookings_max_duration
        CHECK (ends_at - starts_at <= interval '8 hours')
);

Отдельно стоит ограничение EXCLUDE — оно решает задачу, которую UNIQUE решить не может: запрет пересечения интервалов.

-- Требуется расширение для операторов над диапазонами
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Одна комната не может быть забронирована дважды на пересекающееся время
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (
        room_id WITH =,
        tstzrange(starts_at, ends_at) WITH &&
    ) WHERE (status = 'active');