Discussion :

[grabriel]

Bonjour,

Je réalise un modèle de données, j'ai une table ours avec :
- un identifiant
- un nom
- un sexe
- une date de naissance.

Mes règles de gestion :
- Un ours peut-être père ou mère (suivant le sexe) d'un autre ours.
- Sachant qu'un ours n'a qu'1 et 1 seul père, 1 ou 1 seule mère.
- Mais cet ours peut être père ou mère d'un ou plusieurs autre ours (cas d'un ours reproducteur).
- On peux avoir un ours de parent(s) père ou/et mère inconnue.

Ma solution :

Une table Ours (voir modèle au dessus) et une table Famille dans laquelle on à les champs suivant :
père = identifiant de l'ours
mère = identifiant de l'ours
enfant = identifiant de l'ours.
chaque champ est une clé étrangère vers la table ours.
et la clé primaire est les trois champs.
Une contrainte d'unicité sur les couples (mère - enfant) et une autre (père - enfant), qui permettrait de ne pas avoir un enfant avec plus d'1 père et 1 mère.

Avec ça je peux savoir si une mère ours à eu un ou plusieurs enfants puisqu'elle sera présente plusieurs fois dans la table famille, mais avec l'identifiant enfant différent.
Dans le cas de jumeaux je peux retrouver si la date de naissance de ses 2 ours sont identiques.
Pour le cas d'une naissance d'un parent inconnu mas solution serai de créer un ours de nom inconnu qui serait le parent des ours de parent non répertorié.
Je vais créer un ours (0,inconnu,null,null);
Ma question est-ce que cette solution est envisageable?

Une autre solution serai de créer dans la table ours deux champs père et mère, qui seraient par défaut à NULL.

Donc ma question est quelle est la meilleur des deux solutions (la table famille ou les 2 champs) et est-ce qu'une clé étrangère peut être null?

Merci pour l'aide que vous pourrez m'apporter.

[CinePhil]

Reprenons du début avec des règles de gestion qui définiront comment architecturer le MCD.

Du fait qu'un ours peut être de parent(s) inconnu(s), la règle de gestion est la suivante :
"Un ours peut avoir un à deux ours parent(s) et un ours peut être parent d'un à plusieurs ours".

Ce qui donnerait le MCD suivant :
Ours -0,n----AvoirParent----0,n- Ours

Dans un digramme de classe UML, on pourrait mettre une multiplicité maximale à 2 :
Ours -*-------------(parent)----0,2- Ours

Mais dans un MCD, il n'est pas d'usage de mettre une cardinalité maximale différente de 1 ou n.

Dès lors, on voit que dans le MCD, rien n'interdit à un ours d'avoir plus de deux parents. On peut résoudre ce problème en créant deux associations :
Ours -0,1----AvoirPère----0,n- Ours
|--------0,1----AvoirMère----0,n----|

On pourrait aussi penser à une association ternaire :
Ours -0,n--(père)--EtreParent----0,n- Ours
|--------0,n--(mère)-------|

Ce qui correspond à votre proposition de tables.
Mais comme la clé primaire d'une table issue d'une association est composée des identifiants des entités entrant en jeu dans l'association (ici les identifiants du père, de la mère et de l'ours enfant) et qu'un élément de clé primaire ne peut pas être nul, Cette ternaire n'est pas bonne et il faut conserver les deux associations séparées.

Les deux associations séparées garantissent l'unicité des couples (ours, père) et (ours, mère) et permettent à un ours d'être de parent(s) inconnus : il n'y aurait tout simplement pas de ligne dans la table associative pour un ours sans parents connus.

On aurait donc les tables :
Ours (O_Id, O_Nom, O_Sexe, O_DateNaissance)
Pere (P_IdEnfant, P_IdPere)
Mere (M_IdEnfant, M_IdMere)

A noter qu'il faudra, lors de l'implémentation, mettre une contrainte sur le sexe des parents, laquelle devra être vérifiée par un trigger à l'insertion des données.

[grabriel]

