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

ER-модель красива на бумаге, но СУБД понимает только таблицы. Разберём пошаговый алгоритм перевода ER в реляционную схему.

Отображение ER → реляционная схема — это набор правил, по которым сущности становятся таблицами, а связи реализуются через внешние ключи или отдельные связующие таблицы в зависимости от кардинальности.

Зачем нужен формальный перевод

Соблазн «нарисовал схему — сразу пишу таблицы как чувствую» приводит к ошибкам: то связь реализована неверно, то M:N свёрнута в один внешний ключ. На самом деле перевод ER в таблицы почти механический — есть чёткие правила для каждой конструкции. Освоив их, вы получаете корректную схему автоматически, а заодно понимаете, почему реляционные базы выглядят именно так.

Почему перевод почти механический

Главная мысль урока, которую стоит держать в голове: переход от ER к таблицам — это не творчество, а применение правил. Творчество было на предыдущем шаге, когда вы решали, что является сущностью, какова кардинальность связей, какие атрибуты многозначны. Как только ER-модель построена корректно, реляционная схема выводится из неё почти однозначно. Это разделение труда ценно: оно локализует ошибки. Если схема получилась плохой, причина почти всегда в неверной ER-модели (перепутали кардинальность, не заметили многозначный атрибут), а не в самом переводе. Поэтому, отлаживая схему, возвращайтесь к ER-модели и проверяйте её, а не подкручивайте таблицы наугад. Ниже — полный набор правил; пройдём их по порядку.

Правило 1: тип сущности → таблица

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

-- сущности Клиент и Товар → таблицы
CREATE TABLE clients (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  cena  INTEGER NOT NULL
);
SELECT 'таблицы созданы' AS status;

Правило 2: составные и многозначные атрибуты

Составной атрибут (адрес = город + улица + дом) разбивают на отдельные столбцы (city, street, house) — хранить «всё в одной строке» неудобно для поиска. Многозначный атрибут (несколько телефонов) выносят в отдельную таблицу с внешним ключом на владельца — иначе нарушится атомарность (1NF).

CREATE TABLE clients (id INTEGER PRIMARY KEY, name TEXT);
-- многозначный атрибут "телефоны" → отдельная таблица
CREATE TABLE client_phones (
  client_id INTEGER REFERENCES clients(id),
  phone     TEXT
);
INSERT INTO clients VALUES (1,'Анна');
INSERT INTO client_phones VALUES (1,'+7-900-111'), (1,'+7-900-222');

SELECT c.name, p.phone FROM clients c JOIN client_phones p ON p.client_id = c.id;

Вывод: две строки — у Анны два телефона. Каждый телефон в своей строке, атомарность соблюдена.

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

Задержимся на многозначном атрибуте, потому что соблазн «упростить» здесь особенно велик. Начинающий часто думает: «зачем целая таблица ради телефонов, добавлю столбцы phone1, phone2, phone3». Это плохое решение по нескольким причинам сразу. Во-первых, число телефонов заранее неизвестно — у кого-то их пять, и схема не выдержит. Во-вторых, поиск «у кого есть такой номер» придётся вести по трём столбцам через OR. В-третьих, большинство ячеек будут пустыми (NULL), что и неэкономно, и неаккуратно. Отдельная таблица решает всё разом: сколько угодно телефонов, поиск по одному столбцу, ни одной лишней пустой ячейки. Это прямое следствие 1NF (атомарность) из реляционной модели — и наглядный пример того, как теоретическое требование оборачивается практической пользой.

Правило 3: связь 1:N → внешний ключ

Связь «один-ко-многим» реализуется внешним ключом на стороне «многих». У заказа один клиент, поэтому внешний ключ client_id кладут в таблицу orders (сторона «многих»). Никакой отдельной таблицы для связи не нужно.

CREATE TABLE clients (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  client_id INTEGER REFERENCES clients(id),  -- ВК на стороне "многих"
  data TEXT
);
INSERT INTO clients VALUES (1,'Анна');
INSERT INTO orders VALUES (10,1,'2025-01-10'),(11,1,'2025-02-03');

SELECT c.name, o.id FROM clients c JOIN orders o ON o.client_id = c.id;

Вывод: «Анна — 10» и «Анна — 11»: оба заказа ссылаются на клиента. Запомните правило: внешний ключ всегда на стороне «многих».

Правило 4: связь M:N → связующая таблица

Это самый важный пункт. Связь «многие-ко-многим» нельзя выразить одним внешним ключом — её реализуют через связующую (ассоциативную) таблицу. Эта таблица содержит внешние ключи на обе стороны, а её первичным ключом обычно служит их комбинация. Сюда же кладут атрибуты связи (количество, оценку).

CREATE TABLE orders (id INTEGER PRIMARY KEY);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT);
-- связь M:N "заказ содержит товар" → связующая таблица с атрибутом quantity
CREATE TABLE order_items (
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)   -- составной первичный ключ
);
INSERT INTO orders VALUES (100),(101);
INSERT INTO products VALUES (1,'Книга'),(2,'Кружка');
INSERT INTO order_items VALUES (100,1,2),(100,2,1),(101,1,3);

SELECT o.id AS zakaz, p.name, oi.quantity
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.name;

Вывод: заказ 100 — «Книга»(2) и «Кружка»(1); заказ 101 — «Книга»(3). Связующая таблица соединяет две сущности и хранит количество — атрибут самой связи.

Атрибуты связи и куда они попадают

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

Правило 5: связь 1:1

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

Полный пример: магазин в таблицах

Сведём правила воедино на сквозном примере. ER-модель магазина из прошлого урока (Клиент, Заказ, Товар; связи 1:N и M:N; многозначный телефон) переводится в такой набор таблиц: clients (сущность), client_phones (многозначный атрибут), orders с внешним ключом client_id (связь 1:N), products (сущность) и order_items с двумя внешними ключами и атрибутом quantity (связь M:N). Пять таблиц, и каждая появилась по конкретному правилу, а не «потому что так захотелось». Обратите внимание на закономерность: число таблиц обычно больше числа сущностей — добавляются связующие таблицы для M:N и таблицы для многозначных атрибутов. Это нормально и правильно: лишние на первый взгляд таблицы — плата за отсутствие избыточности и аномалий, о которых пойдёт речь в разделе про нормализацию.

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

Типичные ошибки

• M:N через один внешний ключ. Самая частая ошибка: пытаются связать «много-ко-многим» одним столбцом — данные теряются. Нужна связующая таблица.
• Внешний ключ не на той стороне. В связи 1:N ключ кладут на сторону «многих»; перепутали — схема не работает.
• Многозначный атрибут в одной ячейке. Список телефонов через запятую в одном поле нарушает 1NF и убивает поиск.
• 1:1 без UNIQUE. Забыли ограничение — и связь незаметно превращается в 1:N.

Итог

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