Ниже — практическое руководство по нормализации ER-схемы магазина (Customers, Orders, Products, OrderItems) до 3NF, разбор возможных избыточностей и аномалий, а также рекомендации по индексам и оптимизации запроса «топ‑10 продуктов по выручке за квартал».

Нормализация до 3NF (шаги и правила)
Исходные сущности:Customers (customer_id, name, email, phone, address, city, zip, …)Orders (order_id, customer_id, order_date, status, shipping_address, …)Products (product_id, name, sku, price, category_id, …)OrderItems (order_id, product_id, quantity, unit_price, discount, …)

1NF (атомарность):

Все поля атомарны (нет множественных значений в одном поле). Если в Customers хранится несколько телефонов в одной строке — вынести в отдельную таблицу CustomerPhones(customer_id, phone_type, phone).

2NF (устранение частичных зависимостей от составного ключа):

OrderItems: естественный составной ключ (order_id, product_id). Все атрибуты, зависящие только от product_id (например, product_name, product_sku), должны быть вынесены в Products.Если в Orders хранится customer_name, customer_email — это частичная/избыточная зависимость → данные Customer должны быть в Customers, в Orders — только customer_id.

3NF (устранение транзитивных зависимостей):

Если в Customers хранится city и city -> region, country, то region/country не должны транзитивно храниться в Customers (либо хранить только ссылку на таблицу Cities).В Orders не храните информацию, вычисляемую через customer_id или product_id (например, customer_loyalty_level) — либо ссылаться на Customers, либо вынести в отдельную таблицу с актуализацией.

Специфика ценообразования:

Для корректных расчётов выручки важно хранить цену товара на момент покупки в OrderItems (unit_price или price_at_order). Иначе при изменении Products.price исторические продажи «испортятся».Схема корректно нормализована, если OrderItems содержит (order_id, product_id, quantity, unit_price, discount), а Products содержит текущую цену и параметры товара.

Частые избыточности и аномалии (примерные сценарии)

Избыточности:Хранение customer_name, customer_address в Orders → повторяющаяся информация для каждого заказа одного клиента.Хранение product_name/product_description в OrderItems → дублирование больших текстов и изменение в одном месте не обновит прошлые заказы.Аномалии при обновлении:Update anomaly: изменение цены в Products приведёт к искажению исторических выручек, если unit_price не сохранён в OrderItems.Insert anomaly: если Orders содержит customer-details, и приложение требует заполнить customer-details при создании заказа — возможны частичные/неполные записи клиентов.Delete anomaly: если все заказы клиента удалены и клиентские данные находятся только в Orders, то информация о клиенте теряется.Решения:Хранить snapshot цены в OrderItems.Хранить полные данные о клиенте в Customers; в Orders — ссылку customer_id.Ограничения FK + правильные ON DELETE (RESTRICT обычно безопаснее; CASCADE — осторожно).

Индексация для запроса «топ‑10 продуктов по выручке за квартал»
Цель: быстро посчитать SUM(quantity * unit_price) для продуктов за период по orders.order_date.

Рекомендации индексов (общие, подходят для Postgres/MySQL):

На таблице Orders:

Индекс по дате и статусу: (order_date) или составной (order_date, status).CREATE INDEX idx_orders_order_date_status ON orders(order_date, status);Если вы часто фильтруете по range и status — составной индекс помогает.

На таблице OrderItems:

Индекс для соединения с Orders и агрегации по product_id:Индекс по order_id: CREATE INDEX idx_orderitems_order_id ON order_items(order_id);Индекс по product_id: CREATE INDEX idx_orderitems_product_id ON order_items(product_id);Лучший вариант — покрывающий/композитный индекс, который позволяет index-only scan:Postgres: CREATE INDEX idx_oi_prod_ord_qty_price ON order_items(product_id, order_id) INCLUDE (quantity, unit_price);MySQL: CREATE INDEX idx_oi_prod_ord_qty_price (product_id, order_id, quantity, unit_price);Обоснование: агрегирование по product_id с фильтром по order_date→используется сначала индекс по orders.order_date для отбора order_id, потом индекс order_items.order_id для доступа. Покрывающий индекс уменьшит количество обращений к данным.

