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

Question

Je réfléchis à la façon de représenter des structures complexes dans une base de données SQL Server.

Considérons une application qui doit stocker des détails sur une série d'objets partageant certaines propriétés mais possédant de nombreuses autres propriétés inhabituelles. Par exemple, un package d’assurance commerciale peut inclure une assurance responsabilité civile, automobile, propriété et indemnisation dans le même dossier de police.

Réaliser cela, par exemple, en C# est simple, car vous pouvez créer des stratégies qui contiennent des collections de parties, avec des parties héritées selon les besoins pour différents types de remplacements. Cependant, les bases de données relationnelles ne semblent pas permettre cela.

Je vois qu'il existe deux options principales :

1. Créez un tableau de stratégie puis un tableau partiel avec tous les champs requis pour toutes les variantes possibles, dont la plupart sont vides.

2. Créez une table de politiques et plusieurs tables partielles, chaque table correspondant à un type d'assurance.

Les deux alternatives semblent insatisfaisantes, d'autant plus que la requête devrait être écrite dans toutes les parties, ce qui impliquerait soit de nombreuses jointures, soit de nombreuses vérifications nulles.

Quelles sont les meilleures pratiques pour ce scénario ?

Answer 1

La troisième option consiste à créer une table "Policy" puis une table "SectionsMain" pour stocker tous les champs communs aux différents types de sections. Créez ensuite des tableaux supplémentaires pour chaque type de section, contenant uniquement les champs peu courants.

Le choix du meilleur dépend principalement du nombre de champs dont vous disposez et de la manière dont vous souhaitez écrire votre SQL. Ils fonctionneront tous. Si vous n'avez que quelques champs, j'opterais probablement pour le n°1. Pour "beaucoup" de domaines, je pencherais vers le n°2 ou le n°3.

Answer 2

@Bill Karwin Dans son livre SQL Antipatterns, il propose des anti-modèles SQL Entity Property Values. Voici un bref aperçu :

Héritage de table unique (alias héritage de table par hiérarchie) :

Utiliser une seule table comme la première option est probablement la conception la plus simple. Comme vous l'avez mentionné, de nombreuses propriétés spécifiques à un sous-type doivent recevoir des valeurs NULL sur les lignes où ces propriétés ne s'appliquent pas. En utilisant ce modèle, vous aurez un tableau de stratégie qui ressemble à 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 -----/

Garder la conception simple est un avantage, mais les principaux problèmes de cette approche sont les suivants :

• Au fur et à mesure que vous ajoutez de nouveaux sous-types, vous devez modifier le tableau pour accueillir les propriétés qui décrivent ces nouveaux objets. Cela peut rapidement devenir un problème lorsque vous disposez de nombreux sous-types ou lorsque vous prévoyez d’en ajouter régulièrement.

• La base de données ne sera pas en mesure d'appliquer quelles propriétés s'appliquent et lesquelles ne s'appliquent pas, car il n'y a pas de métadonnées pour définir quelles propriétés appartiennent à quels sous-types.

• Vous ne pouvez pas non plus appliquer NOT NULL sur les propriétés de sous-type qui devraient être appliquées. Vous devez gérer cela dans l’application, ce qui n’est généralement pas idéal.

Héritage de table spécifique :

Une autre façon de résoudre le problème d'héritage consiste à créer une nouvelle table pour chaque sous-type, en répétant toutes les propriétés communes 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 résoudra essentiellement le problème identifié par l'approche à table unique :

• Les attributs obligatoires peuvent désormais être appliqués via NOT NULL .

• L'ajout de nouveaux sous-types nécessite l'ajout d'une nouvelle table, et non l'ajout de colonnes à une table existante.

• Il n'y a également aucun risque de définir des attributs inappropriés pour des sous-types spécifiques, comme le champ vehicle_reg_no d'une politique d'attribut.

• Pas besoin d'attribut type comme dans la méthode de table unique. Le type est désormais défini par métadonnées : nom de la table.

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

• Les propriétés publiques sont mélangées avec des propriétés spécifiques à des sous-types et il n'existe pas de moyen simple de les identifier. La base de données ne le sait pas non plus.

• Lors de la définition d'une table, vous devez répéter les propriétés communes pour chaque table de sous-type. Ce n'est certainement pas sec.

• La recherche de toutes les stratégies quel que soit le sous-genre devient difficile et nécessite un tas UNION.

Quel que soit le type, vous devez interroger toutes les stratégies via :

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;

Veuillez noter que l'ajout de nouveaux sous-types nécessitera de modifier la requête ci-dessus avec des UNION ALL supplémentaires pour chaque sous-type. Si vous oubliez de le faire, vous pouvez facilement provoquer des erreurs dans votre application.

Héritage de table de classe (alias héritage de table par type) :

C'est la solution mentionnée par @David dans une autre réponse . Vous créez une table pour la classe de base qui inclut toutes les propriétés publiques. Vous créeriez ensuite des tables spécifiques pour chaque sous-type, dont les clés primaires servent également de tables 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 le problème rencontré dans les deux autres conceptions :

• Les attributs obligatoires peuvent être appliqués via NOT NULL .

• L'ajout de nouveaux sous-types nécessite l'ajout d'une nouvelle table, et non l'ajout de colonnes à une table existante.

• Aucun risque de définir des propriétés inappropriées pour des sous-types spécifiques.

• Pas besoin de l'attribut type.

• Désormais, les propriétés publiques ne sont plus mélangées avec des propriétés spécifiques à un sous-type.

• Nous pouvons enfin rester au sec. La création de table ne nécessite pas de duplication de propriétés communes pour chaque table de sous-type.

• Gérer l'auto-incrémentation des politiques id devient plus facile car cela peut être géré par la table de base plutôt que par chaque table de sous-type les générant indépendamment.

• La recherche de toutes les stratégies (quel que soit le sous-type) est désormais très simple : pas besoin UNION - 只需 SELECT * FROM 策略.

Je pense que l'approche sous forme de tableau est la plus appropriée dans la plupart des cas.

---

Les noms de ces trois modèles proviennent de Martin Fowlerun livre Enterprise Application Architecture Patterns.