Базовое моделирование данных
Данные можно организовать по-разному. Одна и та же сущность «заказ» может жить в виде:
- Одной большой таблицы с дублями.
- Трёх связанных таблиц через foreign keys.
- Семи таблиц в нормализованной модели.
- Star schema из fact и dimensions.
Разные подходы — для разных задач. Этот урок — про нормализацию (как OLTP делает) и денормализацию (как DWH/OLAP делает). Дальше будет глубже в M07.
Зачем моделирование
Без правильной модели данные превращаются в:
- Дубликаты — один клиент записан в 5 местах с разными именами.
- Несогласованность — в одной таблице цена 100, в другой 99.99.
- Аномалии обновления — изменил адрес клиента в одном месте, забыл в другом.
- Анализ невозможен — JOIN на 15 таблиц для простого вопроса.
Моделирование — это дисциплина организации данных, чтобы решить эти проблемы.
Нормализация: для OLTP
Нормализация — теоретический набор правил (нормальных форм), как организовать таблицы, чтобы минимизировать дублирование и избегать аномалий.
Придумал Эдгар Кодд в 1970-х (он же создал реляционную модель).
Пример: до нормализации
«Все в одной таблице»:
| order_id | customer_name | customer_email | product | category | price |
|----------|---------------|------------------|---------------|------------|-------|
| 1 | Alice | [email protected] | iPhone 15 | Phones | 999 |
| 2 | Alice | [email protected] | AirPods | Audio | 199 |
| 3 | Bob | [email protected] | iPhone 15 | Phones | 999 |Проблемы:
- Дубликаты: Alice повторяется. Если она поменяет email — нужно обновить везде.
- Update anomalies: забыли обновить — несогласованность.
- Insert anomalies: чтобы добавить нового клиента, нужен заказ.
- Delete anomalies: удалили последний заказ Alice — потеряли её email.
1NF — атомарные значения
Каждая ячейка содержит одно значение. Не «iPhone, AirPods» в одной ячейке.
| order_id | product |
|----------|---------------|
| 1 | iPhone 15 |
| 1 | AirPods |
| 2 | AirPods |Раньше нарушение 1NF часто было: «список товаров через запятую в одной строке».
2NF — все non-key поля зависят от ВСЕГО ключа
Если у тебя composite primary key (например, order_id + product_id), все остальные колонки должны зависеть от всего ключа, а не от части.
Плохо:
PK: (order_id, product_id)
| order_id | product_id | customer_name | product_name |customer_name зависит только от order_id — это нарушение 2NF.
3NF — все non-key поля зависят ТОЛЬКО от ключа
Никаких транзитивных зависимостей. Если B зависит от A, а C зависит от B, то C зависит от B, а не от A напрямую — выделяй в отдельную таблицу.
Плохо:
PK: order_id
| order_id | product_id | product_category |product_category зависит от product_id, а не от order_id. Нужно отдельную таблицу products.
Результат: 3NF модель
-- customers
| customer_id | name | email |
-- products
| product_id | name | category_id |
-- categories
| category_id | name |
-- orders
| order_id | customer_id |
-- order_items
| order_id | product_id | quantity | price |Пять таблиц вместо одной. JOIN-ы для отчёта, зато:
- Каждая сущность хранится один раз.
- Update в одном месте.
- Никаких аномалий.
Что даёт нормализация
3NF — идеальна для OLTP (Postgres, MySQL), где главное:
- Маленькие транзакции (вставить заказ).
- Целостность данных.
- Конкурентный доступ.
Но плоха для OLAP (аналитика), где главное:
- Большие сканы (тысячи строк за раз).
- Агрегации.
- Скорость чтения.
Денормализация: для OLAP
В DWH мы специально нарушаем 3NF: дублируем данные для скорости.
Пример: денормализованная таблица
| order_id | customer_id | customer_name | customer_email | product_name | category | price |
|----------|-------------|---------------|----------------|--------------|----------|-------|
| 1 | 42 | Alice | [email protected] | iPhone 15 | Phones | 999 |
| 2 | 42 | Alice | [email protected] | AirPods | Audio | 199 |
| 3 | 17 | Bob | [email protected] | iPhone 15 | Phones | 999 |«Alice» повторяется. Это нормально для OLAP.
Преимущества:
- Один SELECT без JOIN — быстро.
- Колоночное хранение Snowflake/BigQuery всё равно сжимает дубликаты.
- Аналитики счастливы — не надо помнить 15 JOIN’ов.
Когда денормализовывать
- Готовые mart-таблицы в DWH (Gold-слой).
- Таблицы для embedded analytics.
- Snapshots для исторического анализа.
Когда НЕ денормализовывать
- Raw / staging слои — храним нормализованно, как пришло.
- Маленькие справочники, которые меняются часто — лучше JOIN’ом.
Star Schema — гибрид
Самый популярный подход в DWH — star schema (Kimball, M07 углубление).
Это гибрид:
Идея:
- Fact table — большая таблица с числовыми метриками (amount, quantity) и foreign keys.
- Dimension tables — справочники (customer, product, date) с атрибутами.
-- Аналитический запрос на star schema
SELECT
d.day_of_week,
c.region,
p.category,
SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY 1, 2, 3;Star schema — быстрая (минимум JOIN’ов), понятная (центр fact + лучи dimensions), гибкая (новый dimension добавить просто).
Star schema: Kimball-методология в деталях Нормализация: 1NF, 2NF, 3NF с интерактивными примерамиСравнение подходов
Реальный путь данных
В одной компании оба подхода сосуществуют:
DE строит все слои:
- В Postgres ничего не трогаем (это работа backend).
- В Bronze — копия.
- В Silver — чистка.
- В Gold — переход на star schema.
Когда что использовать
Правило:
- OLTP: 3NF.
- Bronze в DWH: сохраняем структуру источника (3NF).
- Staging в DWH: чистим, но не объединяем.
- Marts (BI-готовое): star schema или денормализация.
- Embedded analytics, ML features: жёсткая денормализация для скорости.
Углубление
Это введение. Подробное моделирование — модуль 07-dimensional-modeling:
- Snowflake schema (более нормализованный вариант star).
- Slowly Changing Dimensions (SCD): Type 1, 2, 3.
- Bridge tables, factless facts, conformed dimensions.
- Anti-patterns в моделировании.
Попробуй сам
- Возьми любую систему из жизни (например, библиотека: книги, авторы, читатели, выдачи). Спроектируй её в 3NF — нарисуй таблицы и связи. Сколько таблиц получилось?
- Теперь спроектируй ту же библиотеку как star schema для аналитики (fact: выдачи, dimensions: книги, читатели, даты). Какая модель проще для запроса «топ-10 авторов по выданным книгам в этом году»?