Troubleshooting — Data Modeling

Причина

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

Решение

1. Вынесите многозначный атрибут в отдельную таблицу: связь сущности с тегом моделируется junction-таблицей (entity_id, tag_id) с двумя внешними ключами. Теперь можно индексировать, джойнить, считать без парсинга. Если данные действительно неструктурированы и не запрашиваются по элементам — рассмотрите нативный массив или JSONB, но это осознанное исключение, а не дефолт.

Причина

FK-ограничения убрали ради скорости вставки или из-за миграций. В результате ссылочную целостность никто не гарантирует: появляются заказы с несуществующим customer_id, «сиротские» строки накапливаются молча.

Решение

1. Добавьте FK-ограничения для всех связей в OLTP. База будет отклонять некорректные вставки и удаления — это дешевле, чем чинить рассогласованные данные потом. Обязательно создайте индекс на каждом FK-столбце: без него проверки и каскадные действия делают полный скан. Сначала почистите существующих сирот, затем включайте constraint.

Причина

Smart key кодирует в себе город, год, категорию. Когда товар переезжает на другой склад или меняет категорию — ключ должен поменяться, а за ним каскадно все ссылки в FK. Изменение бизнес-правила ломает идентификатор.

Решение

1. Используйте суррогатный первичный ключ без бизнес-смысла (integer identity или UUID). «Умную» строку оставьте как обычный атрибут с UNIQUE-ограничением, если она нужна бизнесу. Идентификатор обязан быть стабильным; всё, что может измениться, не должно быть ключом.

Причина

Natural key выбран как PK, потому что он уникален. Но бизнес-данные меняются: смена email требует UPDATE PK и каскадного обновления всех ссылающихся FK во всех таблицах — дорого и рискованно.

Решение

1. Сделайте PK суррогатным и стабильным. Естественный ключ оставьте атрибутом под UNIQUE-ограничением — он по-прежнему обеспечивает бизнес-уникальность, но его смена больше не трогает ни PK, ни FK. Это базовый аргумент в пользу суррогатных ключей.

Причина

Нарушено правило «все строки fact-таблицы — одного grain». Любая агрегация теперь рискует посчитать одни и те же деньги дважды: и в детальной строке, и в агрегатной.

Решение

1. Объявите единственный grain до проектирования fact-таблицы и держите только его. Для разных уровней детализации делайте отдельные таблицы: одна с атомарным зерном (строка на позицию чека), другая — агрегат (строка на день и категорию), построенный из первой. Не смешивайте уровни в одной таблице.

Причина

Dimension перезаписывает атрибут (Type 1), хотя для отчётности важно, каким он был в момент события. История потеряна безвозвратно: при перезаписи города клиента все прошлые продажи «переезжают» в новый город.

Решение

1. Определяйте тип SCD из требований к отчётности до загрузки. Нужна история — Type 2 (новая строка с surrogate key, effective/end date, current flag). Достаточно текущего состояния — Type 1. Нужно «текущее и предыдущее» — Type 3. Выбор делается на этапе дизайна; вернуть потерянную историю задним числом нельзя.

Причина

Реляционная модель не поддерживает M:N напрямую. Попытка обойтись массивом id или столбцами item_1, item_2, item_3 ломает индексацию, ограничивает число связей и нарушает 1NF.

Решение

1. Введите junction-таблицу с двумя внешними ключами на связываемые сущности; её первичный ключ — составной из этих FK. При необходимости она несёт и собственные атрибуты связи (количество, дата). Это единственный корректный способ моделировать M:N в реляционной БД.

Причина

Привычка нормализовать всё, перенесённая из OLTP в аналитику. Каждый аналитический запрос теперь делает десяток JOIN вместо одного, а понятность схемы для аналитика теряется.

Решение

1. В размерной модели dimension намеренно денормализуют — описательные атрибуты держат в одной широкой таблице. Снежинка оправдана лишь в редких случаях (огромные измерения, разделяемые иерархии). По умолчанию — плоская dimension и один JOIN до неё.

Причина

EAV выбран ради «гибкости» — чтобы добавлять атрибуты без ALTER TABLE. Но запрос даже пяти атрибутов сущности требует пяти self-join, типы данных теряются (всё value — строка), а целостность не проверить.

