Урок 06.02: Нормализация базы данных

Цель урока

Освоить нормализацию реляционных баз данных: научиться приводить таблицы к 1НФ, 2НФ, 3НФ, понимать, какие реальные бизнес-проблемы решает каждая нормальная форма, уметь находить аномалии в данных и — что не менее важно — понимать, когда и почему от нормализации можно отступить в высоконагруженных системах.

---

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

Термин	Определение	Синоним
Нормализация	Процесс устранения избыточности данных и обеспечения их согласованности	—
Аномалия (Anomaly)	Проблема с данными, возникающая из-за плохой структуры таблицы	—
1НФ (1NF)	Первая нормальная форма: атомарные значения, нет повторяющихся групп	1NF
2НФ (2NF)	Вторая нормальная форма: 1НФ + нет частичной зависимости от составного ключа	2NF
3НФ (3NF)	Третья нормальная форма: 2НФ + нет транзитивных зависимостей	3NF
BCNF / НФБК	Усиленная 3НФ: каждый детерминант — кандидат в ключи	Нормальная форма Бойса-Кодда
Функциональная зависимость	Атрибут B функционально зависит от A, если каждому значению A соответствует ровно одно значение B	A → B
Денормализация	Намеренное отступление от нормальных форм ради производительности	Denormalization
Избыточность (Redundancy)	Хранение одного и того же факта в нескольких местах	Data duplication

---

1. Зачем нужна нормализация? Взгляд системного аналитика

1.1. Почему это не «техническая» тема, а тема для аналитика

Многие разработчики воспринимают нормализацию как рутинную техническую задачу: «разбить таблицы, добавить FK, готово». На самом деле нормализация — это способ формализовать бизнес-правила предметной области.

Пример связи:

Бизнес-правило	Отражение в нормализации
«У каждого заказа есть ровно один клиент»	FK order.client_id → client.id (1:N)
«Телефон клиента — свойство клиента, а не заказа»	3НФ — телефон хранится в client.phone, не в order
«Товар может быть в разных категориях»	N:M через промежуточную таблицу
«Цена товара — свойство товара, не позиции»	2НФ — цена зависит от товара, не от заказа

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

1.2. Цена ненормализованных данных

Ненормализованная БД приводит к конкретным бизнес-последствиям:

Проблема	Бизнес-последствие	Пример
Дублирование	Лишние затраты на хранение	1 млн строк × 10 дублей — лишние гигабайты
Аномалия Update	Отправка письма на старый email	Менеджер обновил email в одном месте, а в других — нет; клиент не получил уведомление
Аномалия Insert	Нельзя добавить преподавателя без курса	ВУЗ нанял нового профессора, но его нельзя внести в базу до начала семестра
Аномалия Delete	Потеря последнего заказа удалила клиента	Удалили последний заказ — потеряли контакт клиента навсегда
Отсутствие FK	«Висячие» ссылки	order.user_id = 42, а пользователя 42 уже нет — заказ ничей
Разные типы в колонке	Запросы с GROUP BY дают неверные суммы	В колонке price лежат числа и строки вида «уточнить»

---

2. Аномалии: глубокий разбор на живых примерах

2.1. Аномалия Insert (Insert Anomaly)

Определение: Невозможность добавить запись в таблицу из-за того, что часть обязательных атрибутов неизвестна, а Primary Key требует уникальности.

Пример 1: Клиент без заказа

Таблица (ненормализованная):

order_id (PK)	client_name	client_phone	product_name	quantity
1	Иванов	+7-912-111-11-11	Ноутбук	1
2	Петрова	+7-912-222-22-22	Стол	2

Ситуация отдела продаж:

Менеджер позвонил потенциальному клиенту — Сидорову. Сидоров сказал: «Я подумаю, перезвоню через неделю». Менеджер хочет занести Сидорова в базу, чтобы не забыть контакт.

❌ Попытка вставить:

INSERT INTO orders (client_name, client_phone) VALUES ('Сидоров', '+7-912-333-33-33');

Ошибка: order_id — PK, не может быть NULL. Менеджер не знает order_id, потому что заказ ещё не создан.

Что происходит в реальности:

• Менеджер вынужден создать «пустой» заказ (order_id = 0, product = NULL) — костыль
• Или хранит контакт в Excel — данные уходят из системы
• Или не записывает — забывает перезвонить, теряет продажу

Как нормализация решает:

После 2НФ client — отдельная таблица:

INSERT INTO client (name, phone) VALUES ('Сидоров', '+7-912-333-33-33');
-- ✅ Работает! Клиент добавлен без заказа.

Пример 2: ВУЗ — преподаватель без нагрузки

Таблица (ненормализованная): Расписание с PK = (course_id, semester).

Преподаватель принят на кафедру, но нагрузка ещё не сформирована (не назначен ни один курс).

❌ Нельзя вставить: потому что course_id — часть PK, NULL запрещён.

Реальная проблема: Бухгалтерия не может начислить оклад — «человека нет в системе».

Нормализованное решение: Таблица Instructor существует отдельно от Schedule.

---

