Нормализация данных в аналитике: как выбрать схему

Если хранить имя покупателя и название товара прямо в каждой строке продажи, обновление каталога быстро превращается в задачу с ловушками: где-то останется старое имя, а отчёт начнёт считать одного клиента несколькими. Нормализация убирает такие дубли, но в аналитике за неё платят дополнительными связями и более сложными запросами. Ниже — рабочий способ спроектировать исходные таблицы и понять, где остановиться; примеры рассчитаны на PostgreSQL 16.

Сначала договоритесь, что именно нормализуете

Здесь речь о нормализации структуры реляционных данных: таблицы делят по сущностям и связывают ключами. Это не статистическая нормализация признаков, когда, например, числовые значения приводят к общему масштабу перед анализом модели.

Нормализация нужна, чтобы факт об одном объекте хранился в одном месте. Если адрес клиента записан в тысяче строк заказов, исправить его без пропусков сложно. Если заказ и товар разделены, имя товара не приходится дублировать в каждой продаже. При этом полная нормализация не означает, что аналитическую витрину тоже нужно сделать максимально разветвлённой.

Сноска. Нормальные формы — правила организации таблиц. В прикладной работе чаще всего говорят о первых трёх: атомарных значениях в ячейках, зависимости полей от всего ключа строки и отсутствии зависимостей между неключевыми полями. СУБД не проверяет автоматически, что ваша схема соответствует этим правилам: за смысл таблиц отвечает модель данных.

Хорошая схема хранит каждый факт в подходящем месте. Хорошая аналитическая витрина при этом не обязана повторять устройство исходной базы.

Отталкивайтесь от зерна данных

До создания таблиц сформулируйте зерно — что означает одна строка в каждой таблице. Например: одна строка заказа, одна строка позиции заказа, одна строка клиента. Если это не определить, легко сложить сумму заказа по каждой его позиции несколько раз или сравнить несопоставимые показатели.

Для продаж полезно разделить заказ и его позиции. В заказе находятся дата и покупатель; в позиции — конкретный товар, количество и цена на момент покупки. Текущая цена товара в каталоге не подходит для пересчёта старой выручки: она могла измениться после продажи.

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

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    current_name text NOT NULL
);

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(customer_id),
    ordered_at timestamptz NOT NULL
);

CREATE TABLE order_items (
    order_id bigint NOT NULL REFERENCES orders(order_id),
    line_no integer NOT NULL CHECK (line_no > 0),
    product_id bigint NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price_at_sale numeric(12, 2) NOT NULL CHECK (unit_price_at_sale >= 0),
    PRIMARY KEY (order_id, line_no)
);

В этой модели цена зафиксирована в позиции продажи, а не берётся из текущего каталога. Если важно сохранить и название товара именно на дату покупки, предусмотрите исторические версии товара или снимок названия в позиции. Выбор зависит от того, какие отчёты нужны и как источник трактует переименование.

Рабочий порядок проектирования

1. Запишите зерно каждой таблицы. Например, строка order_items — одна позиция внутри заказа. Проверьте на примере заказа с тремя товарами: в таблице должно быть три строки, а не одна строка с перечнем через запятую.
2. Выделите сущности и повторяющиеся факты. Покупатели, товары, заказы и позиции обычно имеют разные жизненные циклы. Если адрес клиента повторяется в каждой продаже, вынесите его в таблицу клиентов. Проверка: изменение адреса не требует править историю всех заказов.
3. Назначьте ключи и ограничения. Каждая строка должна однозначно определяться ключом; внешние ключи связывают позиции с заказами и товарами. Ограничения помогают отвергать некорректные данные при записи, но сами по себе не проверяют бизнес-смысл. Проверка: база не принимает позицию с несуществующим товаром или отрицательным количеством.
4. Решите, какие значения должны сохраняться исторически. Цена продажи — факт операции, а актуальное название товара — атрибут каталога на текущий момент. Для каждого отчёта уточните, нужны ли значения на дату события. Проверка: переименование товара сегодня не меняет задним числом историческую выручку или разрез, где требуется старое название.
5. Проверьте аналитический запрос на реальных сценариях. Посчитайте выручку по дням и товарам, число заказов и уникальных клиентов. Сверьте результат с контрольным набором заказов, включая заказ с несколькими позициями. Проверка: сумма заказа не умножается на число его строк после соединения таблиц.

Где заканчивается нормализация и начинается витрина

Нормализованная схема удобна для согласованной записи и обновлений. Но для каждого отчёта аналитик может быть вынужден соединять несколько таблиц. В витрине данные часто раскладывают по модели «звезда»: таблица фактов содержит события и измеримые показатели, а таблицы измерений — описания товара, покупателя, даты и других разрезов. Это осознанная денормализация ради понятных запросов, а не ошибка проектирования.

Не переносите в витрину каждое поле «на всякий случай». Сначала зафиксируйте пользователей, показатели и частые разрезы. Для схемы со звездой укажите зерно таблицы фактов и проверьте, что каждая строка соответствует одному событию или другой чёткой единице учёта.

Как выбрать подход и что ломается

Оставляйте данные нормализованными на входе, если источник обновляет сущности отдельно, а ошибки дублирования дорого обходятся. Стройте звезду, если одни и те же отчёты регулярно соединяют транзакции с описаниями объектов. Плоскую таблицу используйте для ограниченной задачи, когда потребитель и срок жизни выгрузки понятны. Универсального числа таблиц, после которого схему пора денормализовать, нет.

Типичные проблемы обычно возникают не из-за «слишком большого числа JOIN», а из-за неоговорённых правил:

• Сумма заказа считается несколько раз. Заказ со множеством позиций соединяют с позиционной таблицей, а затем суммируют сумму на уровне заказа. Сначала агрегируйте данные на нужном зерне или считайте сумму из цены и количества каждой позиции.
• Исторические отчёты меняются после обновления каталога. Отчёт соединяет продажу с текущим названием или категорией. Решите, требуется ли текущая классификация или состояние на дату продажи, и храните историю, если она нужна.
• Внешние ключи не помогают при загрузке. Данные приходят из нескольких систем, а идентификаторы в них могут совпадать. Используйте отдельные ключи источников или явное соответствие идентификаторов; не считайте текстовое имя надёжным ключом.
• Витрина расходится с исходными данными. Повторная загрузка может создать дубли, а исправление события — не попасть в агрегат. Определите, как обрабатываются повторные записи и изменения, и сверяйте число событий и контрольные суммы с источником.

В PostgreSQL 16 внешние ключи и ограничения целостности описывают допустимые связи и значения, но не заменяют проверку аналитических расчётов. Для скорости сначала измерьте конкретный запрос на репрезентативных данных и проверьте план выполнения; денормализация без такого измерения может лишь добавить второй источник расхождений.

Что сделать дальше

Возьмите один отчёт, который регулярно строит команда, и выпишите для него зерно, показатели и нужные разрезы. Затем проследите каждый показатель до исходных строк и проверьте два крайних случая: заказ с несколькими позициями и изменение атрибута товара после продажи. Если расчёт нельзя объяснить без оговорок, сначала уточните модель и определения данных, а уже потом ускоряйте запрос.