Решение

1. Моделируйте известные атрибуты обычными типизированными столбцами — это и есть работа с данными. EAV допустим лишь для действительно открытого набора пользовательских атрибутов; даже тогда часто лучше JSONB-столбец. Не превращайте EAV в схему всей базы.

Причина

Кажется, что date-столбца в fact-таблице достаточно. Но без date dimension нельзя удобно фильтровать по кварталу, фискальному году, флагу выходного или праздника — каждый отчёт пишет эту логику заново.

Решение

1. Создайте отдельное date dimension со строкой на день и атрибутами: год, квартал, месяц, день недели, флаги выходного и праздника, фискальные периоды. Заполните его заранее на нужный диапазон лет. Fact-таблица ссылается на него внешним ключом (часто «умным» integer вида YYYYMMDD).

Причина

Случайный UUIDv4 не упорядочен. Каждая вставка попадает в случайное место B-tree, вызывая расщепление страниц, фрагментацию индекса и плохую локальность кэша. Таблица растёт — деградация ускоряется.

Решение

1. Если нужен распределённо генерируемый ключ — берите упорядоченный по времени идентификатор: UUIDv7 или Snowflake ID. Вставки идут в конец индекса, страницы заполняются плотно. Если распределённая генерация не нужна — обычный integer identity ещё компактнее и быстрее.

Причина

Денормализованная плоская таблица дублирует атрибут товара во многих строках. Обновление цены требует найти и поправить все строки; пропуск части — рассогласованные данные, которые СУБД не предотвратит.

Решение

1. Нормализуйте: справочные атрибуты товара храните один раз в таблице товаров, в строке заказа держите только FK на товар. Тогда цена в одном месте. Внимание: цену на момент продажи в строке заказа хранить как раз нужно — это исторический факт, а не справочный дубль. Различайте справочные данные и снимок факта.

Причина

Отношение разбили на проекции по атрибутам, не образующим общего ключа. Соединение таких проекций даёт лишние, не существовавшие в оригинале строки — декомпозиция не lossless.

Решение

1. Декомпозиция без потерь требует, чтобы общий атрибут проекций был суперключом хотя бы одной из них. Разбивайте отношение строго по функциональным зависимостям: левая часть FD становится связующим ключом. После декомпозиции проверьте lossless-join формально, а не на глаз.

Причина

Трёхзначная логика SQL. Сравнение NULL != 'closed' даёт UNKNOWN, а WHERE пропускает только TRUE. Строки с NULL молча выпадают из результата, хотя интуитивно должны были попасть.

Решение

1. Обрабатывайте NULL явно: WHERE status != 'closed' OR status IS NULL, либо WHERE COALESCE(status, 'unknown') != 'closed'. На уровне модели реши, допустим ли NULL в этом столбце вообще: если каждая строка обязана иметь статус — поставь NOT NULL и значение по умолчанию, и проблема исчезнет.

Причина

3NF отлично подходит для OLTP, но для аналитики десятки JOIN на каждом запросе убивают производительность и делают SQL нечитаемым. Транзакционная схема не предназначена для сканов и агрегаций.

Решение

1. Постройте отдельный аналитический слой — размерную модель (star schema) или OBT — и наполняйте его ELT-процессом из OLTP. Аналитика работает с денормализованными витринами, OLTP остаётся нормализованным. Это две разные модели для двух разных нагрузок, а не одна на всё.

Причина

Остаток, уровень запасов, число активных подписчиков — semi-additive меры. Их можно складывать по другим измерениям, но не по времени: сумма дневных остатков за месяц не имеет смысла.

Решение

1. Классифицируйте каждую меру при дизайне fact-таблицы. Для semi-additive мер по оси времени применяйте не SUM, а последнее значение периода или среднее. Зафиксируйте тип аддитивности в документации модели и в семантическом слое, чтобы BI не суммировал такие меры по ошибке.

Причина

Название категории, имя клиента, регион записаны строками прямо в каждую строку fact-таблицы. Таблица раздувается, повторяющийся текст плохо сжимается, а изменение названия требует UPDATE миллионов строк.

Решение

