Кардинальность: 1:1, 1:N, M:N

В модуле про ключи мы научились идентифицировать строки и связывать таблицы через foreign key. Но foreign key — это механизм; чтобы применять его правильно, нужно понимать, какую именно связь мы реализуем. Связь между двумя сущностями имеет характеристику — кардинальность (cardinality): сколько строк одной таблицы соответствует строке другой.

Кардинальность бывает трёх видов: 1:1, 1:N, M:N. Это первое, что определяют, когда рисуют связь на ER-диаграмме, и от этого напрямую зависит, куда поставить foreign key и нужна ли отдельная таблица. Junior-инженер, который не различает эти три случая, поставит FK не туда — и схема будет либо неверной, либо невозможной для реализации.

Разберём все три по одной схеме: что это значит, как читать, как реализовать в SQL.

Как мыслить о кардинальности

Кардинальность связи между A и B — это ответ на два зеркальных вопроса:

• Сколько строк B может соответствовать одной строке A?
• Сколько строк A может соответствовать одной строке B?

Ответ на каждый вопрос — «одна» или «много». Комбинация двух ответов и даёт вид связи. Прежде чем писать DDL, сформулируйте оба вопроса вслух на языке предметной области — это убережёт от ошибок.

Связь 1:1 — одному соответствует один

1:1 (one-to-one): одной строке A соответствует не более одной строки B, и наоборот.

Пример: users и user_passport. У одного пользователя один паспорт; один паспорт принадлежит одному пользователю.

Связь 1:1 встречается реже двух других и почти всегда требует вопроса «а зачем это две таблицы?». Если у каждого пользователя ровно один паспорт, эти данные часто можно держать в одной таблице. 1:1 как отдельные таблицы оправдан в трёх случаях:

• Безопасность. Чувствительные данные (номер паспорта, медкарта) выносят в отдельную таблицу с ограниченным доступом.
• Опциональность. Атрибуты, которые есть лишь у части строк, в отдельной таблице не плодят NULL-столбцы в основной (вспомните урок про NULL).
• Производительность. Редко используемые «тяжёлые» столбцы (большой текст, blob) выносят, чтобы основная таблица оставалась узкой и быстрой.

Реализация 1:1: foreign key на одной из сторон, и этот FK помечается UNIQUE — именно UNIQUE превращает потенциальную 1:N в 1:1.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    name    TEXT NOT NULL
);

CREATE TABLE user_passport (
    passport_id INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL UNIQUE REFERENCES users(user_id),  -- UNIQUE!
    number      TEXT NOT NULL
);
-- UNIQUE на user_id запрещает два паспорта у одного пользователя -> это 1:1.
-- Без UNIQUE было бы 1:N (несколько паспортов у одного пользователя).

Связь 1:N — одному соответствует много

1:N (one-to-many): одной строке A соответствует много строк B, но одной строке B — только одна строка A.

Это самый частый вид связи в реляционных базах. Примеры повсюду:

• Один пользователь — много заказов (users 1:N orders).
• Одна категория — много товаров.
• Один автор — много книг.

Реализация 1:N — простая и фундаментальная. Правило: foreign key ставится на стороне «многих», и ссылается на primary key стороны «одного». Никакой отдельной таблицы не нужно.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    name    TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(user_id),  -- FK на стороне 'многих'
    amount   NUMERIC(12,2) NOT NULL
);

Почему FK именно на стороне «многих»? Подумаем механически. Заказ принадлежит ровно одному пользователю — значит, в строке заказа достаточно одного значения user_id, чтобы указать владельца. А у пользователя много заказов — если бы мы захотели хранить ссылку в users, пришлось бы хранить список заказов в одной ячейке, что нарушает атомарность (1NF, урок модуля 4). Одно значение помещается в столбец, список — нет. Поэтому FK всегда уходит на сторону «многих»: там нужна ровно одна ссылка на строку.

Связь M:N — многим соответствует много

M:N (many-to-many): одной строке A соответствует много строк B, и одной строке B соответствует много строк A.

Примеры:

• Студенты и курсы: студент записан на много курсов, на курс записано много студентов.
• Авторы и книги: у книги может быть несколько авторов, автор написал несколько книг.
• Товары и заказы: в заказе много товаров, товар встречается в многих заказах.

И здесь — важнейший факт этого урока: реляционная модель не поддерживает связь M:N напрямую. Нельзя реализовать M:N двумя таблицами с foreign key между ними. Попробуем — и упрёмся в ту же стену, что и с 1:N:

• Поставить FK в таблицу students? Пришлось бы хранить список course_id в ячейке — нарушение атомарности.
• Поставить FK в таблицу courses? То же самое — список student_id в ячейке.
• Поставить FK с обеих сторон? Не помогает — каждая сторона всё равно может хранить только одно значение, а нужно много.

Связь M:N реализуется только через третью таблицу — junction table. Это настолько важный и обязательный паттерн, что ему посвящён целиком следующий урок. Здесь зафиксируем суть: M:N всегда раскладывается на две связи 1:N через промежуточную таблицу.

M:N напрямую — НЕВОЗМОЖНО:
  students <--- ??? ---> courses

M:N через junction-таблицу — единственный способ:
  students  1:N  enrollments  N:1  courses

enrollments — это и есть junction table. Каждая её строка =
один факт "студент X записан на курс Y".

Сводка: три кардинальности

Обратите внимание на роль UNIQUE. 1:1 и 1:N реализуются почти одинаково — foreign key на одной стороне. Разница ровно в одном: при 1:1 на этот FK ставится UNIQUE, при 1:N — нет. UNIQUE запрещает повторение значения FK, то есть не даёт стороне «одного» получить несколько связанных строк. Один constraint переключает связь между 1:1 и 1:N.

Как определить кардинальность: процедура

Junior-инженеру полезен чёткий алгоритм. Для связи между A и B:

1. Сформулируйте прямой вопрос: «сколько B у одного A?» Ответ — «один» или «много».
2. Сформулируйте зеркальный вопрос: «сколько A у одного B?» Ответ — «один» или «много».
3. Скомбинируйте:
   • один + один -> 1:1
   • один + много (в любом порядке) -> 1:N
   • много + много -> M:N
4. Реализуйте по таблице-сводке выше.

Пример. Связь «отдел» и «сотрудник». Сколько сотрудников в одном отделе? Много. Сколько отделов у одного сотрудника? Один. Один + много -> 1:N. FK department_id ставится в таблицу employees (сторона «многих»).

Попробуй сам

Для каждой пары сущностей определите кардинальность по процедуре из урока (два вопроса -> комбинация) и набросайте реализацию:

1. Страна и Город. Сколько городов в стране? Сколько стран у города? Какая связь, куда FK?
2. Человек и Свидетельство о рождении. Какая связь? Почему здесь нужен UNIQUE на FK, и что сломается без него?
3. Актёр и Фильм. Сколько фильмов у актёра? Сколько актёров в фильме? Какая связь, и сколько таблиц понадобится для реализации?
4. Сотрудник и Парковочное место (одно место закреплено максимум за одним сотрудником, у сотрудника максимум одно место). Какая связь?

Для случаев 1:1 и 1:N напишите CREATE TABLE обеих таблиц с правильным foreign key (и UNIQUE, где нужно). Для случая M:N просто назовите три таблицы — детальную реализацию junction-таблицы вы сделаете в следующем уроке.