Difficultés matérialisation relation entre trois entités

**apprenant16** · 26/02/2020, 02h33

Merci pour la réponse. Donc je peux reprendre tout le script, virer cette colonne dans la table CONSULTATION_PAYANTE et exécuter pour voir ce que cela donnera ?

**apprenant16** · 26/02/2020, 02h46

Je vais créer un nouveau fichier Access et reprendre le script Access généré par Looping que vous avez posté et tester une nouvelle fois. Je vous tiens au courant.

**fsmrel** · 26/02/2020, 03h38

D’accord, vous reprenez à froid, et tant qu'à faire, postez le script testé, afin qu’on croise les contrôles...

**apprenant16** · 26/02/2020, 15h23

Bonjour,

Chose bizarre

Avec ton script #35 que voici, j'ai crée un nouveau module que j'ai enregistré sur une base vide. Après exécution, tout marche à merveille sans aucune erreur.

Code :

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
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
58
59
60
61
62
63
64
65
66
67
68
Sub Create_Tables()

DoCmd.RunSQL "CREATE TABLE PATIENT(" & _
   "patientId COUNTER," & _
   "patientMatricule INT NOT NULL UNIQUE," & _
   "patientNom VARCHAR(48) NOT NULL," & _
   "CONSTRAINT PATIENT_PK PRIMARY KEY(patientId)" & _
");"   

DoCmd.RunSQL "CREATE TABLE FACTURE(" & _
   "patientId INT," & _
   "factureId COUNTER," & _
   "factureNumero CHAR(8) NOT NULL UNIQUE," & _
   "factureDate DATE NOT NULL," & _
   "factureMontant INT NOT NULL," & _
   "CONSTRAINT FACTURE_PK PRIMARY KEY(patientId, factureId)," & _
   "CONSTRAINT FACTURE_PATIENT_FK FOREIGN KEY(patientId) REFERENCES PATIENT(patientId)" & _
");"   

DoCmd.RunSQL "CREATE TABLE CONSULTATION(" & _
   "patientId INT," & _
   "consultationId COUNTER," & _
   "consultationDate DATE NOT NULL," & _
   "CONSTRAINT CONSULTATION_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PATIENT_FK FOREIGN KEY(patientId) REFERENCES PATIENT(patientId)" & _
");"   

DoCmd.RunSQL "CREATE TABLE CONSULTATION_PAYANTE(" & _
   "patientId_1 INT," & _
   "consultationId INT," & _
   "patientId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId_1, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_1_FK FOREIGN KEY(patientId_1, consultationId) REFERENCES CONSULTATION(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"   

DoCmd.RunSQL "CREATE TABLE TRAITEMENT(" & _
   "patientId INT," & _
   "consultationId INT," & _
   "traitementId COUNTER," & _
   "CONSTRAINT TRAITEMENT_PK PRIMARY KEY(patientId, consultationId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)" & _
");"   

DoCmd.RunSQL "CREATE TABLE TRAITEMENT_PAYANT(" & _
   "patientId_1 INT," & _
   "consultationId INT," & _
   "traitementId_1 INT," & _
   "traitementId INT," & _
   "patientId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PAYANT_PK PRIMARY KEY(patientId_1, consultationId, traitementId_1, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_TRAITEMENT_1_FK FOREIGN KEY(patientId_1, consultationId, traitementId_1) REFERENCES TRAITEMENT(patientId, consultationId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"   

DoCmd.RunSQL "CREATE TABLE INTERVENTION(" & _
   "patientId INT," & _
   "consultationId INT," & _
   "traitementId INT," & _
   "interventionId COUNTER," & _
   "interventionDate DATE NOT NULL," & _
   "CONSTRAINT INTERVENTION_PK PRIMARY KEY(patientId, consultationId, traitementId, interventionId)," & _
   "CONSTRAINT INTERVENTION_TRAITEMENT_FK FOREIGN KEY(patientId, consultationId, traitementId) REFERENCES TRAITEMENT(patientId, consultationId, traitementId)" & _
");"   

End Sub

Nom : Capture.PNG
Affichages : 184
Taille : 17,7 Ko

Ma question est maintenant de savoir si je dois supprimer la colonne ID comme vous me l'aviez suggéré dans la table Consultation_Payante ?

Dans tous les cas, la création des tables s'est déroulée sans soucis. Merci

**fsmrel** · 26/02/2020, 18h18

Bonjour apprenant16,

Le post #35 contient deux scripts : le 1er est celui qui est produit par Looping. Le problème est que les tables CONSULTATION_PAYANTE, TRAITEMENT_PAYANT comportent deux colonnes patientId_1 et patientId faisant qu’on peut affecter les factures du patient apprenant16 au patient fsmrel !

Je constate avec irritation que de nombreux "NOT NULL" sont absents dans ces scripts, Looping ne les a pas générés : chaque colonne de chaque table doit comporter ce paramètre, car l’horrible bonhomme NULL pourrait ficher la patouille (pour rester poli...)

Prenons maintenant le cas de la table CONSULTATION_PAYANTE :

Il s’agit donc d’empêcher les erreurs d’affectation des factures.

1re solution

On peut définir une contrainte obligeant à ce que soit vérifiée l’égalité patientId_1 = patientId

Appelons CONSULTATION_PAYANTE_PATIENT cette contrainte :

