Перед списком колонок закончите фразу: «одна строка этой таблицы означает…». В журнале посещений это может быть одно посещение. В дневном отчёте — один день и один сайт. В истории тарифа — одну версию тарифа на интервале времени. Это зерно (grain), или гранулярность. Ключ должен соответствовать зерну, а не подменять его случайным уникальным номером.
Если источник предоставляет только дневной итог, нельзя назвать каждую строку событием отдельного пользователя. Потерянную детализацию не восстановить JOIN-ом. Поэтому зерно выбирают до физической схемы и проверяют, что источник действительно поставляет нужные записи.
Бизнес-ключ и технический ключ
Бизнес-ключ отличает объект в определённой предметной области: код товара, номер договора внутри системы, идентификатор события источника. Всегда уточняйте область уникальности. Код 42 в CRM и код 42 в биллинге не обязательно обозначают одного человека. Для интеграции может понадобиться пара «источник, код» и проверенное правило сопоставления.
Технический, или суррогатный ключ, создают внутри модели. Он помогает связать таблицы и часто обозначает конкретную историческую версию объекта. Например, товар с одним бизнес-кодом может иметь две строки измерения: до смены категории и после. У строк будут разные суррогатные ключи, хотя объект остаётся тем же.
Классификация ключей
Ключи можно классифицировать по назначению, числу колонок и происхождению. Эти признаки не исключают друг друга: составной бизнес-ключ может быть кандидатным и выбранным первичным одновременно.
| Название | Что означает |
|---|---|
| Кандидатный | Минимальный набор атрибутов, однозначно определяющий строку во всех допустимых состояниях |
| Первичный — primary key | Выбранный кандидатный ключ; в реляционной СУБД обычно закрепляется PRIMARY KEY |
| Альтернативный | Кандидатный ключ, который не выбран первичным |
| Простой / составной | Состоит из одной / нескольких колонок |
| Натуральный / суррогатный | Имеет предметный смысл / вводится технически внутри модели |
| Внешний — foreign key | Ссылается на допустимый ключ другой таблицы или той же таблицы |
Для занятия номер может быть уникален только внутри курса. Тогда (course_id, lesson_no) идентифицирует строку. Если убрать любую часть, уникальность исчезнет. Добавление lesson_title в эту пару не улучшает предметный ключ: название меняется, а минимальности уже нет. При выборе составного ключа проверяют область уникальности, обязательность частей, устойчивость и соответствие зерну. Уникальность в одной выгрузке не доказывает правило для будущих данных.
Внешний ключ course_id повторяется у разных занятий и сам по себе их не идентифицирует. Он выражает связь. При отношении один-к-одному внешний ключ может одновременно быть первичным или уникальным; это дополнительные правила. Составной внешний ключ должен ссылаться на соответствующее сочетание колонок, а не проверять каждую колонку как независимую ссылку.
Как создают суррогатные ключи
Числовая последовательность или identity даёт компактный номер, но для повтора загрузки нужно найти уже созданную строку по её устойчивому предметному ключу. Иначе повтор той же записи получит новый номер. Сам механизм выдачи чисел не заменяет ограничение уникальности.
UUID позволяет независимым процессам выдавать ключи без общей последовательности. Для двух копий одного события процессы могут выдать разные UUID; повторы распознают по устойчивому предметному ключу.
Детерминированный хеш от выбранных ключевых полей даёт одинаковый результат при одинаковом входе. Для него заранее определяют типы, порядок полей, представление NULL, регистр и разделители. Простая склейка 1 + 23 и 12 + 3 без границ даст одинаковую строку ещё до хеширования. Возможны и коллизии самого хеша, поэтому его свойства не заменяют правильный бизнес-ключ и обработку конфликтов. Хеш только бизнес-ключа также не различит версии SCD2: каждой версии нужен отдельный ключ.
Ограничения целостности и значения по умолчанию
Ограничения закрепляют правила, которые выбранная СУБД действительно проверяет.
| Ограничение | Что проверяет |
|---|---|
NOT NULL | Значение обязательно |
UNIQUE | Не допускает повторов указанного сочетания; поведение NULL уточняют у СУБД |
PRIMARY KEY | Выбранный идентификатор уникален и не содержит NULL |
FOREIGN KEY | Непустая ссылка соответствует допустимой строке связанной таблицы |
CHECK | Выполняется условие, например положительная длительность |
DEFAULT задаёт значение, когда вставка не указала колонку, но не является проверкой целостности. Например, DEFAULT 0 не запрещает явно вставить отрицательное число. В PostgreSQL CHECK (duration > 0) допускает NULL, поскольку выражение тогда не ложно; для обязательной длительности дополнительно нужен NOT NULL.
Средства СУБД проверяют отдельно от логической модели. Ключ MergeTree в ClickHouse организует чтение и допускает повторы. Внешний ключ реляционной СУБД проверяет существование ссылки; выбор исторической версии проверяют дополнительно.
Кратность связи — предположение каждого JOIN
Если соединить факт посещения со справочником сайтов по уникальному ключу сайта, справа ожидается не более одной строки. Если соединить по названию сайта, которое повторяется, один факт может размножиться. SUM(длительность) затем завысит итог, хотя каждая исходная таблица по отдельности выглядит корректной.
Для исторического справочника бизнес-код повторяется намеренно: у объекта несколько версий. Соединение должно выбрать одну версию, действовавшую на дату факта. Иначе один факт совпадёт с прошлой и нынешней строкой.
| Проверка после соединения | Что она показывает |
|---|---|
| Число строк факта до и после | Не появились ли лишние совпадения или потери |
| Число совпадений справа на один факт | Выполняется ли ожидаемая кратность |
| Контрольная мера по разрезам | Не переместились ли суммы между категориями |
| Строки без соответствия | Нужно ли их отложить, исправить или показать как неизвестные |
LEFT JOIN сохраняет факты без совпадения, INNER JOIN исключает их. При нескольких совпадениях оба размножают строки, поэтому проверка кратности нужна для любого типа соединения.
Роли одного измерения
Одна таблица календаря может использоваться как дата создания и дата закрытия заявки. Это разные роли одного измерения. Для первого вопроса нужна одна связь, для второго — другая. Аналогично один справочник отделов может обозначать отдел автора и отдел исполнителя. Общая физическая таблица не делает роли взаимозаменяемыми.
Идентификатор документа иногда оставляют прямо в факте без отдельной таблицы описаний. Это называют вырожденным измерением: номер нужен для поиска и детализации, но у него нет набора атрибутов, ради которых стоило бы создавать отдельное измерение.
Когда измерение ещё неизвестно
Факт может прийти раньше справочника. Можно отложить публикацию, создать специальную строку «неизвестно» либо предварительный элемент измерения, который позже дополнится. Выбор зависит от того, что важнее: свежесть или полностью разрешённые связи. «Не пришло», «не применимо» и «ошибка источника» лучше различать — у них разная причина и дальнейшая обработка.
Если для неизвестного элемента выбран специальный ключ, например 0, это соглашение фиксируют в модели и проверяют при загрузке.
Как выбирать зерно
Опишите процесс, одну запись, её время, ключи и допустимые меры. Детальный факт позволяет строить новые разрезы, агрегат сохраняет только выбранные. Детализацию выбирают по ожидаемым вопросам и стоимости хранения.
Агрегаты строят поверх детальных данных, когда это возможно. Если одна таблица содержит событие, а другая дневной итог тех же событий, их суммы нельзя складывать вместе как независимые факты. У каждого набора своё зерно и своё назначение.