Ответ 1
То, что вы описываете, называется Полиморфными ассоциациями. То есть столбец "внешний ключ" содержит значение 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 как supertable. Как упоминает Abie, второй альтернативой является то, что ваши популярные места ссылаются на таблицу типа 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 представляет собой столбец с двумя значениями: it либо государство, либо страна. Вы не сохранили бы человека 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)
...
Но этот формат по-прежнему требует, чтобы вы записывали ссылки на все таблицы. Там не обойти это.
