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

Question

Je réfléchis à la manière de représenter une structure complexe dans une base de données SQL Server.

Considérons une application qui doit stocker les détails d'une famille d'objets, qui partagent certains attributs, mais en ont beaucoup d'autres non communs. Par exemple, un paquet d'assurance commerciale peut inclure des couvertures de responsabilité, de moteur, de propriété et d'indemnité dans le même enregistrement de police.

Il est facile d'implémenter cela en C#, etc., car vous pouvez créer une police avec une collection de sections, où la section est héritée selon les besoins pour les différents types de couverture. Cependant, les bases de données relationnelles ne semblent pas permettre cela facilement.

Je vois qu'il y a deux choix principaux :

1. Créez une table Politique, puis une table Sections, avec tous les champs requis, pour toutes les variations possibles, dont la plupart seraient nulles.

2. Créez un tableau Politique et de nombreux tableaux Section, un pour chaque type de couverture.

Ces deux alternatives ne semblent pas satisfaisantes, d'autant plus qu'il est nécessaire d'écrire des requêtes sur toutes les sections, ce qui impliquerait de nombreuses jointures, ou de nombreux contrôles de nullité.

Quelle est la meilleure pratique pour ce scénario ?

Answer 1

@Bill Karwin décrit trois modèles d'héritage dans son Anti-modèles SQL livre, lorsqu'il propose des solutions au SQL Entité-Attribut-Valeur anti-modèle. En voici un bref aperçu :

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

L'utilisation d'un seul tableau comme dans votre première option est probablement la conception la plus simple. Comme vous l'avez mentionné, de nombreux attributs spécifiques à un sous-type devront se voir attribuer un NULL sur les lignes où ces attributs ne s'appliquent pas. Avec ce modèle, vous disposeriez d'une table des politiques, qui ressemblerait à 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 -----/

La simplicité de la conception est un atout, mais les principaux problèmes de cette approche sont les suivants :

• Lorsqu'il s'agit d'ajouter de nouveaux sous-types, vous devez modifier la table pour y intégrer 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 régulièrement.

• La base de données ne sera pas en mesure de faire respecter 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 devraient être obligatoires. Vous devriez gérer cela dans votre application, ce qui n'est généralement pas idéal.

Héritage des tables en béton :

Une autre approche pour aborder l'héritage consiste à 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 résoudra essentiellement les problèmes identifiés pour la méthode de la table unique :

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

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

• Il n'y a pas non plus de risque qu'un attribut inapproprié soit défini pour un sous-type particulier, comme l'attribut vehicle_reg_no champ pour une police immobilière.

• Il n'est pas nécessaire que le type 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.

Toutefois, ce modèle présente également quelques inconvénients :

• Les attributs communs sont mélangés aux attributs spécifiques des sous-types, et il n'y a pas de moyen facile de les identifier. La base de données ne le saura pas non plus.

• Lors de la définition des tables, vous devrez répéter les attributs communs pour chaque table de sous-type. Ce n'est certainement pas SEC .

• La recherche de toutes les politiques, quel que soit le sous-type, devient difficile et nécessiterait un grand nombre d'outils de recherche. UNION s.

C'est ainsi que vous devrez interroger toutes les polices, quel que soit leur 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;

Notez que l'ajout de nouveaux sous-types nécessiterait de modifier la requête ci-dessus en ajoutant un élément supplémentaire. UNION ALL pour chaque sous-type. Cela peut facilement conduire à des bogues dans votre application si cette opération est oubliée.

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

C'est la solution qui @David mentionne dans l'autre réponse . Vous créez une table unique pour votre classe de base, qui comprend tous les attributs communs. Ensuite, vous créez des tables spécifiques pour chaque sous-type, dont la clé primaire sert aussi 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 renforcés avec NOT NULL .

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

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

• Pas besoin de la type attribut.

• Désormais, les attributs communs ne sont plus mélangés avec les attributs spécifiques au sous-type.

• Nous pouvons enfin rester DRY. Il n'est pas nécessaire de répéter les attributs communs pour chaque table de sous-type lors de la création des tables.

• Gestion d'un système d'auto-incrémentation id pour les politiques devient plus facile, car cela peut être géré par la table de base, au lieu que chaque table de sous-type les génère indépendamment.

• La recherche de toutes les politiques, quel que soit le sous-type, devient maintenant très facile : Non UNION Il suffit d'un SELECT * FROM policies .

Je considère que l'approche des tables de classe est la plus adaptée dans la plupart des situations.

---

Les noms de ces trois modèles proviennent de Martin Fowler livre Modèles d'architecture d'application d'entreprise .

Answer 2

La troisième option consiste à créer une table "Politique", puis une table "SectionsMain" qui stocke tous les champs communs aux différents types de sections. Ensuite, créez d'autres tables pour chaque type de section qui ne contiennent que les champs qui ne sont pas communs.

Le choix de la meilleure solution dépend principalement du nombre de champs dont vous disposez et de la manière dont vous souhaitez écrire votre SQL. Tous ces systèmes peuvent fonctionner. Si vous n'avez que quelques champs, je choisirais probablement la première option. Avec "beaucoup" de champs, je pencherais pour le n° 2 ou le n° 3.

Answer 3

Avec les informations fournies, je modéliserais la base de données de la manière suivante :

POLITIQUES

• POLICY_ID (clé primaire)

PASSIF

• LIABILITY_ID (clé primaire)
• POLICY_ID (clé étrangère)

PROPRIÉTÉS

• PROPERTY_ID (clé primaire)
• POLICY_ID (clé étrangère)

...et ainsi de suite, car je m'attendrais à ce que des attributs différents soient associés à chaque section de la police. Sinon, il pourrait y avoir un seul SECTIONS et en plus de la policy_id il y aurait un section_type_code ...

D'une manière ou d'une autre, cela vous permettrait de prendre en charge des sections facultatives par politique...

Je ne comprends pas ce que vous trouvez insatisfaisant dans cette approche - c'est ainsi que l'on stocke les données tout en maintenant l'intégrité référentielle et en ne dupliquant pas les données. Le terme est "normalisé"...

Le SQL étant basé sur les SET, il est plutôt étranger aux concepts de programmation procédurale/OO et nécessite du code pour passer d'un domaine à l'autre. Les ORM sont souvent envisagés, mais ils ne fonctionnent pas bien dans les systèmes complexes et à fort volume.

Answer 4

Une autre façon de le faire est d'utiliser la fonction INHERITS composant. Par exemple :

CREATE TABLE person (
    id int ,
    name varchar(20),
    CONSTRAINT pessoa_pkey PRIMARY KEY (id)
);

CREATE TABLE natural_person (
    social_security_number varchar(11),
    CONSTRAINT pessoaf_pkey PRIMARY KEY (id)
) INHERITS (person);

CREATE TABLE juridical_person (
    tin_number varchar(14),
    CONSTRAINT pessoaj_pkey PRIMARY KEY (id)
) INHERITS (person);

Il est ainsi possible de définir un héritage entre les tables.

Answer 5

Consultez la réponse que j'ai donnée ici

Mappage flou NHibernate un-à-un avec des clés synthétiques