2.2. Аномалия Update (Update Anomaly)

Определение: Необходимость изменять одни и те же данные в нескольких строках. Если обновить не все строки — данные становятся противоречивыми.

Пример 1: Смена телефона клиента

Таблица (ненормализованная):

order_id	client_name	client_phone	product	quantity
1	Иванов	+7-912-111-11-11	Ноутбук	1
2	Иванов	+7-912-111-11-11	Мышь	2
3	Иванов	+7-912-111-11-11	Коврик	1

Ситуация: Иванов сменил номер телефона на +7-912-000-00-00.

❌ Ошибка оператора:

UPDATE orders SET client_phone = '+7-912-000-00-00'
WHERE order_id = 1;

Результат: В заказе №1 — новый телефон, в заказах №2 и №3 — старый. Менеджер звонит по старому номеру — «абонент недоступен», клиент недоволен, заказ задерживается.

Как выглядит конфликт в БД:

order_id	client_name	client_phone	Старый?
1	Иванов	+7-912-000-00-00	✅ Новый
2	Иванов	+7-912-111-11-11	❌ Старый
3	Иванов	+7-912-111-11-11	❌ Старый

Как нормализация решает:

-- Обновляем в ОДНОМ месте
UPDATE client SET phone = '+7-912-000-00-00' WHERE id = 1;
-- ✅ Все заказы теперь видят новый телефон через JOIN.

Пример 2: Смена фамилии сотрудника

Сотрудница вышла замуж и сменила фамилию с Петрова на Иванова. В ненормализованной таблице Timesheet её фамилия могла повторяться в 500 строках (табели за год).

❌ Риск: UPDATE с неправильным WHERE обновит часть строк.

✅ Нормализация: Таблица Employee — одна строка с актуальной фамилией. Все табели ссылаются на employee_id.

Пример 3: Переименование отдела (бизнес-сценарий)

Отдел «Информационные технологии» переименован в «Цифровая трансформация».

Ненормализованная таблица «Сотрудники»:

employee_id	name	department
1	Анна	Информационные технологии
2	Борис	Информационные технологии
3	Виктор	Маркетинг

❌ Проблема:

-- Нужно обновить ровно 2 строки. Если забудут одну — будет путаница.
UPDATE employee SET department = 'Цифровая трансформация'
WHERE department = 'Информационные технологии';

При 500 сотрудниках в отделе — 500 строк для обновления. Одна ошибка в WHERE, и половина осталась со старым названием.

✅ Решение (3НФ):

-- Отдельная таблица department
UPDATE department SET name = 'Цифровая трансформация'
WHERE id = 1;
-- ✅ Одна строка, все сотрудники отдела получают новое название через JOIN.

---

2.3. Аномалия Delete (Delete Anomaly)

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

Пример 1: Последний заказ удаляет клиента

Таблица (ненормализованная):

order_id	client_name	client_phone	product
1	Петрова	+7-912-222-22-22	Ноутбук

Petrova — новый клиент, сделала один заказ. Через неделю она попросила отменить заказ (брак).

❌ Оператор удаляет:

DELETE FROM orders WHERE order_id = 1;

Результат: Данные Петровой потеряны. Через месяц она звонит в поддержку: «Я возвращала ноутбук, вы мне обещали замену». Оператор не находит её в системе — клиент недоволен, репутационные потери.

⏳ Более жизненный сценарий:

Ситуация с единственным поставщиком эксклюзивного товара:

• В таблице Shipments одна строка: поставщик X, товар Y, количество 100
• Товар Y снят с производства — удаляем последнюю поставку
• Вместе с поставкой удалился и поставщик X
• Через год поставщик X предлагает новый товар — менеджер его не находит в системе

Как нормализация решает:

Supplier — отдельная таблица, Shipment ссылается на supplier_id через FK. Удаление поставки не затрагивает поставщика.

Пример 2: Удаление последнего курса удаляет преподавателя

В таблице CourseAssignment хранится:

• (instructor_id, course_id, semester) — композитный PK
• Преподаватель вёл только один курс
• Курс отменён → удаляется строка → преподаватель исчез из базы

Реальная проблема: Бухгалтерия не может выплатить зарплату — «нет такого сотрудника».

---

2.4. Итоговая таблица аномалий

Аномалия	Суть	Аналогия вне IT	SQL-симптом
Insert	Нельзя добавить объект, пока он не связан с другим	Нельзя добавить клиента в CRM, пока он не купил	Вставка требует PK → NULL невозможен
Update	Нужно менять одно значение в N строках	ФИО написано на всех коробках на складе — если человек поменял имя, нужно переклеить все коробки	UPDATE без WHERE или с неправильным WHERE
Delete	При удалении теряются данные, которые не должны теряться	Сожгли единственную книгу учёта клиентов вместе с мусором	DELETE из таблицы с единственной записью

---

3. Первая нормальная форма (1НФ)

3.1. Три условия 1НФ

1. Атомарность значений: Каждое поле содержит одно неделимое значение (не список, не массив, не JSON)
2. Нет повторяющихся групп: Нет колонок с одинаковым смыслом (item1, item2, item3 — это антипаттерн)
3. Есть первичный ключ (может быть составным)

