Нормализация БД на собеседовании системного аналитика

Зачем нормализация на собесе

Системный аналитик в любом проекте проектирует или критикует схемы БД. На собесе дадут кейс: «вот таблица заказов с колонками order_id, user_id, user_email, user_phone, items, item_prices — что не так и как исправить». Кандидат, который не различает 2НФ и 3НФ, путается в формулировках, не может назвать аномалии — теряет балл.

Главная боль без нормализации — таблица «заказы» с email клиента в каждой строке. Клиент сменил email — приходится обновлять миллион строк. Один забыли обновить — у клиента два email на одного юзера, в отчёте «уникальные email» — дубль. Через год — дата-джунгли, и аналитик идёт чистить.

Нормальные формы — формальные правила, которые предотвращают типовые проблемы хранения. На собесе достаточно знать 1НФ, 2НФ, 3НФ; BCNF — для middle+.

Аномалии денормализованной таблицы

Дано:

orders:
| order_id | user_id | user_email     | user_phone   | item_id | item_name | item_price |
|----------|---------|----------------|--------------|---------|-----------|------------|
| 1        | 100     | a@example.com  | +79991234567 | 5       | Книга     | 500        |
| 2        | 100     | a@example.com  | +79991234567 | 6       | Футболка  | 1500       |
| 3        | 200     | b@example.com  | +79997777777 | 5       | Книга     | 500        |

Три классические аномалии:

Insert anomaly. Нельзя добавить нового клиента без заказа: PK включает order_id, а у клиента без заказов его нет.

Update anomaly. Клиент 100 сменил email — апдейт в N строках. Если апдейтили в скрипте и сеть оборвалась — часть строк со старым, часть с новым.

Delete anomaly. Удалили все заказы клиента 200 — потеряли информацию о клиенте.

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

Первая нормальная форма

1НФ — каждое значение атомарно.

Нарушения:

• Список в одной ячейке: items = "5,6,7"
• Структурированное значение: address = "Москва, Ленина 15" без разбивки на колонки
• Повторяющиеся столбцы: phone1, phone2, phone3 для трёх телефонов

Решение: вынести списки в отдельную таблицу, структурированное — в колонки.

-- было:
| user_id | phones                    |
|---------|---------------------------|
| 1       | "+7999111, +7999222"      |

-- 1НФ:
user_phones:
| user_id | phone        |
|---------|--------------|
| 1       | +7999111     |
| 1       | +7999222     |

Тонкость JSON: колонка JSONB или JSON — формально нарушает 1НФ. На собесе спросят: «JSONB в Postgres — это 1НФ или нет?» Прагматичный ответ: «Формально нет, но если структура динамическая и не нужна реляционная целостность — приемлемо. Атрибуты, по которым ищем, лучше выносить в колонки».

Вторая нормальная форма

2НФ — таблица в 1НФ И каждый неключевой атрибут зависит от ВСЕГО первичного ключа, не только от его части.

Применяется только к таблицам с составным PK. Если PK — одна колонка, 2НФ автоматически выполняется (после 1НФ).

Пример нарушения:

order_items:
PK: (order_id, item_id)
| order_id | item_id | item_name | quantity |

item_name зависит только от item_id, не от пары (order_id, item_id) — нарушение 2НФ. Решение: вынести item_name в items:

order_items:                items:
| order_id | item_id | quantity |    | item_id | item_name |
                                     | 5       | Книга     |

Теперь имя товара хранится один раз. Меняем имя — апдейт одной строки.

Третья нормальная форма и BCNF

3НФ — таблица в 2НФ И нет транзитивных зависимостей: неключевой атрибут не зависит от другого неключевого.

Пример нарушения:

employees:
| emp_id | dept_id | dept_name  | dept_budget |
| 1      | 10      | IT         | 1000000     |

dept_name и dept_budget зависят от dept_id, не от emp_id. Транзитивная зависимость emp_id → dept_id → dept_name. Решение:

employees:               departments:
| emp_id | dept_id |     | dept_id | dept_name | dept_budget |
| 1      | 10      |     | 10      | IT        | 1000000     |

BCNF (Boyce-Codd) — расширение 3НФ. Каждая нетривиальная функциональная зависимость должна быть от суперключа. На практике почти любая 3НФ-таблица в BCNF; разница важна для редких кейсов с пересекающимися кандидатами в ключи.

