ER-диаграмма: как построить с нуля, пример на кейсе подписки
ER-диаграмма — это карта данных системы: какие сущности в ней живут, какими связями сцеплены и по каким ключам. Аналитик рисует её не ради красивой картинки в вики, а чтобы поймать противоречие до того, как оно окаменеет в базе.
Цена ошибки здесь выше, чем в любом другом артефакте. Неудачную формулировку требования правят за пять минут. Неудачную модель данных правят миграцией на живой базе, с простоем и риском потерять данные — и поэтому её обычно не правят, а обрастают костылями.
Разберём построение по шагам на одном кейсе: сервис с платной подпиской и программой лояльности. Пройдём путь от абзаца текста заказчика до схемы с ключами.
Что такое ER-диаграмма и когда её рисуют
🔤 ER-диаграмма (entity-relationship, «сущность — связь») описывает данные предметной области: сущности, их атрибуты и связи между ними.
Три уровня детализации — их путают чаще всего, а разница практическая:
| Уровень | Что показывает | Кому нужен |
|---|---|---|
| Концептуальный | только сущности и связи, без атрибутов | бизнесу и заказчику: «что вообще есть в системе» |
| Логический | атрибуты, ключи, кардинальность; без привязки к СУБД | аналитику и архитектору — основная рабочая версия |
| Физический | типы столбцов, индексы, имена под конкретную СУБД | разработчику и DBA |
Аналитик почти всегда делает логический. Показывать бизнесу логическую модель со всеми внешними ключами — надёжный способ провести встречу впустую: обсуждать будут типы данных, а не смысл.
Момент, когда диаграмму пора рисовать, — сразу после того, как стали понятны процессы и основные требования, но до написания спецификаций на API. Модель данных и контракт интеграции должны согласоваться, а согласовывать проще то, что нарисовано.
Исходные данные: описание от заказчика
Дальше работаем с этим текстом — примерно в таком виде задача и приходит:
«Пользователи регистрируются и оформляют подписку на один из тарифов: месячный или годовой. Раз в период с карты списывается оплата, списание может не пройти — тогда пробуем ещё раз. За каждую успешную оплату начисляем баллы лояльности, ими можно частично оплатить следующий период. Иногда даём промокоды — один промокод может использоваться многими клиентами, и клиент за время жизни может применить несколько разных».
Шесть строк, а в них уже спрятаны все три вида связей и одна ловушка. Разберём.
Шаг 1. Выделяем сущности
Простой приём: подчеркнуть в тексте существительные и отсеять лишние. Сущность — это то, о чём нужно хранить данные и что имеет самостоятельную жизнь.
Кандидаты: пользователь, подписка, тариф, оплата, карта, баллы, промокод.
Отсеиваем и уточняем:
- Клиент — да, сущность.
- Подписка — да: у неё свой жизненный цикл, статус, даты.
- Тариф — да, если тарифов несколько и их меняют без релиза. Если тариф всегда один из двух зашитых — это может быть просто поле-перечисление в подписке. Вопрос заказчику: «тарифы будут заводить менеджеры или их меняет разработчик?» Ответ определяет, таблица это или поле.
- Платёж — да: попытка списания, со статусом и суммой. Именно попытка, а не «успешная оплата»: неуспешные тоже нужно хранить, иначе не ответишь, почему у клиента пропал доступ.
- Начисление баллов — да, но не как «баллы» числом. Хранить нужно операции начисления и списания, а остаток считать. Иначе первый же спор «куда делись мои 300 баллов» будет неразрешимым.
- Промокод — да.
- Карта — пока нет: платёжные данные хранит платёжный провайдер, у нас максимум токен и маска. Это отдельный разговор про безопасность, а не про модель.
⚠️ Две ловушки этого шага. Первая — заводить сущность там, где хватает поля-перечисления. Вторая, более дорогая, — хранить итог вместо операций. «Баллы = число в карточке клиента» выглядит экономно ровно до первого расхождения, и после него историю уже не восстановить.
Шаг 2. Определяем связи и кардинальность
Для каждой пары сущностей задаём два вопроса — с обеих сторон:
- Сколько Б может быть у одного А?
- Сколько А может быть у одного Б?
| Связь | Вопрос | Ответ | Вид |
|---|---|---|---|
| Клиент — Подписка | сколько подписок у клиента? | несколько за время жизни (история) | 1:N |
| Подписка — Платёж | сколько списаний по подписке? | много, по одному за период плюс повторы | 1:N |
| Тариф — Подписка | сколько подписок на одном тарифе? | много | 1:N |
| Клиент — Операция с баллами | сколько операций у клиента? | много | 1:N |
| Клиент — Промокод | сколько промокодов применил клиент? | несколько; и один промокод — у многих клиентов | M:N |
🔤 Кардинальность — сколько экземпляров одной сущности может быть связано с экземпляром другой. Отдельно от неё указывают обязательность: ноль или минимум один.
Обязательность — не формальность. «У клиента ноль или больше подписок» и «у клиента минимум одна подписка» — разные системы. Во второй нельзя зарегистрироваться, не заплатив. Это продуктовое решение, и принимает его не аналитик в одиночку.
Шаг 3. Развязываем «многие ко многим»
Связь M:N в реляционной базе не хранится напрямую — её всегда разбивают на две связи 1:N через промежуточную таблицу.
Клиент 1 ──< Применение промокода >── 1 Промокод
Таблица-связка promo_usage содержит как минимум два внешних ключа: client_id и promo_id.
💡 Почти всегда выясняется, что у связки есть собственные атрибуты — и это признак, что вы нашли не техническую подпорку, а настоящую сущность. Здесь это дата применения, сумма скидки и ссылка на платёж, к которому скидку применили. Без них не ответить на вопрос «сколько денег мы раздали промокодами за август» — а его зададут.
Второй практический смысл связки — ограничение уникальности. Пара (client_id, promo_id) уникальна, если один клиент может применить промокод только раз. Это правило живёт в модели данных, а не в коде: база не даст его нарушить даже при гонке двух одновременных запросов.
Шаг 4. Ключи и атрибуты
🔤 Первичный ключ (PK) однозначно определяет строку. Внешний ключ (FK) — ссылка на первичный ключ другой таблицы; он и есть материальное воплощение связи.
Правило стороны: внешний ключ всегда живёт на стороне «много». У подписки есть client_id, а не у клиента список подписок — потому что список в одну ячейку не помещается.
Собираем схему:
clients subscriptions
───────────────── ─────────────────────────────
id PK 1 ──< client_id FK
email unique plan_id FK
name status
created_at started_at
next_billing_at
price_at_signup_minor
id PK
plans payments
───────────────── ─────────────────────────────
id PK 1 ──< subscription_id FK
code unique amount_minor
title currency
price_minor status
period attempt_no
external_id unique
created_at
paid_at
id PK
promo_codes promo_usage
───────────────── ─────────────────────────────
id PK 1 ──< promo_id FK
code unique client_id FK
discount_percent payment_id FK
valid_until applied_at
max_uses unique (promo_id, client_id)
loyalty_operations
─────────────────────────────
id PK
client_id FK
points (+ начисление, − списание)
reason
payment_id FK (по какой оплате)
created_at
Значки на концах линий — нотация crow's foot: «палочка» — ровно один, «вороньи лапки» (<) — много, кружок — ноль, то есть необязательно. Читается слева направо: «один клиент — много подписок».
Три решения в этой схеме стоит проговорить отдельно, потому что они типовые:
- Деньги —
amount_minor, целое число копеек. Дробные типы округляют, и на тысяче платежей расхождение с бухгалтерией гарантировано. Рядом —currency: даже если валюта сейчас одна, поле дешевле завести сразу, чем добавлять потом ко всем историческим строкам. external_idу платежа с ограничением уникальности. Это идентификатор операции на стороне платёжного провайдера, и он же защита от двойного списания: провайдер может прислать уведомление дважды, база примет только первое. Идемпотентность — свойство модели данных, а не аккуратности разработчика.attempt_noу платежа. Попытки списания нумеруются — иначе не отличишь «клиент заплатил трижды» от «мы трижды пытались списать».
Шаг 5. Проверка нормализацией
Нормализация — это не академический ритуал, а три проверки на аномалии.
1NF: в каждой ячейке одно значение, нет списков. Поле promo_codes_used со строкой "NEWYEAR, SUMMER10" — нарушение: по нему нельзя ни искать, ни считать. Это ровно тот случай, который лечится таблицей-связкой из шага 3.
2NF: неключевые атрибуты зависят от всего первичного ключа, а не от его части. Актуально для таблиц с составным ключом — в нашей схеме это promo_usage. Если бы мы положили туда promo_discount_percent, он зависел бы только от promo_id, но не от клиента — значит, ему место в promo_codes.
3NF: нет зависимости неключевого атрибута от другого неключевого. Если в subscriptions положить plan_price, он будет зависеть от plan_id, а не от подписки: поменяли цену тарифа — и в старых подписках она разъехалась.
⚠️ И сразу — обоснованное исключение. Цену на момент оформления хранить в подписке как раз нужно, но это не денормализация, а другой по смыслу атрибут: price_at_signup — исторический факт, зафиксированный навсегда, а не копия текущей цены. То же с суммой в платеже. Осознанное дублирование ради истории — норма; неосознанное, ради «так быстрее» — источник расхождений.
Семь ошибок, которые ревьюер найдёт первыми
- Сущность вместо перечисления и наоборот. Статус подписки — поле, а не таблица из четырёх строк. Тариф, который меняют менеджеры, — таблица, а не поле.
- Итог вместо операций. «Баллы» числом, «сумма покупок» полем. Всё, что накапливается, хранится операциями; итог считается запросом.
- Забытая сторона «много». Внешний ключ поставлен не туда, и связь 1:N молча превратилась в 1:1.
- M:N нарисована линией. В логической модели так ещё можно, в физической — нет. Не развяжете вы — развяжет разработчик, и без атрибутов, которые вам были нужны.
- Нет обязательности. На диаграмме
1:N, а может ли быть ноль — не указано. Разработчик решит сам, и это решение вы увидите уже в проде. - Деньги дробным типом, даты без часового пояса. Две классические мины замедленного действия.
- Нет истории там, где она нужна бизнесу. Модель отвечает на вопрос «как сейчас», а спрашивают «как было в момент оплаты». Проверка простая: пройдись по отчётам, которые попросят, и убедись, что данных на них хватит.
Чек-лист готовой ER-диаграммы
- У каждой сущности есть первичный ключ.
- Каждая связь имеет кардинальность и обязательность с обеих сторон.
- Ни одной связи M:N без таблицы-связки.
- Внешние ключи стоят на стороне «много».
- Нет ячеек со списками (1NF).
- Нет полей, зависящих от неключевых (3NF) — кроме осознанно зафиксированной истории.
- Деньги в минорных единицах или
DECIMAL, есть валюта. - Есть ограничения уникальности там, где бизнес-правило запрещает дубли.
- Схема отвечает на реальные вопросы отчётности — проверено на трёх конкретных вопросах.
- Названия единообразны: либо везде
client_id, либо вездеuser_id, без смеси.
Что дальше
ER-диаграмма — середина маршрута, а не его начало. До неё должно быть понятно, какие процессы система поддерживает: модель данных вырастает из процесса, а не из фантазии о таблицах. Как описывать процессы, разобрано в статье «BPMN для начинающих», а как фиксировать требования к ним — в «Как писать user story».
Сразу после диаграммы идёт SQL: пока ты не написал к своей же модели десяток запросов, ты не знаешь, удобная она или нет. Это самая быстрая обратная связь, какая бывает у аналитика, — разбор JOIN, GROUP BY и HAVING на этом же кейсе подписки есть в статье «SQL для аналитика с нуля».
В курсе «Аналитик.Про» блок данных построен ровно этой последовательностью: сущности и связи → две нотации ER → нормализация → SELECT → JOIN и агрегаты → подзапросы и оконные функции → блокировки и конкурентный доступ. Кейс сквозной — та самая подписка с лояльностью, — а запросы пишутся прямо в браузере, в SQL-песочнице на этих таблицах: сначала проектируешь модель, потом сам же её и допрашиваешь. Первые блоки открыты без оплаты.