[MLD] Comment caracteriser l'heritage avec MySQL Workbench ?

[gkant]

Bonjour,

Je modelise une application de gestion des annonces dans une localité bien définie.
L'annonce n'est pas categorisée et donc concerne tout type d'annonce que l'utilisateur pourra décider de publier. (Auto, Moto, Immobilier, Bijoux, Informatique, Electronique....).

Voici quelques grandes lignes sur lesquelles je me suis butté.

1. Un Utilisateur peut mettre Une ou Plusieurs Annonce.
2. Une Annonce concerne Un et Un-Seul Produit a la fois.
3. A chaque produit est lié une seule Catégorie(Auto,Moto,Immobilier...)
Ce qui peut se modéliser par:
Utilisateur (0,n) -------------- (1,1) Annonce (1,1) ----------- (1,1) Produit
Produit (1,1) ----------------- (0,n) Categorie.

Alors dans une annonce non categorisée au depart, toute catégorie d'objet peut faire l'objet d'une annonce.
Dans la relation : Produit (1,1) ----------------- (0,n) Categorie, on voit bien qu'il faut différencier les catégories. Par exemple une Auto a pour caracteristique le Type de Carburant (Diesel, Hybride, Essence, Gasoil), le Nombre de Chevaux, le Nombre de Cylindre.... alors que pour un Immobilier on a par exemple : le Type (Maison, Appartement, Chambre, Studio...).

Au regard de tout ceci, on convient donc qu'on ne peut pas a priori se contenter d'une seul entité pour contenir nos différentes catégories. Car il serait fastidieux de devoir gerer toutes les caracteristiques sur un seul tuplet.

Il est donc imperatif, pour chacune des Catégories, de créer une entité qui lui est propre avec ses caracteristiques. Mais la encore il y a un probleme. Si l'application est en production et que l'on décide d'ajouter une catégorie, il va falloir lui créer une entité dans la base de données et également créer son formulaire... ca complique les choses.

Donc pour tenter de résoudre ce probleme de facon plus simple et souple, j'ai pensé a faire de l'héritage mais alors comment représenter l'héritage avec MySQL Workbench ?

Ou alors, si quelqu'un a deja travaillé sur un projet similaire, comment l'a t-il abordé ? et comment a t-il résolu le probleme ?

Merci.

[CinePhil]

Il y a déjà eu des cas similaires de traités sur ce forum.

Sur le principe de l'héritage de données, voir l'article de SQLPro.

MCD :
A -(1,1)----hériter_de----0,1- B

Tables :
B (B_id, [colonnes communes à tous les B])
A (A_id_B, [colonnes spécifiques à A])

[gkant]

Bonjour @CinePhi,

Merci pour ta reponse.

En suivant le lien que tu as donne ci-dessus, je suis tombe sur l'approche de l'heritage conditionnel, je crois que c'est le cas qui me convient le plus dans cette situation.

Mais si je souhaite un heritage conditionnel avec Ajout de Colonne dans la table Mere (Categorie dans mon cas). Cf. image attachee ci-dessous.

Comment est ce que je pourrai ecrire les TRIGGERS pour differencier les differentes categories pendant l'insertion ?

[fsmrel]

Bonsoir gkant,


Reprenons votre diagramme :

Envoyé par gkant

Vous n’avez sans doute pas fait attention à la nature des associations entre les types d’entités (tables dans le cas de MySQL Workbench). Littéralement on vous lit ainsi :

Une catégories (notons que vous imposez le pluriel et qu’il faudra renommer toutes les tables au singulier) est composée de 1 à N véhicules et de 1 à N immobiliers ;

Un véhicules est composé de 1 à N autos et de 1 à N motos ;

Un immobiliers est composé de 1à N appartements et de 1à N studios et de 1 à N maisons.

Alors qu’il faut lire :

Un véhicule est une auto ou une moto ;

Un Immobilier (que j’ai renommé pour ma part en LOGEMENT pour éviter de loucher) est un appartement ou un studio ou une maison.

La représentation attendue est la suivante, par exemple pour les relations entre VEHICULE et ses sous-types :