3.2. Пример нарушения: список товаров в одной колонке

Таблица «Заказ» (нарушение 1НФ):

order_id	client	items	prices
1	Иванов	Ноутбук, Мышь, Коврик	75000, 1500, 800

❌ Почему это плохо:

• Нельзя сделать WHERE items = 'Ноутбук' — пришлось бы использовать LIKE
• Нельзя посчитать SUM(prices) — данные в одной колонке
• Нельзя поставить FK на товар
• Невозможно добавить только один товар в заказ, не перезаписывая весь список

❌ Плохой вариант «исправления» — колонки с суффиксами:

order_id	client	item_1	item_2	item_3	price_1	price_2	price_3
1	Иванов	Ноутбук	Мышь	Коврик	75000	1500	800

❌ Всё ещё плохо:

• Сколько колонок предусмотреть? 10? 100? А если в заказе 101 товар?
• Невозможно запросить «все товары Иванова» — нужно указать все 10 колонок
• Пустые колонки тратят место

3.3. Правильное приведение к 1НФ

Вариант 1 (простой): Развернуть в отдельные строки:

order_id	client	item	price
1	Иванов	Ноутбук	75000
1	Иванов	Мышь	1500
1	Иванов	Коврик	800

Составной PK: (order_id, item) — гарантирует, что один и тот же товар не будет дважды в одном заказе.

✅ Теперь 1НФ выполнена. Можно фильтровать, считать, агрегировать.

3.4. Живой пример: CRM с номерами телефонов

Ситуация: В CRM хранятся контакты. Менеджер хочет записать 3 номера телефона контакта.

❌ Неправильно:

contact_id	name	phone_numbers
1	Иванов	"+7-111, +7-222, +7-333"

Всё в строке — нарушение 1НФ (не атомарно).

✅ Правильно:

contact_id	name

contact_id	phone_number	phone_type
1	+7-111	mobile
1	+7-222	work
1	+7-333	home

Бизнес-выгода: можно найти контакт по любому номеру (WHERE phone_number = '+7-222'), можно пометить «рабочий» и «личный», можно добавить четвёртый номер без изменения схемы.

---

4. Вторая нормальная форма (2НФ)

4.1. Условие 2НФ

Таблица находится в 2НФ, если:

1. Она в 1НФ
2. Нет частичной зависимости (partial dependency): каждый неключевой атрибут зависит от всего первичного ключа целиком, а не от его части

Частичная зависимость возможна только при составном PK. Если PK — одна колонка, таблица в 1НФ автоматически находится и в 2НФ.

4.2. Пример: Заказ с товарами и данными о клиенте

Таблица (PK = (order_id, item_id)):

order_id	item_id	item_name	item_price	quantity	client_name	client_phone
1	101	Ноутбук	75000	1	Иванов	+7-111
1	102	Мышь	1500	2	Иванов	+7-111
2	201	Стол	12000	1	Петрова	+7-222

Анализируем функциональные зависимости:

Атрибут	Зависит от	Это часть PK?	Тип зависимости
item_id	(order_id, item_id) — часть	✅ Сам PK	—
item_name	item_id	⚠️ Часть	ЧАСТИЧНАЯ
item_price	item_id	⚠️ Часть	ЧАСТИЧНАЯ
quantity	(order_id, item_id) — оба	✅ Весь PK	ПОЛНАЯ ✅
client_name	order_id	⚠️ Часть	ЧАСТИЧНАЯ
client_phone	order_id	⚠️ Часть	ЧАСТИЧНАЯ

❌ Проблемы:

1. item_name и item_price зависят только от item_id — если товар существует, его название повторяется в каждой строке заказа
2. client_name и client_phone зависят только от order_id — имя клиента дублируется в каждой позиции заказа
3. Если нужно изменить цену товара — придётся обновлять все строки со старыми заказами (но цена в прошлых заказах должна остаться исторической!)

4.3. Приведение к 2НФ

Разделяем на 4 таблицы:

Items (справочник товаров — актуальные данные):

item_id	item_name	current_price
101	Ноутбук	75000
102	Мышь	1500
201	Стол	12000

Orders (заказы — заголовок):

order_id	client_name	client_phone	order_date
1	Иванов	+7-111	2026-05-01
2	Петрова	+7-222	2026-05-02

OrderItems (содержимое заказа — только то, что зависит от всего ключа):

order_id	item_id	quantity	fixed_price_at_order
1	101	1	75000
1	102	2	1500
2	201	1	12000

Важный аналитический момент: fixed_price_at_order — это не item_price (который зависит от item_id), а цена на момент заказа (фиксация истории). Это осознанное решение: цена товара может меняться, но в старых заказах должна остаться та цена, по которой продали.

4.4. Аналитический взгляд: что мы на самом деле сделали?

До нормализации мы смешивали три разных бизнес-сущности в одной таблице:

Бизнес-объект	Вопрос	Куда вынесли
Товар	«Что это за товар? Сколько стоит сейчас?»	Items
Заказ	«Кто заказал? Когда?»	Orders
Факт покупки	«Что купили, по какой цене, сколько штук?»	OrderItems