DoCmd.RunSQL "CREATE TABLE CONSULTATION_PAYANTE(" & _
   "patientId_1 INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "patientId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId_1, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_1_FK FOREIGN KEY(patientId_1, consultationId) REFERENCES CONSULTATION(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
   " , CONSTRAINT CONSULTATION_PAYANTE_PATIENT CHECK (patientId_1 = patientId)" & _  
");"

2e solution

Mais la solution précédente est laide ! (et coûteuse, du fait qu’elle est par exemple source d’index supplémentaires). La contrainte d’égalité est de facto assurée en remplaçant systématiquement patientId_1 par patientId dans les contraintes puis en virant patientId_1 devenu inutile :

DoCmd.RunSQL "CREATE TABLE CONSULTATION_PAYANTE(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_1_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"

C’est encore plus laid avec la table TRAITEMENT_PAYANT, on pourra y revenir, car en plus de la colonne patientId_1, elle comporte en plus la colonne traitementId_1...

Cent fois sur le métier... Merci de (re)tester la 2e solution.

**apprenant16** · 26/02/2020, 18h53

Bonjour fsmrel,

Merci encore

Voici donc le script corrigé avec la deuxième solution à propos de la table CONSULTATION_PAYANTE. J'ai également ajouté NOT NULL comme vous l'avez si bien remarqué.

Code :

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
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
58
59
60
61
62
63
64
65
66
67
Sub Create_Tables()

DoCmd.RunSQL "CREATE TABLE PATIENT(" & _
   "patientId COUNTER NOT NULL," & _
   "patientMatricule INT NOT NULL UNIQUE," & _
   "patientNom VARCHAR(48) NOT NULL," & _
   "CONSTRAINT PATIENT_PK PRIMARY KEY(patientId)" & _
");"

DoCmd.RunSQL "CREATE TABLE FACTURE(" & _
   "patientId INT NOT NULL," & _
   "factureId COUNTER NOT NULL," & _
   "factureNumero CHAR(8) NOT NULL UNIQUE," & _
   "factureDate DATE NOT NULL," & _
   "factureMontant INT NOT NULL," & _
   "CONSTRAINT FACTURE_PK PRIMARY KEY(patientId, factureId)," & _
   "CONSTRAINT FACTURE_PATIENT_FK FOREIGN KEY(patientId) REFERENCES PATIENT(patientId)" & _
");"

DoCmd.RunSQL "CREATE TABLE CONSULTATION(" & _
   "patientId INT NOT NULL," & _
   "consultationId COUNTER NOT NULL," & _
   "consultationDate DATE NOT NULL," & _
   "CONSTRAINT CONSULTATION_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PATIENT_FK FOREIGN KEY(patientId) REFERENCES PATIENT(patientId)" & _
");"

DoCmd.RunSQL "CREATE TABLE CONSULTATION_PAYANTE(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_1_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");" 

DoCmd.RunSQL "CREATE TABLE TRAITEMENT(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "traitementId COUNTER NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PK PRIMARY KEY(patientId, consultationId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)" & _
");"

DoCmd.RunSQL "CREATE TABLE TRAITEMENT_PAYANT(" & _
   "patientId_1 INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "traitementId_1 INT NOT NULL," & _
   "traitementId INT NOT NULL," & _
   "patientId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PAYANT_PK PRIMARY KEY(patientId_1, consultationId, traitementId_1, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_TRAITEMENT_1_FK FOREIGN KEY(patientId_1, consultationId, traitementId_1) REFERENCES TRAITEMENT(patientId, consultationId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"

DoCmd.RunSQL "CREATE TABLE INTERVENTION(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "traitementId INT NOT NULL," & _
   "interventionId COUNTER NOT NULL," & _
   "interventionDate DATE NOT NULL," & _
   "CONSTRAINT INTERVENTION_PK PRIMARY KEY(patientId, consultationId, traitementId, interventionId)," & _
   "CONSTRAINT INTERVENTION_TRAITEMENT_FK FOREIGN KEY(patientId, consultationId, traitementId) REFERENCES TRAITEMENT(patientId, consultationId, traitementId)" & _
");"

End Sub

**fsmrel** · 26/02/2020, 19h26

OK. Est-ce que le script complet tourne comme une horloge avec ACCESS ?
Si oui, on s'occupera (avec beaucoup de délicatesse, de doigté et de ménagement

) des tables TRAITEMENT, TRAITEMENT_PAYANT et INTERVENTION.

**apprenant16** · 26/02/2020, 19h27

Envoyé par fsmrel

OK. Est-ce que le script complet tourne comme une horloge avec ACCESS ?

Oui ça roule comme sur des roulettes

Je n'ai plus les messages d'erreurs

**fsmrel** · 26/02/2020, 20h45

Parfait, pourvu que ça dure...

Allons-y pour alléger la table TRAITEMENT, en virant la colonne consultationId de la clé primaire :

DoCmd.RunSQL "CREATE TABLE TRAITEMENT(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "traitementId COUNTER NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PK PRIMARY KEY(patientId, traitementId)," & _ 
   "CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)" & _
");"

Conséquence sur la table TRAITEMENT_PAYANT :

La colonne consultationId est à virer de la clé primaire,
La colonne consultationId est à virer de la clé étrangère TRAITEMENT_PAYANT_TRAITEMENT_1_FK.
Ceci fait, la colonne consultationId peut être virée de la table.