A cet effet, la relation entre le surtype VEHICULE et le sous-type AUTO passe par la mise en œuvre d’une relation identifiante (Identifying relationship) 1:1 :



Et pour signifier que seuls certains véhicules sont des autos, on remplace 1 par 0,1 côté AUTO comme ci-dessus. Pour cela, on double-clique sur le lien entre VEHICULE et AUTO pour faire apparaître l’onglet qui va bien (« Foreign key ») ;il ne reste plus qu’à décocher la case « Mandatory » :

Envoyé par gkant

Il est donc imperatif, pour chacune des Catégories, de créer une entité qui lui est propre avec ses caracteristiques. Mais la encore il y a un probleme. Si l'application est en production et que l'on décide d'ajouter une catégorie, il va falloir lui créer une entité dans la base de données...

De toute façon, qu’il s’agisse d’une auto, d’un logement, d’un bijou ou d’un équipement informatique, en partant sur l’héritage, chaque type de produit fera l’objet d’un sous-type, mais ce produit sera accessible via sa clé (ProduitId). Cela vous gêne-t-il ?

La notion de catégorie fait double emploi avec l’héritage, à mon sens elle peut disparaître. On a des produits qui se déclinent en véhicules, logements, etc. :



Si l’utilisateur (au sens large) s’intéresse aux motos, vous pouvez définir une vue à cet effet, qui soit la jointure sur ProduitId des tables PRODUIT, VEHICULE, MOTO et il aura ainsi accès de façon simple à l’ensemble des composants (logés à tous les étages...) d’une moto en particulier (et sans le nombre de portes !)

Envoyé par gkant

Comment est ce que je pourrai ecrire les TRIGGERS pour differencier les differentes categories pendant l'insertion ?

Tout dépend du SGBD. Si vous utilisez MS SQL SERVER je pourrai vous aider, sinon il vous faudra aller à la pêche dans les forums dédiés.

[fsmrel]

En complément :

Envoyé par fsmrel

Si l’utilisateur (au sens large) s’intéresse aux motos, vous pouvez définir une vue à cet effet, qui soit la jointure sur ProduitId des tables PRODUIT, VEHICULE, MOTO et il aura ainsi accès de façon simple à l’ensemble des composants (logés à tous les étages...) d’une moto en particulier.

Partons des définitions suivantes pour les tables :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE TABLE PRODUIT
(       ProduitId          INT          NOT NULL
      , Observations       VARCHAR(64)  NOT NULL
    , CONSTRAINT PRODUIT_PK PRIMARY KEY (ProduitId)) 
; 
CREATE TABLE VEHICULE
(       ProduitId          INT          NOT NULL
      , NbCylindres        INT          NOT NULL
      , NbChevaux          INT          NOT NULL
    , CONSTRAINT VEHICULE_PK PRIMARY KEY (ProduitId)
    , CONSTRAINT VEHICULE_PRODUIT_FK FOREIGN KEY (ProduitId)
          REFERENCES PRODUIT ON DELETE CASCADE)
;
CREATE TABLE AUTOMOBILE
(       ProduitId          INT          NOT NULL
      , NbPortes           INT          NOT NULL
    , CONSTRAINT AUTOMOBILE_PK PRIMARY KEY (ProduitId)
    , CONSTRAINT AUTOMOBILE_VEHICULE_FK FOREIGN KEY (ProduitId)
          REFERENCES VEHICULE ON DELETE CASCADE)
;
CREATE TABLE MOTO
(       ProduitId          INT          NOT NULL
    , CONSTRAINT MOTO_PK PRIMARY KEY (ProduitId)
    , CONSTRAINT MOTO_VEHICULE_FK FOREIGN KEY (ProduitId)
          REFERENCES VEHICULE ON DELETE CASCADE) 
;
CREATE TABLE LOGEMENT
(       ProduitId          INT          NOT NULL
      , Superficie         INT          NOT NULL
    , CONSTRAINT LOGEMENT_PK PRIMARY KEY (ProduitId)
    , CONSTRAINT LOGEMENT_PRODUIT_FK FOREIGN KEY (ProduitId)
          REFERENCES PRODUIT ON DELETE CASCADE) 
