Модель данных описывает объекты, их связи и правила хранения сведений. До CREATE TABLE нужно определить смысл строки и допустимые связи. Для примеров достаточно знать таблицы, ключи, JOIN и GROUP BY.
Представьте систему обучения. Есть курс, преподаватель и занятие. Курс существует независимо от отдельного занятия; преподаватель может вести разные курсы; у занятия есть время и тема. Если записать всё в одну таблицу с повторяющимися именами преподавателя и курса, данные будут работать для первых пяти строк, но усложнят исправления и связи. Модель помогает сначала определить предметные правила.
Сущность, экземпляр и атрибут
Сущность — тип объекта, о котором системе нужны сведения: курс, преподаватель, занятие. Экземпляр — конкретный курс «Введение в SQL» или занятие 15 октября. Атрибут — характеристика объекта: название курса, дата занятия, имя преподавателя. Нужно различать объект и его описание: название меняется, а курс может оставаться тем же.
Выбор сущности зависит от требований. Категория курса может быть атрибутом-подписью. Если у категорий есть собственные коды, владельцы и история изменений, их выделяют в отдельную сущность.
Ключ отличает один экземпляр от другого. Бизнес-ключ приходит из предметной области, например код курса в учебной системе. Технический ключ создаёт база или конвейер. Если один и тот же код может повторяться в разных источниках, для общей идентичности понадобится ещё источник или правило сопоставления.
Название курса может измениться при сохранении его идентичности. Поэтому ключ выбирают по устойчивому правилу, а хранение прежних описаний рассматривают отдельно.
Связи и их кратность
Если у курса много занятий, а каждое занятие относится к одному курсу, это связь один-ко-многим. В таблице занятий обычно хранится внешний ключ курса. Но связь «преподаватель ведёт курс» может быть многие-ко-многим: несколько преподавателей участвуют в одном курсе, а преподаватель ведёт несколько курсов. В реляционной модели её представляют отдельной таблицей пар ключей.
| Связь | Как выглядит правило | Обычная структура |
|---|---|---|
| Один-к-одному | У сотрудника не больше одной актуальной служебной карточки | Внешний ключ с уникальностью |
| Один-ко-многим | У курса много занятий; у занятия один курс | Ключ курса в занятии |
| Многие-ко-многим | У курса несколько преподавателей; у преподавателя несколько курсов | Таблица назначения с двумя внешними ключами |
Кратность и обязательность — разные свойства. Новый курс может пока не иметь ни одного занятия; тогда со стороны курса допустимо ноль или много. Для опубликованного занятия курс может быть обязателен. В диаграммах это пишут как 0..N и 1, но перед значками полезно проговорить правило словами.
Таблица связи иногда содержит свои атрибуты. Например, назначение преподавателя на курс имеет дату начала и роль. Эти поля относятся к паре «преподаватель — курс», а не к преподавателю вообще и не к курсу вообще. Если позднее потребуется учитывать часы каждого преподавателя, мера тоже будет принадлежать назначению.
Концептуальная, логическая и физическая модель
Концептуальная модель называет предметные понятия и основные связи понятным участникам проекта языком: что считается курсом, к скольким курсам относится занятие, как меняются назначения преподавателей.
Логическая модель уточняет атрибуты, ключи, кратности, обязательность и функциональные зависимости. Например: код занятия уникален только внутри курса; у назначения преподавателя есть дата начала; две записи назначения не должны означать один и тот же период. Это правила независимо от конкретной СУБД.
Физическая модель переводит решение в таблицы и средства выбранного движка: типы колонок, ограничения, индексы, партиции, распределение. Одинаковую логическую модель в PostgreSQL и аналитической колоночной системе можно реализовать по-разному. Физическая оптимизация не должна незаметно изменить смысл строки.
Что означает одна строка
Для каждой таблицы закончите фразу: «одна строка означает…». В таблице занятий — одно занятие. В таблице назначений — участие одного преподавателя в одном курсе в определённом периоде. В дневном агрегате — один день и один курс. Это зерно, или гранулярность.
При JOIN занятий с назначениями занятие повторится по числу преподавателей. Для общей длительности занятий это завысит показатель; часы участия преподавателей требуют отдельного правила расчёта. Зерно результата и показатель определяют до агрегации.
Состояние, событие и история
Текущее состояние отвечает «что сейчас»: активен ли курс. Событие отвечает «что произошло»: занятие проведено, назначение изменено. История описания отвечает «каким был объект раньше»: кто вёл курс в прошлом семестре. Если хранить только текущее значение преподавателя у курса, прошлый состав команды восстановить нельзя.
Ограничения выражают часть правил
PRIMARY KEY, FOREIGN KEY, NOT NULL и CHECK помогают СУБД проверять модель. Внешний ключ проверяет существование курса, а выбор исторической версии и пересечения периодов назначения требуют дополнительных проверок.