Merci beaucoup pour cette réponse bien complète!!

Du coup je ne pourrais pas gérer les couples d'ours de même sexe qui auront adopté un ourson!!

[CinePhil]

Du coup je ne pourrais pas gérer les couples d'ours de même sexe qui auront adopté un ourson!!

Ce n'était pas dans le cahier des charges annoncé mais avec une modélisation différente, c'est faisable !

[grabriel]

Désolé je reviens j'ai oublié un bout!!!

Pour cette solution qu'est ce qui n'est pas bon.

Une autre solution serai de créer dans la table ours deux champs père et mère, qui seraient par défaut à NULL.

Ca pourrait gérer tous les cas en rajoutant simplement 2 champs, si l'ours n'a pas de père ou mère connu(e), la valeur serait à null.
Faut juste faire un contrôle pour pas que l'ours ait le même O_id dans le champ père et mère.

C'est beaucoup plus simple, par contre le truc qui me dérange c'est de mettre une clé étrangère à NULL.

Merci.

[CinePhil]

C'est une solution simplifiée que j'avais utilisée sur une table de bovins à l'INRA mais disons que ce n'est pas très propre sur le plan conceptuel.

Je crois que ça peut aussi avoir des conséquences sur certaines opérations :
- Un COUNT(DISTINCT cleEtrangere) comptera chaque NULL pour autant d'éléments distincts, ce qui oblige à agrémenter la requête d'un WHERE cleEtrangere IS NOT NULL, ce qui ralentit l'exécution.
- Je crois que ça peut pénaliser la performance des index également, donc des jointures.

Mais je n'ai jamais testé ça en grandeur réelle. Un expert plus... expérimenté que moi, tels fsmrel ou SQLPro, pourrait sans doute en dire plus sur le sujet.

[grabriel]

Merci pour ces informations !

J'en avais déduis un peu les mêmes conclusions, le coup de la clé étrangère null j'aime pas trop. Je préfère faire une entité ours avec un O_Id à 0 par exemple que j'appellerai inconnue et qui serait le père de tous les orphelins. (un peu comme le père noël, mais lui c'est plus avec les rennes ).
Au moins mon champ de clé étrangère père sera non null et aura une valeur par défaut. Même chose avec le champ mère.

Attendons l'avis des experts alors. En espérant que leurs explications restent simple à comprendre pour le simple mortel que je suis!!

fsmrel et SQLPro

beaucoup pour ton aide!!!

J'ai battu mon record de smiley dans un même post.... vivement ce w-e

[fsmrel]

un peu comme le père noël, mais lui c'est plus avec les rennes

En fait, le Père Noël passait du côté de la halle aux rennes, avec ses appeaux (pour chasser le Null) et il chantait : « En passant par la halle aux rennes, avec mes appeaux... »

Bon, revenons au sujet. Le défi est de ne pas voir apparaître le Bonhomme Null. Quant aux valeurs par défaut, pourquoi pas, mais on peut s'en passer, en reprenant ce que propose CinePhil :

Les deux associations séparées garantissent l'unicité des couples (ours, père) et (ours, mère) et permettent à un ours d'être de parent(s) inconnus : il n'y aurait tout simplement pas de ligne dans la table associative pour un ours sans parents connus.

On aurait donc les tables :
Ours (O_Id, O_Nom, O_Sexe, O_DateNaissance)
Pere (P_IdEnfant, P_IdPere)
Mere (M_IdEnfant, M_IdMere)