;

Supposons que vous vouliez créer les données pour une automobile. Il y a 3 tables à mettre à jour : PRODUIT, VEHICULE et AUTOMOBILE. Pour vous épargner de la peine, vous pouvez directement mettre à jour la vue VOITURE ainsi définie :

Code SQL :

1
2
3
4
5
6
7
CREATE VIEW VOITURE (ProduitId, NbCylindres, NbChevaux, NbPortes, Observations)
AS
    SELECT x.ProduitId, NbCylindres, NbChevaux, NbPortes, Observations
    FROM   PRODUIT AS x 
               JOIN VEHICULE AS y ON x.ProduitId = y.ProduitId 
               JOIN AUTOMOBILE AS z ON x.ProduitId = z.ProduitId
;

Mais à ce jour, quasiment tous les SGBD répugnent — à tort ! — à mettre à jour une vue qui comporte des jointures : on est donc obligé de mettre en œuvre des triggers associés à la vue pour que les choses se passent bien.

Un trigger (MS SQL Server en l’occurrence) pour insérer les données d’une voiture (ou plusieurs) :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER VOITURE_TRIGGER_INSERT ON VOITURE INSTEAD OF INSERT AS
 
INSERT INTO PRODUIT (ProduitId, Observations)
    SELECT ProduitId, Observations
    FROM   INSERTED
;
INSERT INTO VEHICULE (ProduitId, NbCylindres, NbChevaux)
    SELECT ProduitId, NbCylindres, NbChevaux
    FROM   INSERTED
;
INSERT INTO AUTOMOBILE (ProduitId, NbPortes)
    SELECT ProduitId, NbPortes
    FROM   INSERTED
;

Un trigger pour modifier les données d’une voiture (ou plusieurs) :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE TRIGGER VOITURE_TRIGGER_UPDATE ON VOITURE INSTEAD OF UPDATE AS
 
IF UPDATE (ProduitId)  
     BEGIN
        RAISERROR ('On ne touche pas aux clés primaires !', 16, 1)
        ROLLBACK TRAN
        RETURN
    END
;
IF UPDATE (Observations)  
    BEGIN
        UPDATE PRODUIT 
            SET Observations = (SELECT Observations 
                                FROM   INSERTED
                                WHERE  PRODUIT.ProduitId = INSERTED.ProduitId
                               )
                                WHERE  ProduitId IN (SELECT PRODUIT.ProduitId 
                                                     FROM   PRODUIT JOIN INSERTED
                                                     ON PRODUIT.ProduitId = INSERTED.ProduitId)
    END
;
IF UPDATE (NbCylindres)  
    BEGIN
        UPDATE VEHICULE 
            SET NbCylindres = (SELECT NbCylindres 
                               FROM   INSERTED
                               WHERE  VEHICULE.ProduitId = INSERTED.ProduitId
                              )
                               WHERE  ProduitId IN (SELECT VEHICULE.ProduitId 
                                                    FROM   VEHICULE JOIN INSERTED
                                                    ON VEHICULE.ProduitId = INSERTED.ProduitId)
    END
;
IF UPDATE (NbChevaux)  
    BEGIN
        UPDATE VEHICULE 
            SET NbChevaux = (SELECT NbChevaux 
                             FROM   INSERTED
                             WHERE  VEHICULE.ProduitId = INSERTED.ProduitId
                            )
                             WHERE  ProduitId IN (SELECT VEHICULE.ProduitId 
                                                  FROM   VEHICULE JOIN INSERTED
                                                  ON VEHICULE.ProduitId = INSERTED.ProduitId)
    END
;
IF UPDATE (NbPortes)  
    BEGIN
        UPDATE AUTOMOBILE 
            SET NbPortes = (SELECT NbPortes 
                            FROM   INSERTED
                            WHERE  AUTOMOBILE.ProduitId = INSERTED.ProduitId
                           )
                            WHERE  ProduitId IN (SELECT AUTOMOBILE.ProduitId 
                                                 FROM   AUTOMOBILE JOIN INSERTED
                                                 ON AUTOMOBILE.ProduitId = INSERTED.ProduitId)
    END
