← Все статьи

Схема базы данных интернет-магазина: товары, заказы и позиции

· 9 мин чтения

DBML-схема для интернет-магазина: таблицы товаров, заказов и позиций. Разбор связей, индексов, типов данных и нормализации. Практический пример с комментариями для PostgreSQL.

Когда продукт-менеджер просит добавить поле «вес брутто» в карточку товара, а разработчик обнаруживает, что вес хранится в трёх местах: в таблице товара, в позиции заказа и в CSV-выгрузке для логистики, возникает классическая проблема рассинхронизации данных. Аналогично, когда бухгалтер пытается сверить сумму заказа, но видит расхождение между полем total_amount в таблице orders и суммой positions.price * quantity в таблице order_items, это сигнал, что схема плохо спроектирована или нарушена целостность данных.

Зачем нужна единая схема данных

Интернет-магазин оперирует тремя ключевыми сущностями: товарами (catalog items), заказами (transactions) и позициями (line items). Товары описывают ассортимент, заказы фиксируют факт покупки, позиции связывают конкретный товар с конкретным заказом и количеством. Без чёткой схемы эти три слоя быстро расходятся: цена товара меняется, но старые заказы сохраняют старую цену; товар удаляется из каталога, но позиции в архивных заказах становятся «висячими»; количество в корзине не совпадает с количеством в заказе.

Реляционная модель позволяет выразить связи и часть ограничений. Обязательные FK подтверждают существование товаров и заказов для строк, а сохранённые цена и сумма отражают выбранный момент покупки. Историческое хранение и согласованность итогов не возникают автоматически из нормализации: для них нужны отдельные правила и проверки.

Основные таблицы и их роли

Минимальная рабочая схема состоит из четырёх таблиц:

  • products — справочник товаров: id, name, sku, price, stock_quantity, created_at, updated_at;
  • orders — шапка заказа: id, customer_id, status, total_amount, currency, created_at, updated_at;
  • order_items — позиции заказа: id, order_id, product_id, quantity, unit_price, line_total;
  • customers — покупатели: id, email, full_name, phone, created_at.

Таблица order_items является связующей (junction table) между orders и products. Она разрешает многие-ко-многим связь: один заказ содержит много позиций, одна позиция относится к одному заказу, но один товар может фигурировать во многих позициях разных заказов. Критически важно, что unit_price дублируется из products.price в момент оформления заказа. Это позволяет сохранять историю цен: если завтра цена товара изменится, старые заказы продолжут отображать ту цену, по которой покупатель действительно платил.

Типы данных и их обоснование

Выбор типов данных в PostgreSQL влияет на точность, производительность и совместимость. Ниже — рекомендации для основных полей:

ПолеТипОбоснование
id (все таблицы)BIGINT GENERATED ALWAYS AS IDENTITYАвтоматический первичный ключ; BIGINT поддерживает >9×10¹⁸ записей; IDENTITY проще, чем SERIAL
price, total_amount, line_totalNUMERIC(12,2)Точное хранение денег; FLOAT приводит к округлениям; 12 цифр до запятой, 2 после
quantitySMALLINTКоличество единиц товара в позиции; SMALLINT (max 32767) достаточно для большинства магазинов
statusVARCHAR(20)Статус заказа: pending, paid, shipped, delivered, cancelled; VARCHAR гибче ENUM, который сложно менять
created_at, updated_atTIMESTAMPTZВременная метка с таймзоной; TIMESTAMPTZ хранит UTC, что критично для глобальных систем
emailVARCHAR(255)Email клиента; уникальный индекс обязателен
skuVARCHAR(64)Артикул товара; уникальный идентификатор в системе учёта

Использование NUMERIC для денежных величин — не рекомендация, а требование. Float8 (double precision) хранит числа в двоичном виде, что делает невозможным точное представление десятичных дробей вроде 0.1. Попытка сложить 0.1 + 0.2 в float даст 0.30000000000000004, что неприемлемо для финансовых операций. NUMERIC хранит значения как строки цифр, обеспечивая точную арифметику.

Связи и ограничения целостности