Каждая сущность теперь имеет свой PK и свой жизненный цикл:

• Товар можно добавить до того, как его купили ✅ (нет Insert anomaly)
• Клиент может существовать без заказа (если вынести client отдельно) ✅
• Цена товара меняется — не затрагивает исторические заказы ✅

---

5. Третья нормальная форма (3НФ)

5.1. Условие 3НФ

Таблица находится в 3НФ, если:

1. Она в 2НФ
2. Нет транзитивной зависимости (transitive dependency): неключевой атрибут не должен зависеть от другого неключевого атрибута

Символически: если A → B и B → C, то A → C — это транзитивная зависимость. B должно быть ключом!

Формально: таблица в 3НФ, если для каждой функциональной зависимости X → Y:

• X — это суперключ (надмножество PK), ИЛИ
• Y — это часть минимального ключа

5.2. Пример: Заказ с данными клиента (продолжение)

Таблица Orders после 2НФ:

order_id (PK)	client_name	client_phone	client_email	order_date
1	Иванов	+7-111	ivanov@mail.com	2026-05-01
2	Петрова	+7-222	petrova@mail.com	2026-05-02
3	Иванов	+7-111	ivanov@mail.com	2026-05-10

Функциональные зависимости:

• order_id → client_name — ✅ зависит от PK
• order_id → client_phone — ⚠️ зависит от PK, но косвенно
• order_id → client_email — ⚠️ зависит от PK, но косвенно
• client_name → client_phone — ❌ транзитивная зависимость: телефон зависит от клиента, а клиент привязан к заказу

Проблема: Если Иванов сменит телефон, нужно обновить обе строки (order_id = 1 и 3) — Update anomaly возвращается!

5.3. Приведение к 3НФ

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

Customers:

customer_id	name	phone	email
1	Иванов	+7-111	ivanov@mail.com
2	Петрова	+7-222	petrova@mail.com

Orders (после 3НФ):

order_id	customer_id (FK)	order_date
1	1	2026-05-01
2	2	2026-05-02
3	1	2026-05-10

Теперь:

• ✅ Телефон хранится один раз в Customers
• ✅ Изменение телефона — UPDATE одной строки
• ✅ Все заказы клиента автоматически видят новый телефон через JOIN

5.4. Живой пример: Кафедры и преподаватели (реальный бизнес-сценарий)

Ситуация: В университете есть таблица Расписание.

До 3НФ:

schedule_id (PK)	course_name	instructor_name	instructor_phone	instructor_department
1	Математика	Сидоров	+7-111	Математический
2	Физика	Сидоров	+7-111	Математический
3	История	Иванова	+7-222	Гуманитарный

Проблемы:

1. Сидоров сменил кафедру с «Математический» на «Прикладная математика» — нужно обновить 2 строки
2. Сидоров сменил телефон — обновить 2 строки
3. Если Сидоров уволится — нужно удалить все его курсы (потеря расписания)

После 3НФ:

instructor_id	name	phone	department
1	Сидоров	+7-111	Прикладная математика
2	Иванова	+7-222	Гуманитарный

schedule_id	course_name	instructor_id (FK)
1	Математика	1
2	Физика	1
3	История	2

Результат: Смена кафедры Сидорова — 1 UPDATE. Увольнение — можно удалить преподавателя, но расписание останется (вопрос: что делать с курсами без преподавателя? — это бизнес-правило, которое аналитик должен обсудить отдельно).

---

6. Более высокие нормальные формы (для справки)

6.1. Нормальная форма Бойса-Кодда (BCNF / НФБК)

Условие: Каждый детерминант (атрибут, от которого функционально зависит другой атрибут) должен быть кандидатом в ключи.

Когда 3НФ, но не BCNF:

-- Студент может записаться на курс, который ведёт только один преподаватель.
-- Преподаватель может вести только один курс.
-- Ключ: (student_id, course_id)
-- Но: instructor → course (преподаватель определяет курс)
-- instructor — не часть ключа → нарушение BCNF

На практике: 99% таблиц в 3НФ автоматически в BCNF. Случаи, когда 3НФ ≠ BCNF, редки и требуют специфических бизнес-правил.

6.2. 4НФ — четвёртая нормальная форма

Условие: Нет многозначных зависимостей (multivalued dependency).

Пример нарушения 4НФ:

Сотрудник 1 знает языки: Английский, Немецкий. Имеет навыки: Java, SQL.

employee_id	language	skill
1	Английский	Java
1	Английский	SQL
1	Немецкий	Java
1	Немецкий	SQL

Проблема: 4 строки вместо 2+2 = 2. Избыточность из-за комбинирования независимых атрибутов.

Решение: Разделить на две таблицы — EmployeeLanguage и EmployeeSkill.

6.3. Когда аналитику достаточно 3НФ

