Проектирование баз данных

01Сущности и связи

Начинайте с бизнеса, а не с таблиц. Перечислите существительные (клиент, товар, заказ, платёж) и глаголы, которые их связывают (клиент оформляет заказы, заказ содержит позиции). Каждое важное существительное становится таблицей — сущностью. Затем определите кардинальность каждой связи. Один-ко-многим встречается чаще всего: у одного клиента много заказов, поэтому в orders появляется внешний ключ customer_id. Многие-ко-многим, как товары и категории, требует связующей таблицы (product_categories) с обоими внешними ключами и составным первичным ключом. Один-к-одному встречается редко и обычно означает необязательные дополнительные данные, например профиль клиента. Внешние ключи заставляют базу отказываться от строк-сирот; поведение ON DELETE выбирайте осознанно: RESTRICT для бизнес-записей, CASCADE — только для настоящих дочерних записей, например позиций черновой корзины.

• Существительные становятся таблицами, глаголы — связями.
• 1:N: внешний ключ на стороне «многих».
• N:M: связующая таблица с составным ключом.
• Называйте единообразно: таблицы во множественном числе, внешние ключи singular_id, snake_case.

02Нормализация и денормализация

Нормализация означает хранить каждый факт один раз. На практике цель — третья нормальная форма (3NF): каждый столбец зависит от ключа, от всего ключа и ни от чего, кроме ключа. Если email клиента скопирован в каждый заказ, его изменение означает обновление многих строк, и какие-то обязательно будут пропущены. Денормализация — это намеренное копирование данных ради скорости или истории. Два случая не просто допустимы, а обязательны. Снимки: позиция заказа должна хранить название товара и цену за единицу на момент покупки, потому что товар потом изменится. Read-модели: кэшированный order_count у клиентов или поисковый индекс. Нормализуйте по умолчанию, денормализуйте осознанно и документируйте, кто поддерживает копию в актуальном состоянии — триггер, джоба или приложение.

03Ключи: естественные и суррогатные, UUID и bigint

Естественный ключ — значение из реального мира, например email или SKU; суррогатный ключ — бессмысленный сгенерированный id. Естественные значения меняются (люди меняют email, SKU переименовывают), поэтому используйте суррогатный первичный ключ, а естественное значение защищайте ограничением UNIQUE. Для суррогатного ключа bigint с автоинкрементом компактен (8 байт), быстр и сохраняет порядок вставок в B-tree, но id легко угадать, и генерировать их должна база. UUID (16 байт) можно генерировать где угодно, что подходит распределённым системам, офлайн-клиентам и публичным URL. Случайные UUIDv4 разбрасывают вставки по всему индексу и на масштабе вредят производительности записи; упорядоченные по времени UUIDv7 это исправляют. В PostgreSQL 18 есть встроенная функция uuidv7(); в остальных случаях генерируйте v7 в приложении.

• bigint: внутренние таблицы, join'ы, большой объём записи.
• UUIDv7: публичные id, создание офлайн или в нескольких системах.
• Никогда не раскрывайте последовательные id там, где перебор — это риск.
• Естественные ключи держите как UNIQUE-столбцы, а не как первичные ключи.

04Стратегия индексов

Индекс — это отсортированная копия некоторых столбцов, которая позволяет базе находить строки без сканирования всей таблицы, как предметный указатель в конце книги. У каждого первичного ключа и уникального ограничения он есть. Добавляйте другие под запросы, которые реально выполняются: внешние ключи, используемые в join'ах, столбцы из частых WHERE и ORDER BY. В составном индексе порядок столбцов важен. Правило левого префикса гласит: индекс по (customer_id, created_at) помогает запросам с фильтром по customer_id или по customer_id и диапазону дат, но не по одному created_at. Ставьте столбцы с равенством первыми, а столбцы диапазона или сортировки — последними. Индексы не бесплатны: каждый замедляет запись и занимает память, поэтому удаляйте неиспользуемые. Всегда проверяйте через EXPLAIN.

1-- "latest orders of a customer" in one index scan
2CREATE INDEX orders_customer_created_idx
3  ON orders (customer_id, created_at DESC);
4 
5EXPLAIN
6SELECT id, total_minor, status
7FROM orders
8WHERE customer_id = 42
9ORDER BY created_at DESC
10LIMIT 20;

05Деньги и время

Никогда не храните деньги в столбце с плавающей точкой: в двоичных float 0.1 + 0.2 не равно 0.3, а ошибки округления накапливаются на тысячах заказов. Используйте целые минорные единицы (центы) в bigint или DECIMAL/NUMERIC с фиксированным масштабом и всегда храните код валюты (ISO 4217, например EUR или USD) рядом с суммой. Цены, скидки, налоги и итоги храните в самом заказе, чтобы история никогда не менялась. Моменты времени храните в UTC. timestamptz в PostgreSQL хранит UTC и конвертирует при отображении; в MySQL у DATETIME нет часового пояса, поэтому храните UTC по соглашению и избегайте TIMESTAMP для дат в далёком будущем, потому что он заканчивается в 2038 году. Для будущих локальных событий, например бронирования на 10:00 в каком-то городе, храните локальное время плюс имя часового пояса IANA, например Asia/Yerevan, потому что правила часовых поясов могут меняться.