Comme dans le cas de la table CONSULTATION_PAYANTE, la colonne patientId_1 doit être remplacée par la colonne patientId dans la clé primaire de la table TRAITEMENT_PAYANT et dans la clé étrangère TRAITEMENT_PAYANT_TRAITEMENT_1_FK.
Ceci fait, la colonne patientId_1 peut être virée de la table.

A son tour, la colonne traitementId_1 doit être virée de la clé primaire de la table TRAITEMENT_PAYANT et remplacée par la colonne traitementId dans la clé étrangère TRAITEMENT_PAYANT_TRAITEMENT_1_FK.
Ceci fait, la colonne traitementId_1 peut être virée de la table.

=>

DoCmd.RunSQL "CREATE TABLE TRAITEMENT_PAYANT(" & _
   "patientId INT NOT NULL," & _
   "traitementId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PAYANT_PK PRIMARY KEY(patientId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_TRAITEMENT_1_FK FOREIGN KEY(patientId, traitementId) REFERENCES TRAITEMENT(patientId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"

La table INTERVENTION subit évidemment ces allégements :

La colonne consultationId est à virer de la clé primaire,
La colonne consultationId est à virer de la clé étrangère INTERVENTION_TRAITEMENT_FK.
Ceci fait, la colonne consultationId peut être virée de la table.

A son tour, la colonne traitementId peut être virée de la clé primaire de cette table.

=>

DoCmd.RunSQL "CREATE TABLE INTERVENTION(" & _
   "patientId INT NOT NULL," & _
   "traitementId INT NOT NULL," & _
   "interventionId COUNTER NOT NULL," & _
   "interventionDate DATE NOT NULL," & _
   "CONSTRAINT INTERVENTION_PK PRIMARY KEY(patientId, interventionId)," & _
   "CONSTRAINT INTERVENTION_TRAITEMENT_FK FOREIGN KEY(patientId, traitementId) REFERENCES TRAITEMENT(patientId, traitementId)" & _
");"

Chez moi ça fonctionne parfaitement.

Ça peut paraître lourd du point de vue des manips effectuées mais, paradoxe ! les tables ne sont plus laides et on garantit que les factures du patient apprenant16 ne pourront pas être affectées au patient fsmrel ! (et vice versa)...

Si vous avez des problèmes, n’oubliez pas de poster votre script…

**Paprick** · 27/02/2020, 02h34

Bonjour François,
Une fois n'est pas coutume, je ne suis pas d'accord avec une de tes suggestions !

En effet, tu indiques à apprenant16 qu'il doit rajouter des "NOT NULL" pour les clés primaires...

Or chacun sait que la clé primaire d'une table est une contrainte d'unicité, composée d'une ou plusieurs colonnes, et qui permet d'identifier de manière unique chaque ligne de la table.
Par conséquent :
- Contrainte d'unicité => clause UNIQUE.
- Identifier chaque ligne de manière unique => une clé primaire ne peut pas être NULL.

Ces deux constats résument très bien l'essence des clés primaires. En gros, une clé primaire est un index UNIQUE sur une colonne qui ne peut pas être NULL.
Par conséquent, lorsque l'on définit une clé primaire, pas besoin de préciser qu'elle est UNIQUE, et pas besoin non plus de rajouter la contrainte NOT NULL.
En fait, une contrainte de clé primaire est donc une combinaison des deux contraintes UNIQUE et NOT NULL.
Et ce constat reste vrai pour des clés composées dont l'ensemble est UNIQUE et dont aucun des éléments ne peut être NULL (la contrainte PRIMARY KEY s'en assure dans tous les SGBD).

Bon... j'étais quand même le premier il y a quelques années à dire concernant cette clause NOT NULL pour les identifiants : "sans va sans dire , mais ça va mieux en le disant" ... Et là, j'ai pris la foudre de la part de DBAs qui m'ont expliqué ce que pouvait coûter en performance le fait de tester inutilement des contraintes. Même si je pense que les SGBD doivent s’accommoder aisément de ces redondances, j'ai dû reconnaître que c'était mal de spécifier ce qui n'a pas besoin de l'être

... Et Looping ne le fait donc plus depuis sa version 2.3 !

**fsmrel** · 27/02/2020, 05h38

Je réponds rapidement, sans prendre le temps nécessaire, mais on pourra approfondir.

Envoyé par Paprick

tu indiques à apprenant16 qu'il doit rajouter des "NOT NULL" pour les clés primaires...

Je suggère à apprenant16 de blinder son système, surtout avec ACCESS qui n’est pas un SGBD relationnel, deux précautions valent mieux qu’une, je suis favorable au triplet « ceinture, bretelles et épingles à nourrice ». Ma règle : chaque colonne d’une table doit être "NOT NULL", quoi qu’en disent les DBA. J’en fus un, particulièrement vigilant sur le sujet très chaud des performances, et j’ai passé trente ans de vie à engager mo entreprise (une SSII) auprès de nos clients (banques, assurances, distribution, industrie, etc.) sur ce sujet extrêmement sensible, et crois-moi je sais de quoi je parle. Cette histoire de performance dégradée par "NOT NULL" me fait doucement rigoler, et comme dirait mon ami Chris Date, c’est un "red herring". C’est un peu comme le DBA en chef d’une grande banque qui m’avait demandé de me déplacer de Paris (près de 1000 km), parce qu’il était persuadé qu’il fallait violer la troisième forme normale, vois ici. Il dut reconnaître que la malheureuse n’était pour rien dans ses problèmes (de performance !), contrairement aux index plaqués sur la table en cause.

