Нормализация

Нормализация¶

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

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

Без нормализации данные дублируются, а дубликаты рано или поздно расходятся. Три классические аномалии:

Нормализация устраняет эти аномалии, разбивая данные на связанные таблицы.

Ключевые понятия¶

Прежде чем разбирать формы, уточним терминологию:

1NF — первая нормальная форма¶

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

Пример: таблица products из etl_kitchen_db¶

Столбец attributes типа JSONB хранит сразу несколько характеристик товара — с точки зрения классической теории это нарушение 1NF:

SELECT product_id, name, attributes
FROM products
WHERE product_id IN (1, 4);

Приведение к 1NF¶

Выносим атрибуты в отдельную таблицу, где каждому атрибуту соответствует одна строка:

CREATE TABLE product_attributes_1nf (
    product_id  INT REFERENCES products(product_id),
    attr_name   TEXT,
    attr_value  TEXT,
    PRIMARY KEY (product_id, attr_name)
);

INSERT INTO product_attributes_1nf (product_id, attr_name, attr_value)
SELECT product_id, key, value
FROM products, jsonb_each_text(attributes);

Теперь ты можешь отфильтровать все товары Apple обычным WHERE attr_value = 'Apple' без JSONB-функций.

2NF — вторая нормальная форма¶

Правило: таблица в 1NF + каждый неключевой атрибут полностью зависит от всего составного ключа, а не от его части.

2NF актуальна только для таблиц с составным ключом. Если у тебя простой PK (один столбец) и таблица в 1NF — она автоматически в 2NF.

Пример: order_items с лишним полем¶

Представь, что в order_items добавили product_name для удобства. Ключ — {order_id, product_id}:

-- Антипаттерн: частичная зависимость
CREATE TABLE bad_order_items (
    order_id     INT REFERENCES orders(order_id),
    product_id   INT REFERENCES products(product_id),
    product_name TEXT,     -- зависит только от product_id!
    quantity     INT,
    PRIMARY KEY (order_id, product_id)
);

product_name дублируется в каждой строке. Переименуешь товар — обновлять придётся везде. Забудешь одну строку — аномалия обновления.

Декомпозиция¶

-- order_items: только связи и данные, зависящие от всего ключа
CREATE TABLE order_items_fixed (
    order_id   INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity   INT,
    PRIMARY KEY (order_id, product_id)
);

-- products: описание товара в одном месте
CREATE TABLE products_fixed (
    product_id INT PRIMARY KEY,
    name       TEXT
);

Название товара хранится ровно в одном месте — классический принцип «ничего, кроме ключа».

3NF — третья нормальная форма¶

Правило: таблица в 2NF + нет транзитивных зависимостей. Неключевой атрибут не должен зависеть от другого неключевого атрибута.

По формуле Уильяма Кента: поле содержит факт «о ключе, о всём ключе и ни о чём, кроме ключа».

Пример: customers из etl_kitchen_db¶

В таблице customers есть поля city и region. Если город однозначно определяет регион (Москва → Центральный), возникает цепочка:

1. customer_id → city — знаем город клиента
2. city → region — город определяет регион
3. customer_id → region — транзитивная зависимость через город

SELECT customer_id, name, city, region
FROM customers
WHERE city = 'Москва';

Регион Центральный дублируется для каждого москвича.

Декомпозиция¶

-- Справочник городов
CREATE TABLE cities (
    city_name   TEXT PRIMARY KEY,
    region_name TEXT
);

-- Клиенты ссылаются на город
CREATE TABLE customers_3nf (
    customer_id INT PRIMARY KEY,
    name        TEXT,
    city        TEXT REFERENCES cities(city_name)
);

Теперь регион хранится в одном месте — в справочнике cities.

BCNF — форма Бойса-Кодда¶

Усиленная 3NF: каждый детерминант (левая часть функциональной зависимости) является суперключом. 3NF допускает исключения для ключевых атрибутов, BCNF — нет.

На практике 3NF = BCNF для большинства таблиц. Разница возникает при перекрывающихся составных ключах.

Классический пример: бронирование кортов¶

Зависимости:

• {court, start_time} → rate_type — пара определяет тариф
• rate_type → court — каждый тариф привязан к конкретному корту

Потенциальные ключи: {court, start_time} и {rate_type, start_time}. Все атрибуты входят в ключи — 3NF соблюдена. Но rate_type → court, а rate_type сам по себе не суперключ — BCNF нарушена.

Исправление: разбить на rate_types(rate_type, court) и bookings(rate_type, start_time).

Денормализация¶

Денормализация — осознанное добавление избыточности для ускорения чтения. Ты жертвуешь производительностью записи, чтобы избежать тяжёлых JOIN в аналитических запросах.

Пример: витрина продаж по категориям¶

CREATE MATERIALIZED VIEW category_sales_mart AS
SELECT
    c.name            AS category_name,
    SUM(oi.quantity)  AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(DISTINCT oi.order_id)      AS orders_count
FROM order_items oi
JOIN products p   ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.name;

-- Обновление после загрузки новых данных:
REFRESH MATERIALIZED VIEW category_sales_mart;

Антипаттерн: денормализация в OLTP¶

Хранить customer_name прямо в таблице orders при 1000 записей в секунду — плохая идея. Каждое обновление имени клиента требует обновления всех его заказов, а рассинхронизация данных — вопрос времени.

Нормализация vs модели DWH¶

Как определить нормальную форму¶

1. Есть повторяющиеся группы, массивы или JSON в ячейках? → Не 1NF
2. Есть составной ключ и часть атрибутов зависит только от части ключа? → Не 2NF
3. Есть атрибут, который зависит от другого неключевого атрибута? → Не 3NF
4. Есть детерминант, который не является суперключом? → Не BCNF

Что запомнить¶

• Нормализация защищает от аномалий вставки, обновления и удаления
• 1NF — атомарные значения, нет повторяющихся групп (массивов, JSON)
• 2NF — все поля зависят от всего составного ключа, а не от его части
• 3NF — неключевые поля не зависят друг от друга (нет транзитивных зависимостей)
• BCNF — каждый детерминант является суперключом (актуально при перекрывающихся составных ключах)
• Для OLTP — нормализуй до 3NF, для DWH — денормализуй в Star Schema / витрины
• Главное правило DE: нормализуй на входе (staging), денормализуй на выходе (mart)

Попробуй сам¶

Интерактивная песочница с настоящим PostgreSQL прямо в браузере. База etl_kitchen_db уже загружена — попробуй развернуть JSONB в плоскую таблицу или найти транзитивные зависимости.

Проверь себя¶

Источники¶

• Database normalization — Wikipedia — формальные определения нормальных форм (1NF–BCNF)
• PostgreSQL: Constraints — ограничения целостности и внешние ключи
• Kimball Group: Dimensional Modeling Techniques — денормализация в контексте DWH