На таблице Products:

Индекс по product_id — обычно PK. Можно создать индекс по (category_id) при необходимости дополнительной фильтрации.

Дополнительно:

Партиционирование Orders (range по order_date, например по месяцам/кварталам) сильно ускорит фильтр по дате и уменьшит объем сканируемых данных для больших БД.Материализованные/сводные таблицы: pre-aggregate (product_id, date, revenue) или (product_id, month, revenue) — лучший вариант для быстрых отчётов. Обновление по расписанию (ежедневно/часово) или инкрементально при поступлении заказов.Пример оптимизированного запроса (общая версия)
Корректный запрос (учитывает unit_price в OrderItems — важный момент):

SELECT p.product_id,
p.name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_date >= '2025-01-01' -- начальная дата квартала
AND o.order_date < '2025-04-01' -- следующая дата (исключительно)
AND o.status = 'completed' -- при необходимости
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 10;

Нюансы оптимизации:

Не подтягивайте поля из Products, которые не нужны для группировки/вывода (минимизируйте JOIN).Не используйте подзапросы/CTE, которые препятствуют оптимизации (в Postgres prior to v12 CTE является optimization fence). Лучше использовать простой JOIN на (SELECT id FROM orders WHERE ...) или прямой JOIN.Если Orders очень велик, сначала фильтровать Orders по индексу по order_date, получить нужные order_id, затем агрегировать по order_items, чтобы избежать сканирования лишних order_items.

Альтернативная конструкция (чтобы сначала отбросить лишние заказы — помогает в некоторых планировщиках):

SELECT p.product_id, p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM (
SELECT order_id
FROM orders
WHERE order_date >= '2025-01-01'
AND order_date < '2025-04-01'
AND status = 'completed'
) AS o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 10;

Дополнительные оптимизации и операционные рекомендации

Проверить план выполнения (EXPLAIN [ANALYZE]) и смотреть:Использует ли индекс по order_date?Какой порядок JOIN — полезно, чтобы фильтрация по датам применялась первой.Параметры СУБД:Для больших агрегаций увеличить work_mem (Postgres) для избежания disk spill при сортировке/aggregating.Обновить статистику (ANALYZE).Материализованные/инкрементальные агрегаты:Создать таблицу daily_product_revenue(product_id, day, revenue) и обновлять её при обработке заказов.Тогда запрос «топ-10 за квартал» — простая агрегация по этой таблице, быстро и эффективно.Архивирование:Старые заказы архивировать/переносить, если оперативные отчёты работают только по свежим периодам.

Контракты целостности и дизайн PK/FK

PK: customers.customer_id, orders.order_id, products.product_id, order_items(order_id, product_id) или отдельный surrogate id для order_items.FK: orders.customer_id → customers(customer_id); order_items.order_id → orders(order_id); order_items.product_id → products(product_id).Constraints: CHECK на quantity > 0, unit_price >= 0; ENUM/lookup для status.

Краткое резюме практики для «топ‑10 по выручке за квартал»

Храните unit_price в OrderItems (цена на момент продажи).Сделайте индекс по orders.order_date (и status, если есть), и покрывающий индекс по order_items(product_id, order_id, quantity, unit_price).Рассмотрите партиционирование Orders по дате и/или pre-aggregated таблицу (materialized view / daily_product_revenue) для мгновенных отчётов.Отладьте и проверьте EXPLAIN ANALYZE; при необходимости увеличьте память для агрегаций и обновите статистику.

Если хотите, могу:

Предложить конкретные DDL/CREATE INDEX под вашу СУБД (Postgres или MySQL) и объём данных,Составить пример materialized view и стратегию его обновления,Проанализировать EXPLAIN‑план вашего текущего запроса (вставьте вывод).