Par ailleurs, le concept de clé primaire a été défini en 1970 par Ted Codd inventeur de la théorie relationnelle, et pour parler des clés, on est dans la dunette, dans la théorie des ensembles, alors que les index que tu évoques sont dans la soute, ils relèvent de la plomberie et s’ils sont extrêmement précieux, chaque chose en temps et à sa place ! Quand je me situe à une altitude platonicienne (je provoque), je parle de clés candidates, je répète que je suis dans la théorie des ensembles (tu noteras l’utilisation que je fais en l’occurrence des accolades dans mes posts).

Quand tu dis : Contrainte d'unicité => clause UNIQUE, cela vaut pour chaque clé candidate, par exemple la clé alternative {patientMatricule} de la table PATIENT, puisqu’elle identifie (alternativement) elle aussi chaque ligne de la table, alors pourquoi offrir la possibilité du choix NULL/NOT NULL pour ce type de clé ? En passant, je rappelle que le concept de clé primaire n’existe plus dans la théorie relationnelle (cf. ici ou là), Ockham est passé par là.

Envoyé par Paprick

En gros, une clé primaire est un index UNIQUE sur une colonne qui ne peut pas être NULL.

Non ! Je le rappelle encore, une clé primaire est un ensemble, au sens de la théorie des ensembles ! Un index c’est seulement de la mécanique pour booster les performance, on n’est pas au même niveau.

Envoyé par Paprick

Et là, j'ai pris la foudre de la part de DBAs qui m'ont expliqué ce que pouvait coûter en performance le fait de tester inutilement des contraintes. Même si je pense que les SGBD doivent s’accommoder aisément de ces redondances, j'ai dû reconnaître que c'était mal de spécifier ce qui n'a pas besoin de l'être ...

Tu me présenteras ces DBA, que je leur botte les fesses ! Je te rappelle que j’ai fait le DBA pendant trente ans et que pour ma part j’aurais évité de te rouler dans la farine !

A noter que la norme SQL ignore superbement les index (trouve-moi dans la norme l’instruction CREATE INDEX si chère aux DBA et qui m'a rendu de fieffés services). La norme n'a rien à voir avec la tuyauterie.

Supposons maintenant que la clé primaire d’une table change et porte désormais sur des colonnes NOT NULL. Il va falloir y virer ce NOT NULL, opération qui n’est pas transparente et l’ajouter aux colonnes composant l’ex clé primaire, l’orthogonalité et l’indépendance des concepts en prennent un coup. Pour mémoire, NULL a disparu depuis belle lurette de la théorie relationnelle, puisque la logique trivalente imposée de facto par NULL en a été exclue, mais, Sahib, ceci est une autre histoire.

**Paprick** · 27/02/2020, 09h56

Ah François, j'attendais avec gourmandise ta réaction à ma petite provocation !

Sache déjà que, sur un plan purement théorique, tu prêches un convaincu et que ton "ceinture, bretelles et épingles à nourrice" va dans le même sens que mon "sans va sans dire , mais ça va mieux en le disant"! Tu m'as déjà convaincu de l'intérêt des clés alternatives (qui verront le jour dans la version 2.6 de Looping), sachant que tu connais quand même mon attachement à Miss PK sans laquelle la définition des clés étrangères des tables associées seraient des plus délicates.

Néanmoins, j'ai un coté pragmatique qui parfois (voire... souvent

) prend le dessus : il faudra que je t'envoie mon bouquin pour que tu t'étouffes sur certaines de mes envolées pragmatiques

!
Concernant tous les SGBD que j'ai côtoyés depuis plus de 30 ans, y compris Access, tous sans exception interdisent le NULL dans les clés primaires : alors, dire que préciser NOT NULL est inutile ne me parait pas spécialement répréhensible (même si le préciser n'est pas bien grave non plus !).

Supposons maintenant que la clé primaire d’une table change et porte désormais sur des colonnes NOT NULL. Il va falloir y virer ce NOT NULL, opération qui n’est pas transparente et l’ajouter aux colonnes composant l’ex clé primaire, l’orthogonalité et l’indépendance des concepts en prennent un coup.

Dans ce cas, il n'est pas non plus nécessaire de virer le NOT NULL qui, même s'il ne sert à rien, ne fait pas de mal non plus ; et le rajouter aux rubriques qui ne l'ont pas parce que ex-clés primaires me parait être un tribu bien léger par rapport au bordel relationnel qu'un tel changement générera derrière !

Pour mémoire, NULL a disparu depuis belle lurette de la théorie relationnelle, puisque la logique trivalente imposée de facto par NULL en a été exclue, mais, Sahib, ceci est une autre histoire.