1. В fact-таблице держите только внешние ключи на измерения и числовые меры. Описательные атрибуты — в dimension-таблицах. Исключение — degenerate dimension (номер заказа), у которого нет своих атрибутов и которому отдельная таблица не нужна.

Причина

Процесс загрузки вставляет новую версию, но забывает проставить end date и снять current flag у предыдущей. В результате на одну сущность две «текущие» строки, и любой JOIN с fact-таблицей даёт дубли.

Решение

1. Загрузка SCD2 — это две операции: закрыть старую версию (end date = дата изменения, current flag = N) и вставить новую (effective date = дата изменения, end date = NULL или 9999-12-31, current flag = Y). Выполняйте их в одной транзакции. Проверьте инвариант: на каждую сущность ровно одна строка с current flag = Y.

Причина

Чтобы найти версию строки, действовавшую на дату, СУБД делает range scan по паре effective/end date для каждой сущности. На большой dimension с длинной историей это дорого.

Решение

1. Для частых запросов «как было на дату» постройте PIT-таблицу: для нужных дат заранее посчитайте, какая версия каждой строки актуальна. Запрос превращается в простой equi-join вместо range scan. Также поставьте индекс на current flag — запросы «текущее состояние» станут мгновенными.

Причина

Fact соединяется с dimension через bridge с большим fan-out — одна fact-строка размножается на число связей. SUM по мере складывает одну и ту же сумму многократно.

Решение

1. Добавьте в bridge-таблицу весовой коэффициент (allocation factor), сумма которого по группе равна единице, и умножайте меру на него — тогда распределение корректно. Либо явно решите, что для этого отчёта нужен не аллоцированный, а полный (impact) показатель, и не суммируйте по дублирующему измерению.

Причина

Составной первичный ключ (a_id, b_id) автоматически создаёт индекс с порядком столбцов a_id, b_id. Запросы и JOIN, начинающиеся с b_id, этот индекс использовать не могут и делают полный скан.

Решение

1. Создайте отдельный индекс на втором внешнем ключе (b_id) — тогда соединения с обеих сторон связи M:N будут индексными. В целом помните: порядок столбцов в составном индексе важен, индекс работает слева направо по префиксу.

Причина

Сущность с отношением 1:1 разбита на две таблицы «для порядка». Но связь 1:1 чаще всего означает, что это одна сущность, и разделение лишь добавляет JOIN на каждом чтении.

Решение

1. По умолчанию объединяйте 1:1 в одну таблицу. Разделение оправдано только при конкретной причине: редко используемые «тяжёлые» столбцы (большой текст, blob), разные требования к доступу/безопасности, или опциональная часть данных, которая есть лишь у меньшинства строк.

Причина

Иерархию пытались уложить фиксированными столбцами level_1_manager, level_2_manager — глубина ограничена, а изменение структуры ломает схему. Либо self-FK есть, но обход дерева не продуман.

Решение

1. Моделируйте иерархию само-ссылающимся внешним ключом manager_id на PK той же таблицы — глубина не ограничена. Для обхода используйте рекурсивный CTE (WITH RECURSIVE). Если иерархия часто запрашивается в аналитике, разверните её в bridge-таблицу иерархии или closure table.

Причина

3NF допускает зависимость X->A, где A — prime атрибут, а X — не суперключ. Такая FD оставляет избыточность и аномалии обновления, которые 3NF формально разрешает.

Решение

1. Проверьте каждую нетривиальную FD: если её левая часть не суперключ — отношение не в BCNF. Декомпозируйте до BCNF (она всегда lossless). Учтите компромисс: BCNF-декомпозиция может не сохранять зависимости — иногда сознательно останавливаются на 3NF, но это должно быть осознанным решением, а не недосмотром.

Причина

В одной таблице соседствуют, например, навыки сотрудника и его проекты — два независимых many-valued факта. Чтобы покрыть все комбинации, приходится хранить декартово произведение: нарушение 4NF, многозначная зависимость.

Решение

1. Разнесите независимые многозначные факты по отдельным таблицам: одна — сотрудник-навык, другая — сотрудник-проект. Число строк падает с произведения до суммы, а добавление навыка больше не требует дублирования по всем проектам. Это и есть приведение к 4NF.

