От ER-диаграммы к таблицам: правила трансформации

ER-диаграмма — это logical-модель. Чтобы получить из неё рабочую базу, диаграмму надо превратить в реляционные таблицы. Это превращение не делается «на глаз» — для него есть набор чётких правил (mapping rules), и они механические: каждому элементу ER-модели соответствует определённый приём. Освоив эти правила, вы можете взять любую ER-диаграмму и уверенно написать по ней DDL.

Этот урок — финал модуля про ER-моделирование. Он связывает всё: сущности, атрибуты, виды атрибутов, слабые сущности, иерархии — и показывает, как каждое из этого ложится в CREATE TABLE. Это и есть мост от модели к схеме.

Правило 1: сильная сущность становится таблицей

Каждая сильная (регулярная) сущность превращается в отдельную таблицу. Простые однозначные атрибуты сущности становятся колонками. Ключевой атрибут становится первичным ключом (PRIMARY KEY).

-- Сущность "Клиент" с атрибутами id, имя, email -> таблица:
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL
);

Это базовое правило, от него отталкивается всё остальное. Одна сильная сущность — одна таблица.

Правило 2: составной атрибут разбивается на колонки

Составной атрибут не становится одной колонкой — он разбивается на отдельные колонки по своим компонентам. Сам составной атрибут как единое имя в таблице не присутствует.

-- Составной атрибут "ФИО" (фамилия + имя + отчество) -> три колонки:
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    last_name   TEXT NOT NULL,
    first_name  TEXT NOT NULL,
    middle_name TEXT,
    email       TEXT NOT NULL
);

Это прямое следствие урока про типы атрибутов: компоненты составного атрибута хранят раздельно ради возможности фильтровать и сортировать по каждому.

Правило 3: многозначный атрибут выносится в отдельную таблицу

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

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name        TEXT NOT NULL
);

-- Многозначный атрибут "телефоны" -> отдельная таблица:
CREATE TABLE customer_phones (
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    phone       TEXT   NOT NULL,
    PRIMARY KEY (customer_id, phone)
);

Это правило — структурная защита от нарушения 1NF. Многозначный атрибут списком в ячейке недопустим, поэтому он всегда даёт отдельную таблицу со связью один-ко-многим.

Правило 4: вычисляемый атрибут не становится колонкой

Вычисляемый (derived) атрибут при трансформации не превращается в хранимую колонку. Он вычисляется выражением в запросе или, при необходимости, объявляется как вычисляемая колонка (generated column), которая не хранит значение, а считает его.

-- Вычисляемого "возраста" в таблице нет — есть хранимая дата рождения:
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name        TEXT NOT NULL,
    birth_date  DATE NOT NULL
    -- колонки age НЕТ: возраст вычисляется в SELECT из birth_date
);

Причина — из урока про типы атрибутов: хранение выводимого значения порождает риск рассинхрона.

Правило 5: связи — три случая по кардинальности

Самое содержательное правило. Как связь превращается в структуру, зависит от её кардинальности.

Связь 1:N (один-ко-многим). Внешний ключ ставится на стороне «многих». Никакой новой таблицы не нужно. У связи «Клиент оформляет Заказы» (1:N) внешний ключ customer_id добавляется в таблицу orders — на стороне «многих».

-- Связь 1:N "Клиент - Заказ": FK на стороне "многих" (в orders)
CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    order_date  DATE NOT NULL,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id)
);

Связь 1:1 (один-к-одному). Внешний ключ ставится в одну из двух таблиц (часто в ту, где участие необязательное), и помечается UNIQUE, чтобы гарантировать «не более одного». Иногда две сущности с 1:1 вообще объединяют в одну таблицу.

-- Связь 1:1 "Сотрудник - Парковочное место": FK с UNIQUE
CREATE TABLE parking_spots (
    spot_id     BIGINT PRIMARY KEY,
    location    TEXT NOT NULL,
    employee_id BIGINT UNIQUE REFERENCES employees(employee_id)
);
-- UNIQUE гарантирует: одно место — максимум одному сотруднику