En fait, c'est là que nous touchons le fond du problème : je suis parti en guerre depuis bien longtemps contre cet aspect de la théorie relationnelle qui ne veut pas entendre parler de NULL.
Je sais que je vais faire saigner ton petit cœur de DBA et m'attirer les foudres de tous les grands théoriciens, mais la théorie du zéro NULL poussée à son paroxysme engendre de telles lourdeurs dans certains schémas relationnels que cela en devient parfois ridicule : cela conduit, par exemple, à faire un usage abusif de la notion d'héritage au seul prétexte que pour une famille d’entités donnée d'une classe, une rubrique n'est pas nécessaire ; cela conduit à imposer la création d'une table de correspondance dans le cas d'une association avec cardinalité 0,1 ... et les exemples sont légions où le refus systématique du NULL pour certaines rubriques conduit à un alourdissement significatif du schéma relationnel et, oui j'ose le dire, à une dégradation des performances. Cependant, je sais pertinemment que mon discours met à mal la logique binaire en engendrant une logique à trois états (true, false, null) qui peut mettre en difficulté certains SGBD.

Je sais que nous ne serons jamais d'accord sur ce point, mais je considère que la valeur NULL, bien qu’absente de la théorie relationnelle, n’est cependant pas une "malédiction", et il est également légitime de la préférer au rajout d’une table alourdissant la structure de données et les requêtes qui vont avec.

Voilà François, tu peux taper maintenant !!!

**fsmrel** · 28/02/2020, 03h59

Salve Paprick,

Envoyé par Paprick

la théorie du zéro NULL poussée à son paroxysme engendre de telles lourdeurs dans certains schémas relationnels que cela en devient parfois ridicule.

Vois ci-dessous le cas de la table VOYAGE. Cela dit, la théorie relationnelle est bâtie sur la logique du 1er ordre (Aristote) et la théorie des ensembles (Cantor), elle n’a donc rien à dire au sujet des schémas dits « relationnels » (la norme SQL non plus du reste !) Bien qu’habitant au Relationland, j’ai évidemment du mal à rester dans les hauteurs platoniciennes et je redescends sur terre, disons dans le Meriseland, en illustrant donc certaines structures au moyen de schémas jouant le rôle de champs opératoires (quelques tables). Quand la base de données comporte 1000 ou 2000 tables, j’urbanise en sous-modèles, sous-sous-modèles, de telle sorte que tout cela reste lisible. J’ai ainsi conservé un MLD occupant une cinquantaine de pages A3 : ce MLD a plus de 20 ans, mais je pense que je pourrais encore raconter le pourquoi de chaque table qu’il comporte.

Envoyé par Paprick

cela conduit, par exemple, à faire un usage abusif de la notion d'héritage au seul prétexte que pour une famille d’entités donnée d'une classe, une rubrique n'est pas nécessaire ; cela conduit à imposer la création d'une table de correspondance dans le cas d'une association avec cardinalité 0,1.

Certes ! Mais au besoin cela peut rester sous le capot, et les vues (qui ne sont jamais que des variables relationnelles virtuelles, donc manipulables comme des variables relationnelles de base) sont là pour permettre d’encapsuler les "abus".

Dans le schéma du post #35, la classe CONSULTATION_PAYANTE est en fait une sous-classe de CONSULTATION. De même, TRAITEMENT_PAYANT n’est jamais qu’une sous-classe de TRAITEMENT. On aurait pu en faire l’économie, sans que l’utilisateur trouve à redire, comme ici :

Nom : apprenant16_factures_idRel_Chemin_NULL.png
Affichages : 93
Taille : 20,4 Ko

Script correspondant (SQL Server) :

CREATE TABLE PATIENT
(
   patientId INT  IDENTITY(1,1),
   patientMatricule INT NOT NULL UNIQUE,
   patientNom VARCHAR(48) NOT NULL,
   CONSTRAINT PATIENT_PK PRIMARY KEY (patientId)
);

CREATE TABLE CONSULTATION
(
   patientId INT,
   consultationId INT IDENTITY (1,1),
   consultationDate DATE NOT NULL,
   CONSTRAINT CONSULTATION_PK PRIMARY KEY(patientId, consultationId),
   CONSTRAINT CONSULTATION_PATIENT_FK FOREIGN KEY(patientId) 
       REFERENCES PATIENT(patientId)
);

CREATE TABLE TRAITEMENT
(
   patientId INT,
   consultationId INT,
   traitementId INT IDENTITY (1,1),
   CONSTRAINT TRAITEMENT_PK PRIMARY KEY(patientId, consultationId, traitementId),
   CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) 
       REFERENCES CONSULTATION(patientId, consultationId)
);

CREATE TABLE INTERVENTION
(
   patientId INT,
   consultationId INT,
   traitementId INT,
   interventionId INT IDENTITY (1,1),
   interventionDate DATE NOT NULL,
   CONSTRAINT INTERVENTION_PK PRIMARY KEY(patientId, consultationId, traitementId, interventionId),
   CONSTRAINT INTERVENTION_TRAITEMENT_FK FOREIGN KEY(patientId, consultationId, traitementId) 
       REFERENCES TRAITEMENT(patientId, consultationId, traitementId)
);

CREATE TABLE FACTURE
(
   patientId_2 INT,
   factureId INT IDENTITY (1,1),
   factureNumero CHAR(8) NOT NULL UNIQUE,
   factureDate DATE NOT NULL,
   factureMontant INT NOT NULL,
   patientId INT,
   consultationId INT,
   traitementId INT,
   patientId_1 INT,
   consultationId_1 INT,
   CONSTRAINT FACTURE_PK PRIMARY KEY(patientId_2, factureId),
   CONSTRAINT FACTURE_PATIENT_2_FK FOREIGN KEY(patientId_2) 
       REFERENCES PATIENT(patientId),
   CONSTRAINT FACTURE_TRAITEMENT_FK FOREIGN KEY(patientId, consultationId, traitementId) 
       REFERENCES TRAITEMENT(patientId, consultationId, traitementId),
   CONSTRAINT FACTURE_CONSULTATION_1_FK FOREIGN KEY(patientId_1, consultationId_1) 
       REFERENCES CONSULTATION(patientId, consultationId)
);

