Jak reprezentować dziedziczenie w bazie danych?

@Bill Karwin opisuje trzy modele dziedziczenia w swojej książce SQL Antipatterns , proponując rozwiązania dla SQL Entity-atrybut-Value antipattern. Jest to krótki przegląd:

Dziedziczenie pojedynczej tabeli (aka dziedziczenie tabeli według hierarchii):

Użycie pojedynczej tabeli jak w pierwszej opcji jest prawdopodobnie najprostszym projektem. Jak już wspomniałeś, wiele atrybutów, które są specyficzne dla podtypów, będzie musiało otrzymać wartość NULL w wierszach, w których te atrybuty nie mają zastosowania. W tym modelu można mieć jedną tabelę zasad, która wyglądałaby mniej więcej tak:

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

Utrzymanie prostoty projektu jest plusem, ale główne problemy z tym podejściem są następujące:

• Jeśli chodzi o dodawanie nowych podtypów, trzeba by zmienić tabelę, aby dostosować atrybuty opisujące te nowe obiekty. Może to szybko stać się problematyczne, gdy masz wiele podtypów lub jeśli planujesz dodać podtypy na regularnie.

• Baza danych nie będzie w stanie wymusić, które atrybuty mają zastosowanie, a które nie, ponieważ nie ma metadanych do określenia, które atrybuty należą do których podtypów.

• Nie można również wymuszać NOT NULL na atrybutach podtypu, które powinny być obowiązkowe. Musiałbyś sobie z tym poradzić w swojej aplikacji, która w ogóle nie jest idealna.

Dziedziczenie Tabeli Betonowej:

Inne podejście do problemu dziedziczenia jest utworzenie nowej tabeli dla każdego podtypu, powtarzając wszystkie wspólne atrybuty w każdej tabeli. Na przykład:

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+

--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

Ten projekt zasadniczo rozwiąże problemy zidentyfikowane dla metody pojedynczej tabeli:

• Atrybuty obowiązkowe mogą być teraz egzekwowane za pomocą NOT NULL.

• Dodanie nowego podtypu wymaga dodania nowej tabeli zamiast dodawania kolumn do istniejącej.

• Nie ma również ryzyka, że niewłaściwy atrybut zostanie ustawiony dla określonego podtypu, np. pole vehicle_reg_no dla polityki majątkowej.

• Nie ma potrzeby stosowania atrybutu type jak w metodzie single table. Typ jest teraz definiowany przez metadane: nazwę tabeli.

• Wspólne atrybuty są mieszane z atrybutami specyficznymi dla podtypu i nie ma łatwego sposobu ich identyfikacji. Baza danych nie będzie wiedziała ani jedno, ani drugie.

• Podczas definiowania tabel należy powtórzyć wspólne atrybuty dla każdej tabeli podtypów. To zdecydowanie nie jest suche .

• Znalezienie wszystkich zasad niezależnie od podtypu staje się trudne i wymaga zbioru UNION s.

W ten sposób będziesz musiał odpytywać wszystkie zasady niezależnie od typu:

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

Zauważ, że dodanie nowych podtypów wymagałoby, aby powyższe zapytanie było zmodyfikowano dodatkową UNION ALL dla każdego podtypu. Może to łatwo prowadzić do błędów w aplikacji, Jeśli ta operacja zostanie zapomniana.

Dziedziczenie tabeli klas (aka tabela na dziedziczenie typu):

To jest rozwiązanie, które @David wspomina w drugiej odpowiedzi . Tworzysz jedną tabelę dla swojej klasy bazowej, która zawiera wszystkie wspólne atrybuty. Następnie tworzysz konkretne tabele dla każdego podtypu, których klucz podstawowy służy również jako klucz obcy do stołu bazowego. Przykład:

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

To rozwiązanie rozwiązuje problemy zidentyfikowane w dwóch pozostałych projektach:

• Atrybuty obowiązkowe mogą być egzekwowane za pomocą NOT NULL.

• Dodanie nowego podtypu wymaga dodania nowej tabeli zamiast dodawania kolumn do istniejącej.

• Nie ma ryzyka, że nieodpowiedni atrybut zostanie ustawiony dla określonego podtypu.

• Nie ma potrzeby używania atrybutu type.

• Teraz atrybuty wspólne nie są już mieszane z atrybutami specyficznymi podtypu.

• W końcu możemy się wyschnąć. Nie ma potrzeby powtarzania wspólnych atrybutów dla każdej tabeli podtypów podczas tworzenia tabel.

• Zarządzanie automatyczną inkrementacją id dla zasad staje się łatwiejsze, ponieważ może to być obsługiwane przez tabelę bazową, zamiast każdej tabeli podtypów generującej je niezależnie.

• Wyszukiwanie wszystkich polis niezależnie z podtypu staje się teraz bardzo łatwe: Nie UNIONs potrzebne-tylko SELECT * FROM policies.

Uważam podejście tabeli klasowej za najbardziej odpowiednie w większości sytuacji.