Нормализация и проектирование схемы

Нормализация и проектирование схемы

Главная идея

Как это выглядит на практике

1. Ты проектируешь систему управления сотрудниками. Начинаешь с одной таблицы employees: id, name, email, phone1, phone2, phone3, department_name, manager_name. Кажется удобным — всё в одном месте.
2. Через месяц появляется требование хранить произвольное количество телефонов. Столбцы phone1, phone2, phone3 не справляются — нарушение 1NF. Создаёшь отдельную таблицу employee_phones(employee_id, phone, type). Первый урок нормализации.
3. HR просит переименовать отдел 'Engineering' в 'Software Engineering'. Пишешь UPDATE employees SET department_name = 'Software Engineering' WHERE department_name = 'Engineering' — обновляется 847 строк. На следующий день видишь в данных 'Engineering' у троих — пропустили строки с опечатками в исходном названии. Аномалия обновления в действии.
4. Решаешь вынести departments в отдельную таблицу: departments(id, name), в employees остаётся department_id FK. Теперь переименование — одна строка в departments. Это 3NF.
5. Проектируешь систему курсов: студент может записаться на много курсов, курс может иметь много студентов. Создаёшь enrollments(student_id, course_id, enrolled_at, grade). При удалении студента — ON DELETE CASCADE убирает записи. Правильная many-to-many через join-таблицу.
6. Аналитик просит добавить в список заказов столбец 'сумма заказа'. Текущий запрос требует JOIN order_items + SUM + GROUP BY — медленно при 1M заказов. Добавляешь total_amount в orders с триггером для поддержания актуальности. Осознанная денормализация с документированием.
7. Коллега предлагает хранить теги товара в JSON-массиве внутри products.tags = ['электроника', 'смартфоны']. Спрашиваешь: нужно ли искать товары по тегу? Нужно ли считать количество товаров в каждом теге? Если да — создаёшь отдельную таблицу tags и product_tags. Если только отображение — JSONB может быть оправдан.
8. Добавляешь UUID как первичный ключ для таблицы событий аналитики — нужна глобальная уникальность между сервисами. Через неделю замечаешь что INSERT в эту таблицу замедлился. EXPLAIN ANALYZE показывает random I/O при обновлении индекса. Переходишь на UUID v7 (time-ordered) — проблема исчезает.
9. На code review видишь таблицу: invoices(id, client_id, client_name, client_email, client_address). Объясняешь: client_name, client_email, client_address зависят от client_id (транзитивно) — нарушение 3NF. Предлагаешь вынести в clients или хранить снимок данных клиента на момент выставления счёта (что может быть оправдано для юридических документов).
10. Проектируешь схему для OLAP-аналитики (ClickHouse или BigQuery). Нормализация здесь вредна: аналитические БД оптимизированы для широких денормализованных таблиц. Создаёшь fact_orders с денормализованными dimension-полями: user_country, product_category, etc. OLAP и OLTP — разные требования к нормализации.
11. Пишешь миграцию для добавления NOT NULL колонки в таблицу с 5M строк. Понимаешь что ALTER TABLE заблокирует таблицу. Правильный подход: ADD COLUMN с DEFAULT, затем UPDATE батчами, затем NOT NULL constraint. Нормализованная схема облегчает такие операции — изменений меньше мест.
12. На собеседовании просят спроектировать схему для системы бронирования отелей. Начинаешь с сущностей: hotels, rooms, guests, bookings. Проговариваешь функциональные зависимости вслух, определяешь составные ключи, объясняешь почему room_price хранится в bookings (снимок цены), а не только в rooms. Понимание нормализации позволяет принимать обоснованные решения.

Примеры кода

Диагностика нарушений нормальных форм

-- НАРУШЕНИЕ 1NF: неатомарные значения
-- Плохо: телефоны в одной строке
CREATE TABLE users_bad (
  id         SERIAL PRIMARY KEY,
  name       TEXT,
  phones     TEXT  -- '79001234567, 79009876543'
);

-- Плохо: повторяющиеся группы столбцов
CREATE TABLE orders_bad (
  id         SERIAL PRIMARY KEY,
  product1   TEXT,
  qty1       INT,
  product2   TEXT,
  qty2       INT
);

-- Хорошо 1NF: отдельные таблицы
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  phone   TEXT NOT NULL,
  type    TEXT CHECK (type IN ('mobile', 'work', 'home'))
);

-- НАРУШЕНИЕ 2NF: частичная зависимость от составного ключа
-- Плохо: product_name зависит только от product_id, не от (order_id, product_id)
CREATE TABLE order_items_bad (
  order_id     INT,
  product_id   INT,
  product_name TEXT,   -- нарушение 2NF!
  quantity     INT,
  PRIMARY KEY (order_id, product_id)
);

-- Хорошо 2NF: product_name в таблице products
CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10,2)
);

CREATE TABLE order_items (
  order_id   INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,  -- снимок цены на момент заказа
  PRIMARY KEY (order_id, product_id)
);

3NF, BCNF и практические паттерны

-- НАРУШЕНИЕ 3NF: транзитивная зависимость
-- employees.department_name зависит от department_id (неключевого),
-- а не напрямую от id сотрудника
CREATE TABLE employees_bad (
  id              SERIAL PRIMARY KEY,
  name            TEXT,
  department_id   INT,
  department_name TEXT,  -- нарушение 3NF!
  department_head TEXT   -- нарушение 3NF!
);

-- Хорошо 3NF: вынести зависимые данные в отдельную таблицу
CREATE TABLE departments (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  head_employee_id INT  -- circular reference — осторожно с FK
);

CREATE TABLE employees (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hired_at      DATE NOT NULL DEFAULT CURRENT_DATE
);

