P粉7225212042023-08-30 11:57:12
@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.
répondre
0