Comment représenter l'héritage dans une base de données?

@Bill Karwin décrit trois modèles d'héritage dans son SQL Antipatterns Livre, en proposant des solutions au SQL entité-valeur-attribut antipattern. Voici un bref aperçu:

Seul l'Héritage de Table (alias d'une Table Par Hiérarchie d'Héritage):

L'utilisation d'une table simple comme dans votre première option est probablement la conception la plus simple. Comme vous l'avez mentionné, de nombreux les attributs qui sont spécifiques à un sous-type devront recevoir une valeur NULL sur les lignes où ces attributs ne s'appliquent pas. Avec ce modèle, vous auriez une table de politiques, qui ressemblerait à quelque chose comme ceci:

+------+---------------------+----------+----------------+------------------+
| 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 -----/

en Gardant le design simple est un plus, mais les principaux problèmes de cette approche sont les suivants:

• Lorsqu'il s'agit d'ajouter de nouveaux sous-Types, vous devez modifier le tableau pour accueillir les attributs qui décrivent ces nouveaux objets. Cela peut rapidement devenir problématique lorsque vous avez de nombreux sous-types, ou si vous prévoyez d'ajouter des sous-types sur une base régulière.

• la base de données ne pourra pas appliquer les attributs qui s'appliquent et ceux qui ne s'appliquent pas, puisqu'il n'y a pas de métadonnées pour définir quels attributs appartiennent à quels sous-types.

• vous ne pouvez pas non plus appliquer NOT NULL sur les attributs d'un sous-type qui devrait être obligatoire. Vous devriez traiter cela dans votre demande, ce qui en général n'est pas idéal.

Héritage De Table En Béton:

une autre approche pour aborder l'héritage est de créer une nouvelle table pour chaque sous-type, en répétant tous les attributs communs dans chaque table. Par exemple:

--// 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    |   
+------+---------------------+------------------+

Cette conception fondamentalement résoudre les problèmes identifiés pour la méthode de la table unique:

• les attributs obligatoires peuvent maintenant être appliqués avec NOT NULL .

• Ajout d'un nouveau sous-type nécessite l'ajout d'une nouvelle table au lieu d'ajouter des colonnes à un existant.

• il n'y a pas non plus de risque qu'un attribut inapproprié soit défini pour un sous-type particulier, tel que le champ vehicle_reg_no pour une police de propriété.

• l'attribut type n'est pas nécessaire comme dans la méthode de la table unique. Le type est maintenant défini par les métadonnées: le nom de la table.

cependant, ce modèle présente aussi quelques inconvénients:

• les attributs communs sont mélangés avec les attributs spécifiques du sous-type, et il n'y a pas de moyen facile de les identifier. La base de données n' le faire savoir.

• lorsque vous définissez les tables, vous devez répéter les attributs communs à chaque table de sous-types. Ce n'est certainement pas DRY .

• la recherche de toutes les polices quel que soit le sous-Type devient difficile, et nécessiterait un tas de UNION S.

C'est ainsi que vous devez interroger tous les politiques quel que soit le type:

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;

noter comment l'ajout de nouveaux sous-types nécessiterait que la requête ci-dessus soit modifiée avec un UNION ALL supplémentaire pour chaque sous-type. Cela peut facilement conduire à des bugs dans votre demande si cette opération est oublié.

Classe de l'Héritage de Table (alias d'une Table Par Type d'Héritage):

C'est la solution que @David mentionne dans l'autre réponse . Vous créer une table unique pour votre classe de base, qui comprend tous les attributs communs. Ensuite, vous créerez des tables spécifiques pour chaque sous-type, dont la clé primaire sert également de clé étrangère à la table de base. Exemple:

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

Cette solution résout les problèmes identifiés dans les deux autres conceptions:

• les attributs obligatoires peuvent être appliqués avec NOT NULL .

• Ajout d'un nouveau sous-type nécessite l'ajout d'une nouvelle table au lieu d'ajouter des colonnes à un existant.

• aucun risque qu'un attribut inapproprié soit défini pour un sous-type particulier.

• pas besoin de l'attribut type .

• maintenant, les attributs communs ne sont pas mélangés avec le sous-type attributs spécifiques plus.

• nous pouvons rester au sec. Il n'est pas nécessaire de répéter les attributs communs pour chaque table de sous-types lors de la création des tables.

• gérer une incrémentation automatique id pour les politiques devient plus facile, parce que cela peut être manipulé par la table de base, au lieu de chaque table de sous-type les générant indépendamment.

• recherche pour toutes les politiques quel que soit le sous - Type devient maintenant très facile: Pas de UNION S nécessaire-juste un SELECT * FROM policies .

je considère que l'approche de la table de concordance est la plus appropriée dans la plupart des situations.

---

Les noms de ces trois modèles viennent de Martin Fowler livre Modèles d'Architecture Enterprise Application .