Уровень	Когда нужен	Проектов
1НФ	Никогда (минимальный порог)	100%
2НФ	Только если есть составной PK	~70%
3НФ	Оптимальный уровень для большинства проектов	~90%
BCNF	Специфические бизнес-правила с перекрывающимися ключами	~5%
4НФ+	Многозначные независимые атрибуты	~1%

Золотое правило: Сначала нормализуйте до 3НФ. Если производительность не устраивает — измерьте и осознанно денормализуйте.

---

7. Пошаговый метод нормализации

Вход: ненормализованная таблица

student_id	student_name	course_name	instructor_name	instructor_phone
1	Анна	Математика	Сидоров	+7-912-333-33-33
1	Анна	Физика	Петров	+7-912-444-44-44
2	Борис	Математика	Сидоров	+7-912-333-33-33
2	Борис	История	Иванова	+7-912-555-55-55

Шаг 1. Приведение к 1НФ

✅ Уже атомарно, нет повторяющихся групп.

Определяем PK: Ни одна колонка не уникальна сама по себе. Только пара (student_id, course_name) уникальна → составной PK.

Шаг 2. Приведение к 2НФ

Ищем частичные зависимости (зависимости от части составного PK):

Атрибут	Зависит от	Часть PK?	Проблема
student_name	student_id	⚠️ Да (часть)	✅ Выносим
instructor_name	course_name	⚠️ Да (часть)	✅ Выносим
instructor_phone	course_name	⚠️ Да (часть)	✅ Выносим

Создаём таблицы:

Students:

student_id (PK)	student_name

Courses:

course_name (PK)	instructor_name	instructor_phone

Enrollments:

student_id (FK)	course_name (FK)
(PK = (student_id, course_name))

Шаг 3. Приведение к 3НФ

Ищем транзитивные зависимости: course_name → instructor_name → instructor_phone

instructor_phone зависит от instructor_name, а не от course_name. Если два курса ведёт один преподаватель — телефон дублируется.

Создаём таблицы:

Instructors:

instructor_id (PK)	instructor_name	instructor_phone

Courses (исправленная):

course_name (PK)	instructor_id (FK)

Результат: 5 таблиц вместо 1

[Students] ─1:N── [Enrollments] ─N:1── [Courses] ─N:1── [Instructors]

Нет аномалий:

• ✅ Insert: можно добавить преподавателя без курса
• ✅ Update: смена телефона преподавателя — одна строка
• ✅ Delete: удаление курса не удаляет преподавателя

---

8. Осознанная денормализация в Highload-системах

8.1. Голос Архитектора: почему нормализация — это дорого

Проблема нормализации для высоконагруженных систем:

-- 3НФ-запрос главной страницы (лента товаров):
SELECT p.id, p.title, p.price, c.name AS category_name,
       AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON r.product_id = p.id
WHERE p.status = 'active'
GROUP BY p.id, c.name
ORDER BY p.created_at DESC
LIMIT 50;

Что происходит на 10 000 RPS:

• JOIN категории — каждый раз читаем categories
• JOIN отзывов — GROUP BY + COUNT + AVG на каждую пачку товаров
• 4 таблицы → 4 блокировки, 4 набора IOPS

Стоимость в цифрах (условно):

Операция	1НФ (1 таблица)	3НФ (4 таблицы)
SELECT 50 товаров	1 чтение	4 чтения (JOIN)
Запись 1 заказа	1 INSERT	3 INSERT (order, order_item, log)
Кэширование	Просто (1 ключ)	Сложно (инвалидация нескольких таблиц)
Нагрузка на CPU (JOIN)	0	~0.05ms на JOIN

На масштабе: 10 000 RPS × 4 JOIN × 0.05ms = 2000ms CPU-времени только на JOIN. Это два ядра процессора, работающие постоянно только на соединение таблиц.

8.2. Голос Аналитика: почему нормализация — это безопасность

Риски денормализации:

Риск	Описание	Пример
Data inconsistency	Данные в разных местах расходятся	category_name в products = «Электроника», а в categories переименовали в «Электронные товары»
Обновление в нескольких местах	UPDATE должен затронуть N строк	Смена категории у 5000 товаров → UPDATE 5000 строк
Race condition	Два запроса обновляют данные одновременно	Один поток меняет цену, другой читает — получили старое значение
Рост размера БД	Дублирование данных	10 млн товаров × 50 байт дублирования = 500 МБ лишних
Lock contention	Блокировка строк при массовом обновлении	UPDATE категории блокирует 5000 строк — SELECT ждут

8.3. Типы осознанной денормализации

Тип 1. Pre-joined columns (добавление данных родительской таблицы)

Ситуация: Категория товара — один из самых частых запросов.

Нормализовано (3НФ):

SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id;

Денормализовано:

ALTER TABLE products ADD COLUMN category_name VARCHAR(255);

UPDATE products p
SET category_name = c.name
FROM categories c
WHERE p.category_id = c.id;

Выигрыш: −1 JOIN на каждый запрос списка товаров.

Риск: Если категорию переименуют — category_name в products устареет. Нужна синхронизация.

Тип 2. Агрегированные данные (pre-computed aggregates)

Ситуация: На странице товара нужно показать средний рейтинг и количество отзывов.

