Discussion :

[ced]

Bonjour,

Je vais partir d'un exemple très courant et tout à fait similaire au cas que je rencontre.
Une compagnie d'aviation dispose de pilotes. Un pilote ne peut appartenir qu'à une et une seule compagnie. Chaque pilote peut être parrainé par un autre pilote. Règle de gestion supplémentaire que j'explicite : un pilote ne peut parrainer qu'un pilote qui fait partie de la même compagnie.

Voici comment je modélise l'appartenance d'un pilote à une compagnie :
Compagnie (id_cie, nom_cie)-0,n-----------embaucher-------------0,1-Pilote(id_pilote, nom, prénom).

Pour le parrainage, j'ai prévu de faire une association réflexive :
Pilote(id_pilote, nom, prénom, id_parrain)-0,n----------parrainer----------0,1-Pilote(id_pilote, nom, prénom, id_parrain)

L'identifiant de la compagnie et du pilote sont, pour le moment, générés automatiquement par une clé informatique numérique.

Pour vérifier la règle de gestion "un pilote ne peut parrainer qu'un pilote de la même compagnie", je vois 2 solutions qui vont différer au passage au modèle physique de données :

• mettre en place un déclencheur dans la base de données qui s'assure que pilote et parrain sont dans la même compagnie ;
• faire une identification relative dans la table des pilotes avec une clé composée de la compagnie à laquelle appartient le pilote et un identifiant complémentaire. Dans ce cas, la clé étrangère de l'association réflexive est composée de l'identifiant de la compagnie et de l'identifiant complémentaire du parrain. Il ne reste qu'à mettre une contrainte de vérification d'égalité des deux identifiants de compagnie pour un pilote (sa compagnie et celle de son parrain).

Ce qui me fait pencher vers la deuxième solution, c'est en cas d'import massif de pilotes (ce qui sera mon cas), je me dis que le déclencheur peut ralentir les traitements, alors que la contrainte de vérification ira plus vite, sera moins gourmande en ressources.

Je balaie tout de suite les questions du genre : "Et que ferez-vous si, un jour, un pilote peut être embauché dans plusieurs compagnie ?". Dans le cas que j'ai à gérer, comme explicité ci-dessus, cette hypothèse est éliminée d'entrée de jeu...

Laquelle des deux solutions vous semblent la meilleure quand je passerai au modèle physique (déclencheur, contrainte de vérification...) ?
Y a-t-il une autre solution envisageable encore meilleure ?

Merci d'avance pour votre aide.

[escartefigue]

Bonjour,

Je suppose que dans votre application, un pilote peut changer de compagnie aérienne, auquel cas une identification relative est inadéquate.

[ced]

Non, justement, ce n'est pas prévu dans mon cas. D'où l'identification relative (en fait, on met en place une table d'historisation des modifications).
Mais même si c'était le cas, la contrainte de vérification obligerait à garantir la cohérence des données (tout comme le déclencheur).
Pourquoi l'identification relative ne conviendrait pas ?

[escartefigue]

Une clef primaire doit avant tout être stable ce ne serait pas le cas ici :
S'il y a identification relative et que des pilotes changent d'employeur, alors il faut mettre à jour en cascade l'ensemble des identifiants dans la table pilote et dans toutes les tables enfants
en fonction du volume dans les tables et index concernés, ça peut prendre un temps considérable et provoquer des déplacements de pages.

Edit : c'est pourquoi l'identification relative est utilisée pour les cas où l'entité-type seule n'a pas d'existence propre. Les cas d'espèce sont les lignes de commandes qui ne peuvent exister sans commande ou les lignes de factures sans facture. Ces cas sont associés à des contraintes avec delete cascade
A l'inverse, un pilote peut exister sans compagnie aérienne, même si à terme il aura probablement du mal à boucler ses fins de mois. La suppression (faillite par exemple) d'une compagnie aérienne, n'entraine pas automatiquement la suppression (suicide ? ) de ses pilotes