Foreign keys обеспечивают referential integrity: нельзя удалить товар, на который ссылаются активные позиции заказов, и нельзя создать позицию заказа, указывая несуществующий id товара или заказа. В DBML это выражается через refers to.

Для order_items.product_id следует выбрать ON DELETE RESTRICT. Это предотвратит удаление товара из каталога, если он присутствует хотя бы в одном заказе. Альтернатива — ON DELETE SET NULL — сделала бы product_id nullable, что усложняет запросы и теряет связь с каталогом. ON DELETE CASCADE недопустим: удаление товара должно автоматически стирать всю историю покупок, что разрушает аудит-трейл.

Для order_items.order_id допустим ON DELETE CASCADE: если заказ удаляется администратором, его позиции должны удаляться вместе с ним. Однако в продакшене прямое удаление заказов редко практикуется; чаще используется soft delete через флаг is_deleted или смену статуса на cancelled.

PostgreSQL не создаёт индекс на ссылающихся колонках FK автоматически. Индекс order_items.order_id может быть полезен для чтения строк и проверки зависимостей при удалении, но его необходимость подтверждают запросами, объёмом и планом. Отсутствие индекса не означает, что любой JOIN обязательно использует один и тот же план.

DBML-схема с комментариями

Ниже — полная DBML-схема для PostgreSQL 16. Комментарии объясняют каждую часть. Формат DBML позволяет экспортировать схему в SQL DDL, что упрощает миграции.

// Таблица товаров
Table products {
  id bigint [pk, increment]
  name varchar(255) [not null]
  sku varchar(64) [unique, not null] // Артикул
  description text [null]
  price numeric(12,2) [not null, default: 0] // Цена в базовой валюте
  stock_quantity smallint [default: 0] // Текущий остаток на складе
  category_id bigint [null] // Ссылка на категорию (не показана)
  created_at timestamptz [default: `now()`, not null]
  updated_at timestamptz [default: `now()`, not null]
}

// Таблица покупателей
Table customers {
  id bigint [pk, increment]
  email varchar(255) [unique, not null]
  full_name varchar(255) [not null]
  phone varchar(20) [null]
  created_at timestamptz [default: `now()`, not null]
}

// Таблица заказов (шапка)
Table orders {
  id bigint [pk, increment]
  customer_id bigint [ref: > customers.id, not null]
  status varchar(20) [default: 'pending', not null] // pending, paid, shipped, delivered, cancelled
  total_amount numeric(12,2) [default: 0, not null] // Итоговая сумма заказа
  currency char(3) [default: 'USD', not null] // ISO 4217
  notes text [null]
  created_at timestamptz [default: `now()`, not null]
  updated_at timestamptz [default: `now()`, not null]
  indexes {
    customer_id [name: 'idx_orders_customer']
  }
}

// Таблица позиций заказа (join table)
Table order_items {
  id bigint [pk, increment]
  order_id bigint [ref: > orders.id, not null]
  product_id bigint [ref: > products.id, not null]
  quantity smallint [default: 1, not null]
  unit_price numeric(12,2) [not null] // Цена на момент покупки
  line_total numeric(12,2) [not null] // quantity * unit_price
  created_at timestamptz [default: `now()`, not null]
  indexes {
    order_id [name: 'idx_order_items_order']
    product_id [name: 'idx_order_items_product']
  }
}

// UNIQUE для sku и email уже создаёт индексы в PostgreSQL.




Поле line_total дублирует вычисляемое значение quantity * unit_price. Это denormalization, но оправданная: она ускоряет агрегатные запросы (SUM(line_total)) и защищает от ошибок вычисления при чтении. Обновление line_total должно происходить в той же транзакции, что и изменение quantity или unit_price, либо через trigger.

Учебный пример: создание заказа

Рассмотрим сценарий: клиент с id=101 оформляет заказ на 2 шт. товара id=501 (цена 19.99) и 1 шт. товара id=502 (цена 49.50).

-- Шаг 1: Начинаем транзакцию
BEGIN;

-- Шаг 2: Создаём заказ
INSERT INTO orders (customer_id, status, total_amount, currency)
VALUES (101, 'paid', 89.48, 'USD')
RETURNING id;
-- Допустим, возвращён id = 9001

