От ER-модели к реляционной схеме

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

Словарь перевода

Перевод ER-модели в таблицы похож на перевод текста по словарю. Для каждого слова чертежа есть готовое слово схемы:

На диаграмме	В схеме
сущность	таблица
простой атрибут	колонка
ключевой атрибут	PRIMARY KEY или UNIQUE
составной атрибут	несколько колонок
многозначный атрибут	отдельная таблица
производный атрибут	не хранится, вычисляется в запросе
связь 1:N	внешний ключ на стороне «много»
связь 1:1	внешний ключ с UNIQUE
связь M:N	связующая таблица
обязательное участие	NOT NULL

Пройдём по словарю на нашей школе.

Сущности и простые атрибуты

Каждая сущность становится таблицей, каждый простой атрибут колонкой. Ключевой атрибут становится ключом. Как мы обсуждали в уроке про ключи, часто заводят суррогатный id, а естественный ключ защитить UNIQUE:

CREATE TABLE teachers (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  email text NOT NULL UNIQUE
);

Составные и производные атрибуты

Составной атрибут раскладывают на части. Адрес студента это не одна колонка address, а несколько:

CREATE TABLE students (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  email      text NOT NULL UNIQUE,
  birth_date date NOT NULL,
  city       text,
  street     text,
  house      text
);

Производный атрибут, возраст, в таблицу не попадает. Его считают в запросе, когда нужен:

SELECT name, date_part('year', age(birth_date)) AS age FROM students;

Многозначный атрибут

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

CREATE TABLE student_phones (
  student_id bigint NOT NULL REFERENCES students (id),
  phone      text   NOT NULL,
  PRIMARY KEY (student_id, phone)
);

Составной первичный ключ (student_id, phone) не даёт записать один и тот же телефон одному студенту дважды.

Связь 1:N: ключ на стороне «много»

Преподаватель ведёт много курсов, у курса один преподаватель. Куда положить связь? Представь бирки на чемоданах в аэропорту. Бирку вешают на чемодан, и на ней написан владелец. Наоборот не получится: на владельца не повесишь список всех чемоданов, у кого-то их пять.

С таблицами так же. Внешний ключ кладут на сторону «много», в courses, и каждый курс «носит бирку» со своим преподавателем:

CREATE TABLE courses (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text    NOT NULL,
  price      numeric NOT NULL CHECK (price >= 0),
  teacher_id bigint  NOT NULL REFERENCES teachers (id)
);

Обрати внимание на NOT NULL у teacher_id. На диаграмме участие курса в связи было обязательным: курс без преподавателя не бывает. Обязательность превращается в NOT NULL. Если бы курс мог временно остаться без преподавателя, колонку оставили бы допускающей NULL, как в уроке про NULL.