Czy można zrobić klucz obcy MySQL do jednej z dwóch możliwych tabel?

To, co opisujesz, nazywa się asocjacjami polimorficznymi. Oznacza to, że kolumna "klucz obcy" zawiera wartość id, która musi istnieć w jednej z tabel docelowych. Zazwyczaj tabele docelowe są powiązane w jakiś sposób, na przykład są instancjami jakiejś popularnej superklasy danych. Potrzebujesz również innej kolumny wzdłuż kolumny klucza obcego, aby w każdym wierszu można było wyznaczyć, która tabela docelowa jest odwołana.

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
);

Nie ma sposobu na modelowanie związków polimorficznych korzystanie z ograniczeń SQL. Ograniczenie klucza obcego zawsze odwołuje się do jednej tabeli docelowej.

Skojarzenia polimorficzne są obsługiwane przez frameworki takie jak Rails i Hibernate. Ale wyraźnie mówią, że musisz wyłączyć ograniczenia SQL, aby korzystać z tej funkcji. Zamiast tego aplikacja lub ramy muszą wykonać równoważną pracę, aby upewnić się, że odniesienie jest spełnione. Oznacza to, że wartość w kluczu obcym jest obecna w jednej z możliwych tabel docelowych.

Polimorficzny Asocjacje są słabe w odniesieniu do egzekwowania spójności bazy danych. Integralność danych zależy od tego, czy wszyscy klienci uzyskują dostęp do bazy danych z tą samą, referencyjną logiką integralności, a także musi być wolna od błędów.

Oto kilka alternatywnych rozwiązań, które wykorzystują integralność referencyjną wymuszoną bazą danych:

Utwórz jedną dodatkową tabelę na cel. na przykład popular_states i popular_countries, które odnoszą się odpowiednio do states i countries. Każdy z tych tabele "popularne" odnoszą się również do profilu użytkownika.

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),
);

Oznacza to, że aby uzyskać wszystkie popularne ulubione miejsca użytkownika, musisz odpytywać obie te tabele. Oznacza to jednak, że możesz polegać na bazie danych, aby wyegzekwować spójność.

Utwórz tabelę places jako supertable. Jak wspomina Abie, drugą alternatywą jest to, że Twoje Popularne miejsca odwołują się do tabeli takiej jak places, która jest rodzicem zarówno states, jak i countries. Oznacza to, że zarówno państwa, jak i państwa również mieć klucz obcy places (możesz nawet sprawić, że ten klucz obcy będzie również kluczem podstawowym states i 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)
);

Użyj dwóch kolumn. zamiast jednej kolumny, która może odwoływać się do jednej z dwóch tabel docelowych, użyj dwóch kolumn. Te dwie kolumny mogą być NULL; w rzeczywistości tylko jedna z nich powinna być nie-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)
);

W teorii relacyjnej Asocjacje polimorficzne naruszają pierwszą postać normalną , ponieważ {[21] } jest w efekcie kolumną z dwoma znaczenie: to albo Państwo, albo Państwo. Nie przechowywałbyś age i phone_number w jednej kolumnie i z tego samego powodu nie powinieneś przechowywać zarówno state_id, jak i country_id w jednej kolumnie. Fakt, że te dwa atrybuty mają kompatybilne typy danych jest przypadkowy; nadal oznaczają różne byty logiczne.

Skojarzenia polimorficzne naruszają również trzecią normalną formę , ponieważ znaczenie kolumny zależy od dodatkowej kolumny, która nazywa tabela, do której odnosi się klucz obcy. W trzeciej normalnej formie atrybut w tabeli musi zależeć tylko od klucza głównego tej tabeli.

Re komentarz od @ SavasVedova:

Nie jestem pewien, czy podążam za twoim opisem, nie widząc definicji tabeli lub przykładowego zapytania, ale wygląda na to, że po prostu masz wiele tabel Filters, z których każda zawiera klucz obcy, który odwołuje się do centralnej tabeli 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...

Łączenie produktów z określonym typem filtra jest łatwe, jeśli wiesz, do którego typu chcesz dołączyć:]}

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

Jeśli chcesz, aby Typ filtra był dynamiczny, musisz napisać kod aplikacji, aby skonstruować zapytanie SQL. SQL wymaga, aby tabela była określona i ustalona w momencie pisania zapytania. Nie można sprawić, aby połączona tabela była wybierana dynamicznie na podstawie wartości znalezionych w poszczególnych wierszach Products.

Jedyną inną opcją jest dołączenie do wszystkich filtrowanie tabel za pomocą złączy zewnętrznych. Te, które nie mają dopasowania product_id zostanie zwrócony jako pojedynczy wiersz null. Ale nadal musisz kodować na twardo wszystkie połączone tabele, a jeśli dodasz nowe tabele filtrów, musisz zaktualizować swój kod.

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

Innym sposobem dołączenia do wszystkich tabel filtrów jest zrobienie tego seryjnie:

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)
...

Ale ten format nadal wymaga pisania odniesień do wszystkich tabel. Nie da się tego obejść.