Связи: один-к-одному, один-ко-многим, многие-ко-многим

Проектирование БД · как таблицы держатся друг за друга

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

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

Зачем вообще разбивать данные

Представьте, что вы храните сотрудников и их отделы в одной таблице. У отдела «Продажи» 50 человек — значит, название «Продажи», его адрес и телефон повторятся 50 раз. Переименовали отдел — придётся править 50 строк, и одну наверняка забудете. Разбив данные на две таблицы и связав их ключом, мы храним каждый факт ровно один раз. Это и есть смысл связей.

Один-ко-многим (1:N) — самый частый случай

Один отдел содержит много сотрудников, но каждый сотрудник работает ровно в одном отделе. Это связь один-ко-многим. Правило размещения ключа простое: внешний ключ ставится на стороне «многих». То есть в таблице employees появляется столбец dept_id, который ссылается на departments.

departments              employees
+---------+----------+   +--------+-----------+---------+
| dept_id | name     |   | emp_id | emp_name  | dept_id |
+---------+----------+   +--------+-----------+---------+
|   1     | Продажи  |<--+   101  | Анна      |    1    |
|   2     | Разработка|  |   102  | Борис     |    1    |
+---------+----------+   |   103  | Вера      |    2    |
                         +--------+-----------+---------+

Несколько сотрудников указывают на один и тот же dept_id — отсюда «много к одному» с одной стороны и «один ко многим» с другой. Если бы мы поставили ключ наоборот (в departments ссылку на сотрудника), отдел смог бы хранить только одного человека — а это уже другая связь.

Многие-ко-многим (M:N) — нужна связующая таблица

Студент записан на много курсов, и на каждый курс ходит много студентов. Ни в students, ни в courses один внешний ключ это не выразит: в одну ячейку нельзя положить список. Решение — третья, связующая таблица (её ещё зовут таблицей-мостом или junction-таблицей). Каждая её строка — это один факт «студент X записан на курс Y».

Связующая таблица содержит два внешних ключа: один на students, другой на courses. Их пара образует первичный ключ — так мы запрещаем дублировать одну и ту же запись. Запустите блок ниже: он создаёт три таблицы, заполняет их и собирает пары «студент — курс» через двойной JOIN.

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT
);

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT
);

-- Связующая таблица: каждая строка = одна запись на курс
CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

INSERT INTO students VALUES (1, 'Анна'), (2, 'Борис'), (3, 'Вера');
INSERT INTO courses  VALUES (10, 'SQL'), (20, 'Python'), (30, 'Алгоритмы');

INSERT INTO enrollments VALUES
  (1, 10), (1, 20),          -- Анна: SQL и Python
  (2, 10), (2, 30),          -- Борис: SQL и Алгоритмы
  (3, 20);                   -- Вера: Python

SELECT s.student_name, c.course_name
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
ORDER BY s.student_name, c.course_name;

В результате вы увидите пять строк: «Анна — Python», «Анна — SQL», «Борис — SQL», «Борис — Алгоритмы», «Вера — Python». Заметьте: Анна встречается дважды, курс SQL — тоже дважды. Именно эту «многократность с обеих сторон» и хранит связующая таблица. Хотите записать Веру ещё и на SQL — просто добавьте строку (3, 10), не трогая две основные таблицы.

Один-к-одному (1:1) — редкий гость

Связь один-к-одному означает, что строке одной таблицы соответствует не более одной строки другой. Пример: пользователь и его расширенный профиль (паспортные данные), которые хранят отдельно ради безопасности или потому что заполняются они редко. Реализуется так же, как 1:N, но внешний ключ объявляют уникальным (UNIQUE) — это и ограничивает связь до «одного».

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  login TEXT
);

CREATE TABLE passports (
  passport_id INTEGER PRIMARY KEY,
  user_id INTEGER UNIQUE,            -- UNIQUE превращает 1:N в 1:1
  number TEXT,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users VALUES (1, 'anna'), (2, 'boris');
INSERT INTO passports VALUES (100, 1, '4509 123456');

SELECT u.login, p.number
FROM users u
LEFT JOIN passports p ON p.user_id = u.user_id;

Здесь anna получит номер паспорта, а у boris в столбце номера будет пусто (NULL) — у него профиля ещё нет. Благодаря UNIQUE один пользователь физически не сможет получить два паспорта. На практике 1:1 чаще решают объединением в одну таблицу; отдельную таблицу заводят, когда часть данных секретна, объёмна или редко нужна.

Сводная таблица: какой тип когда выбрать

Как распознать тип связи

Задайте два вопроса от каждой таблицы к другой: «может ли одной строке соответствовать много строк второй таблицы?» Если «много» только в одну сторону — это 1:N. Если «много» в обе стороны — M:N, нужна связующая таблица. Если максимум «одна» в обе стороны — это 1:1.

Частые ошибки

• Внешний ключ не на той стороне в 1:N. Ключ всегда живёт у «многих» (в employees, а не в departments), иначе отдел сможет ссылаться лишь на одного сотрудника.
• Список значений в одной ячейке для M:N. Хранить «SQL, Python» строкой в одном столбце — путь к боли: по такому полю нельзя нормально соединять и фильтровать. Нужна связующая таблица.
• Забыли составной первичный ключ в связующей таблице. Без PRIMARY KEY (student_id, course_id) одну и ту же запись на курс можно продублировать.
• Путают 1:1 и 1:N. Без UNIQUE на внешнем ключе связь остаётся «один-ко-многим», как бы вы её ни называли.

Коротко

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