[ced]

Exact.

Dans mon cas, il n'y a aucune suppression prévue, mais une "désactivation" des lignes et ajout de nouvelles lignes (y compris pour les compagnies). Ce qui fait qu'on est bien dans un cas où la clé primaire est stable dans le temps.
D'où mon choix d'identification relative.

Du coup, je repose ma question première : est-ce que c'est techniquement plus judicieux de passer par cette solution ?

Merci en tout cas pour ton aide.

[escartefigue]

Exact.

Dans mon cas, il n'y a aucune suppression prévue, mais une "désactivation" des lignes et ajout de nouvelles lignes (y compris pour les compagnies). Ce qui fait qu'on est bien dans un cas où la clé primaire est stable dans le temps.
D'où mon choix d'identification relative.

Du coup, je repose ma question première : est-ce que c'est techniquement plus judicieux de passer par cette solution ?

Merci en tout cas pour ton aide.

Avec ces réserves c'est en tout cas possible, il est probable qu'une contrainte check sera plus performante qu'un trigger, et ce malgré le pouillème de dégradation des perfs lié à l'augmentation de la taille de la clef primaire. Je n'ai jamais confronté les 2 solutions pour pouvoir répondre avec certitude.

[ced]

OK, c'est aussi mon avis, mais comme moi non plus je n'ai jamais confronté les deux
Encore merci pour ton aide.

Je laisse la discussion ouverte, si quelqu'un a déjà comparé les deux solutions...

[fsmrel]

Bonsoir ced, bonsoir capitaine,


Passons au niveau SQL. Pour déclarer les tables COMPAGNIE et PILOTE, pas de problème :

CREATE TABLE COMPAGNIE
(
        id_cie             INT               NOT NULL
      , nom_cie            VARCHAR(32)       NOT NULL
    , CONSTRAINT COMPAGNIE_PK PRIMARY KEY (id_cie) 
) ;

CREATE TABLE PILOTE
(
        id_pilote          INT               NOT NULL
      , nom_pilote         VARCHAR(32)       NOT NULL
      , prenom_pilote      VARCHAR(32)       NOT NULL
    , CONSTRAINT PILOTE_PK PRIMARY KEY  (id_pilote)
) ;

A partir de là, je propose une solution alternative, permettant d’éviter de mettre en œuvre des triggers et autres lourdeurs pour s’assurer qu’un filleul et son parrain font partie de la même compagnie.

Tout d’abord, comme un pilote peut n’appartenir à aucune compagnie, afin d’éviter l’intrusion du bonhomme NULL, pour l’association entre COMPAGNIE et PILOTE, je mets en oeuvre une table de clé primaire {id_pilote}, avec des NOT NULL partout :

CREATE TABLE PILOTE_CIE
(
        id_cie             INT               NOT NULL
      , id_pilote          INT               NOT NULL
      , date_embauche      DATE              NOT NULL
    , CONSTRAINT PILOTE_CIE_PK PRIMARY KEY (id_pilote)
    , CONSTRAINT PILOTE_CIE_AK UNIQUE (id_cie, id_pilote) 
    , CONSTRAINT PILOTE_CIE_PILOTE_FK FOREIGN KEY (id_pilote) 
          REFERENCES PILOTE (id_pilote)  
    , CONSTRAINT PILOTE_CIE_COMPAGNIE_FK FOREIGN KEY (id_cie) 
          REFERENCES COMPAGNIE (id_cie)  
) ;

Observez la présence d’une surclé {id_cie, id_pilote}, c’est grâce à elle qu’on sous-traitera au SGBD le contrôle de la contrainte.


Concernant le parrainage : pour éviter là encore l’intrusion de NULL, je mets en œuvre une table à cet effet, en relation non pas avec PILOTE mais avec PILOTE_CIE, afin que l’attribut id_cie fasse partie de l’en-tête de la table :