Нормализовано: AVG(r.rating) + COUNT(r.id) — каждый раз сканируем reviews.

Денормализовано:

ALTER TABLE products ADD COLUMN avg_rating NUMERIC(3,2) DEFAULT 0;
ALTER TABLE products ADD COLUMN review_count INTEGER DEFAULT 0;

Триггер синхронизации:

CREATE OR REPLACE FUNCTION update_product_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE products SET
        avg_rating = (SELECT AVG(rating) FROM reviews WHERE product_id = NEW.product_id),
        review_count = (SELECT COUNT(*) FROM reviews WHERE product_id = NEW.product_id)
    WHERE id = NEW.product_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_review_rating
AFTER INSERT OR UPDATE OR DELETE ON reviews
FOR EACH ROW EXECUTE FUNCTION update_product_rating();

Выигрыш: Чтение рейтинга — 1 SELECT вместо JOIN + GROUP BY + агрегация.

Риск: Триггер увеличивает время записи отзыва (~10ms → ~15ms). При 1000 отзывов/сек — дополнительная нагрузка.

Тип 3. Материализованные представления (Materialized Views) — компромисс

Лучший компромисс между нормализацией и производительностью:

CREATE MATERIALIZED VIEW mv_product_catalog AS
SELECT p.id, p.title, p.price, p.image_url,
       c.name AS category_name,
       AVG(r.rating) AS avg_rating,
       COUNT(r.id) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, c.name;

-- Обновление по расписанию (каждые 5 минут)
REFRESH MATERIALIZED VIEW mv_product_catalog;

Плюсы:

• Чтение — из одной таблицы (быстро)
• Исходные таблицы остаются в 3НФ
• Данные обновляются пачкой, не на каждый INSERT

Минусы:

• Stale data (отставание до 5 минут)
• Нужен планировщик (cron, pg_cron, по событию)

Тип 4. JSON/JSONB колонки как «escape hatch»

Ситуация: У товара может быть множество необязательных атрибутов (вес, размер, цвет, материал, страна производства), которые различаются по категориям.

Нормализовано (EAV — антипаттерн):

CREATE TABLE product_attributes (
    product_id UUID,
    attr_name VARCHAR(100),
    attr_value TEXT,
    PRIMARY KEY (product_id, attr_name)
);

Проблема: сложные запросы, медленно, много JOIN.

Денормализовано (JSONB в PostgreSQL):

ALTER TABLE products ADD COLUMN attributes JSONB;

-- Пример данных:
-- {"weight": "1.5 кг", "color": "чёрный", "material": "алюминий", "warranty": "2 года"}

Запрос:

SELECT * FROM products
WHERE attributes @> '{"color": "чёрный"}';

Плюсы: Гибкость, не нужно менять схему при добавлении атрибута.

Минусы: Нет FK-проверок внутри JSON, сложнее индексировать, риск невалидных данных.

8.4. Сводная таблица: техники денормализации

Техника	Выигрыш	Риск синхронизации	Когда применять
Pre-joined column	−1 JOIN	Высокий (обновление в 2 местах)	Редко меняемые данные (category_name)
Агрегаты (счётчики)	−GROUP BY	Средний (триггер/событие)	Рейтинг, кол-во отзывов, кол-во лайков
Materialized View	−все JOIN	Низкий (обновление по расписанию)	Отчёты, витрины данных, каталоги
JSONB колонка	−EAV JOIN	Средний (нет схемы)	Динамические атрибуты, настройки
Полное дублирование таблицы	−все JOIN	Очень высокий	Search-индексы (Elasticsearch), кэши (Redis)

8.5. Decision Framework: когда денормализовать?

Алгоритм для аналитика и архитектора:

flowchart TD
    A[Есть проблема с производительностью?] -->|Нет| B[Оставьте 3НФ. Не усложняйте.]
    A -->|Да| C[Измерьте. JOIN — реально bottleneck?]
    C -->|Нет, bottleneck в другом| B
    C -->|Да, JOIN тормозят| D[Какие данные дублировать?]
    D --> E{Данные редко меняются?}
    E -->|Да (категория, бренд)| F[Pre-joined column + триггер]
    E -->|Нет (рейтинг, цена)| G{Терпима ли задержка?}
    G -->|Да (5-15 минут)| H[Materialized View]
    G -->|Нет (нужна real-time точность)| I[Триггер или<br>CQRS + Eventual consistency]
    D --> J{Атрибуты динамические?}
    J -->|Да| K[JSONB колонка]
    J -->|Нет| L[Стандартные колонки]

8.6. Риски рассогласования и их mitigation

Риск	Описание	Mitigation
Stale data	Пользователь видит старую категорию после переименования	Acceptable delay for non-critical data. Использовать Materialized View с refresh
Partial update	Триггер упал — данные не синхронизировались	Мониторинг: проверять расхождение раз в сутки. Background job для сверки
Race condition	Два триггера одновременно обновили счётчик	Операции UPDATE ... SET counter = counter + 1 (атомарно)
Schema drift	JSON-атрибут со временем изменил структуру	Валидация на уровне приложения (JSON Schema)
Lock escalation	UPDATE 5000 строк блокирует таблицу	Chunked updates (по 100 строк). Online DDL

