Проектирование схемы базы данных веб-приложения
Схема базы данных — фундамент, который сложнее всего менять после запуска. Неправильно нормализованные таблицы, отсутствующие FK или неверные типы данных превращаются в технический долг, который копится годами. Мы сталкивались с этим десятки раз: когда проект растёт, а база трещит по швам — перепроектирование обходится в разы дороже. На одном проекте клиент потерял два месяца из-за отсутствия индексов — запросы выполнялись по 30 секунд. После внедрения правильной схемы всё стало летать за 50 миллисекунд. В среднем, рефакторинг схемы занимает 2-3 дня, но предотвращает месяцы переработок. Мы гарантируем, что после нашей работы запросы станут в десятки раз быстрее.
Основные принципы и типовые проблемы
В 80% проектов мы находим одни и те же ошибки: N+1 запросы из-за отсутствия внешних ключей, фрагментация индексов при использовании случайных UUID, потеря точности в финансовых расчётах из-за FLOAT и магические числа вместо enum-ов. Для большинства приложений достаточно третьей нормальной формы (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 сервера данные теряют смысл.
| Тип данных | Рекомендация |
|---|---|
| INT | BIGINT для PK |
| FLOAT | DECIMAL |
| VARCHAR без CHECK | VARCHAR с CHECK |
Как выбрать первичный ключ: BIGSERIAL или UUID?
-- 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 генерируются на стороне клиента или нужно скрыть предсказуемость.
| Тип | Размер | Производительность записи | Глобальная уникальность |
|---|---|---|---|
| SERIAL (8 байт) | 8 байт | Высокая | Нет |
| UUID v4 | 16 байт | Средняя (фрагментация) | Да |
| ULID | 16 байт | Высокая (упорядочен) | Да |
Как избежать типичных ошибок при проектировании схемы?
Ключ к успеху — заранее продумать паттерны доступа. Если приложение часто читает корзину с товарами, используйте агрегатные поля и избегайте глубоких JOIN. Для исторических данных (заказы) намеренно денормализуйте unit_price, чтобы сохранить снимок цены. Всегда проверяйте, поддерживает ли выбранный PK диапазон роста — BIGSERIAL покрывает 9.2 квинтильона записей, чего хватит на десятилетия.
Пример: схема интернет-магазина
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 — намеренная денормализация: цена продукта изменится со временем, а историческая цена в заказе должна остаться неизменной.
ON DELETE RESTRICT vs CASCADE — правило: CASCADE только когда дочерние записи не имеют смысла без родителя (order_items без order). RESTRICT, когда удаление родителя должно быть явно предотвращено (нельзя удалить категорию с товарами).
Какие индексы создавать сразу и soft delete
Добавляем сразу при создании схемы:
-- 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;
-- Audit table
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()
);
Partial index по WHERE deleted_at IS NULL — активные записи индексируются отдельно. Удалённые записи не попадают в индекс и не замедляют запросы.
Процесс работы и что входит
- Аналитика — выявление бизнес-сущностей, связей, частых запросов.
- Проектирование — построение ER-диаграммы, нормализация, выбор типов.
- Создание DDL — SQL-скрипты с индексами, FK, constraints.
- Документирование — описание схемы, комментарии, guide для разработчиков.
- Аудит — ревью существующей схемы, выявление проблем, рекомендации.
В результат работы входит: ER-диаграмма (до 15 таблиц) в формате PlantUML или Draw.io, SQL DDL с индексами и констрейнтами, документация по схеме (README с описанием таблиц и полей), рекомендации по миграциям и консультация в течение недели после сдачи.
Сроки
Проектирование схемы для нового проекта (до 15 таблиц): 1–2 дня. Ревью и рефакторинг существующей схемы: 1–3 дня. Стоимость рассчитывается индивидуально — напишите нам, и мы оценим ваш проект. Закажите проектирование схемы — мы учтём все нюансы. Получите профессиональную консультацию, даже если не уверены в объёме работ.