• Суммы: bigint в минорных единицах или NUMERIC(19,4), никогда float.
• Всегда храните валюту рядом с суммой.
• Моменты времени — в UTC; будущие локальные события — как локальное время + зона IANA.

06Мягкое удаление и безопасные миграции

Мягкое удаление помечает строку как удалённую (метка времени deleted_at) вместо её физического удаления, так что её можно восстановить и сохранить историю. Цена: каждый запрос должен её отфильтровывать, а уникальные ограничения должны игнорировать удалённые строки. В PostgreSQL это решает частичный уникальный индекс; в MySQL используйте генерируемый столбец. Применяйте мягкое удаление для пользовательского контента, а не для всего подряд; для аудита часто чище append-only таблица истории, а законы о приватности могут требовать настоящего удаления. Миграции меняют живую схему, поэтому делайте их обратно совместимыми с работающим кодом по схеме expand and contract: добавьте новый nullable-столбец, задеплойте код, который пишет в оба, заполните данные пачками, переключите чтение, а затем удалите старый столбец в следующем релизе. Большие индексы стройте без блокировки записи (CREATE INDEX CONCURRENTLY в PostgreSQL).

1-- PostgreSQL: email unique only among non-deleted customers
2CREATE UNIQUE INDEX customers_email_live_uq
3  ON customers (lower(email))
4  WHERE deleted_at IS NULL;
5 
6-- expand step: safe on a live table
7ALTER TABLE orders ADD COLUMN currency char(3);
8-- backfill in batches, then in a later release:
9-- ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;

07Пример: базовая схема e-commerce

Вот компактное ядро, которое показывает все идеи вместе. Товары хранят общую информацию; варианты — то, что реально продаётся (размер, цвет, SKU, цена). Заказы принадлежат клиенту, хранят свою валюту и итоги и имеют статус с ограничением CHECK. Позиции заказа ссылаются на вариант, но также хранят снимок названия и цены за единицу, так что заказ, распечатанный через пять лет, по-прежнему покажет, сколько заплатил клиент. Платежи вынесены в отдельную таблицу, потому что у одного заказа может быть несколько попыток, возвратов или частичных списаний. Вокруг этого ядра вы добавили бы адреса (тоже со снимком в заказе), движения склада, применённые промоакции и таблицу истории статусов для аудита.

1CREATE TABLE products (id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name text NOT NULL);
2CREATE TABLE variants (
3  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
4  product_id bigint NOT NULL REFERENCES products(id),
5  sku text NOT NULL UNIQUE,
6  price_minor bigint NOT NULL CHECK (price_minor >= 0));
7CREATE TABLE orders (
8  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
9  customer_id bigint NOT NULL REFERENCES customers(id),
10  status text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled','refunded')),
11  currency char(3) NOT NULL,
12  total_minor bigint NOT NULL,
13  created_at timestamptz NOT NULL DEFAULT now());
14CREATE TABLE order_items (
15  order_id bigint NOT NULL REFERENCES orders(id),
16  variant_id bigint NOT NULL REFERENCES variants(id),
17  name_snapshot text NOT NULL,
18  unit_price_minor bigint NOT NULL,
19  qty int NOT NULL CHECK (qty > 0),
20  PRIMARY KEY (order_id, variant_id));

08Моделирование деревьев: adjacency list, materialised path и closure table

Категории, ветки комментариев, оргструктуры и папки — это деревья, и в SQL есть несколько способов их хранить. Adjacency list даёт каждой строке parent_id: просто, перемещать узел дёшево, а с рекурсивными CTE (WITH RECURSIVE, поддерживаются в PostgreSQL и MySQL 8) читать поддерево вполне нормально при умеренной глубине. Materialised path хранит полный путь строкой, например /1/7/42/; поиск всех потомков — быстрый поиск по префиксу, но перенос ветки переписывает все пути под ней. Расширение ltree в PostgreSQL делает этот паттерн полноценным. Closure table хранит каждую пару предок–потомок с глубиной в отдельной таблице; чтение любого поддерева или цепочки предков становится одним индексированным join ценой большего числа строк и большей работы при вставке и переносе. Nested sets быстры на чтение, но мучительны при обновлении, и сегодня редко того стоят. Выбирайте по доминирующей операции: частые переносы — в пользу adjacency list, тяжёлые чтения поддеревьев — в пользу путей или closure table.

1CREATE TABLE category_paths (
2  ancestor_id   bigint NOT NULL REFERENCES categories(id),
3  descendant_id bigint NOT NULL REFERENCES categories(id),
4  depth         int    NOT NULL,
5  PRIMARY KEY (ancestor_id, descendant_id)
6);
7 
8-- all products in category 7 and every subcategory, one join
9SELECT p.*
10FROM category_paths cp
11JOIN products p ON p.category_id = cp.descendant_id
12WHERE cp.ancestor_id = 7;