Связь M:N (многие-ко-многим). Реляционная модель не поддерживает M:N напрямую. Связь M:N требует отдельной таблицы-связки (junction / associative table) с двумя внешними ключами — по одному на каждую сущность. Первичный ключ таблицы-связки — обычно пара этих внешних ключей.

-- Связь M:N "Студент - Курс" -> таблица-связка:
CREATE TABLE enrollments (
    student_id BIGINT NOT NULL REFERENCES students(student_id),
    course_id  BIGINT NOT NULL REFERENCES courses(course_id),
    enrolled_at DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);
-- Один студент -> много курсов, один курс -> много студентов

Правило 6: слабая сущность — таблица с составным ключом

Слабая сущность становится таблицей, первичный ключ которой — составной: внешний ключ на сущность-владельца плюс частичный ключ (дискриминатор) самой слабой сущности.

CREATE TABLE orders (
    order_id   BIGINT PRIMARY KEY,
    order_date DATE NOT NULL
);

-- Слабая сущность "Позиция заказа": PK = FK владельца + частичный ключ
CREATE TABLE order_lines (
    order_id    BIGINT  NOT NULL REFERENCES orders(order_id),
    line_number INTEGER NOT NULL,
    product_id  BIGINT  NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_number)
);

Это правило прямо реализует урок про слабые сущности: полный идентификатор слабой сущности — ключ владельца плюс частичный ключ, и именно эта пара становится первичным ключом.

Правило 7: иерархия supertype/subtype — один из трёх способов

Иерархия обобщения/специализации не имеет единственного правила трансформации — есть три способа, выбор зависит от данных и запросов (это разбиралось в уроке про подтипы):

• single table — одна таблица на надтип и все подтипы, плюс колонка-дискриминатор;
• table per subtype — отдельная таблица на каждый подтип, каждая с общими и своими атрибутами;
• table per type — таблица надтипа плюс отдельные таблицы подтипов, связанные с надтипом.

В отличие от правил 1-6, здесь нужно осознанное проектное решение, а не механическое применение.

Сводная таблица правил

Полный пример: модель проката в DDL

Соберём всё. Возьмём ER-модель проката из первого урока модуля — Клиент, Автомобиль, Аренда — и применим правила.

Сущности Клиент и Автомобиль — сильные, дают таблицы (правило 1). Сущность Аренда — тоже отдельная таблица. Связи «Клиент оформляет Аренду» (1:N) и «Автомобиль участвует в Аренде» (1:N) дают внешние ключи на стороне «многих» — то есть в таблице rentals (правило 5).

CREATE TABLE customers (
    customer_id   BIGINT PRIMARY KEY,
    name          TEXT NOT NULL,
    license_no    TEXT NOT NULL
);

CREATE TABLE cars (
    car_id        BIGINT PRIMARY KEY,
    brand         TEXT NOT NULL,
    plate_number  TEXT NOT NULL UNIQUE,
    year          INTEGER NOT NULL
);

CREATE TABLE rentals (
    rental_id     BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL REFERENCES customers(customer_id),
    car_id        BIGINT NOT NULL REFERENCES cars(car_id),
    start_date    DATE NOT NULL,
    end_date      DATE,
    total_cost    NUMERIC(10,2) CHECK (total_cost >= 0)
);

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

Попробуй сам

Возьмите ER-модель школы онлайн-курсов, которую вы строили в задании первого урока этого модуля (студенты, курсы, преподаватели, уроки, оценки). Примените к ней все семь правил трансформации и напишите полный DDL. Особое внимание трём местам: связь «студенты — курсы» почти наверняка M:N — реализуйте её через таблицу-связку с двумя внешними ключами; проверьте, нет ли многозначных атрибутов, и если есть — вынесите в отдельные таблицы; проверьте, нет ли слабых сущностей (например, урок внутри курса) — им нужен составной первичный ключ. Создайте получившуюся схему в SQLite или PostgreSQL, вставьте несколько тестовых строк и убедитесь, что внешние ключи работают: попробуйте вставить запись, ссылающуюся на несуществующего студента, и проверьте, что база её отклоняет.