Пример: Acceptance Criteria для денормализации (шаблон):

## Денормализация: category_name в products
### Обоснование
- Запрос списка товаров (5000 RPS) JOIN категории даёт +5ms latency
- Категории меняются редко (~1 раз в квартал)

### Реализация
1. Добавить `category_name VARCHAR(255)` в `products`
2. Триггер на UPDATE/DELETE категории: обновить `category_name` во всех товарах
3. Scheduled job: раз в час проверять расхождение:
   ```sql
   SELECT p.id FROM products p
   JOIN categories c ON p.category_id = c.id
   WHERE p.category_name != c.name;

Допустимое рассогласование (SLA)

• Максимальное отставание: 1 час
• Допустимый процент расхождения: 0.01%
• Действие при превышении: алерт в PagerDuty

### 8.7. Пример «Аналитик + Архитектор»: диалог на реальном проекте

> **Проект:** Интернет-магазин. 1 млн товаров, 10 000 RPS на каталог.
> **Архитектор:** «Надо денормализовать — JOIN категории убивает CPU».
> **Аналитик:** «Понял. Категории меняются раз в месяц? Ок, давай pre-joined column. Но нам нужна обратная связь: если менеджер переименовал категорию, а в товарах осталось старое — это баг. Давай триггер или scheduled job на сверку. И в документации пропишем: максимальное рассогласование — 5 минут».
> **Архитектор:** «Идёт. Для рейтинга — триггер, latency записи отзыва +5ms — ок для нашего объёма».

**Итог:** Компромисс найден. Производительность выросла, риски описаны и приняты командой.

---

## 9. Пример: нормализация таблицы для Task Manager (сквозной кейс)

### Исходная (ненормализованная) таблица

| project_name | user_name | user_email | task_title | task_status | label_name |
|:------------:|:---------:|:----------:|:----------:|:-----------:|:----------:|
| Backend | Анна | anna@mail.com | Настроить БД | In Progress | Backend |
| Backend | Анна | anna@mail.com | Написать API | To Do | API |
| Frontend | Иван | ivan@mail.com | Сверстать форму | Done | UI |
| Backend | Пётр | petr@mail.com | Настроить CI/CD | To Do | DevOps |

### Анализ аномалий

| Аномалия | В этой таблице |
|----------|----------------|
| **Insert** | Нельзя создать проект без хотя бы одной задачи |
| **Update** | email Анны повторяется 2 раза — смена email = 2 UPDATE |
| **Delete** | Удаление единственной задачи проекта — потеря проекта |

### Нормализованная модель (3НФ)

**Projects:** `id (PK), name`
**Users:** `id (PK), name, email`
**Tasks:** `id (PK), project_id (FK), title, status, assignee_id (FK)`
**Labels:** `id (PK), name`
**TaskLabels:** `task_id (FK), label_id (FK)` — N:M связь

**SQL DDL:**

```sql
CREATE TABLE projects (
    id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL
);

