Какой паттерн использовать для создания связи многие-ко-многим в базе данных с одним столбцом-источником, который ссылается на два столбца многие-ко-многим?

Как сделать many-to-many связь между таблицами, используя поле, которое может быть в любом из двух столбцов, не имея возможности указать точное поле?
  • 14 января 2024 г. 14:38
Ответы на вопрос 2
Создание связей многие-ко-многим в базах данных обычно требует использования промежуточной таблицы, которая соединяет две таблицы через их первичные ключи. Однако, когда необходимо настроить связь с одним источником, который может указывать на два разных поля, задача становится более сложной. Давайте рассмотрим пример и попробуем предложить решение.

Предположим, у нас есть две таблицы `Products` и `Categories`, и мы хотим создать таблицу `Tags`, связи которой с таблицами `Products` и `Categories` будут многие-ко-многим. Тег может быть связан как с продуктом, так и с категорией.

Сначала создадим основные таблицы:

```sql
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255)
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(255)
);
```

Теперь создадим таблицу `Tags`:

```sql
CREATE TABLE Tags (
    TagID INT PRIMARY KEY,
    TagName VARCHAR(255)
);
```

Теперь необходимо создать связующую таблицу. Здесь мы сталкиваемся с ситуацией, когда тег может быть связан либо с продуктом, либо с категорией. В одном из решений мы можем определить две колонки-внешние ключи, которые будут указывать на `ProductID` и `CategoryID` соответственно, и обе эти колонки можно сделать NULLable, чтобы позволить связь только с одним из типов:

```sql
CREATE TABLE TagRelations (
    TagRelationID INT PRIMARY KEY,
    TagID INT,
    ProductID INT NULL,
    CategoryID INT NULL,
    FOREIGN KEY (TagID) REFERENCES Tags (TagID),
    FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
    FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID)
);
```

Такой подход позволяет нам установить связи многие-ко-многим, указав значение для `ProductID` или `CategoryID` в зависимости от того, с чем мы хотим связать тег.

Однако есть некоторые ограничения и недостатки:

1. В одной строке связующей таблицы может быть не более одной связи. То есть, `ProductID` и `CategoryID` не должны быть заполнены одновременно.
2. Нужно обеспечить данные ограничения на уровне приложения или создать триггер, чтобы гарантировать, что только одно из полей (`ProductID` или `CategoryID`) будет содержать значение.
3. Также следует контролировать целостность данных: когда удаляется продукт или категория, связанные строки в `TagRelations` должны быть обновлены или удалены.

Таким образом, данный паттерн подойдет для создания связи многие-ко-многим с одним источником, который может ссылаться на разные объекты.
Тут 2 варианта - через View, либо триггеры, т.к. очки меняются часто. Поэтому вариант с таблицей отдельной я отбрасываю. Триггеры привносят непонятки, поэтому тоже убираю. 
View можно сделать так.

В добавок можно сделать его materialized view, чтобы от лишних перерасчетов избавиться
Похожие вопросы