Схема нормализации (пример, 3NF/BCNF):
- Users
- user_id (PK)
- email (UNIQUE)
- password_hash
- name
- Addresses
- address_id (PK)
- user_id (FK → Users.user_id)
- country, region, city, street, postal_code
- is_billing, is_shipping
- Products
- product_id (PK)
- sku (UNIQUE)
- title, description, base_price
- Categories
- category_id (PK)
- name, parent_category_id (FK → Categories.category_id) — для иерархии
- ProductCategories
- product_id (FK → Products.product_id)
- category_id (FK → Categories.category_id)
- (PK = product_id, category_id)
- Inventory
- sku_id (PK) или product_id (FK → Products.product_id)
- quantity, warehouse_id
- Prices / Promotions (если сложная логика цен)
- price_id (PK)
- product_id (FK), price, valid_from, valid_to
- Orders
- order_id (PK)
- user_id (FK → Users.user_id)
- billing_address_id (FK → Addresses.address_id)
- shipping_address_id (FK → Addresses.address_id)
- status, created_at, total_amount
- OrderItems
- order_item_id (PK)
- order_id (FK → Orders.order_id)
- product_id (FK → Products.product_id)
- quantity, unit_price, discount_id (FK)
- Payments
- payment_id (PK)
- order_id (FK), amount, method, status, paid_at
- Shipments
- shipment_id (PK)
- order_id (FK), carrier, tracking_number, shipped_at
- Reviews
- review_id (PK)
- product_id (FK), user_id (FK), rating, text, created_at
- (Дополнительно) Coupons, Wishlists, ProductAttributes (EAV или отдельные таблицы для атрибутов)
Почему это нормализовано: данные разделены по смысловым сущностям, повторяющиеся атрибуты вынесены в отдельные таблицы; функциональные зависимости сведены к ключам → соответствие $\text{1NF}$, $\text{2NF}$, $\text{3NF}$ (и при строгом проектировании — BCNF).
Компромиссы между нормализацией и производительностью:
- Плюсы нормализации:
- меньше избыточности и аномалий при обновлении;
- экономия места и консистентность данных.
- Минусы / издержки:
- больше JOIN-ов → увеличение времени выборок (I/O и CPU);
- сложнее покрывать запросы индексами (нужны составные/покрывающие индексы);
- при высоконагруженных OLTP сценариях транзакции могут быть дороже из‑за каскадных FK;
- для аналитики агрегации по многим таблицам медленнее.
- Компромиссы:
- денормализация (кэшированные поля, агрегаты, дублирование колонок) ускоряет чтение, но усложняет и дорожит обновление (нужны триггеры/транзакции/обновления нескольких мест);
- материализованные представления/репликация/кеш (Redis) решают чтение, но усложняют архитектуру и задержку свежести данных;
- выборочная нормализация: нормализовать «горячие» операции записи меньше, а для часто читаемых срезов — иметь денормализованные проекции.
Стратегии смягчения:
- Использовать денормализованные проекции или материализованные VIEW для «частых» запросов.
- Кеширование (Redis, Memcached) и read-replicas.
- Покрывающие индексы для сокращения IO.
- Batch-обновления агрегатов или событийную систему (eventual consistency) для неприоритетных полей.
Примеры запросов, которые становятся дороже после нормализации (с пояснением):
1) Список товаров с категорией, остатком и средним рейтингом — требует нескольких JOIN и агрегации:
SQL:
SELECT p.product_id, p.title, c.name AS category, i.quantity, AVG(r.rating) AS avg_rating
FROM Products p
JOIN ProductCategories pc ON pc.product_id = p.product_id
JOIN Categories c ON c.category_id = pc.category_id
JOIN Inventory i ON i.product_id = p.product_id
LEFT JOIN Reviews r ON r.product_id = p.product_id
GROUP BY p.product_id, p.title, c.name, i.quantity
Пояснение: требуется примерно $3$–$4$ JOIN и агрегирование по Reviews — дорогая операция при большом объёме отзывов и товаров.
2) Полный заказ пользователя (шапка заказа + позиции + адреса + трекинг + скидки):
SQL:
SELECT o.order_id, o.created_at, u.name, ba.*, sa.*, oi.*, p.title, s.tracking_number
FROM Orders o
JOIN Users u ON u.user_id = o.user_id
JOIN Addresses ba ON ba.address_id = o.billing_address_id
JOIN Addresses sa ON sa.address_id = o.shipping_address_id
JOIN OrderItems oi ON oi.order_id = o.order_id
JOIN Products p ON p.product_id = oi.product_id
LEFT JOIN Shipments s ON s.order_id = o.order_id
Пояснение: восстановление полного объекта заказа требует много JOIN; в денормализованной схеме часть адресов/итоговых сумм могла бы храниться в Orders для быстрого чтения.
3) Отчёт «топ‑продаваемые товары за период» — агрегация по OrderItems с join на Products:
SQL:
SELECT p.product_id, p.title, SUM(oi.quantity) AS sold_qty
FROM OrderItems oi
JOIN Orders o ON o.order_id = oi.order_id
JOIN Products p ON p.product_id = oi.product_id
WHERE o.created_at BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY p.product_id, p.title
ORDER BY sold_qty DESC
LIMIT $10$ Пояснение: агрегирование большого объёма строк OrderItems требует сканирования/индексного чтения и сортировки; в денормализации можно держать предагрегаты.
4) Поиск каталога с фильтрами по атрибутам (если атрибуты в отдельных таблицах EAV):
SQL (упрощённо):
SELECT p.*
FROM Products p
JOIN ProductAttributes pa1 ON pa1.product_id = p.product_id AND pa1.attr = 'color' AND pa1.value = 'red'
JOIN ProductAttributes pa2 ON pa2.product_id = p.product_id AND pa2.attr = 'size' AND pa2.value = 'M'
Пояснение: многократные само‑JOIN по таблице атрибутов увеличивают стоимость; альтернативы — inverted index или денормализованный JSON/ключ‑значение с GIN‑индексом.
Короткие рекомендации практического применения:
- Нормализовать данные там, где важна целостность и обновляемость.
- Для частых сложных чтений — создавать денормализованные представления/материализованные таблицы и кешировать их.
- Профилировать реальные запросы и оптимизировать индексы/репликацию под нагрузку.
Если нужно, могу построить физическую схему с именами полей и индексами или показать примеры денормализации для ускорения конкретных запросов.