CREATE TABLE users (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title       VARCHAR(255) NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'to_do',
    assignee_id UUID REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE labels (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE task_labels (
    task_id  UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    label_id UUID NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
    PRIMARY KEY (task_id, label_id)
);

---

10. Вопросы для самопроверки

Базовый уровень

1. Какие три аномалии возникают в ненормализованной таблице? Приведите реальный пример каждой.
2. Какие два условия должны быть выполнены для 1НФ? Что такое «атомарность» на практике?
3. Что такое частичная зависимость? При каком условии она возможна? Какая нормальная форма её устраняет?
4. Что такое транзитивная зависимость? Приведите пример из жизни (не IT).
5. Почему 3НФ достаточно для 90% проектов?
6. Можно ли быть в 1НФ, но не в 2НФ? Приведите пример с составным PK.

Продвинутый уровень

7. Ситуация: В таблице Orders есть атрибут customer_name. Вы говорите: «Это нарушение 3НФ!» Вам отвечают: «Но это ускоряет запрос на 30%». Как вы примете решение?
8. Денормализация: Предложите две разные техники денормализации для страницы «Товар» (рейтинг, категория, похожие товары). Оцените риски каждой.
9. Materialized View: В чём разница между VIEW и MATERIALIZED VIEW? Когда MV — идеальное решение?
10. EAV vs JSONB: В проекте каталог товаров с 50+ различными характеристиками. Что выберете — нормализацию (EAV с таблицей атрибутов) или JSONB-колонку? Почему?
11. Race condition: Как избежать ситуации, когда два пользователя одновременно лайкают товар, и счётчик лайков показывает 1 вместо 2?
12. Sync strategy: В таблицу categories добавили колонку slug (URL-символьный код). Категории переименовывают. Нужно ли синхронизировать slug во всех товарах этой категории? Если да — как?
13. Анализ компромисса: Вы работаете над банковской системой. Архитектор предлагает денормализовать баланс счёта (хранить в таблице account.balance и в транзакциях). Ваши аргументы ЗА и ПРОТИВ?

---

11. Практическое задание

Задание 1. Нормализация до 3НФ (2 балла)

Дана ненормализованная таблица из регистратуры поликлиники:

doctor_id	doctor_name	specialization	patient_id	patient_name	appointment_date	diagnosis	treatment
1	Иванова	Терапевт	101	Петров А.С.	2026-05-20	ОРВИ	Парацетамол
1	Иванова	Терапевт	102	Сидорова М.И.	2026-05-20	Гипертония	Эналаприл
2	Петров	Хирург	103	Кузнецов В.П.	2026-05-21	Перелом	Гипс
1	Иванова	Терапевт	101	Петров А.С.	2026-05-25	Контроль	—
3	Смирнова	Окулист	101	Петров А.С.	2026-06-01	Близорукость	Очки

Выполните:

1. Определите текущую нормальную форму (0НФ? 1НФ?) — объясните, почему.
2. Приведите к 1НФ — укажите, что пришлось изменить.
3. Найдите PK — какой составной ключ можно предложить?
4. Приведите к 2НФ — перечислите частичные зависимости, создайте таблицы.
5. Приведите к 3НФ — найдите транзитивные зависимости, создайте таблицы.
6. Напишите SQL DDL (CREATE TABLE) для каждой таблицы в 3НФ с PK и FK.

Подсказка: Обратите внимание, что appointment_date — это свойство приёма, а не врача. diagnosis и treatment — свойства приёма, зависящие от всего составного ключа.

Задание 2. Определите нормальную форму (1 балл)

Для каждой таблицы определите, в какой нормальной форме она находится (0НФ, 1НФ, 2НФ, 3НФ). Если не в 3НФ — опишите проблему и предложите исправление.

Таблица А:

id	name	phone_numbers
1	Анна	+7-111-111, +7-222-222

Таблица Б:

student_id	course_code	student_name	course_name	grade
1	CS101	Анна	Программирование	5
1	CS102	Анна	Базы данных	4
2	CS101	Борис	Программирование	3

Таблица В:

id	name	department_name	department_phone
1	Анна	IT	12-34
2	Борис	IT	12-34
3	Виктор	HR	56-78

Таблица Г (сложный случай):

employee_id	skill	language	skill_level	language_level
1	Java	Английский	5	C1
1	SQL	Английский	4	C1
1	Java	Немецкий	5	A2
1	SQL	Немецкий	4	A2

Вопрос: Какая нормальная форма у таблицы Г? Какая аномалия здесь есть? (Сложный случай — подсказка: подумайте про 4НФ).

Задание 3. Денормализация для Highload (1 балл)

Контекст: Вы проектируете БД для ленты новостей социальной сети (как VK/Twitter).

3НФ-модель:

Post (id, author_id, text, created_at)
User (id, name, avatar_url)
Like (user_id, post_id, created_at)
Comment (id, post_id, author_id, text, created_at)

Нагрузка: Лента новостей — 100 000 запросов в минуту. Каждый запрос загружает 30 постов с именем автора, аватаром, количеством лайков, количеством комментариев и превью последнего комментария.

Задания:

1. Выявите bottleneck-запрос — напишите SQL, который нужен для получения ленты в 3НФ. Сколько JOIN он содержит?
2. Предложите 2 варианта денормализации. Для каждого укажите:
   • Какие данные дублируются и куда
   • Выигрыш в производительности (сколько JOIN убирается)
   • Риски рассогласования и способ синхронизации
3. Выберите лучший вариант с учётом того, что:
   • Количество лайков должно быть точным (±0)
   • Имя и аватар автора — стабильны (меняются редко)
   • Pre-joined column + триггер допустимы

Ожидаемый формат ответа:

### Вариант 1: Pre-joined колонки в Post
- Добавить: author_name, author_avatar_url, like_count, comment_count
- Выигрыш: −4 JOIN
- Риски: ... 
- Синхронизация: ...

### Вариант 2: Materialized View
...

### Выбор: Вариант 1, потому что ...

---

12. Дополнительные материалы

• Книга: К. Дж. Дейт — «Введение в системы баз данных» — классика по нормализации
• Книга: Мартин Фаулер — «Шаблоны корпоративных приложений» — раздел о денормализации и Unit of Work
• Книга: Мартин Клеппман — «Высоконагруженные приложения» — глава 5 (репликация, согласованность), глава 12 (CQRS)
• Статья: «Database Normalization Explained in Simple English» — essentialsql.com
• Статья: «When to Denormalize in a Database» — несколько подходов на medium.com
• Документация PostgreSQL: Materialized Views — postgresql.org/docs/current/sql-creatematerializedview.html
• Документация PostgreSQL: JSONB — postgresql.org/docs/current/datatype-json.html
• Шпаргалка: «Normal Forms Cheat Sheet» — таблица 1НФ–BCNF с примерами
• Практика: Нормализовать «Телефонную книгу» (Контакты → Группы → Номера)
• Практика: Спроектировать БД для интернет-магазина (3НФ), а затем осознанно денормализовать каталог для highload