Cette solution est satisfaisante. Par exemple, en SQL Server (en changeant un peu les noms) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
CREATE TABLE OURS
(      OursId         Int           Not Null
     , OursNom        Varchar(32)   Not Null
     , DateNaissance  CHAR(10)      Not Null
     , Sexe           CHAR(1)       Not Null
  , CONSTRAINT OURS_PK  PRIMARY KEY (OursId)     
  , CONSTRAINT SEXE_CHK1 CHECK (Sexe IN ('M', 'F'))
) ;
CREATE TABLE FILIATION1
(      OursId              Int   Not Null
     , OursParentId        Int   Not Null
  , CONSTRAINT  FILIATION1_PK  PRIMARY KEY (OursId)
  , CONSTRAINT  FILIATION1_FK1 FOREIGN KEY (OursId) 
       REFERENCES OURS ON DELETE CASCADE
  , CONSTRAINT  FILIATION1_FK2 FOREIGN KEY (OursParentId) 
       REFERENCES OURS
  , CONSTRAINT FILIATION1_CHK1 CHECK (OursId <> OursParentId)
) ;
CREATE TABLE FILIATION2
(      OursId              Int   Not Null
     , OursParentId        Int   Not Null
  , CONSTRAINT  FILIATION2_PK  PRIMARY KEY (OursId)
  , CONSTRAINT  FILIATION2_FK1 FOREIGN KEY (OursId) 
       REFERENCES OURS ON DELETE CASCADE
  , CONSTRAINT  FILIATION2_FK2 FOREIGN KEY (OursParentId) 
       REFERENCES OURS
  , CONSTRAINT FILIATION2_CHK1 CHECK (OursId <> OursParentId)
) ;

Comme vous le signalez, il faudra s’assurer (disons par des triggers) que le sexe du père et celui de la mère d’un ours sont différents. Il faudrait aussi s’assurer qu’un ours est né après ses parents, qu’il n’est pas le parent de ses parents, etc. (la routine, quoi...)

Exemples de triggers permettant de s’assurer que les parents sont de sexe différent :

Un premier trigger :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER Filiation1Trg1 ON FILIATION1 FOR INSERT, UPDATE AS
  IF EXISTS (SELECT 'x'
             FROM        INSERTED AS x 
                    JOIN FILIATION2 As y
                           ON x.OursId = y.OursId
                    JOIN OURS AS z
                           ON x.OursParentId = z.OursId   
                    JOIN OURS AS t
                           ON y.OursParentId = t.OursId   
             WHERE  z.Sexe = t.sexe)
     BEGIN
         RAISERROR ('Parents de même sexe !', 20, 1)
     END ;

Son jumeau :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER Filiation2Trg1 ON FILIATION2 FOR INSERT, UPDATE AS
  IF EXISTS (SELECT 'x' 
             FROM        INSERTED AS x 
                    JOIN FILIATION1 As y
                           ON x.OursId = y.OursId
                    JOIN OURS AS z
                           ON x.OursParentId = z.OursId   
                    JOIN OURS AS t
                           ON y.OursParentId = t.OursId   
             WHERE  z.Sexe = t.sexe)
     BEGIN
         RAISERROR ('Parents de même sexe !', 20, 1)
     END ;

Et leur petit frère (au cas où l’on modifierait le sexe d’un ours parent) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TRIGGER OursTrg1 ON OURS FOR UPDATE AS
  IF EXISTS (SELECT 'x' 
             FROM        FILIATION1 AS x 
                    JOIN FILIATION2 As y
                           ON x.OursId = y.OursId
                    JOIN INSERTED AS z
                           ON x.OursParentId = z.OursId   
                    JOIN OURS AS u
                           ON y.OursParentId = u.OursId
            WHERE  z.Sexe = u.sexe
           UNION  
            SELECT 'x' 
             FROM        FILIATION1 AS x 
                    JOIN FILIATION2 As y
                           ON x.OursId = y.OursId
                    JOIN INSERTED AS z
                           ON y.OursParentId = z.OursId   
                    JOIN OURS AS u
                           ON y.OursParentId = u.OursId
            WHERE  z.Sexe = u.sexe)
    BEGIN
         RAISERROR ('Parents de même sexe !', 20, 1)
    END

Ceci dit, je ne suis pas un as de SQL, si donc SQLpro passe par là (avec ses appeaux...)

[grabriel]

Bonjour et Joyeux noël,

Merci pour cette réponse très complète!!!

J'ai opté pour la solution 1 mais j'ai une question complémentaire pour la solution 2.

Concernant la réponse :

