Modélisation par héritage

Date de publication : 29/11/2003

Par SQLPro (autres articles) (CV)
 

niveau : intermédiaire

Les modèles par héritage possèdent de nombreux avantages. Parmis ceux-ci, l'économie en volume de données stocké, la standardisation des types et formats de données. Cet article fait le point sur la modélisation des entités par héritage afin de vous permettre de l'implémenter au sein de vos applications, et cela, en toute sérénité.


1. Modélisation sans héritage
2. Modélisation d'un héritage de personnes
3. Un héritage induit
4. Transposition en modèle physique
5. Héritage avec exclusion mutuelle
6. Héritage conditionnel
6.1. Avec vue
6.2. Avec ajout de colonne
7. Quelques liens pour en savoir plus


1. Modélisation sans héritage

Pour comprendre l'intérêt de la modélisation par héritage, commençons par une application très banale qui devra contenir des prospects, des clients et des employés... Une gestion commerciale par exemple...
Voici un exemple de modèle de données

Nous constatons que certaines entités possèdent des attributs identiques :
NOM, PRENOM se retrouvent dans EMPLOYE, CLIENT et PROSPECT. De même NUMxxx et DATE se retrouve dans COMMANDE et FACTURE.
Enfin, les associations portent sur contiennent elles aussi les mêmes attributs.
Ce modèle possède un inconvénient majeur :

  1. si un prospect devient un client les informations doivent être recopiées
  2. un employé peut aussi être un client de sa propre entreprise ce qui oblige aussi à recopier les informations
  3. un client ne peut avoir qu'une seule adresse alors qu'il arrive qu'un même client ait besoin d'être livré à une adresse données et facturé à une autre
La première idée consiste à rassembler tous les individus dans une seule et même table. Il faut ensuite les décliner en client, prospect et employé. C'est la technique de l'héritage...


2. Modélisation d'un héritage de personnes

Nous voyons désormais que les entités PROSPECT, EMPLOYE et CLIENT n'ont plus de clef. C'est normal ces entités vont hériter des données de la table PERSONNE contenant une clef.

A ce stade il existe deux possibilités :

  • chacune des tables filles (PROSPECT, EMPLOYE, CLIENT) hérite de tous les attributs de PERSONNE lors du passage au modèle physique
  • les tables filles n'héritent que de la clef de la table PERSONNE et il faut faire une jointure entre table fille et mèere pour retrouver toutes les données

3. Un héritage induit

Précisons encore notre modèle en faisant un héritage avec les tables COMMANDE et FACTURE :

Contrat est une table qui rassemble aussi bien les commandes que les factures. Ce qui permet de distinguer une commande d'une facture sera le contenu de la colonne NATURE.
C'est aussi une modélisation d'héritage d'une autre forme...

Enfin pour résoudre la problématique des addresses multiples, rien ne nous empêche d'externaliser les adresses dans une tables à part :

