← Все статьи

Связи один-к-одному, один-ко-многим и многие-ко-многим: примеры на одной модели

· 8 мин чтения

Практическое руководство по моделированию связей один-к-одному, один-ко-многим и многие-ко-многим в DBML. Разбираем реализацию через первичные ключи, внешние ссылки и таблицы соединения. Показываем, как правильно выбирать тип связи и избегать типовых ошибок проектирования.

Когда аналитик рисует ER-диаграмму, самое сложное — это не нарисовать прямоугольники, а правильно определить, сколько объектов одного типа могут быть связаны с объектом другого. Путаница возникает чаще всего в связях один-к-одному и во многих-ко-многим. В этой статье мы разберем три базовых типа отношений на примере единой предметной области: управление складскими запасами. Мы посмотрим, как эти связи выглядят в DBML, какие таблицы создаются физически и какие ограничения необходимо задать, чтобы данные оставались консистентными.

Выбор предметной области

Для демонстрации трех типов связей возьмем простую систему учета товаров на складе. Нам понадобятся следующие сущности:

  • Product (Товар) — основная единица инвентаря.
  • Warehouse (Склад) — физическое место хранения.
  • StockRecord (Запись о наличии) — количество конкретного товара на конкретном складе.

Эта структура позволяет нам продемонстрировать все три типа связей без лишней сложности. Товар может находиться на одном или нескольких складах, склад содержит много товаров, а конкретное сочетание "товар-склад" имеет уникальную запись о количестве.

Связь один-ко-многим: классический случай

Самая частая связь в реляционных базах данных — это один-ко-многим (one-to-many). В нашей модели она существует между Warehouse и StockRecord. Один склад может иметь множество записей о наличии товаров, но каждая запись о наличии относится ровно к одному складу.

В DBML такая связь описывается с помощью внешнего ключа (foreign key) в таблице "многие". Фрагмент кода:

Table Product {
  id int [pk]
  name varchar
}

Table Warehouse {
  id int [pk]
  name varchar
}

Table StockRecord {
  id int [pk]
  warehouse_id int [not null, ref: > Warehouse.id]
  product_id int [not null, ref: > Product.id]
  quantity int
}

Здесь warehouse_id в таблице StockRecord ссылается на id в таблице Warehouse. Знак > указывает направление отношения: от множества к одному. Физически это означает, что в таблице StockRecord будет колонка warehouse_id, которая принимает значения из набора ключей таблицы Warehouse. Если попытаться удалить склад, который еще используется в записях наличия, база данных либо запретит удаление (CASCADE OFF), либо удалит связанные записи (CASCADE ON), в зависимости от настроек.

Связь один-к-одному: когда она нужна?

Связь один-к-одному (one-to-one) встречается реже, чем кажется. Часто новички пытаются использовать ее там, где достаточно просто добавить колонку. Однако есть легитимные случаи. В нашем примере связь один-к-одному может существовать между Product и его BarcodeLabel (этикеткой с штрих-кодом). Для товара допускается не более одной этикетки, а каждая существующая этикетка принадлежит ровно одному товару. Обязательное наличие этикетки у каждого товара потребовало бы дополнительного правила: показанный внешний ключ его не обеспечивает.

Реализация один-к-одному в реляционной модели требует осторожности. Есть два основных подхода:

  1. Общая первичный ключ: Таблица BarcodeLabel использует product_id как свой первичный ключ. Это гарантирует, что для каждого товара будет не более одной этикетки.
  2. Внешний ключ с ограничением UNIQUE: В таблице BarcodeLabel есть собственный id (PK) и внешний ключ product_id, на который накладывается ограничение UNIQUE.

DBML поддерживает оба варианта. Второй подход гибче, так как позволяет этикетке иметь свою историю изменений или дополнительные атрибуты, не привязанные жестко к PK товара. Фрагмент DBML для второго подхода:

Table Product {
  id int [pk]
  name varchar
}

Table BarcodeLabel {
  id int [pk]
  product_id int [not null, unique, ref: > Product.id]
  code varchar(128)
  created_at timestamp
}

Ключевое слово [unique] перед ссылкой критически важно. Без него мы получили бы связь один-ко-многим, что нарушило бы ограничение «не более одной этикетки». При проверке данных база данных будет отклонять вставку второй этикетки для одного и того же товара.

Связь многие-ко-многим: таблица соединения

Связь многие-ко-многим (many-to-many) нельзя реализовать напрямую в реляционной модели. Она всегда требует промежуточной структуры, которую называют таблицей соединения (junction table, association table, bridge table). В нашей модели связь многие-ко-многим возникает, если мы хотим отслеживать, какие товары были поставлены на какие склады за определенный период времени, независимо от текущего количества.

Но давайте рассмотрим более чистый пример: связь между Supplier (Поставщик) и Product. Один поставщик может поставлять много товаров, и один товар может поступать от многих поставщиков.Для реализации мы создаем таблицу SupplierProduct:

Table Supplier {
  id int [pk]
  name varchar
}

Table Product {
  id int [pk]
  sku varchar [unique]
  name varchar
}

Table SupplierProduct {
  supplier_id int [not null, ref: > Supplier.id]
  product_id int [not null, ref: > Product.id]
  min_order_qty int
  
  indexes {
    (supplier_id, product_id) [unique]
  }
}