;

Un trigger pour supprimer une voiture (ou plusieurs) :

Code SQL :

1
2
3
4
5
6
CREATE TRIGGER VOITURE_TRIGGER_DELETE ON VOITURE INSTEAD OF DELETE AS
 
    DELETE FROM PRODUIT
           WHERE ProduitId IN (SELECT ProduitId
                               FROM   DELETED) 
;

Ce trigger est moins bavard que les précédents, mais efficace ! En effet, si on supprime au niveau PRODUIT, par le jeu de l’effet CASCADE déclaré dans les contraintes référentielles VEHICULE_PRODUIT_FK et AUTOMOBILE_VEHICULE_FK, la suppression est propagée dans les tables VEHICULE et AUTOMOBILE. Cette façon de faire est justifiée par le fait qu’on est dans un contexte d’héritage et que conceptuellement parlant, VOITURE est à considérer comme un produit à part entière.

Dans tout cela, le trigger pour exclusion devient inutile.

Dans ce genre de contexte, il est préférable d’interdire les mises à jour directes des tables, donc de supprimer les droits correspondants et n’autoriser que la mise à jour de la « table » VOITURE (même principe pour les motos, les appartements, etc.)


Un début de jeu d’essai (toujours MS SQL Server) :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE @CHAR AS TABLE
(
        ProduitId          INT          NOT NULL
      , NbCylindres        INT          NOT NULL
      , NbChevaux          INT          NOT NULL
      , NbPortes           INT          NOT NULL
      , Observations       VARCHAR(64)  NOT NULL
    , PRIMARY KEY (ProduitId)
) ;
 
INSERT INTO @CHAR (ProduitId, NbCylindres, NbChevaux, NbPortes, Observations) VALUES (1, 7, 8, 5, 'Pas mal') ;
INSERT INTO @CHAR (ProduitId, NbCylindres, NbChevaux, NbPortes, Observations) VALUES (2, 7, 8, 5, 'Pas cher') ;
INSERT INTO @CHAR (ProduitId, NbCylindres, NbChevaux, NbPortes, Observations) VALUES (3, 5, 6, 3, 'Mouais...') ;
INSERT INTO @CHAR (ProduitId, NbCylindres, NbChevaux, NbPortes, Observations) VALUES (4, 5, 6, 1, 'Bof...') ;
 
INSERT INTO VOITURE (ProduitId, NbCylindres, NbChevaux, NbPortes, Observations)
    SELECT ProduitId, NbCylindres, NbChevaux, NbPortes, Observations
    FROM   @CHAR
;
SELECT '' AS 'PRODUIT', * FROM   PRODUIT ;
SELECT '' AS 'VEHICULE', * FROM   VEHICULE ;
SELECT '' AS 'AUTOMOBILE', * FROM   AUTOMOBILE ;
 
UPDATE VOITURE 
    SET   NbPortes = NbPortes + 1
        , Observations = 'Vendu'
        , NbCylindres = NbCylindres + 1
        , NbChevaux = NbChevaux + 1
    WHERE ProduitId = 2
;
UPDATE VOITURE 
    SET NbPortes = NbPortes + 1
 ;
SELECT '' AS 'PRODUIT', * FROM   PRODUIT ;
SELECT '' AS 'VEHICULE', * FROM   VEHICULE ;
SELECT '' AS 'AUTOMOBILE', * FROM   AUTOMOBILE ;
 
DELETE FROM VOITURE
    WHERE NbPortes < 5
;
SELECT '' AS 'PRODUIT', * FROM   PRODUIT ;
SELECT '' AS 'VEHICULE', * FROM   VEHICULE ;
SELECT '' AS 'AUTOMOBILE', * FROM   AUTOMOBILE ;

Si par hasard vous utilisiez MySQL, la technique utilisée poserait un problème, car je crois savoir qu'il refuse les triggers associés aux vues...