Алгоритм нормализации до 3НФ:

1. Привести в 1НФ — атомарность.
2. Найти все функциональные зависимости (FD): что от чего зависит.
3. Если PK составной и есть зависимость от части — 2НФ нарушена. Вынести в отдельную таблицу.
4. Найти транзитивные зависимости. Вынести в отдельную таблицу.

На собесе ожидают, что кандидат может разобрать конкретный пример: «вот таблица, привести в 3НФ, обосновать каждый шаг».

Когда денормализуем

Нормализация — не догма. В DWH/OLAP осознанно денормализуют:

Star schema (Kimball). Факты — нормализованные, dimensions — широкие, с дублированием атрибутов.

fact_orders:
| order_id | user_sk | item_sk | order_date | amount |

dim_user:
| user_sk | user_id | name | email | city | country | continent |

continent денормализован (зависит от country). На запросах вместо JOIN с dim_country читаем готовое.

Зачем:

• JOIN дорог на больших фактах
• Колоночный движок (ClickHouse, Snowflake) хорошо сжимает повторяющиеся значения
• Аналитики пишут проще запросы

Цена:

• Update непросто (continent поменялся → апдейт во всех строках)
• Storage больше

Правило: OLTP — нормализуем (3НФ), OLAP/DWH — денормализуем точечно.

Кэшированные агрегаты. Колонка total_orders_count в users, обновляемая триггером. Денормализация ради скорости чтения. На собесе спросят: «как поддерживаем актуальность» — ответ: триггеры, периодический пересчёт, материализованные представления.

Частые ошибки

Нормализовать всё до 6НФ ради «академичности». В OLTP остановиться на 3НФ. Дальше — компромиссы.

Не использовать ER-диаграммы при проектировании. В ТЗ обязательно ERD: сущности, связи, кратность. Без неё разраб догадывается.

N:M связи без junction-таблицы. Студент учится на нескольких курсах, курс посещают много студентов — связь N:M реализуется через student_courses (student_id, course_id). Нельзя «массив course_id в строке студента».

Surrogate key vs natural key. Использовать surrogate (auto-increment, UUID) — устойчивее к изменениям. Natural key (например, ИНН) кажется удобным, но при изменениях в источнике (опечатка → исправили) FK в дочерних таблицах ломаются.

Соглашаться на «плоскую» таблицу, где надо нормализовать. На собесе кандидат соглашается «ну ладно, оставим email в каждой строке» — теряет балл. Нужно явно проговорить trade-offs.

Игнорировать NULLs в нормализации. Если поле часто пустое и не относится к большинству записей — кандидат на отдельную таблицу.

Не различать 2НФ и 3НФ. На собесе классический вопрос: «В чём разница?» 2НФ — про зависимость от части составного ключа. 3НФ — про транзитивность через неключевой атрибут.

Связанные темы

• ACID и уровни изоляции на собесе SA
• Подготовка к собесу системного аналитика
• Сoбеседование системного аналитика
• SQL vs NoSQL: когда что
• Как написать SQL-запрос

FAQ

Какую нормальную форму считать достаточной?

3НФ почти всегда. BCNF — формальное усиление 3НФ, на практике совпадает в 95% случаев. 4НФ и 5НФ — редкость, спрашивают только в академических собесах.

Денормализация — это всегда плохо?

Нет. В OLAP — норма (star schema). В OLTP — точечно ради скорости чтения, при соблюдении актуальности (триггеры, периодический пересчёт). Главное — осознанно, с обоснованием.

Как проверить, в какой нормальной форме таблица?

(1) Все ячейки атомарны — 1НФ. (2) Все неключевые атрибуты зависят от полного PK — 2НФ. (3) Нет транзитивных зависимостей через неключевые атрибуты — 3НФ. На собесе ожидается, что вы проговорите все три по конкретной таблице.

Что выбрать — surrogate или natural key?

Surrogate почти всегда. Natural — только для неизменяемых сущностей (страна по ISO-коду, валюта). Если есть риск, что natural key изменится в источнике — surrogate.

JSONB в Postgres нарушает нормализацию?

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

Это официальная информация?

Нет. Статья основана на работах Кодда (1970-1971), Дейта, Kimball и общей практике моделирования. Конкретные требования к нормализации зависят от компании и архитектора.

Тренируйте системный анализ — откройте тренажёр с 1500+ вопросами для собесов.