Обратите внимание на два момента. Во-первых, таблица соединения не обязана иметь собственный искусственный первичный ключ (id). Комбинация внешних ключей (supplier_id, product_id) может служить составным первичным ключом или уникальным индексом. Во-вторых, наличие дополнительных атрибутов (в данном случае min_order_qty — минимальное количество заказа) делает эту связь "богатой" (rich relationship). Если бы связь была чистой (без атрибутов), мы могли бы обойтись без отдельных колонок, но даже в этом случае таблица соединения необходима для соблюдения нормализации.

Сравнительная характеристика связей

Чтобы не путаться при выборе типа связи, полезно сверяться со следующей таблицей:

Тип связи Реализация в DBML Количество FK в "многих" Ограничение UNIQUE Пример из модели
Один-ко-многим FK в таблице "многие" 1 Не требуется Sklad -> StockRecord
Один-к-одному FK + UNIQUE в таблице "второй" 1 Обязательно Product -> BarcodeLabel
Многие-ко-многим Отдельная таблица с 2 FK 2 (в junction) Часто (composite UK) Supplier <-> Product

Эта таблица служит быстрым ориентиром. Если вы видите, что одна сущность должна быть связана с несколькими экземплярами другой, и наоборот — вам нужна третья таблица. Если связь строго одна-к-одной, не забудьте про UNIQUE constraint.

Полиморфные связи и их ограничения

Иногда возникает желание сделать связь, где "один" конец относится к разным типам сущностей. Например, "Комментарий" может относиться к Товару, к Складу или к Поставщику. В DBML и реляционных БД это называется полиморфной связью.

Стандартный способ реализации — использование двух колонок: entity_type (varchar) и entity_id (int). Однако такой подход имеет серьезные недостатки:

  • Нет внешнего ключа, который гарантировал бы целостность данных. База данных не знает, какой таблице соответствует значение entity_id.
  • Индексация сложнее: нужен составной индекс по обоим полям.
  • Запросы JOIN становятся громоздкими и требуют UNION ALL.

Для учебной модели лучше избегать полиморфных связей. Вместо таблицы Comment с полиморфным FK, создайте отдельные таблицы: ProductComment, WarehouseComment и т.д. Это увеличивает количество таблиц, но сохраняет строгость реляционной модели и позволяет использовать внешние ключи.

Типовые ошибки при проектировании

Даже зная теорию, аналитики часто допускают одни и те же ошибки при переходе от концептуальной модели к физической (DBML):

  1. Забытый UNIQUE в one-to-one. Аналитик создает таблицу UserProfile со ссылкой на User, но забывает указать [unique]. В результате пользователь может получить несколько профилей, что ломает логику приложения.
  2. Избыточные таблицы для one-to-many. Иногда пытаются создать отдельную таблицу для связи, которая является просто расширением основной. Если OrderItem всегда относится к одному Order, и у OrderItem нет других связей, иногда проще оставить все в одной таблице, если объем данных небольшой. Но в DBML лучше разделять, чтобы соблюдать нормальную форму.
  3. Использование varchar для FK. Внешние ключи должны ссылаться на первичные ключи. Использование строковых идентификаторов (GUID, SKU) для связей возможно, но требует точного совпадения типов и длины. Числовые ID (int/bigint) быстрее индексируются и занимают меньше места.
  4. Игнорирование каскадных действий. Не указано, что происходит с дочерними записями при удалении родителя. В DBML действие удаления задают в настройках отдельного Ref, например Ref: StockRecord.warehouse_id > Warehouse.id [delete: restrict]. Доступны варианты [delete: cascade] или [delete: restrict]. Отсутствие явного указания приводит к поведению по умолчанию, которое может быть опасным (например, запрет удаления).

Проверка корректности модели

После написания DBML файла, важно проверить, что модель компилируется и генерирует ожидаемый DDL. В редакторе helper-sa можно открыть файл DBML и скачать SQL для выбранной СУБД. Обратите внимание на:

  • Создание индексов по всем внешним ключам (если их нет, запросы JOIN будут медленными).
  • Наличие ограничений NOT NULL там, где связь обязательна. Если связь один-к-одному необязательная (nullable), поле FK должно быть nullable. Если связь обязательная — NOT NULL.
  • Отсутствие циклических зависимостей, которые могут привести к бесконечным JOIN'ам или сложностям в миграциях.

Также полезно провести "walkthrough": представьте, что вы добавляете новый объект. Какие таблицы затрагиваются? Удаляете ли вы родительский объект? Что происходит с дочерними? Если ответы на эти вопросы не очевидны из схемы — схема неполная.

Где применить этот навык

Понимание этих трех типов связей — фундамент для любого проектирования базы данных. Когда вы будете работать над реальной системой, начните с определения сущностей и их кардинальностей. Затем выберите тип связи для каждой пары. Наконец, опишите это в DBML, используя правила, рассмотренные выше. Начните с простых связей один-ко-многим, добавьте один-к-одному там, где действительно нужна отдельная сущность, и используйте таблицы соединения для многих-ко-многим. Такой подход обеспечит четкость, расширяемость и целостность ваших данных.