09Целостность при конкурентности: ограничения, upsert и оптимистичные блокировки

Проверки в приложении вида «если email не существует, вставь его» — это гонки: два запроса могут оба пройти проверку и оба вставить запись. Только база данных может атомарно обеспечивать правила, поэтому держите их там. UNIQUE-ограничение плюс upsert (INSERT ... ON CONFLICT в PostgreSQL, ON DUPLICATE KEY UPDATE в MySQL) превращает дубликаты в определённый результат. Для внешних повторов, например webhook оплаты или двойного клика по оформлению заказа, храните idempotency key с уникальным ограничением и при повторе возвращайте сохранённый результат. Exclusion constraints в PostgreSQL могут запрещать пересекающиеся диапазоны — это правильный способ предотвратить двойное бронирование. Для записей, которые редактируют люди, оптимистичная блокировка позволяет не держать блокировки, пока кто-то печатает: заведите колонку version, обновляйте WHERE version = прочитанное значение и считайте ноль затронутых строк конфликтом, который нужно показать пользователю. Пессимистичные блокировки (SELECT ... FOR UPDATE) подходят для коротких горячих путей вроде списания остатков.

1-- optimistic lock: succeeds only if nobody changed the row since we read version 7
2UPDATE products
3SET price_cents = 1990, version = version + 1
4WHERE id = 42 AND version = 7;
5-- 0 rows affected => someone else saved first; reload and ask the user
6 
7-- PostgreSQL: no two bookings of a room may overlap
8CREATE EXTENSION IF NOT EXISTS btree_gist;
9ALTER TABLE bookings ADD CONSTRAINT no_overlap
10  EXCLUDE USING gist (room_id WITH =, during WITH &&);

10Рост: партиционирование, retention и архивирование

Большинству таблиц партиционирование никогда не понадобится. Таблицам, в которые в основном дописывают и которые разрастаются до сотен миллионов строк — события, логи, журналы аудита или старые заказы, — рано или поздно понадобится. Партиционирование делит одну логическую таблицу на физические части по ключу, обычно по диапазону времени на месяц. Запросы с фильтром по этому ключу трогают только подходящие партиции (partition pruning), индексы остаются достаточно маленькими, чтобы помещаться в память, а retention становится мгновенным: отсоединение или удаление старой партиции заменяет DELETE миллионов строк, который раздул бы таблицу и завалил репликацию. Правила, которые нужно знать: первичный ключ и каждое уникальное ограничение должны включать ключ партиционирования — и в PostgreSQL, и в MySQL; запросы без фильтра по ключу сканируют все партиции; слишком много партиций замедляет планирование, поэтому сотни — нормально, десятки тысяч — нет. Создавайте будущие партиции заранее плановой задачей или расширением вроде pg_partman. Отсоединённые партиции архивируйте в более дешёвое хранилище, если их нужно сохранить.

1CREATE TABLE events (
2  id         bigint GENERATED ALWAYS AS IDENTITY,
3  created_at timestamptz NOT NULL,
4  type       text NOT NULL,
5  payload    jsonb,
6  PRIMARY KEY (id, created_at)
7) PARTITION BY RANGE (created_at);
8 
9CREATE TABLE events_2026_11 PARTITION OF events
10  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
11 
12-- retention: instant, no row-by-row delete
13ALTER TABLE events DETACH PARTITION events_2025_11;
14DROP TABLE events_2025_11;

11Паттерны, которые воюют с базой: полиморфные связи, EAV и ссылки между тенантами

Некоторые популярные паттерны тихо жертвуют целостностью. Полиморфные ассоциации, например комментарии с commentable_type и commentable_id, не могут иметь foreign key, поэтому сироты и опечатки остаются незамеченными. Альтернативы: по одному nullable foreign key на каждый тип родителя с CHECK, что задан ровно один, или отдельная связующая таблица на каждого родителя. Таблицы entity-attribute-value (entity_id, attribute, value) выглядят гибкими, но превращают каждое чтение во множество join и хранят каждое значение как текст; JSONB-колонка для изменчивой части с настоящими колонками для того, по чему вы фильтруете, почти всегда лучше. В мультитенантных схемах foreign key только по customer_id не мешает заказу ссылаться на клиента другого тенанта. Сделайте тенант частью ключа: уникальное ограничение на (tenant_id, id) у родителя и составной foreign key (tenant_id, customer_id) у потомка. Тогда ссылки между тенантами невозможны по построению, а не благодаря код-ревью.

1ALTER TABLE customers ADD CONSTRAINT uq_customers_tenant_id UNIQUE (tenant_id, id);
2 
3ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_same_tenant
4  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id);
5 
6-- exclusive arc instead of a polymorphic pair
7ALTER TABLE comments ADD CONSTRAINT one_parent CHECK (
8  num_nonnulls(post_id, product_id) = 1
9);