Проектирование схемы: ER, типы данных, суррогатные ключи, UUID

↑ 1.1 Реляционная модель и SQL · ← Предыдущая · → Следующая

Обязательно~3 мин чтенияУровень: junior

Зачем это на собесе

Ошибки проектирования схемы дорого исправлять. Ожидают, что вы выберете типы и ключи осознанно.

Процесс

1. Выделить сущности и их атрибуты из требований.
2. Определить связи и кардинальности (1:1, 1:N, N:M).
3. Построить ER-диаграмму (dbdiagram.io, draw.io, pgModeler).
4. Нормализовать, затем спроектировать индексы под запросы.
5. Учесть жизненный цикл: мягкое удаление, аудит, версии, архив.

Выбор типов (PostgreSQL)

Данные	Тип	Заметки
Целые	integer, bigint	для id лучше bigint
Деньги	numeric(p,s)	не float / real
Строки	text	varchar(n) даёт мало пользы, лимит — через CHECK
Время	timestamptz	хранит момент времени в UTC; избегайте timestamp без зоны
Дата	date
Булево	boolean
Идентификатор	uuid
Произвольная структура	jsonb	не замена схеме
Перечисления	text + CHECK, справочник, enum	enum сложнее менять
Диапазоны	tstzrange, int4range	+ EXCLUDE

Проблемы: float для денег даёт ошибки округления; timestamp без зоны теряет смысл при разных таймзонах; char(n) дополняет пробелами.

Суррогатный или естественный ключ

	Суррогатный	Естественный
Плюсы	стабилен, компактен, не зависит от бизнеса	смысл в самих данных
Минусы	не несёт смысла	может меняться (email, паспорт), длинный

Практика: суррогатный первичный ключ + UNIQUE на естественный.

Автоинкремент и UUID

id bigint GENERATED ALWAYS AS IDENTITY          -- последовательность
id uuid DEFAULT gen_random_uuid()               -- UUID v4, встроено с PostgreSQL 13

	bigint identity	UUID v4	UUID v7
Размер	8 байт	16 байт	16 байт
Порядок вставки	возрастает	случайный (фрагментация индекса)	по времени (хороший локализм)
Генерация на клиенте	нет	да	да
Угадываемость	легко перебрать	нет	нет
Распределённые системы	сложно	удобно	удобно

UUID v7 (в PostgreSQL 18 — uuidv7()) сочетает уникальность и упорядоченность по времени; для B-tree значительно лучше случайного v4.

Полезные шаблоны

• created_at, updated_at, при необходимости created_by;
• мягкое удаление (deleted_at): нужен частичный уникальный индекс WHERE deleted_at IS NULL, все запросы обязаны фильтровать;
• аудит: отдельная таблица истории или триггер;
• оптимистичная блокировка: version / xmin;
• многоарендность: tenant_id в каждой таблице + индексы и RLS;
• иерархии: parent_id + рекурсивный CTE, ltree, closure table.

Антипаттерны

• EAV (entity-attribute-value) вместо схемы;
• хранение списков в строке '1,2,3';
• «универсальный» data jsonb для всего;
• отсутствие внешних ключей «ради скорости»;
• числа как строки, даты как строки.

Вопросы с ответами

Почему для денег нельзя использовать float?

Двоичная плавающая точка не представляет десятичные дроби точно (0.1 + 0.2 ≠ 0.3). Нужен numeric или хранение в минимальных единицах (копейки) в целом типе.

Чем плох UUID v4 в качестве первичного ключа?

Случайные значения вставляются в произвольные места B-tree, растёт фрагментация и объём записи в WAL, кэш работает хуже. UUID v7 или bigint решают проблему.