-- Шаг 3: Создаём первую позицию
INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
VALUES (9001, 501, 2, 19.99, 39.98);

-- Шаг 4: Создаём вторую позицию
INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)
VALUES (9001, 502, 1, 49.50, 49.50);

-- Шаг 5: Коммит
COMMIT;

Сумма 89.48 равна 39.98 + 49.50. Если бы мы хранили только unit_price и quantity, вычисление суммы требовало бы JOIN и SUM, что медленнее. Денормализация total_amount в orders и line_total в order_items — компромисс между нормализацией и производительностью чтения.

В учебном профиле total_amount должен равняться сумме line_total. Это межстрочное правило не выражается обычным CHECK одной строки PostgreSQL. Возможны согласованная атомарная процедура, триггер с корректной обработкой конкуренции или вычисление итога при чтении. Выбор проверяют на нескольких одновременных изменениях строк, а не только на одном сохранении.

Проверка целостности данных

После наполнения схемы полезно выполнить проверочные запросы, выявляющие рассинхронизацию:

-- Проверка 1: заказы с нулевой суммой, но имеющими позиции
SELECT o.id, o.total_amount, COALESCE(SUM(oi.line_total), 0) AS calculated_sum
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.total_amount
HAVING o.total_amount != COALESCE(SUM(oi.line_total), 0);

-- Результат: пусто, если данные консистентны

-- Проверка 2: позиции, ссылающиеся на несуществующие товары
SELECT oi.id, oi.product_id
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.id
WHERE p.id IS NULL;

-- Результат: пусто, если FK работают корректно

-- Проверка 3: заказы без позиций
SELECT id FROM orders WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.order_id = orders.id
);

Эти запросы должны возвращать пустые результаты в здоровой системе. Если они находят записи, это указывает на нарушение логики приложения или ручное редактирование БД.

Частые ошибки проектирования

  • Хранение цены только в products — при изменении цены теряется история: старые заказы начинают отображать новую цену. Решение: дублировать unit_price в order_items.
  • Использование FLOAT для денег — приводит к накоплению ошибок округления. Решение: NUMERIC(12,2).
  • Отсутствие индексов на FK — JOINs выполняются медленно. Решение: явное создание индексов на order_items.order_id и order_items.product_id.
  • ON DELETE CASCADE на product_id — удаление товара стирает историю заказов. Решение: ON DELETE RESTRICT.
  • Хранение количества в orders — например, поле total_items. Это избыточно: количество вычисляется из sum(quantity) in order_items. Избыточные поля создают риск рассинхронизации.
  • Nullable product_id в order_items — делает невозможным гарантированную связь с каталогом. Решение: not null.

Ограничения и крайние случаи

Схема выше не учитывает несколько важных аспектов реального магазина:

  • Варианты товара — размер, цвет, конфигурация. Требуют отдельной таблицы product_variants с FK на products. Позиция заказа должна ссылаться на variant_id, а не только на product_id.
  • Скидки и налоги — требуют дополнительных колонок в order_items (discount, tax) или отдельной таблицы discounts. Сумма line_total должна включать или исключать налоги согласно бизнес-правилам.
  • Soft delete — вместо физического удаления товаров или заказов лучше использовать флаг is_deleted или timestamp deleted_at. Это сохраняет историю для аудита.
  • Мультиязычность — названия товаров могут требовать таблицы product_translations с locale.

Эти расширения увеличивают сложность схемы, но необходимы для полноценного e-commerce. Начинать стоит с базовой модели, описанной выше, и добавлять специализированные таблицы по мере необходимости.

Как использовать DBML в работе

DBML-файл можно открыть в редакторе DBML, отредактировать и экспортировать в SQL DDL для PostgreSQL. Это позволяет держать схему в текстовом формате, версионировать её в git и применять миграции. Для понимания общих принципов построения моделей данных см. статью «Концептуальная, логическая и физическая модель данных».

При доработке схемы помните: любое изменение структуры требует обновления миграций, тестов и документации. Схема данных — живой артефакт, который эволюционирует вместе с продуктом.