Le défi est de ne pas voir apparaître le Bonhomme Null.

C'est à dire que d'une manière générale avoir une clé étrangère à NULL c'est [dans certains cas c'est possible / mal / pas conseiller / à éviter / houlaaa!! jamais au grand jamais] (Rayer la mention inutile).

J'ai jamais eu à modéliser quelque chose qui une fois en SQL se traduit par une clé étrangère à NULL. C'est une autre personne qui m'a proposé la solution 2 (pour rappel deux champs dans la table ours, qui prendront la valeur O_Id et si l'ours n'a pas de parent connu alors on met null).

Je suis un peu troublé face à ce cas. C'est peut être une question basique mais dans quel cas peut on avoir une clé étrangère à NULL?

Merci d'éclairer ma lanterne.

[CinePhil]

Je ne pense pas que ce soit carrément interdit par la norme ou les bonnes pratiques mais c'est plutôt à éviter.
D'une manière générale, si une colonne contiendra une grande majorité de valeurs nulles, il vaut mieux extarnaliser dans une autre table et faire une clé étrangère dans celle-ci.
Si par contre un tout petit nombre de lignes auront une valeur NULL dans cette colonne, alors il est plus pratique et probablement plus rapide de laisser la possibilité de cette valeur nulle.

[grabriel]

Merci pour votre aide à tous les deux!!

Je vais enfin pouvoir dormir tranquillement!

[fsmrel]

Le défi est de ne pas voir apparaître le Bonhomme Null.

C'est à dire que d'une manière générale avoir une clé étrangère à NULL c'est [dans certains cas c'est possible / mal / pas conseiller / à éviter / houlaaa!! jamais au grand jamais] (Rayer la mention inutile).

Pour mon usage personnel, je coche la case « jamais au grand jamais ». En ce qui vous concerne, comme dit Laspalès à Chevallier : C’est vous qui voyez...

Si vous lisez le paragraphe 1.2. « Retour aux sources » de l’article Bases de données relationnelles et normalisation , vous noterez la définition de ce qu’est une relation au sens de la théorie relationnelle, relation dont la table SQL est un avatar plus ou moins conforme :

« Le terme de relation est utilisé ici dans son acception mathématique. Étant donnés les ensembles S1, S2, ..., Sn (non nécessairement distincts), R est une relation sur ces n ensembles si c'est un ensemble de n-uplets, le 1er élément de chacun d'eux tirant sa valeur de S1, le 2e de S2, et ainsi de suite (de manière plus concise, R est un sous-ensemble du produit cartésien S1 X S2 X ... X Sn). On fera référence à Sj comme étant le jième domaine de R. Suite à ce qui vient d'être énoncé, on dit que R est de degré n. Les relations de degré 1 sont souvent dites unaires, celles de degré 2 binaires, de degré 3 ternaires, et celles de degré n n-aires. »

S1, ..., Sn sont des ensembles, et dans un ensemble, NULL n’a pas de sens puisque ça n’est pas une valeur. Dans le cadre de la théorie relationnelle, un n-uplet (ligne en SQL) est une valeur construite à partie de ces ensembles et on n’a pas à y injecter NULL, sinon la définition « R est un sous-ensemble du produit cartésien S1 X S2 X ... X Sn) » n’est pas respectée. Un « n-uplet » marqué NULL n’est pas un n-uplet et une « relation » contenant un tel « n-uplet » n’est pas une relation. Le concept de NULL est banni de la théorie relationnelle. N’oublions pas non plus que l’algèbre relationnelle (avec ses opérateurs UNION, INTERSECTION, PROJECTION, JOINTURE, etc.) permet de manipuler des relations pour produire d’autres relations qui à leur tour pourront être utilisées pour engendrer d’autres relations, en respectant nécessairement le principe de fermeture et il n’est pas prouvé que ce principe ne soit pas mis en échec quand le bonhomme NULL intervient.

