Базы данных для аналитика: ER-модель, ключи, нормализация, транзакции

Я как-то принял требование «у заказа есть имя и телефон клиента» и спокойно отдал в разработку. А через месяц клиент сменил телефон — и выяснилось, что в трёхстах его старых заказах остался старый номер, а в новых новый, и служба доставки звонит не туда. Проблема была не в коде. Проблема была в том, что я спроектировал данные так, что один и тот же факт хранился в сотне мест. Это лечится на этапе требований, и лечится знанием на полстраницы.

Аналитик не пишет миграции и индексы — это работа разработчика. Но именно аналитик решает, какие сущности есть в системе, как они связаны и где какой факт хранится. Ошибка здесь дороже всех остальных: переписать API можно за спринт, перетряхнуть схему данных в работающей системе — это боль на месяцы.

Таблица, строка, столбец

Реляционная база — это набор таблиц, по сути продвинутый Excel. Таблица хранит сущности одного типа: таблица «клиенты», таблица «заказы». Столбец — это поле, одно свойство (имя, телефон, дата). Строка — одна конкретная запись (один клиент, один заказ). Всё проектирование данных сводится к вопросам: какие нужны таблицы, какие в них столбцы и как таблицы связаны между собой.

Первичный и внешний ключ

Чтобы отличать строки друг от друга, у каждой должен быть первичный ключ (PK, primary key) — поле, которое уникально и не повторяется. Обычно это служебный id (1, 2, 3…). Два клиента могут быть полными тёзками с одним телефоном, но id у них разные — и система их не перепутает.

Внешний ключ (FK, foreign key) — это поле, которое ссылается на первичный ключ другой таблицы. Вернёмся к заказам. Мы не храним имя и телефон клиента в каждом заказе. Мы храним их один раз в таблице «клиенты», а в заказе кладём только client_id — ссылку на клиента. Сменил клиент телефон — поправили одну строку в «клиентах», и все его заказы автоматически «видят» новый номер, потому что они на него ссылаются, а не копируют.

ER-модель и связи

ER-модель (Entity-Relationship, «сущности и связи») — это способ нарисовать данные до того, как они станут таблицами. Сущности — это «клиент», «заказ», «товар». Связи — как они соотносятся по количеству:

• Один-ко-многим (1:N) — самая частая. У одного клиента много заказов, но у каждого заказа ровно один клиент.
• Многие-ко-многим (N:M) — у заказа много товаров, и один товар в многих заказах. Такую связь в БД разбивают через промежуточную таблицу (например «позиции заказа»).
• Один-к-одному (1:1) — редкая, например пользователь и его расширенный профиль.

erDiagram
  CLIENT ||--o{ ORDER : "размещает"
  ORDER ||--|{ ORDER_ITEM : "содержит"
  PRODUCT ||--o{ ORDER_ITEM : "входит в"
  CLIENT {
    int id PK
    string name
    string phone
  }
  ORDER {
    int id PK
    int client_id FK
    datetime created_at
  }
  ORDER_ITEM {
    int order_id FK
    int product_id FK
    int qty
  }
  PRODUCT {
    int id PK
    string title
    int price
  }

Схема выше — ER-диаграмма интернет-магазина. Один клиент (CLIENT) размещает много заказов (ORDER) — связь один-ко-многим, поэтому в заказе лежит внешний ключ client_id. Один заказ содержит несколько позиций (ORDER_ITEM), а каждый товар (PRODUCT) входит в множество позиций — это связь многие-ко-многим между заказами и товарами, разбитая через промежуточную таблицу ORDER_ITEM с двумя внешними ключами. Имя и телефон клиента хранятся ровно в одном месте — в CLIENT, заказы на них только ссылаются.

Нормализация простыми словами

Нормализация — это правило «каждый факт хранится ровно в одном месте». Та самая история с телефоном клиента — это нарушение нормализации: телефон был размазан по сотне заказов. Формально выделяют нормальные формы, но на пальцах их три:

Форма	Что чинит
1НФ	В одной ячейке — одно значение. Не «товары: молоко, хлеб, яйца» в одном поле, а отдельные строки.
2НФ	Никаких полей, зависящих от части ключа. Цена товара хранится у товара, а не дублируется в каждой позиции заказа.
3НФ	Никаких полей, зависящих от не-ключа. Имя клиента хранится у клиента, а не копируется в заказ.

Рабочее правило: если вы видите, что один и тот же текст копируется в несколько строк — скорее всего, его надо вынести в отдельную таблицу и ссылаться по ключу. Это и есть нормализация на практике.

Транзакции и ACID

Перевод денег: списать со счёта А и зачислить на счёт Б. Это две операции, но для нас это одно действие. Представьте, что списание прошло, а зачисление упало — деньги исчезли. Транзакция — это группа операций по принципу «всё или ничего»: либо выполнятся все, либо ни одна, и база откатится в исходное состояние.

Гарантии транзакций описывают четырьмя буквами — ACID:

• A — Atomicity (атомарность): всё или ничего, как с переводом денег.
• C — Consistency (согласованность): после транзакции данные не нарушают правил (баланс не уходит в минус, FK ссылается на существующую строку).
• I — Isolation (изолированность): параллельные транзакции не лезут друг другу в промежуточное состояние; два одновременных перевода не перепутаются.
• D — Durability (надёжность): если транзакция подтверждена, данные не потеряются даже при выключении сервера.

На SQL та же логика выглядит так:

BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;

Между BEGIN и COMMIT — единое целое. Если на втором UPDATE что-то пойдёт не так, вместо COMMIT произойдёт откат (ROLLBACK), и первое списание тоже отменится. Когда в требованиях есть слова «перевод», «оплата», «списание» — это сигнал проговорить транзакционность явно.

SQL и NoSQL одним абзацем

Всё выше — про реляционные (SQL) базы: строгие таблицы, связи, ACID (PostgreSQL, MySQL). Есть и NoSQL: документные (MongoDB), ключ-значение (Redis), графовые. Они жертвуют частью строгости ради гибкости схемы и масштаба. Грубое правило: данные со связями и деньгами — берите SQL; гибкие документы без жёстких связей или огромный поток событий — повод посмотреть на NoSQL. По умолчанию для бизнес-данных берут реляционную базу, и не зря. Этот выбор разобран подробно в записи SQL или NoSQL, а как читать и писать сами запросы — в записи SQL: SELECT и JOIN.

Частые вопросы

Что такое первичный и внешний ключ?

Первичный ключ (PK) — поле, уникально опознающее строку в таблице, обычно служебный id. Внешний ключ (FK) — поле, ссылающееся на первичный ключ другой таблицы; так таблицы связываются. Пример: в таблице заказов поле client_id (FK) ссылается на id клиента (PK), поэтому имя и телефон клиента хранятся один раз, а не копируются в каждый заказ.

Что такое нормализация БД простыми словами?

Это правило «каждый факт хранится ровно в одном месте», чтобы данные не дублировались и не разъезжались. Если один и тот же текст копируется в несколько строк — его выносят в отдельную таблицу и ссылаются по ключу. Иногда дубли оставляют намеренно ради скорости чтения — это денормализация, осознанный размен скорости на риск рассинхрона.

Что такое транзакция и ACID?

Транзакция — группа операций по принципу «всё или ничего»: либо выполнятся все, либо ни одна (классика — перевод денег: списание и зачисление вместе). ACID — четыре гарантии транзакций: Atomicity (всё или ничего), Consistency (правила не нарушаются), Isolation (параллельные транзакции не мешают друг другу), Durability (подтверждённое не теряется при сбое).