CREATE TABLE PARRAINAGE
(
        id_cie             INT               NOT NULL
      , id_pilote          INT               NOT NULL
      , id_parrain         INT               NOT NULL
      , date_parrainage    DATE              NOT NULL
    , CONSTRAINT PARRAINAGE_PK PRIMARY KEY (id_pilote)
    , CONSTRAINT PARRAINAGE_FILLEUL_FK FOREIGN KEY (id_cie, id_pilote) 
          REFERENCES PILOTE_CIE (id_cie, id_pilote)  
    , CONSTRAINT PARRAINAGE_PARRAIN_FK FOREIGN KEY (id_cie, id_parrain) 
          REFERENCES PILOTE_CIE (id_cie, id_pilote)  
    , CONSTRAINT PARRAINAGE_CK1 CHECK (id_pilote <> id_parrain) 
) ;

Observez que les clés étrangères font référence (c’est légal) non pas à la clé primaire de PILOTE_CIE, mais à la surclé.


Un début de jeu d’essai conforme :

INSERT INTO COMPAGNIE (id_cie, nom_cie) VALUES (1, 'Air Dugoineau') ;
INSERT INTO COMPAGNIE (id_cie, nom_cie) VALUES (2, 'Air Mado') ;

INSERT INTO PILOTE (id_pilote, nom_pilote, prenom_pilote) VALUES (1, 'Naudin', 'Fernand') ;
INSERT INTO PILOTE (id_pilote, nom_pilote, prenom_pilote) VALUES (2, 'Volfoni', 'Raoul') ;
INSERT INTO PILOTE (id_pilote, nom_pilote, prenom_pilote) VALUES (3, 'Volfoni', 'Paul') ;
INSERT INTO PILOTE (id_pilote, nom_pilote, prenom_pilote) VALUES (4, 'Delafoix', 'Antoine') ;
INSERT INTO PILOTE (id_pilote, nom_pilote, prenom_pilote) VALUES (5, 'Haddock', 'Archibald') ;

INSERT INTO PILOTE_CIE (id_cie, id_pilote, date_embauche) VALUES (1, 1, '1960-01-01') ;
INSERT INTO PILOTE_CIE (id_cie, id_pilote, date_embauche) VALUES (1, 2, '1961-02-01') ;
INSERT INTO PILOTE_CIE (id_cie, id_pilote, date_embauche) VALUES (2, 3, '1962-02-01') ;
INSERT INTO PILOTE_CIE (id_cie, id_pilote, date_embauche) VALUES (1, 4, '1961-02-01') ;

INSERT INTO PARRAINAGE (id_cie, id_pilote, id_parrain, date_parrainage) VALUES (1, 2, 1, '1970-01-01') ;
INSERT INTO PARRAINAGE (id_cie, id_pilote, id_parrain, date_parrainage) VALUES (1, 4, 1, '1970-01-01') ;

Essayons de faire parrainer un pilote par un parrain d’une autre compagnie :

INSERT INTO PARRAINAGE (id_cie, id_pilote, id_parrain, date parrainage) VALUES (1, 3, 1, '1970-01-01') ;

Il est évident que le SGBD rejette l'insert. Le pilote 3 fait bien partie de la compagnie 1, mais son parrain 1 fait partie de la compagnie 2, or il n’y a qu’une seule valeur possible pour la compagnie (attribut id_cie) d’un pilote et celle de son parrain...

L’objet de la surclé de la table PILOTE_CIE était de permettre de propager l’attribut id_cie jusqu’à l’en-tête de la table PARRAINAGE, ce qui permet de garantir qu’un pilote et son parrain font bien partie de la même compagnie. □�⃞⃞

[ced]

Bonjour,

Un grand merci, fsmrel, pour cette solution d'une limpidité et d'une efficacité lumineuses (comme toujours).
Je pars là-dessus.

ced