-- MANY-TO-MANY через join-таблицу с атрибутами
CREATE TABLE students (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE courses (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  max_capacity INT DEFAULT 30
);

-- Join-таблица с дополнительными атрибутами — не просто связь
CREATE TABLE enrollments (
  student_id    INT REFERENCES students(id) ON DELETE CASCADE,
  course_id     INT REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at   TIMESTAMPTZ DEFAULT NOW(),
  grade         NUMERIC(3,1) CHECK (grade BETWEEN 0 AND 10),
  status        TEXT DEFAULT 'active' CHECK (status IN ('active', 'completed', 'dropped')),
  PRIMARY KEY (student_id, course_id)
);

-- Проверка аномалии обновления: ПРАВИЛЬНО (3NF)
-- Переименовать департамент — одна строка
UPDATE departments SET name = 'Engineering & Data' WHERE id = 5;
-- Сотрудники автоматически видят новое название через JOIN

-- Проверка аномалии обновления: НЕПРАВИЛЬНО (нарушение 3NF)
-- Переименовать департамент — тысячи строк
UPDATE employees_bad SET department_name = 'Engineering & Data'
WHERE department_id = 5;
-- Если пропустишь хоть одну строку — данные несогласованны

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

-- Нормализованная схема (правильно для OLTP)
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  status     TEXT DEFAULT 'pending'
  -- НЕТ total_amount — считаем через SUM от order_items
);

-- Получение суммы заказа (нормализованный запрос)
SELECT
  o.id,
  o.created_at,
  SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.created_at;

-- Денормализация: кэшируем total_amount в orders
ALTER TABLE orders ADD COLUMN total_amount NUMERIC(10,2);

-- Поддерживаем кэш через триггер
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE orders
  SET total_amount = (
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
  )
  WHERE id = COALESCE(NEW.order_id, OLD.order_id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_items_total_trigger
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

-- Теперь список заказов без JOIN
SELECT id, created_at, total_amount FROM orders ORDER BY created_at DESC LIMIT 20;

-- UUID vs BIGSERIAL: практические компромиссы
-- UUID v4 (случайный) — плохо для первичного ключа в B-tree индексе
CREATE TABLE events_uuid (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,  -- фрагментация индекса
  data JSONB
);

-- BIGSERIAL — последовательный, эффективный для B-tree
CREATE TABLE events_serial (
  id   BIGSERIAL PRIMARY KEY,
  data JSONB
);

-- UUID v7 (time-ordered) — компромисс: глобально уникален + последователен
-- Доступен через расширение или приложение

Что происходит под капотом

• Функциональная зависимость (A → B): для каждого значения A существует ровно одно значение B. Нормальные формы определяют допустимые функциональные зависимости между атрибутами таблицы.
• 1NF: атомарность ячеек означает что СУБД может индексировать, сортировать и фильтровать по значению без парсинга строк. JSON-массив в PostgreSQL — технически атомарен с точки зрения хранения, но логически нарушает 1NF если нужна фильтрация по элементам.
• 2NF актуальна при составном первичном ключе. При суррогатном BIGSERIAL PK нарушение 2NF невозможно по определению — один атрибут не может быть 'частью' скалярного ключа.
• 3NF и BCNF: BCNF (Boyce-Codd Normal Form) строже 3NF — требует что каждый детерминант является потенциальным ключом. На практике разница проявляется в схемах с несколькими перекрывающимися кандидатными ключами, что редко.
• Аномалии обновления (update anomaly): при изменении данных нужно обновить несколько строк. Если операция прервётся — данные становятся несогласованными. Нормализация делает такие операции атомарными по природе.
• Аномалия вставки (insert anomaly): невозможно вставить одну сущность без другой. В ненормализованной employees(id, name, department_name) нельзя записать информацию о новом пустом департаменте.
• Аномалия удаления (delete anomaly): удаление одной сущности уничтожает информацию о другой. Удаление последнего сотрудника из ненормализованной таблицы уничтожает информацию о департаменте.
• B-tree индекс и UUID: B-tree требует вставки в отсортированном порядке для эффективности. Случайные UUID (v4) вызывают random write — страница индекса читается с диска, модифицируется, записывается обратно. При высокой нагрузке это thrashing. UUID v7 monotonic решает проблему.
• Денормализация через триггер vs через приложение: триггер гарантирует консистентность при любом источнике изменений (прямой SQL, миграции, другие сервисы). Денормализация через приложение — риск рассинхронизации при нескольких точках записи.
• JSONB для гибкой схемы: PostgreSQL JSONB позволяет хранить структурированные данные с переменными полями. Можно индексировать (GIN индекс), фильтровать (->>, @>), но нельзя ссылаться через FK. Это компромисс между нормализацией и гибкостью — правильный выбор для полиморфных данных.

Термины урока

Мини-разбор реальной ситуации

E-commerce команда хранит категорию товара прямо в таблице products: category_name TEXT. Всё работает пока категорий мало. Маркетинг решает переименовать 'Электроника' в 'Бытовая электроника' перед праздниками. Запрос UPDATE products SET category_name = 'Бытовая электроника' WHERE category_name = 'Электроника' затрагивает 200 000 строк, блокирует таблицу на 8 секунд в прайм-тайм, и всё равно оставляет 340 строк с вариантами 'Электроника ' (с пробелом) и 'электроника' (строчная). После инцидента: создают таблицу categories(id, name, slug), добавляют category_id FK в products, мигрируют данные. Теперь переименование — одна строка: UPDATE categories SET name = 'Бытовая электроника' WHERE id = 3. Мгновенно, без блокировок, без несогласованности.