Indépendamment du fait qu’une facture à régler par apprenant16, puisse sans problème faire référence à une consultation faite par fsmrel et à un traitement subi par Paprick, la structure de la table FACTURE est horriblement laide.

Et le bonhomme NULL vient ficher la zoubia.

Exemple :

Il n’existe pas dans la table PATIENT de ligne pour laquelle "patientid = 5", et pourtant l’insert qui suit ne pose aucun problème au SGBD (à moi, oui !) :

[

INSERT INTO FACTURE (patientId_2, factureId, factureNumero, factureDate, factureMontant, 
                     patientId, consultationId, traitementId, 
                     patientId_1, consultationId_1)
    VALUES (1, 2, '00000012', '2018-12-07', 110, 3, 3, 3, 5, NULL)
;

Si je regarde le contenu de la table :

SELECT patientId_2, factureNumero, patientId_1, consultationId_1 
FROM   FACTURE ;

alors que vois-je ?!

 
patientId_2   factureNumero   patientId_1   consultationId_1
1             00000011        2             2
1             00000012        5             NULL

Le bonhomme NULL a réussi à corrompre la base de données.

Envoyé par Paprick

oui j'ose le dire, à une dégradation des performances

Ce qui reste à démontrer ! Et j’ai passé hélas ! mon temps à démontrer le contraire. Exemple : on m’a demandé en 1991 (comme le temps passe...) d’expertiser une base de données dont la table la plus sensible, appelons-la VOYAGE, comportait les données relatives au transport de voyageurs, plus précisément des données portées par les titres de transport. Un titre de transport, ça peut correspondre à un voyage allant de Toulouse à Dunkerque, en passant par Cancale et Verdun. Mais ça peut être aussi un aller Paris Saint-Lazare-Pont-Cardinet, c’est-à-dire Paris intra-muros, ce qui quantitativement devient non négligeable. Surprise ! En consultant le contenu de la table à l’écran, je ne voyais que de la « neige », conséquence de l’omniprésence du bonhomme NULL. Et pourquoi tant de neige ? Ben, certaines colonnes de la table concernaient le passage par la Tarentaise (ça coûte plus cher au voyageur, car on passe par des ouvrages d’art), d’autres, les taxis grenoblois, encore d’autres, les allers-retours Mimizan-Ville, Mimizan-plage (ou quelque chose comme ça...), j’en passe et des meilleures. Le défi était de ne plus voir un seul NULL, bref de faire suivre une sévère cure d’amaigrissement à la table VOYAGE obèse. Une petite table pour la Tarentaise, une autre pour les taxis grenoblois, etc., plein de petites tables, très peu volumineuses. A-t-on dégradé la performance ? Le temps de traitement quotidien alimentant la table (et ses quelques copines) a été divisé par 30 (ramené à 8 heures)...

Envoyé par Paprick

je sais pertinemment que mon discours met à mal la logique binaire en engendrant une logique à trois états (true, false, null)

Je ne m’inquiète pas, tu sais bien que la logique binaire date d’Aristote, elle a à peu près 2380 ans, on peut donc être rassuré à son sujet. Quant à la logique trivalente, tout dépend des valeurs (de vérité) prises par le trio quand on utilise les connecteurs NON, ET, OU, ainsi que pour les tests d’égalité et apparentés.

En passant, je rappelle que NULL n’est pas une valeur, mais un marqueur.

Pour leur part, les auteurs de la norme SQL sont tombés dans le panneau en voulant rendre équivalents UNKNOWN (qui est une valeur en logique trivalente, (true, false, unknown)) et NULL (qui n’en est pas une !), aussi le rouge de la confusion a-t-il dû envahir leurs joues par la suite...

Envoyé par Paprick

il faudra que je t'envoie mon bouquin pour que tu t'étouffes sur certaines de mes envolées pragmatiques

Je m’étoufferai avec plaisir et ne manquerai pas de commenter...

Au plaisir.

**fsmrel** · 28/02/2020, 04h01

apprenant16, comment les choses se passent-elles de votre côté ?

**apprenant16** · 01/03/2020, 02h04

Bonsoir fsmrel,

Désolé, une mission qui m'avait un peu éloigné

Merci pour tout. Voici le script mis à jour et exécuté avec succès

, est-ce qu'il y a d'autres corrections à faire :

Code :

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
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
58
59
60
61
62
63
64
65
66
67
68
Option Compare Database
Option Explicit

Sub Create_Tables()

DoCmd.RunSQL "CREATE TABLE PATIENT(" & _
   "patientId COUNTER NOT NULL," & _
   "patientMatricule INT NOT NULL UNIQUE," & _
   "patientNom VARCHAR(48) NOT NULL," & _
   "CONSTRAINT PATIENT_PK PRIMARY KEY(patientId)" & _
");"

DoCmd.RunSQL "CREATE TABLE FACTURE(" & _
   "patientId INT NOT NULL," & _
   "factureId COUNTER NOT NULL," & _
   "factureNumero CHAR(8) NOT NULL UNIQUE," & _
   "factureDate DATE NOT NULL," & _
   "factureMontant INT NOT NULL," & _
   "CONSTRAINT FACTURE_PK PRIMARY KEY(patientId, factureId)," & _
   "CONSTRAINT FACTURE_PATIENT_FK FOREIGN KEY(patientId) REFERENCES PATIENT(patientId)" & _
");"

DoCmd.RunSQL "CREATE TABLE CONSULTATION(" & _
   "patientId INT NOT NULL," & _
   "consultationId COUNTER NOT NULL," & _
   "consultationDate DATE NOT NULL," & _
   "CONSTRAINT CONSULTATION_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PATIENT_FK FOREIGN KEY(patientId) REFERENCES PATIENT(patientId)" & _
");"

DoCmd.RunSQL "CREATE TABLE CONSULTATION_PAYANTE(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_1_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"

DoCmd.RunSQL "CREATE TABLE TRAITEMENT(" & _
   "patientId INT NOT NULL," & _
   "consultationId INT NOT NULL," & _
   "traitementId COUNTER NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PK PRIMARY KEY(patientId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)" & _
");"

DoCmd.RunSQL "CREATE TABLE TRAITEMENT_PAYANT(" & _
   "patientId INT NOT NULL," & _
   "traitementId INT NOT NULL," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT TRAITEMENT_PAYANT_PK PRIMARY KEY(patientId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_TRAITEMENT_1_FK FOREIGN KEY(patientId, traitementId) REFERENCES TRAITEMENT(patientId, traitementId)," & _
   "CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"


DoCmd.RunSQL "CREATE TABLE INTERVENTION(" & _
   "patientId INT NOT NULL," & _
   "traitementId INT NOT NULL," & _
   "interventionId COUNTER NOT NULL," & _
   "interventionDate DATE NOT NULL," & _
   "CONSTRAINT INTERVENTION_PK PRIMARY KEY(patientId, interventionId)," & _
   "CONSTRAINT INTERVENTION_TRAITEMENT_FK FOREIGN KEY(patientId, traitementId) REFERENCES TRAITEMENT(patientId, traitementId)" & _
");"


End Sub

**Paprick** · 01/03/2020, 16h55

Bonjour,
Je suis un peu gêné par les identifiants composés que vous avez choisis pour certaines tables.
Par exemple, FACTURE est composée de patientId et factureId, patientId étant un identifiant relatif venant de PATIENT.
Je sais que François (fsmrel) aime bien faire circuler les clés à travers cette notion d'identifiant relatif, car il y trouve de nombreuses vertus, notamment en termes d'optimisation de requêtes.
Cela me pose néanmoins un problème de fond : en effet, une des qualités que doit posséder une clé primaire, c'est l'irréductibilité, c'est à dire le fait que la clé soit minimale. Or, alors que factureNumero était également un candidat acceptable (mais ça, c'est un autre débat que j'ai avec François !!!

), le choix de factureId doit se suffire à lui-même, surtout avec un type COUNTER qui garantit l'unicité.
Donc, si factureId est une clé candidate, une clé avec le couple {patientId, factureId} n'est forcément pas minimale... ce qui est théoriquement rédhibitoire.

Pour François => pour revenir sur nos histoires de NULL, je pense que tout est une question de mesure : abuser de rubriques NULL pour éviter de spécifier certaines classes d'entités clairement identifiées dans le SI, est bien sûr une erreur. Mais, ériger en règle d'or le fait de ne jamais en faire usage est tout aussi malvenu. En fait, ça reste le rôle du concepteur de faire les bons choix ! Une chose est sure, et je pense que nous nous rejoindrons sur ce point, concevoir un MCD peu raisonnable (dans un sens ou dans l'autre) en se disant qu'il faudra corriger le tir au niveau du schéma relationnel est une très mauvaise approche : pour moi le schéma relationnel doit être le reflet du MCD (d'où toutes les possibilités de codage SQL que nous avons prévues dans Looping ) et se décharger sur les DBA de ce type de problématique conduit systématiquement les concepteurs à négliger la réflexion conceptuelle, et à réaliser les MLD sans passer par la case MCD... et ça c'est pas bien !!!

Bref, entre théorie, normes et pragmatisme, il faut savoir trouver son chemin ! Certes, il est important de respecter les règles... tout en sachant parfois les transgresser... mais toujours en connaissance de cause !

(François, envoie-moi ton adresse postale en MP pour que je te fasse parvenir mon bouquin )

**fsmrel** · 02/03/2020, 00h59

Salve omnes,

Envoyé par Paprick

Je suis un peu gêné par les identifiants composés que vous avez choisis pour certaines tables.

apprenant16 a suivi mes conseils, et il a bien fait.

Envoyé par Paprick

une des qualités que doit posséder une clé primaire, c'est l'irréductibilité, c'est à dire le fait que la clé soit minimale. Or, alors que factureNumero était également un candidat acceptable (mais ça, c'est un autre débat que j'ai avec François !!! ), le choix de factureId doit se suffire à lui-même, surtout avec un type COUNTER qui garantit l'unicité.
Donc, si factureId est une clé candidate, une clé avec le couple {patientId, factureId} n'est forcément pas minimale... ce qui est théoriquement rédhibitoire.

Si ACCESS fournissait des COUNTER relatifs, on s’en servirait ici ! Déments-moi, mais je n’ai pas l’impression qu’avec ACCESS ça soit le cas. On va donc faire jouer au COUNTER « absolu » le rôle de COUNTER « relatif ». On part du principe qu’on ne sait pas ce qui se passe sous le capot et au lieu de voir ça :

FACTURE {patientId    factureId    factureNumero    factureDate    factureMontant}
         1            1            314116           01/04/2018     110
         1            2            314133           05/05/2018     124
         1            3            314212           12/09/2018     131
         2            1            314105           02/03/2018     210
         2            2            314130           01/05/2018     228
         2            3            314170           14/07/2018     235

Ben on voit plutôt ça :

FACTURE {patientId    factureId    factureNumero    factureDate    factureMontant}
         1            199          314116           01/04/2018     110
         1            206          314133           05/05/2018     124
         1            305          314212           12/09/2018     131
         2            102          314105           02/03/2018     210
         2            204          314130           01/05/2018     228
         2            293          314170           14/07/2018     235

Subtilité : si grâce à son gnagnascope le DBA constate factuellement une dépendance fonctionnelle {factureId} → {factureNumero}, ça n’est pour autant que cette DF existe fonctionnellement ! Au lieu de voir des répétitions (1, 2, 3, ...) on n’en voit pas, la belle affaire ! Le COUNTER c’est juste de la mécanique, une facilité technique pour créer des valeurs uniques, mais ça n’a pas à influencer la théorie de la normalisation qui joue dans une autre cour.

Cela dit, on pourrait aussi bien déclarer {factureId} comme clé primaire et la paire {patientId, factureId} comme surclé, référencée par les clés étrangères, mais franchement qu’en tirerait-on de plus ?

L’important dans tout ça c’est de propager l’attribut patientId, pour résoudre les problèmes fonctionnels (contraintes de chemin) et de performance (clustering des tables sur cet attribut).

Ça fait belle lurette que j’utilise ce procédé, et personne ne s’est jamais risqué à me démontrer que j’avais tort...

Envoyé par Paprick

Donc, si factureId est une clé candidate, une clé avec le couple {patientId, factureId} n'est forcément pas minimale... ce qui est théoriquement rédhibitoire.

Du point de vue de la théorie c’est évident. Mais dans notre cas, {factureId} n’est pas une clé candidate, même si un passage au gnagnascope montre des valeurs uniques au lieu de doublons ! Ah ! Ce fichu hasard fait quand même bien mal les choses...

Envoyé par Paprick

pour revenir sur nos histoires de NULL

Personnellement je code toujours NOT NULL, et le préconise mais bien sûr sans interdire des NULL occasionnels. Le défi est de ne pas laisser filer, ne pas être laxiste, bref, ne pas faire l’éloge de la paresse...

**Paprick** · 02/03/2020, 02h07

Salut François,

Envoyé par fsmrel

Si ACCESS fournissait des COUNTER relatifs, on s’en servirait ici ! Déments-moi, mais je n’ai pas l’impression qu’avec ACCESS ça soit le cas.

Ce n'est effectivement pas le cas pour Access, et j'ai des doutes sur le fait que ce soit le cas pour beaucoup de SGBD avec les AUTO INCREMENT ou autre SERIAL...
Par ailleurs, rajouter une rubrique qui n'appartient pas au SI pour en faire un identifiant qui n'est que relatif...

... c'est bien un truc de DBA ça !

L’important dans tout ça c’est de propager l’attribut patientId, pour résoudre les problèmes fonctionnels (contraintes de chemin) et de performance (clustering des tables sur cet attribut). Ça fait belle lurette que j’utilise ce procédé, et personne ne s’est jamais risqué à me démontrer que j’avais tort...

Tu es parfait dans ton rôle de DBA

! Mais sur le plan modélisation conceptuelle du SI, cela me pose problème, au même titre que cette règle qui consiste à ne jamais utiliser de rubriques significatives pour les identifiants, même s'il est démontré qu'elles sont pérennes (faisant fi au passage des dates et autres numéros d'ordre qui échappent miraculeusement à la règle

).
En fait, pour être consensuel, je dirais que ton approche à un grand intérêt pour de grosses BD où les problèmes fonctionnels et de performance auxquels tu fais référence ont un impact considérable. Mais pour les BD plus modestes, je continue de penser que la simplicité du MCD reste une priorité.

Personnellement je code toujours NOT NULL, et le préconise mais bien sûr sans interdire des NULL occasionnels. Le défi est de ne pas laisser filer, ne pas être laxiste, bref, ne pas faire l’éloge de la paresse...

Nous sommes bien d'accord, le choix ne doit pas relever de la paresse, mais bien au contraire d'une réflexion conceptuelle approfondie et bien menée en rapport avec le système d'information à modéliser.

**apprenant16** · 02/03/2020, 02h11

Bonsoir à tous, belle discussion

@ fsmrel : vous pensez quoi de mon précédent post avec le script ? Il y aurait des choses à revoir ?

Merci encore pour l'aide apportée

**fsmrel** · 02/03/2020, 03h49

Bonsoir à nouveau,

apprenant16, le script m’a tout l’air d’être parfaitement correct. Reste à secouer la base de données !
Si problème, n’hésitez pas à vous manifester.

En passant, n’hésitez pas à liker les duettistes.

Difficultés matérialisation relation entre trois entités [MLD]

Schéma

Discussions similaires

Partager

Partager