Таблица базы данных, связанная с несколькими возможными другими таблицами [duplicate]

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

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

Невозможно моделировать полиморфные ассоциации с использованием ограничений SQL. Ограничение внешнего ключа всегда ссылается на одну целевую таблицу.

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

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

Вот некоторые альтернативные решения, которые используют преимущества ссылочной целостности, :

Создайте одну дополнительную таблицу для каждой цели. Например, popular_states и popular_countries, которые ссылаются соответственно на states и countries. Каждая из этих «популярных» таблиц также ссылается на профиль пользователя.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

Это означает, что для получения всех популярных любимых мест пользователя вам нужно запросить обе эти таблицы. Но это означает, что вы можете полагаться на базу данных, чтобы обеспечить согласованность.

Создайте таблицу places как надёжную. Как упоминает Аби, второй альтернативой является то, что ваши популярные места ссылаются на таблицу типа places, которая является родителем для обоих states и countries. То есть, оба государства и страны также имеют внешний ключ для places (вы даже можете сделать этот внешний ключ также первичным ключом states и countries).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Использовать два столбца. Вместо одного столбца, который может ссылаться на одну из двух таблиц назначения, используйте два столбца. Эти два столбца могут быть NULL; на самом деле только один из них должен быть не NULL.

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

В терминах теории относительности полиморфные ассоциации нарушают первую нормальную форму , поскольку popular_place_id в действительности столбца с двумя значениями: это либо государство, либо страна. Вы не сохранили бы age человека и их phone_number в одном столбце, и по той же причине вы не должны хранить оба state_id и country_id в одном столбце. Тот факт, что эти два атрибута имеют совместимые типы данных, является совпадением; они по-прежнему обозначают разные логические сущности.

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

Re comment from @SavasVedova:

Я не уверен, что я следуйте своему описанию, не видя определения таблиц или примерный запрос, но похоже, что у вас просто несколько таблиц Filters, каждый из которых содержит внешний ключ, который ссылается на центральную таблицу Products.

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

Присоединение продуктов к определенному типу фильтра легко, если вы знаете, к какому типу вы хотите присоединиться:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

Если вы хотите, чтобы тип фильтра был динамический, вы должны написать код приложения для построения SQL-запроса. SQL требует, чтобы таблица была указана и исправлена ​​во время написания запроса. Вы не можете заставить динамически использовать объединенную таблицу на основе значений, найденных в отдельных строках Products.

Единственным другим вариантом является объединение в всех таблиц фильтров с использованием внешние соединения. Те, у кого нет соответствующего product_id, будут возвращены только как одна строка с нулями. Но вам все равно придется жестко задавать все объединенные таблицы, и если вы добавляете новые таблицы фильтров, вам необходимо обновить свой код.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

Другой способ присоединиться ко всем фильтрам таблицы должны делать это серийно:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

Но этот формат по-прежнему требует, чтобы вы пишете ссылки на все таблицы. Это не так.