Модуль 3.1. Моделирование данных: ER, нормализация, ключи
Темы: сущности, атрибуты, связи, аномалии. ER-моделирование и глоссарий — системный аналитик данных. Артефакт: ER-диаграмма домена. Практика: ER-модель «Заказы–Клиенты–Платежи».
Системный аналитик (SA) переводит бизнес-понятия в устойчивую модель данных, которая:
- без двусмысленностей отражает домен;
- поддерживает интеграции и отчётность;
- предотвращает аномалии вставки/обновления/удаления;
- дружит со статусами/правилами (UC/DMN) и API-контрактами.
На выходе модуля вы умеете строить ER-модель (сущности/связи/кардинальности/ключи), нормализовать до 3НФ/BCNF там, где нужно, оформлять глоссарий и фиксировать инварианты в SRS/DDL.
Базовые понятия (как сотруднику — к исполнению)
Сущность, атрибут, домен
- Сущность — именованный набор однотипных объектов (Customer, Order, Payment).
- Атрибут — свойство сущности (email, status, amount).
- Домен — множество допустимых значений (строго: тип + формат + ограничения).
Ключи
- Естественный ключ (Natural) — смысловой уникальный идентификатор (ИНН, email).
- Суррогатный ключ (Surrogate) — техн. идентификатор (UUID/BIGINT).
- Первичный ключ (PK) — уникальный, NOT NULL, минимальный.
- Внешний ключ (FK) — ссылка на PK родительской таблицы (обеспечивает ссылочную целостность).
- Составной ключ — несколько полей (order_id + line_no).
- Альтернативный ключ (AK/UK) — ещё одно уникальное ограничение (UNIQUE).
Связи и их свойства
- 1:1 — редкость, часто признак субтипа/опционального блока (Customer ↔ Customer_KYC).
- 1:N — базовый случай (Customer —< Order).
- M:N — реализуется через стыковочную таблицу (Order —< Order_Item >— Product).
- Идентифицирующая (толстая линия) — PK дочерней включает FK на родителя (Order_Item).
- Неидентифицирующая — PK дочерней независим от FK.
- Обязательность: обязательная (NOT NULL FK) vs опциональная (NULL FK).
- Каскады: ON DELETE/UPDATE (RESTRICT/NO ACTION/SET NULL/CASCADE) — выбираются осознанно.
Нормализация и аномалии
Аномалии на примере «плоской» таблицы
OrdersFlat(order_id, customer_email, customer_phone, shipping_address,
item1_name, item1_price, item2_name, item2_price, ... , total_amount)
- Вставки: нельзя добавить клиента без заказа.
- Обновления: смена телефона в 15 строках — риск рассинхрона.
- Удаления: удалили последний заказ — потеряли данные клиента.
Нормальные формы (практично)
- 1НФ: атомарные значения (никаких item1/item2 — выносим Order_Item).
- 2НФ: нет частичных зависимостей от части составного PK (атрибуты Order_Item зависят от (order_id, line_no), а не от части).
- 3НФ: нет транзитивных зависимостей (адрес клиента — в Customer_Address, а не в Order).
- BCNF: усиленная 3НФ — когда есть нетривиальные ФЗ (чаще в сложных справочниках).
Когда денормализовать: строго осознанно и документируя: «для отчёта X храним агрегат Y, обновляем триггером/процедурой».
Глоссарий и единый язык
Зачем: убирает «заказ/покупка/ордер»-хаос.
Формат записи (минимум):
Термин: Order
Определение: юридический документ о покупке; источник правды — сервис заказов.
Синонимы/антонимы: "покупка" (разг.), не путать с "счет".
Атрибуты: order_id, status, created_at, customer_id
Статусы и правила: CREATED→PAID→SHIPPED→DELIVERED (см. state-модель)
Источники: SRS §3, ER v1.3, API /orders/*
Правило: первые упоминания терминов в SRS/ER/UC ссылаются на глоссарий; одно поле — одно имя по всему стеку.
Конвенции проектирования (чтобы не наступать на грабли)
- Идентификаторы: uuid (PK) или bigserial (PK), внешние идентификаторы храним отдельно (external_id).
- Денежные суммы: amount DECIMAL(18,2) + currency CHAR(3); никаких FLOAT.
- Даты/время: timestamptz (UTC), храните часовой пояс в UI.
- Домены/справочники: маленькие — ENUM; изменяемые — reference-таблицы (order_status).
- История изменений: слепки (SCD2) или audit-log (кто/когда/что).
- Уникальность бизнес-операций: идемпотентные ключи (idempotency_key UNIQUE).
- PII: хранение и маски по политике безопасности; доступ по ролям.
Референс-ER «Заказы–Клиенты–Платежи» (диаграмма + пояснения)
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_ITEM : contains
PRODUCT ||--o{ ORDER_ITEM : is_in
ORDER ||--o| PAYMENT : has
PAYMENT ||--o{ REFUND : creates
CUSTOMER ||--o{ CUSTOMER_ADDRESS : has
CUSTOMER {
uuid customer_id PK
string email UK
string phone
timestamp created_at
}
CUSTOMER_ADDRESS {
uuid address_id PK
uuid customer_id FK
string line1
string city
string country
boolean is_default
timestamptz valid_from
timestamptz valid_to
}
ORDER {
uuid order_id PK
uuid customer_id FK
string status // CREATED, PAID, SHIPPED, DELIVERED, CANCELLED
decimal total_amount
char(3) currency
timestamptz created_at
timestamptz paid_at
}
ORDER_ITEM {
uuid order_item_id PK
uuid order_id FK
uuid product_id FK
int qty
decimal price
decimal amount // qty*price (денорм, опционально)
int line_no
}
PRODUCT {
uuid product_id PK
string sku UK
string name
}
PAYMENT {
uuid payment_id PK
uuid order_id FK
string status // AUTHORIZED, CAPTURED, FAILED
decimal amount
char(3) currency
string idempotency_key UK
timestamptz created_at
timestamptz captured_at
}
REFUND {
uuid refund_id PK
uuid payment_id FK
decimal amount
string status // PENDING, COMPLETED, FAILED
timestamptz created_at
}
Пояснения и инварианты
- Order.total_amount = Σ ORDER_ITEM.qty*price (инвариант; можно держать в колонке amount для быстрого чтения и валидировать триггером/проверкой).
- Payment.amount ≤ Order.total_amount (для частичных — сумма CAPTURED по платежам ≤ total_amount).
- Refund.amount ≤ сумма CAPTURED − сумма уже REFUNDed.
- Order.status изменяем по state-машине; PATCH адреса запрещён при SHIPPED (правило/DMN).
- Idempotency: payment.idempotency_key — UNIQUE (повторы не создают дублей).
- Address.versioning: храним промежутки валидности (valid_from/to) — прошлые адреса не теряются.
От ER к DDL/ограничениям (минимум, который должен попросить SA)
Ограничения целостности (SQL-идеи)
-- Уникальность SKU и email:
ALTER TABLE product ADD CONSTRAINT uq_product_sku UNIQUE (sku);
ALTER TABLE customer ADD CONSTRAINT uq_customer_email UNIQUE (email);
-- Нельзя списать больше оплаченного:
ALTER TABLE refund ADD CONSTRAINT chk_refund_non_negative_amount CHECK (amount > 0);
-- (Пример функции) Сумма возвратов ≤ сумма CAPTURED:
-- реализуется триггером на refund или представлением с CHECK в бизнес-слое.
-- Идемпотентность платежа:
ALTER TABLE payment ADD CONSTRAINT uq_payment_idempotency UNIQUE (idempotency_key);
-- Валюты согласованы:
ALTER TABLE payment ADD CONSTRAINT chk_payment_currency CHECK (currency ~ '^[A-Z]{3}$');
Индексация (основы)
- FK-поля индексируем (order.customer_id, order_item.order_id, payment.order_id).
- Часто фильтруемые — order.status, payment.status.
- Составные по «горячим» выборкам (например, payment(order_id, status)).
Связь данных с процессом и API
- ER ↔ UC/State: статусы/переходы фиксируют допустимые изменения данных.
- ER ↔ OpenAPI: типы/домены/обязательность полей должны совпадать (валидация схем).
- ER ↔ DMN: таблицы правил опираются на домены (например, разрешённые статусы для операций).
Типовые ошибки и как их гасить
-
Смешение разных смыслов в одной сущности (Order хранит адрес клиента навсегда).
→ Адрес заказа — копия на момент оформления (Order_Shipping_Address), историю адресов клиента ведём в другой сущности. -
M:N без стыковочной таблицы.
→ Всегда через Order_Item, храните line_no и инвариант суммы. -
Отсутствие доменов (строки «ACTIVE/Act/1»).
→ Единые справочники или ENUM; валидаторы. -
FLOAT для денег/времени.
→ Только DECIMAL(18,2)/NUMERIC, timestamptz. -
Отсутствие уникальности бизнес-событий.
→ Idempotency-key + UNIQUE, аудит. -
Зависимость от каскадных удалений.
→ Для бизнес-сущностей чаще RESTRICT, используйте «мягкое удаление» (deleted_at) и бизнес-архив. -
Дубли клиентов.
→ Согласование «мастера» (master data), UK(email) + процессы слияния (merge). -
Неопределённость валюты/НДС.
→ Деньги всегда с валютой; налоговые поля отдельно; правила округления фиксируются (HALF_UP).
Роль «системного аналитика данных» (Data-oriented SA)
- Ведёт глоссарий/ER; хранит канон в Git, рендерит в Confluence.
- Определяет домены, инварианты, DQ-проверки (data quality).
- Согласует источник правды для каждой сущности (master).
- Участвует в дизайне миграций (обратимость, окна, блокировки).
- Меряет качество данных: дубликаты, пропуски, расхождения агрегатов.
Мини-набор DQ-SQL для регулярных проверок
-- Дубли по email: SELECT email, COUNT(*) c FROM customer GROUP BY email HAVING COUNT(*) > 1; -- Заказы с несоответствующей суммой: SELECT o.order_id, SUM(i.qty*i.price) calc, o.total_amount FROM "order" o JOIN order_item i USING (order_id) GROUP BY o.order_id, o.total_amount HAVING SUM(i.qty*i.price) <> o.total_amount; -- Возврат больше оплаченного: SELECT r.refund_id FROM refund r JOIN payment p ON p.payment_id = r.payment_id GROUP BY r.refund_id, p.amount HAVING SUM(r.amount) > p.amount;
Вопрос–Ответ
Q: Суррогатный или естественный ключ?
A: В OLTP-модели — чаще суррогатный (устойчив, не меняется). Естественный сохраняйте как уникальный атрибут (UK), используйте для интеграций.
Q: Когда 1:1 оправдан?
A: Когда часть атрибутов встречается редко или требует особого доступа/жизненного цикла (например, KYC). Это признак субтипа или «опционального блока».
Q: Нужна ли колонка amount в Order_Item, если её можно посчитать?
A: Это денормализация ради скорости. Допустима при наличии инварианта (проверка/триггер) и чёткого описания в SRS.
Q: Где хранить адрес — у клиента или в заказе?
A: Оба: у клиента — история адресов (для будущих заказов), в заказе — снимок на момент покупки (юридически значимый).
Q: Как учесть мультивалютность?
A: Всегда amount + currency. Если нужны конвертации — фиксируйте курс и момент конвертации; агрегируйте по валютам отдельно.
Q: Чем ER отличается от классов/ORM?
A: ER — логическая модель отношений и ограничений домена; ORM — техн. отображение в код. Никогда не подстраивайте домен под удобство ORM.
Практика: ER-модель «Заказы–Клиенты–Платежи» (2–4 часа)
Задача: спроектировать и отдать в репозиторий ER-модель домена с глоссарием и инвариантами.
Шаги
- Границы домена: чекаут интернет-магазина (без склада).
- Список сущностей: Customer, Customer_Address, Order, Order_Item, Product, Payment, Refund.
- Глоссарий: определения + статусы + источник правды (см. §3).
- ER-диаграмма: кардинальности, обязательность, PK/FK, типы, домены (как в §5).
- Инварианты: суммы/лимиты/идемпотентность/статусы (списком).
- DDL-эскиз: PK/FK/UK/CHK/индексы для ключевых таблиц (см. §6).
- DQ-проверки: 3–5 запросов контроля качества (см. выше).
- Версионирование: er-domain v0.1.0, changelog.
Критерии зачёта
- Ясные кардинальности и обязательность; M:N через стыковочную.
- Денежные поля корректны (DECIMAL + валюты).
- Инварианты сформулированы и проверяемы.
- Есть глоссарий; термины совпадают с названиями полей.
- DQ-проверки находят потенциальные нарушения.
- Исходники диаграммы (drawio/PlantUML/Mermaid) и DDL лежат в /docs.
Структура сдачи (рекомендация):
/docs /er/commerce-orders.mmd # исходник диаграммы /ddl/commerce.sql # PK/FK/UK/CHK/INDEX /glossary/glossary.md /dq/checks.sql CHANGELOG.md
Шпаргалка (распечатайте)
- ER описывает смыслы, а не UI/ORM.
- PK — минимальный, устойчивый; бизнес-уникальность → UK.
- Денежные значения: DECIMAL + валюта, никакого FLOAT.
- M:N всегда через стыковочную, храните line_no.
- Правила и статусы → инварианты и state-машины.
- Нормализуйте до 3НФ/BCNF, денормализуйте осознанно.
- Глоссарий — ваш «единый словарь»; имена полей ≡ терминам.
- Любая денормализация → описанный инвариант и проверка.
- Идемпотентность денег → ключ + UNIQUE.
- Канон в Git, витрина в Confluence, изменения через PR с проверками.