La nature des adresses est soit explicite (attribut UTILITE de l'assocation possède) soit implicite du fait de la relation avec la table des contrats (adresse de facturation ou de commande).
Regardez comme ce modèle est devenu simple ! Nous sommes passés de 46 attributs à 33 tout en augmentant le nombre de rubriques pour les différentes entités (par exemple par rapport au premier modèle une prospect n'avait pas de titre !)...


4. Transposition en modèle physique

En générant le modèle physique d'après le modèle ci dessus, nous obtenons le diagramme suivant :

On note que les entités PROSPECT, EMPLOYE et CLIENT héritent de la colonne clef de la table mère PERSONNE.


5. Héritage avec exclusion mutuelle

Une autre possibilité dans l'utilisation des héritages est de faire en sorte qu'il n'existe qu'un seul héritier dans les diverses tables filles. Cela s'avère parfois indispensable.
Comme un bon exemple est souvent plus explicite qu'un long discours, modélisons les différents types de location de véhicule d'une gance comme Hertz, Budget ou EuropCar... Ce qui nous intéresse c'est une entité générique des véhicules et des entités filles par type de véhicule (moto, voiture ou camion)...

Voici un premier exemple d'une telle modélisation :

Veuillez noter dans ce modèle la présence de la croix dans le symbole de l'héritage. Il indique une exclusion mutuelle entre les entités filles...

Mais comment résoudre cette exclusion mutuelle ?
La simple technique des intégrités référentielles est insuffisante à gérer ce cas de figure. Il faut recourrir à un ensemble de triggers...

Voyons d'abord comment le modèle physique est construit à partir du modèle conceptuel :

Dans le principe il faut que, pour chaque insertion dans l'une des tables filles s'assurer que le véhicule n'est pas déjà utilisé par les autres tables...

Voici les triggers d'insertion/modification pour les trois tables filles, dans le langage Transact SQL de SQL Server :

/*  Trigger sur T_CAMION_CMN pour contrôler l'insertion  */
CREATE TRIGGER TRG_INS_T_CAMION
       ON T_CAMION_CMN
       FOR INSERT, UPDATE
AS
BEGIN
   DECLARE
      @errno    INTEGER,
      @errmsg   VARCHAR(255)

/*  La clef de "T_VEHICULE_VHC" doit exister pour la création de "T_CAMION_CMN"  */
   IF NOT EXISTS(SELECT *
                 FROM   T_VEHICULE_VHC T
                        INNER JOIN INSERTED I
                              ON T.VHC_ID = I.VHC_ID)
   BEGIN
      SET @errno  = 30002
      SET @errmsg = 'Clef de "T_VEHICULE_VHC" inconnu. Insertion dans "T_CAMION_CMN" impossible.'
      GOTO LBL_ERROR
   END

/* l'identifiant de camion ne doit pas être utilisé par les autres tables filles */
   IF EXISTS (SELECT *
              FROM   (SELECT VHC_ID
                      FROM T_MOTO_MTO
                      UNION ALL
                      SELECT VHC_ID
                      FROM T_VOITURE_VTR) T
              WHERE VHC_ID IN (SELECT VHC_ID
                               FROM   INSERTED))
   BEGIN
      SET @errno  = 30002
      SET @errmsg = 'Clef de "T_VEHICULE_VHC" déjà utilisée par ailleur. Insertion dans "T_CAMION_CMN" impossible.'
      GOTO LBL_ERROR
   END

   RETURN

/*  Gestion d'erreurs  */
LBL_ERROR:
    RAISERROR @errno @errmsg
    ROLLBACK TRANSACTION
END
GO
 
/*  Trigger sur T_MOTO_MTO pour contrôler l'insertion  */
CREATE TRIGGER TRG_INS_T_MOTO
       ON T_MOTO_MTO
       FOR INSERT, UPDATE
AS
BEGIN
   DECLARE
      @errno    INTEGER,
      @errmsg   VARCHAR(255)

/*  La clef de "T_VEHICULE_VHC" doit exister pour la création de "T_MOTO_MTO"  */
   IF NOT EXISTS(SELECT *
                 FROM   T_VEHICULE_VHC T
                        INNER JOIN INSERTED I
                              ON T.VHC_ID = I.VHC_ID)
   BEGIN
      SET @errno  = 30002
      SET @errmsg = 'Clef de "T_VEHICULE_VHC" inconnu. Insertion dans "T_MOTO_MTO" impossible.'
      GOTO LBL_ERROR
   END

/* l'identifiant de moto ne doit pas être utilisé par les autres tables filles */
   IF EXISTS (SELECT *
              FROM   (SELECT VHC_ID
                      FROM T_CAMION_CMN
                      UNION ALL
                      SELECT VHC_ID
                      FROM T_VOITURE_VTR) T
              WHERE VHC_ID IN (SELECT VHC_ID
                               FROM   INSERTED))
   BEGIN
      SET @errno  = 30002
      SET @errmsg = 'Clef de "T_VEHICULE_VHC" déjà utilisée par ailleur. Insertion dans "T_MOTO_MTO" impossible.'
      GOTO LBL_ERROR
   END

   RETURN

/*  Gestion d'erreurs  */
LBL_ERROR:
    RAISERROR @errno @errmsg
    ROLLBACK TRANSACTION
END
GO
 
/*  Trigger sur T_VOITURE_VTR pour contrôler l'insertion  */
CREATE TRIGGER TRG_INS_T_VOITURE
       ON T_VOITURE_VTR
       FOR INSERT, UPDATE
AS
BEGIN
   DECLARE
      @errno    INTEGER,
      @errmsg   VARCHAR(255)

/*  La clef de "T_VEHICULE_VHC" doit exister pour la création de "T_VOITURE_VTR"  */
   IF NOT EXISTS(SELECT *
                 FROM   T_VEHICULE_VHC T
                        INNER JOIN INSERTED I
                              ON T.VHC_ID = I.VHC_ID)
   BEGIN
      SET @errno  = 30002
      SET @errmsg = 'Clef de "T_VEHICULE_VHC" inconnu. Insertion dans "T_VOITURE_VTR" impossible.'
      GOTO LBL_ERROR
   END

/* l'identifiant de moto ne doit pas être utilisé par les autres tables filles */
   IF EXISTS (SELECT *
              FROM   (SELECT VHC_ID
                      FROM T_CAMION_CMN
                      UNION ALL
                      SELECT VHC_ID
                      FROM T_MOTO_MTO) T
              WHERE VHC_ID IN (SELECT VHC_ID
                               FROM   INSERTED))
   BEGIN
      SET @errno  = 30002
      SET @errmsg = 'Clef de "T_VEHICULE_VHC" déjà utilisée par ailleur. Insertion dans "T_VOITURE_VTR" impossible.'
      GOTO LBL_ERROR
   END

   RETURN

/*  Gestion d'erreurs  */
LBL_ERROR:
    RAISERROR @errno @errmsg
    ROLLBACK TRANSACTION
END
GO
Notez que SQL Server utilise la pseudo table INSERTED pour les insertions en cours. Certains SGBDR utilise la table NEW (norme SQL).

Il faut aussi créer un trigger dans la table mère afin d'empêcher la suppression si le véhicule est référencé dans l'une des tables filles.

/*  Trigger TRG_DEL_VEHICULE pour contrôler la suppression dans la table "T_VEHICULE_VHC"  */
CREATE TRIGGER TRG_DEL_VEHICULE
       ON T_VEHICULE_VHC
       FOR DELETE
AS
BEGIN
   DECLARE
      @errno    INTEGER,
      @errmsg   VARCHAR(255)

   
    /*  Suppression interdit s'il existe un fils dans une des tables filles  */
   IF EXISTS (SELECT *
              FROM   (SELECT VHC_ID
                      FROM T_CAMION_CMN
                      UNION ALL
                      SELECT VHC_ID
                      FROM T_MOTO_MTO
                      UNION ALL
                      SELECT VHC_ID
                      FROM T_VOITURE_VTR) T
              WHERE VHC_ID IN (SELECT VHC_ID
                               FROM   INSERTED))
    BEGIN
       SET @errno  = 30006
       SET @errmsg = 'Ce véhicule est encore référencé dans une des tables filles. Suppression impossible.'
       GOTO LBL_ERROR
    END
   
    RETURN

/*  Gestion d'erreurs  */
LBL_ERROR:
   RAISERROR @errno @errmsg
   ROLLBACK TRANSACTION
END
GO
Pour vérifier ce fonctionnement, vous pouvez utiliser le jeu de données suivant :

INSERT INTO T_VEHICULE_VHC VALUES (1, '1234 AB 56')
INSERT INTO T_VEHICULE_VHC VALUES (2, '9876 ZY 54')
INSERT INTO T_VEHICULE_VHC VALUES (3, '7777 MN 33')

INSERT INTO T_MOTO_MTO VALUES (1, 750)

INSERT INTO T_CAMION_CMN VALUES (2, 38)

6. Héritage conditionnel

Plus rarement implémenté, voici l'héritage conditionnel. Il s'agit de rajouter dans la table mère une colonne particulière permettant de spécifier quelle est la table fille héritière. Cela peut se faire de deux manières :

  • par construction d'une vue
  • dans la structure même de la table mère

6.1. Avec vue

Reprenons notre exemple précédent... Ajoutons la vue suivante :

CREATE VIEW V_VEHICULE_VHC
AS
SELECT VHC_ID, VTR_NOMBRE_PLACE, NULL AS MTO_CYLINDREE, NULL AS CMN_TONNAGE, 1 AS TYPE_VEHICULE
FROM T_VOITURE_VTR
UNION ALL
SELECT VHC_ID, NULL VTR_NOMBRE_PLACE, MTO_CYLINDREE, NULL AS CMN_TONNAGE, 2 AS TYPE_VEHICULE
FROM T_MOTO_MTO
UNION ALL
SELECT VHC_ID, NULL VTR_NOMBRE_PLACE, NULL AS MTO_CYLINDREE, CMN_TONNAGE, 3 AS TYPE_VEHICULE
FROM T_CAMION_CMN
Les avantages sont considérables... On peut simplifier les jointures du fait qu'il ne suffit plus que d'utiliser la jointure de véhicule sur la vue pour obtenir toutes les informations sur tous les véhicules quelque soit leurs types :

SELECT *
FROM   T_VEHICULE_VHC T1
       INNER JOIN  V_VEHICULE_VHC T2
             ON T1.VHC_ID = T2.VHC_ID
 
VHC_ID VHC_IMMATRICULATION VHC_ID      VTR_NOMBRE_PLACE MTO_CYLINDREE   CMN_TONNAGE  TYPE_VEHICULE
-------------------------- ----------- ---------------- --------------- ------------ -------------
1      1234 AB 56          1           NULL             750.0           NULL         2
2      9876 ZY 54          2           NULL             NULL            38.0         3

6.2. Avec ajout de colonne

Bien entendu si l'on a opté pour l'ajout d'une colonne spécifique dans la table mère il faudra la renseigner lors de l'exécution du trigger d'insertion/modification.


7. Quelques liens pour en savoir plus

Héritage :

http://dchaffiol.free.fr/info/langages/obj/art_obj_HeritAssoc_r.htm

Base d'UML et comparaisons :

http://www.usask.ca/frenchciv/ronald/la_boite_a_objets/modelisation_avec_uml.html

PowerDesigner :

http://medias.obs-mip.fr/www/Activite/Formation/Informatique/Bdweb2/bdweb2003.html

Modélisation :

http://philippe.guezelou.free.fr/mcd/modelis.htm


Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

Copyright © 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.

Vos questions techniques : forum d'entraide SGBD & SQL - Publiez vos articles, tutoriels et cours
et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones
Nous contacter - Hébergement - Participez - Copyright © 2000-2009 www.developpez.com - Legal informations.