Проектирование схемы базы данных веб-приложения
Схема базы данных — фундамент, который сложнее всего менять после запуска. Неправильно нормализованные таблицы, отсутствующие FK или неверные типы данных превращаются в технический долг, который копится годами. Разберём принципы и конкретные решения для типичного веб-приложения.
Нормализация и денормализация
Третья нормальная форма (3NF) — разумный уровень для большинства приложений. Каждое поле зависит только от первичного ключа, без транзитивных зависимостей.
Когда денормализация оправдана:
- Счётчики (
comments_count,likes_count) — вместо COUNT JOIN при каждом запросе. - Кешированные агрегаты для отчётов (суммы заказов за месяц).
- Flatten иерархических данных для поиска.
Когда денормализация вредна:
- Персональные данные, дублированные в нескольких таблицах.
- Статусы, которые изменяются часто.
Типы данных: частые ошибки
-- Плохо
user_id INT -- переполнится на 2.1 млрд записей
price FLOAT -- потери точности при финансовых вычислениях
status INT -- магические числа, нет domain constraint
created VARCHAR(30) -- сортировка строк вместо дат
settings TEXT -- нет структуры, нет индекса
-- Хорошо
user_id BIGINT -- или UUID
price DECIMAL(12, 2) -- точная арифметика
status VARCHAR(20) CHECK (status IN ('draft', 'published', 'archived'))
created TIMESTAMPTZ -- с timezone
settings JSONB -- структурированный, индексируемый
TIMESTAMPTZ хранит время в UTC и конвертирует при чтении согласно TimeZone сессии. TIMESTAMP хранит "как есть" — при смене timezone сервера данные теряют смысл.
Первичные ключи: SERIAL vs UUID vs ULID
-- SERIAL (автоинкремент): просто, компактно (8 байт), предсказуемо
id BIGSERIAL PRIMARY KEY
-- UUID v4: уникально глобально, но 16 байт, случайный порядок = фрагментация индекса
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- ULID через pg_ulid или генерацию на стороне приложения:
-- лексикографически сортируется по времени, 16 байт
id UUID PRIMARY KEY DEFAULT uuid_generate_v7() -- PostgreSQL 17+
Для большинства веб-приложений BIGSERIAL — оптимальный выбор. UUID нужен, когда ID генерируются на стороне клиента или нужно скрыть предсказуемость.
Пример: схема интернет-магазина
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL,
sort_order INT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
slug VARCHAR(520) NOT NULL UNIQUE,
category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
price DECIMAL(12, 2) NOT NULL CHECK (price > 0),
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'published', 'archived')),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
specs JSONB,
search_vector TSVECTOR, -- для full-text search
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
total DECIMAL(12, 2) NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'USD',
meta JSONB, -- delivery address и т.д.
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(12, 2) NOT NULL, -- цена на момент покупки
UNIQUE (order_id, product_id)
);
unit_price в order_items — намеренная денормализация: цена продукта изменится со временем, а историческая цена в заказе должна остаться неизменной.
ON DELETE RESTRICT vs CASCADE — правило: CASCADE только когда дочерние записи не имеют смысла без родителя (order_items без order). RESTRICT, когда удаление родителя должно быть явно предотвращено (нельзя удалить категорию с товарами).
Индексы: что добавлять при проектировании
Добавляем сразу при создании схемы:
-- FK columns — всегда, иначе DELETE родителя = seq scan дочерней таблицы
CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
-- Частые фильтры
CREATE INDEX idx_products_status_created ON products (status, created_at DESC);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
-- Partial index для активных записей
CREATE INDEX idx_products_published ON products (category_id, created_at DESC)
WHERE status = 'published';
-- GIN для JSONB
CREATE INDEX idx_products_specs ON products USING GIN (specs);
Аудит и soft delete
Паттерн soft delete:
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_products_deleted_at ON products (deleted_at) WHERE deleted_at IS NULL;
Partial index по WHERE deleted_at IS NULL — активные записи индексируются отдельно. Удалённые записи не попадают в индекс и не замедляют запросы.
Паттерн аудита через триггер:
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
row_id BIGINT NOT NULL,
operation CHAR(1) NOT NULL CHECK (operation IN ('I', 'U', 'D')),
old_data JSONB,
new_data JSONB,
changed_by BIGINT REFERENCES users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Сроки
Проектирование схемы для нового проекта (до 15 таблиц, ER-диаграмма, SQL DDL, обсуждение индексов и FK-стратегий): 1–2 дня. Ревью и рефакторинг существующей схемы с выявлением проблем (неправильные типы, отсутствующие FK, избыточные индексы): 1–3 дня в зависимости от объёма.