Причина

Денормализацию применили без дисциплины: один и тот же атрибут продублирован в нескольких таблицах, но процесс синхронизации не описан. Источник истины потерян.

Решение

1. Денормализация — осознанное решение, а не побочный эффект. Зафиксируйте единый источник истины для каждого атрибута, а дубли стройте как производные ELT-процессом из него. В аналитическом слое денормализация нормальна именно потому, что таблицы пересоздаются из нормализованного источника, а не правятся вручную.

Причина

Привычка реляционного моделирования: сначала сущности и связи, потом запросы. В wide-column СУБД нет JOIN — схема, спроектированная от связей, потребует соединений, которых движок не делает.

Решение

1. В wide-column применяйте query-first моделирование: начните со списка запросов приложения и спроектируйте таблицу под каждый. Дублирование одних данных по нескольким таблицам — норма. Выбирайте partition key так, чтобы данные одного запроса лежали в одной партиции, а нагрузка распределялась равномерно по кластеру.

Причина

Реляционная нормализация перенесена в документную модель: данные разнесены по коллекциям и связаны ссылками. Чтобы собрать один экран, приложение делает несколько последовательных запросов вместо одного чтения документа.

Решение

1. Решайте embedding против referencing по шаблону доступа. Если связанные данные читаются вместе, это отношение «содержит» и 1:N ограниченной мощности — вкладывайте (embed). Ссылайтесь (reference) при больших, независимо изменяемых или many-to-many данных. Денормализация в документной модели — норма, а не дефект.

Причина

Ключ партиционирования имеет низкую кардинальность или перекос (например, страна, где 90% трафика — одна страна). Все эти строки хешируются в один узел: он перегружен, остальные простаивают.

Решение

1. Выбирайте partition key с высокой кардинальностью и равномерным распределением. Если естественный ключ перекошен — добавьте в него компонент (составной partition key, бакетирование по дате или хешу). Цель: и данные, и нагрузка размазаны по кластеру, и при этом данные одного запроса остаются в одной партиции.

Причина

Широкую денормализованную таблицу сделали основной моделью. Любой новый атрибут измерения нужно дописать во все исторические строки (backfill), а изменение атрибута переписывает миллионы строк. Историчность измерений теряется.

Решение

1. Стройте OBT как downstream-производную от нормализованного или размерного слоя, а не как источник истины. Источником пусть будет star schema или нормализованная модель, где атрибуты живут в одном месте; OBT пересобирается из неё ELT-процессом для скорости чтения. Тогда добавление атрибута — это правка измерения и пересборка, а не ручной backfill.

Причина

Чтобы fact-таблица была компактной, выбрали агрегированный grain (строка на день и магазин). Теперь нельзя ответить на вопрос про конкретную позицию чека или время покупки — детализация потеряна на этапе загрузки.

Решение

1. Проектируйте fact-таблицу на самом низком (атомарном) зерне — строка на позицию чека. Атомарный grain даёт максимум гибкости: любой агрегат строится из него запросом. Колоночное сжатие хорошо справляется с миллиардами узких строк. Агрегатные таблицы добавляйте как производные, а не вместо детальной.

Причина

В hub-таблицу положили имя, статус, адрес. Hub по определению хранит только уникальные business keys; описательные атрибуты меняются, и hub начинает требовать обновлений и историзации, для которой он не предназначен.

Решение

1. Держите hub строго: hash key, бизнес-ключ(и), load date, record source — и ничего больше. Все описательные атрибуты и их история — в satellite, привязанном к hub. Связи между бизнес-ключами — в link. Разделение hub/link/satellite и есть смысл Data Vault; смешивание ролей его ломает.

Причина

Satellite либо вставляет новую строку на каждую загрузку (дубли без изменений), либо не вставляет ничего. Причина — hashdiff не вычисляется или считается не от того набора атрибутов.

Решение

1. Hashdiff — это хеш конкатенации всех описательных атрибутов satellite в фиксированном порядке. На загрузке сравните hashdiff входящей строки с последним hashdiff в satellite: совпал — изменений нет, строку не вставляем; не совпал — вставляем новую версию. Следите, чтобы набор и порядок атрибутов в hashdiff были стабильны.