Concernant la théorie SQL, les choses ne sont pas claires. L’avatar de la relation qu’est la table peut contenir des lignes (avatars des n-uplets) en double (donc une table n’est pas un ensemble), notamment dans le résultat d’un SELECT. L’algèbre relationnelle devient une algèbre de « sacs » (un sac est comme un ensemble, à ceci près que les doublons y sont autorisés). Et bien entendu, dans une table, le bonhomme NULL est chez lui.

dans quel cas peut on avoir une clé étrangère à NULL?

Puisque dans la théorie relationnelle NULL est hors-la-loi, une clé étrangère ne peut pas héberger le bonhomme NULL. En SQL, NULL tend des pièges. Par exemple, si une clé étrangère comporte deux attributs ou plus, il suffit que l’un deux soit marqué NULL pour que vous puissiez valoriser le reste des attributs avec n’importe quoi : l’intégrité référentielle n’est pas vérifiée (La norme SQL permet de s’assurer de l’intégrité d’une partie de clé en cas de présence de NULL, mais assurez-vous que votre SGBD soit en conformité, ce qui n’est en général pas le cas (paramètre MATCH (FULL | PARTIAL | SIMPLE)).

Vous pouvez aussi consulter les discussions :

http://www.developpez.net/forums/d26...orie-pratique/

http://www.developpez.net/forums/d50...sum-x-p-sum-y/

http://www.dbdebunk.com/page/page/2928212.htm

Je ne pense pas que ce soit carrément interdit par la norme ou les bonnes pratiques mais c'est plutôt à éviter.

La norme étant déviante par rapport à la théorie relationnelle, elle autorise la présence de NULL dans les clés étrangère, avec des possibilités de contrôle plus ou moins satisfaisants, comme je viens de le préciser. Comme vous vous en doutez, je ne connais pour ma part qu’une bonne pratique et je n'en dévie pas (et cela depuis de longues années...)

[CinePhil]

OK sur le principe général d'éviter le bonhomme NULL, comme je l'ai dit moi-même.
Mais comment se débrouille la théorie relationnelle quand la valeur d'un attribut n'est pas connue ?
Par exemple, si j'ai (en revenant aux tables SQL) une table de films contenant une colonne permettant d'enregistrer la durée du film en minutes.
J'enregistre un nouveau film mais je ne connais pas encore sa durée. Je ne vais quand même pas mettre 0 qui serait une valeur fausse ?
Dans un tel cas, j'autorise la colonne à être à NULL.
Comment feriez-vous en interdisant le NULL ?

[fsmrel]

Mais comment se débrouille la théorie relationnelle quand la valeur d'un attribut n'est pas connue ?

Avec la théorie relationnelle il n’y a aucun problème, grâce à l’opérateur EXTEND ou aux RVA (voyez la référence : An Introduction To Database Systems (8th edition), de Chris Date au paragraphe 19.5, et accessoirement Bases de données relationnelles et normalisation).

Quand je passe à SQL et que je code l’instruction CREATE TABLE, je ne connais que NOT NULL :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 CREATE TABLE FILM
(   FilmId       INT          NOT NULL
  , NomFilm      VARCHAR(48)  NOT NULL
  , Duree        SMALLINT     NOT NULL
  , Sortie       SMALLINT     NOT NULL
  , ...
 , CONSTRAINT FILM_PK PRIMARY KEY (FilmId)
) ;

Si donc on ignore la valeur de certaines informations lors de leur saisie, les attributs correspondants dégagent dans des tables appendices (par exemple, Duree, Sortie) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE FILM
(   FilmId       INT          NOT NULL
  , NomFilm      VARCHAR(48)  NOT NULL
  , ...
 , CONSTRAINT FILM_PK PRIMARY KEY (FilmId)
) ; 
CREATE TABLE FILM_DUREE
(   FilmId       INT          NOT NULL
  , Duree        SMALLINT     NOT NULL     
 , CONSTRAINT FILM_DUREE_PK PRIMARY KEY (FilmId)
 , CONSTRAINT FILM_DUREE_FK FOREIGN KEY (FilmId)
        REFERENCES FILM  ON DELETE CASCADE
) ; 
CREATE TABLE FILM_SORTIE
(   FilmId       INT          NOT NULL
  , Sortie       SMALLINT     NOT NULL     
 , CONSTRAINT FILM_SORTIE_PK PRIMARY KEY (FilmId)
 , CONSTRAINT FILM_SORTIE_FK FOREIGN KEY (FilmId)
        REFERENCES FILM  ON DELETE CASCADE
) ;

Si l’on veut présenter une table unique à l’utilisateur, on crée une vue qui recolle les morceaux :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE VIEW FILMV (FilmId, FilmNom, Duree, Sortie) AS
   SELECT  x.FilmId, x.FilmNom, y.Duree, z.Sortie
   FROM    FILM AS x LEFT OUTER JOIN FILM_DUREE  AS y 
              ON x.FilmId = y.FilmId   
                     LEFT OUTER JOIN FILM_SORTIE AS z 
              ON x.FilmId = z.FilmId
;

Et on affiche « Inconnu » quand l’information ne figure pas dans les tables appendices :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT   FilmId, FilmNom
       , Coalesce(Cast(Duree As Char),  'inconnue') As Durée 
       , Coalesce(Cast(Sortie As Char), 'inconnue') As Sortie 
FROM     FILMV 
;

[CinePhil]

En gros ça met en oeuvre un mécanisme d'héritage :
FilmDurée -(1,1)---------Etre----0,1- Film
FilmDateSortie -(1,1)----Etre----0,1-|

Je trouve quand même que ça complique le modèle pour pas grand chose !
Et je ne suis pas sûr que ce soit optimal au niveau des performances en cas de table avec un grand nombre de lignes.

[fsmrel]

En gros ça met en oeuvre un mécanisme d'héritage

Graphiquement ça se ressemble.
Sémantiquement c’est complètement différent. Concernant l’héritage, on sait par exemple qu’un cercle est une ellipse et que le cercle hérite des propriétés de l’ellipse (formules de calcul de la surface, etc.), tout comme l’ellipse hérite des propriétés de la figure plane, même chose pour le polygone. Dans notre cas, il s’agit de prendre en compte des données (temporairement) inconnues, mais dans le principe, un film a toujours une durée et a toujours une date de sortie (passée ou à venir). Veuillez noter que j’utilise ici le verbe avoir et pas le verbe être.

Un AGL connaissant la théorie relationnelle devrait donc être à même de permettre la production du MLD ci-dessous à partir d’un MCD dans lequel les attributs Duree et Sortie de l’entité-type FILM sont notés non obligatoires :

Je trouve quand même que ça complique le modèle pour pas grand chose !

Mais comme les AGL ne connaissent pas la théorie relationnelle, on bidouillera en sorte qu’ils produisent le MLD attendu :




Ceci dit, tout cela n’est pas bien compliqué. Bon d’accord, il est préférable d’adjoindre la vue FILMV, mais on ne peut pas dire qu’elle représente quelque chose de bien compliqué à fabriquer. En revanche, la mise en œuvre d’une logique ternaire (vrai, faux, inconnu) à la place de la logique binaire traditionnelle (vrai, faux) est quelque chose de très compliqué (pour vous ça peut rester transparent, mais pas pour l’optimiseur du SGBD).

Et je ne suis pas sûr que ce soit optimal au niveau des performances en cas de table avec un grand nombre de lignes.

Soyons attentifs à ne pas mélanger le niveau sémantique et le niveau fer à souder. Ceci dit, J’ai pour ma part appliqué le « zéro NULL » pour des tables de dizaines de millions de NULL avec DB2 for z/OS sans aucun problème de performance. Avec votre SGBD, utilisez EXPLAIN et prototypez la performance (comme dans le cas de l’héritage, où cet aspect des choses bassement matériel est comparable). Si ça ne passe pas, utilisez NULL, mais vous n’aurez pas relevé le défi sémantique (ceci vaut pour l’héritage, mutatis mutandis).

Maintenant, souhaitez-moi bon courage, je pars chez le dentiste...