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

**apprenant16** · 30/12/2019, 02h15

Bonjour fsmrel, et merci pour ce retour très détaillé et clair.

Pour le stockage par défaut, effectivement c'était à MyISAM

. J'ai modifié pour mettre InnoDB par défaut.

Maintenant pour ce qui est de la numérotation automatique, je réfléchissais comme ci j'étais en access pour laisser la BD gérer l'unicité des enregistrement. Cependant avec vos explications, je peux laisser ainsi et continuer sur le modèle sans Numéro automatique.

Toutefois, je vais saisir manuellement ces données lorsque je vais créer un patient, une facture etc. ?

Vous pensez que c'est bon comme départ, si oui, je vais mettre le post en résolu. Vous m'avez bien aider à la modélisation de cette base de données

**fsmrel** · 30/12/2019, 15h25

Bonsoir,

Envoyé par apprenant16

Y a pas de quoi !

Vous souhaitez ne pas vous servir de la numérotation automatique : pas de problème. Bien entendu, en contrepartie c’est à vous de fournir les valeurs pour les colonnes participant aux clés primaires (et reprises par les clés étrangères) ; considérez ces valeurs comme sans signification, donc invariantes, sinon vous vous compliqueriez singulièrement la vie pour être en phase avec le contrôle de l’intégrité référentielle par le SGBD (quel que soit celui-ci, il ne vous raterait pas...) Bien des projets sont morts pour avoir oublié cela...

En passant, n’oubliez pas de voter pour les réponses qui ont pu vous apporter une aide ou vous conforter dans vos choix.

Alea jacta est !

**apprenant16** · 30/12/2019, 16h24

Bonjour,

Ok et si je choisis d'utiliser les clés primaires, je dois modifier mon script pour adapter en fonction des derniers scripts que vous avez postés. Peut-être que c'est la meilleur solution, ainsi je ne me soucierai pas de renseigner moi-même ces champs auto.
Vous confirmez ?

En passant, n’oubliez pas de voter pour les réponses qui ont pu vous apporter une aide ou vous conforter dans vos choix.

Je l'ai fait pour toutes vos réponses qui à chaque fois sont pertinentes et très utiles.

**fsmrel** · 30/12/2019, 17h09

Envoyé par apprenant16

si je choisis d'utiliser les clés primaires, je dois modifier mon script pour adapter en fonction des derniers scripts que vous avez postés. Peut-être que c'est la meilleur solution, ainsi je ne me soucierai pas de renseigner moi-même ces champs auto.
Vous confirmez ?

Je ne sais pas interpréter ce que vous avancez... Pour vous, « choisir les clés primaires » est-il synonyme de « pas d’auto-incrémentation » ?

En attendant, je joins un petit exemple pour les inserts, sur la base de mon script du post #13 (pas d’auto-incrémentation).

Création des patients Fernand Naudin, Raoul Volfoni, Paul Volfoni, Maître Folace :

INSERT INTO PATIENT (patientId, patientMatricule, patientNom)
VALUES
    (2018001, 45, 'Fernand Naudin'), (2018012, 58, 'Raoul Volfoni'), (2019001, 143, 'Paul Volfoni')
  , (2018024, 112, 'Me Folace')    
;

Création de factures pour ces messieurs :

INSERT INTO FACTURE (patientId, factureId, factureNumero, factureDate, factureMontant)
VALUES
    (2018001, 1, '00000011', '2018-12-07', 100), (2018001, 2, '00000017', '2018-12-08', 100)
  , (2018001, 3, '00000094', '2019-01-09', 200)
  , (2018012, 1, '00000015', '2018-12-08', 200), (2018012, 2, '00000195', '2019-08-08', 200)
  , (2019001, 1, '00000202', '2019-02-06', 200)    
  , (2018024, 1, '00000203', '2019-02-06', 200)    
;

Une variante, où les seules valeurs utilisées sont celles des propriétés naturelles, création par exemple d’une facture supplémentaire pour M. Fernand Naudin :

INSERT INTO FACTURE (patientId, factureId, factureNumero, factureDate, factureMontant)
    (SELECT (SELECT patientId FROM PATIENT WHERE patientMatricule = 45), 4, '00000204', '2019-02-06', 100)
;

Création d’une consultation pour M. Fernand Naudin (utilisation des propriétés naturelles) :

INSERT INTO CONSULTATION (patientId, consultationId, consultationDate)
    SELECT (SELECT patientId FROM PATIENT WHERE patientMatricule = 45), 1, '2018-12-07'
;

Cette consultation pour M. Fernand Naudin est payante (utilisation des propriétés naturelles) :

INSERT INTO CONSULTATION_PAYANTE (patientId, consultationId, factureId)
            SELECT x.patientId, y.consultationId, z.factureId 
            FROM   PATIENT as x 
             JOIN  CONSULTATION as y ON x.patientId = y.patientId
             JOIN  FACTURE as z ON x.patientId = z.patientId
            WHERE  patientMatricule = 45
               AND consultationDate = '2018-12-07'
               AND factureNumero = '00000011'
;

Bien entendu, vous pouvez préférer utiliser « en dur » les valeurs des propriétés « artificielles » :

 INSERT INTO CONSULTATION_PAYANTE (patientId, consultationId, factureId)
            SELECT 2018001, 1, 1  
;

Au besoin, affichez votre propre script.

Merci pour les votes !

**apprenant16** · 30/12/2019, 17h20

Pour vous, « choisir les clés primaires » est-il synonyme de « pas d’auto-incrémentation » ?

Exactement cela. Je choisirai les id de façon aléatoire, surtout qu'il n'y a pas possibilité d'avoir des doublons.

Sinon, pour tes exemple, c'est très clair je pense.

Pour le script, je vais compléter les différentes tables ensuite je regénère le script et vous montre.

**apprenant16** · 30/12/2019, 18h38

Envoyé par apprenant16

Pour le stockage par défaut, effectivement c'était à MyISAM

. J'ai modifié pour mettre InnoDB par défaut.

Et voici le rendu sur PhpMyAdmin

Nom : Sans titre.png
Affichages : 361
Taille : 166,4 Ko

**fsmrel** · 31/12/2019, 15h54

Bonjour,

A propos de votre diagramme

Votre diagramme s’améliore ! Il ne semble pas être contradictoire avec le script du post #13, toutefois je suis surpris que (au moins visuellement) la clé primaire de la table FACTURE soit le triplet {patientId, factureId, factureNumero} et non pas la paire {patientId, factureId}...

Je ne sais pas interpréter les couleurs des associations entre les tables ni interpréter les mickeys terminaux qu’elles portent, je ne peux donc pas me prononcer sur la validité sémantique des clés étrangères (cardinalités min, max). Quel logiciel avez-vous utilisé pour cette représentation graphique ? Si je voulais le tester, quels seraient les prérequis ? Installer PhpMyAdmin ?

A propos du type INT(11)

Au paragraphe 11.2.5, Numeric Type Attributes du manuel « MySQL 5.7 Reference Manual », on lit ceci :

« MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. »

Donc le nombre 11 n’a pas d’incidence sur le contenu des colonnes, ça n’est qu’à l’affichage des valeurs que seraient présents 11 chiffres. C’est bien cela ? (jusqu’à 99999999999 factures, ça représente un très très très important cabinet dentaire

)

**apprenant16** · 31/12/2019, 16h07

Bonjour,

Envoyé par fsmrel

Bonjour,

Votre diagramme s’améliore ! Il ne semble pas être contradictoire avec le script du post #13, toutefois je suis surpris que (au moins visuellement) la clé primaire de la table FACTURE soit le triplet {patientId, factureId, factureNumero} et non pas la paire {patientId, factureId}...

Pourtant c'est le script que j'ai exécuté directement dans PhpMyAdmin, cela sans aucune modification. Peut-être quelque chose a échappé

Envoyé par fsmrel

Je ne sais pas interpréter les couleurs des associations entre les tables ni interpréter les mickeys terminaux qu’elles portent, je ne peux donc pas me prononcer sur la validité sémantique des clés étrangères (cardinalités min, max). Quel logiciel avez-vous utilisé pour cette représentation graphique ? Si je voulais le tester, quels seraient les prérequis ? Installer PhpMyAdmin ?

Juste intaller WampServer qui intgègre MySQL et PhpMyAdmin. Ensuite exécuter le script #13.

Envoyé par fsmrel

A propos du type INT(11)

Au paragraphe 11.2.5, Numeric Type Attributes du manuel « MySQL 5.7 Reference Manual », on lit ceci :

« MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. »

Donc le nombre 11 n’a pas d’incidence sur le contenu des colonnes, ça n’est qu’à l’affichage des valeurs que seraient présents 11 chiffres. C’est bien cela ? (jusqu’à 99999999999 factures, ça représente un très très très important cabinet dentaire

)

Le INT(11) est venu sûrement par défaut et c'est vraiment excessif

On peut modifier en INT(4) ? cela serait sûrement mieux.

**fsmrel** · 31/12/2019, 17h30

Envoyé par apprenant16

Juste installer WampServer qui intègre MySQL et PhpMyAdmin.

Heu... D’après ce que je vois, WampServer installerait plein de logiciels qui me sont totalement inconnus (Apache, etc.) Ça ne me paraît pas bien raisonnable alors que je cherche simplement à interpréter un diagramme…

Envoyé par apprenant16

On peut modifier en INT(4) ? cela serait sûrement mieux.

Si ça ne concerne bien que l’affichage, vous seriez limité à 9999 factures : dans ces conditions, passer prudemment à INT(5), 100000 factures pour un cabinet dentaire c’est déjà pas mal !

**apprenant16** · 05/01/2020, 03h51

Bonsoir fmrel,

Bonne et heureuse année 2020 ; Mes meilleurs vœux.

Envoyé par fsmrel

Heu... D’après ce que je vois, WampServer installerait plein de logiciels qui me sont totalement inconnus (Apache, etc.) Ça ne me paraît pas bien raisonnable alors que je cherche simplement à interpréter un diagramme…

Oui c'est le cas

Envoyé par fsmrel

Si ça ne concerne bien que l’affichage, vous seriez limité à 9999 factures : dans ces conditions, passer prudemment à INT(5), 100000 factures pour un cabinet dentaire c’est déjà pas mal !

Bien noté, je passe en INT(5) par précaution.

Je vais mettre mon modèle à jour et vous soumets une version nouvelle.

**apprenant16** · 18/01/2020, 23h06

Salut fsmrel,

Je crois que je vais opter pour les clés primaires AUTO INCREMENT.
J'ai mis à jour le modèle, et vos corrections sur le nouveau script sont les bienvenues.

Bonne soirée et merci

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
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
-- MySQL Script generated by MySQL Workbench
-- Sat Jan 18 21:39:53 2020
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering
 
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
 
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
 
-- -----------------------------------------------------
-- Table `PATIENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PATIENT` (
  `patientId` INT NOT NULL AUTO_INCREMENT,
  `patientMatricule` INT NOT NULL,
  `patientNom` VARCHAR(48) NOT NULL,
  `patientPrenom` VARCHAR(48) NOT NULL,
  `patientDateNaissance` DATE NOT NULL,
  `patientAdresse` VARCHAR(48) NOT NULL,
  `patientProfession` VARCHAR(45) NOT NULL,
  `patientNumTelephone` VARCHAR(45) NULL,
  `patientEmail` VARCHAR(45) NULL,
  PRIMARY KEY (`patientId`),
  UNIQUE INDEX `PATIENT_MAT_AK` (`patientMatricule` ASC) VISIBLE);
 
 
-- -----------------------------------------------------
-- Table `FACTURE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FACTURE` (
  `patientId` INT NOT NULL,
  `factureId` INT(5) NOT NULL,
  `factureNumero` CHAR(8) NOT NULL,
  `factureDate` DATE NOT NULL,
  `factureMontant` INT NOT NULL,
  PRIMARY KEY (`patientId`, `factureId`),
  UNIQUE INDEX `FACTURE_NUMERO_AK` (`factureNumero` ASC) VISIBLE,
  CONSTRAINT `FACTURE_PATIENT_FK`
    FOREIGN KEY (`patientId`)
    REFERENCES `PATIENT` (`patientId`));
 
 
-- -----------------------------------------------------
-- Table `CONSULTATION`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CONSULTATION` (
  `patientId` INT NOT NULL,
  `consultationId` INT NOT NULL AUTO_INCREMENT,
  `consultationDate` DATE NOT NULL,
  PRIMARY KEY (`patientId`, `consultationId`),
  UNIQUE INDEX `CONSULTATION_DATE_AK` (`patientId` ASC, `consultationDate` ASC) VISIBLE,
  CONSTRAINT `CONSULTATION_PATIENT_FK`
    FOREIGN KEY (`patientId`)
    REFERENCES `PATIENT` (`patientId`));
 
 
-- -----------------------------------------------------
-- Table `CONSULTATION_PAYANTE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CONSULTATION_PAYANTE` (
  `patientId` INT NOT NULL,
  `consultationId` INT NOT NULL,
  `factureId` INT NOT NULL,
  PRIMARY KEY (`patientId`, `consultationId`),
  UNIQUE INDEX `CONSULTATION_PAYANTE_FACTURE_AK` (`patientId` ASC, `factureId` ASC) VISIBLE,
  CONSTRAINT `CONSULTATION_PAYANTE_CONSULTATION_FK`
    FOREIGN KEY (`patientId` , `consultationId`)
    REFERENCES `CONSULTATION` (`patientId` , `consultationId`)
    ON DELETE CASCADE,
  CONSTRAINT `CONSULTATION_PAYANTE_FACTURE_FK`
    FOREIGN KEY (`patientId` , `factureId`)
    REFERENCES `FACTURE` (`patientId` , `factureId`));
 
 
-- -----------------------------------------------------
-- Table `TRAITEMENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRAITEMENT` (
  `patientId` INT NOT NULL,
  `consultationId` INT NOT NULL,
  `traitementId` INT NOT NULL AUTO_INCREMENT,
  `traitementDescription` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`patientId`, `traitementId`),
  INDEX `TRAITEMENT_CONSULTATION_FK` (`patientId` ASC, `consultationId` ASC) VISIBLE,
  CONSTRAINT `TRAITEMENT_CONSULTATION_FK`
    FOREIGN KEY (`patientId` , `consultationId`)
    REFERENCES `CONSULTATION` (`patientId` , `consultationId`));
 
 
-- -----------------------------------------------------
-- Table `TRAITEMENT_PAYANT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRAITEMENT_PAYANT` (
  `patientId` INT NOT NULL,
  `traitementId` INT NOT NULL,
  `factureId` INT NOT NULL,
  PRIMARY KEY (`patientId`, `traitementId`),
  UNIQUE INDEX `TRAITEMENT_PAYANT_FACTURE_AK` (`patientId` ASC, `factureId` ASC) VISIBLE,
  CONSTRAINT `TRAITEMENT_PAYANT_TRAITEMENT_FK`
    FOREIGN KEY (`patientId` , `traitementId`)
    REFERENCES `TRAITEMENT` (`patientId` , `traitementId`)
    ON DELETE CASCADE,
  CONSTRAINT `TRAITEMENT_PAYANT_FACTURE_FK`
    FOREIGN KEY (`patientId` , `factureId`)
    REFERENCES `FACTURE` (`patientId` , `factureId`));
 
 
-- -----------------------------------------------------
-- Table `INTERVENTION`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `INTERVENTION` (
  `patientId` INT NOT NULL,
  `interventionId` INT NOT NULL AUTO_INCREMENT,
  `traitementId` INT NOT NULL,
  `interventionDate` DATE NOT NULL,
  `interventionDescription` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`patientId`, `interventionId`),
  UNIQUE INDEX `INTERVENTION_DATE_AK` (`patientId` ASC, `interventionDate` ASC) VISIBLE,
  INDEX `INTERVENTION_TRAITEMENT_FK` (`patientId` ASC, `traitementId` ASC) VISIBLE,
  CONSTRAINT `INTERVENTION_TRAITEMENT_FK`
    FOREIGN KEY (`patientId` , `traitementId`)
    REFERENCES `TRAITEMENT` (`patientId` , `traitementId`));
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Nom : Capture.PNG
Affichages : 331
Taille : 152,1 Ko

Avec le script, lorsque je le met dans l'éditeur pour l'éxécuter, je vois des alerte message d'erreur comme le montre l'image (il s'agit visiblement des index

) :

**apprenant16** · 19/01/2020, 02h00

la réponse de aieeeuuuuu dans ce fil répond à ma question sur le problème de l'erreur rencontré. Merci à lui

Voici le nouveau script corrigé (sans le mot clé VISIBLE) :

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
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
-- -----------------------------------------------------
-- Table `PATIENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PATIENT` (
  `patientId` INT NOT NULL AUTO_INCREMENT,
  `patientMatricule` INT NOT NULL,
  `patientNom` VARCHAR(48) NOT NULL,
  `patientPrenom` VARCHAR(48) NOT NULL,
  `patientDateNaissance` DATE NOT NULL,
  `patientAdresse` VARCHAR(48) NOT NULL,
  `patientProfession` VARCHAR(45) NOT NULL,
  `patientNumTelephone` VARCHAR(45) NULL,
  `patientEmail` VARCHAR(45) NULL,
  PRIMARY KEY (`patientId`),
  UNIQUE INDEX `PATIENT_MAT_AK` (`patientMatricule` ASC));
 
 
-- -----------------------------------------------------
-- Table `FACTURE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FACTURE` (
  `patientId` INT NOT NULL,
  `factureId` INT(5) NOT NULL,
  `factureNumero` CHAR(8) NOT NULL,
  `factureDate` DATE NOT NULL,
  `factureMontant` INT NOT NULL,
  PRIMARY KEY (`patientId`, `factureId`),
  UNIQUE INDEX `FACTURE_NUMERO_AK` (`factureNumero` ASC),
  CONSTRAINT `FACTURE_PATIENT_FK`
    FOREIGN KEY (`patientId`)
    REFERENCES `PATIENT` (`patientId`));
 
 
-- -----------------------------------------------------
-- Table `CONSULTATION`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CONSULTATION` (
  `patientId` INT NOT NULL,
  `consultationId` INT NOT NULL AUTO_INCREMENT,
  `consultationDate` DATE NOT NULL,
  PRIMARY KEY (`patientId`, `consultationId`),
  UNIQUE INDEX `CONSULTATION_DATE_AK` (`patientId` ASC, `consultationDate` ASC),
  CONSTRAINT `CONSULTATION_PATIENT_FK`
    FOREIGN KEY (`patientId`)
    REFERENCES `PATIENT` (`patientId`));
 
 
-- -----------------------------------------------------
-- Table `CONSULTATION_PAYANTE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CONSULTATION_PAYANTE` (
  `patientId` INT NOT NULL,
  `consultationId` INT NOT NULL,
  `factureId` INT NOT NULL,
  PRIMARY KEY (`patientId`, `consultationId`),
  UNIQUE INDEX `CONSULTATION_PAYANTE_FACTURE_AK` (`patientId` ASC, `factureId` ASC),
  CONSTRAINT `CONSULTATION_PAYANTE_CONSULTATION_FK`
    FOREIGN KEY (`patientId` , `consultationId`)
    REFERENCES `CONSULTATION` (`patientId` , `consultationId`)
    ON DELETE CASCADE,
  CONSTRAINT `CONSULTATION_PAYANTE_FACTURE_FK`
    FOREIGN KEY (`patientId` , `factureId`)
    REFERENCES `FACTURE` (`patientId` , `factureId`));
 
 
-- -----------------------------------------------------
-- Table `TRAITEMENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRAITEMENT` (
  `patientId` INT NOT NULL,
  `consultationId` INT NOT NULL,
  `traitementId` INT NOT NULL AUTO_INCREMENT,
  `traitementDescription` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`patientId`, `traitementId`),
  INDEX `TRAITEMENT_CONSULTATION_FK` (`patientId` ASC, `consultationId` ASC),
  CONSTRAINT `TRAITEMENT_CONSULTATION_FK`
    FOREIGN KEY (`patientId` , `consultationId`)
    REFERENCES `CONSULTATION` (`patientId` , `consultationId`));
 
 
-- -----------------------------------------------------
-- Table `TRAITEMENT_PAYANT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TRAITEMENT_PAYANT` (
  `patientId` INT NOT NULL,
  `traitementId` INT NOT NULL,
  `factureId` INT NOT NULL,
  PRIMARY KEY (`patientId`, `traitementId`),
  UNIQUE INDEX `TRAITEMENT_PAYANT_FACTURE_AK` (`patientId` ASC, `factureId` ASC),
  CONSTRAINT `TRAITEMENT_PAYANT_TRAITEMENT_FK`
    FOREIGN KEY (`patientId` , `traitementId`)
    REFERENCES `TRAITEMENT` (`patientId` , `traitementId`)
    ON DELETE CASCADE,
  CONSTRAINT `TRAITEMENT_PAYANT_FACTURE_FK`
    FOREIGN KEY (`patientId` , `factureId`)
    REFERENCES `FACTURE` (`patientId` , `factureId`));
 
 
-- -----------------------------------------------------
-- Table `INTERVENTION`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `INTERVENTION` (
  `patientId` INT NOT NULL,
  `interventionId` INT NOT NULL AUTO_INCREMENT,
  `traitementId` INT NOT NULL,
  `interventionDate` DATE NOT NULL,
  `interventionDescription` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`patientId`, `interventionId`),
  UNIQUE INDEX `INTERVENTION_DATE_AK` (`patientId` ASC, `interventionDate` ASC),
  INDEX `INTERVENTION_TRAITEMENT_FK` (`patientId` ASC, `traitementId` ASC),
  CONSTRAINT `INTERVENTION_TRAITEMENT_FK`
    FOREIGN KEY (`patientId` , `traitementId`)
    REFERENCES `TRAITEMENT` (`patientId` , `traitementId`));
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

l'exécution donne le message suivant :

Nom : Sans titre1.png
Affichages : 415
Taille : 23,4 Ko

**fsmrel** · 19/01/2020, 14h28

Bonjour apprenant16,

Concernant votre erreur #1075 :

De façon maladroite, plutôt sibylline, MySQL réclame au niveau physique un index pour la colonne auto-incrémentée. L’index n’est en fait pas à déclarer tel que, il suffit de façon bien plus propre de faire ce que j’ai fait dans le post #20, à savoir déclarer une clé alternative (et sous le capot MySQL se générera son index) :

CONSTRAINT CONSULTATION_ID_AK UNIQUE (consultationId)

Je reprends ici le CREATE TABLE du post #20 :

CREATE TABLE CONSULTATION
(
        patientId            INT             NOT NULL
      , consultationId       INT             NOT NULL AUTO_INCREMENT
      , consultationDate     DATE            NOT NULL
    , CONSTRAINT CONSULTATION_PK PRIMARY KEY (patientId, consultationId)
    , CONSTRAINT CONSULTATION_ID_AK UNIQUE (consultationId)  
    , CONSTRAINT CONSULTATION_DATE_AK UNIQUE (patientId, consultationDate)
    , CONSTRAINT CONSULTATION_PATIENT_FK FOREIGN KEY (patientId)
          REFERENCES PATIENT (patientId)
) ;

De façon générale, ne mélangeons pas le niveau physique (les index) et le niveau logique (les clés).

Certes, ceci fonctionne avec MySQL, mais c’est très laid. :

UNIQUE INDEX CONSULTATION_DATE_AK (patientId, consultationDate)

Préférez :

CONSTRAINT CONSULTATION_ID_AK UNIQUE (consultationId)

Même chose pour toutes les tables.

Revoyez le post #20. Je reprends et complète le code qui y figure :

DROP TABLE IF EXISTS INTERVENTION ;
DROP TABLE IF EXISTS TRAITEMENT_PAYANT ;
DROP TABLE IF EXISTS TRAITEMENT ;
DROP TABLE IF EXISTS CONSULTATION_PAYANTE ;
DROP TABLE IF EXISTS CONSULTATION ;
DROP TABLE IF EXISTS FACTURE ;
DROP TABLE IF EXISTS PATIENT ;

-- -----------------------------------------------------
-- Table PATIENT
-- -----------------------------------------------------
CREATE TABLE PATIENT
(
        patientId            INT             NOT NULL AUTO_INCREMENT
      , patientMatricule     INT             NOT NULL
      , patientNom           VARCHAR(48)     NOT NULL
    , CONSTRAINT PATIENT_PK PRIMARY KEY (patientId)
    , CONSTRAINT PATIENT_MAT_AK UNIQUE (patientMatricule)
) ;

-- -----------------------------------------------------
-- Table FACTURE
-- -----------------------------------------------------
CREATE TABLE FACTURE
(
        patientId            INT             NOT NULL
      , factureId            INT             NOT NULL AUTO_INCREMENT
      , factureNumero        CHAR(8)         NOT NULL
      , factureDate          DATE            NOT NULL
      , factureMontant       INT             NOT NULL
    , CONSTRAINT FACTURE_PK PRIMARY KEY (patientId, factureId) 
    , CONSTRAINT FACTURE_ID_AK UNIQUE (factureId) 
    , CONSTRAINT FACTURE_NUMERO_AK UNIQUE (factureNumero)
    , CONSTRAINT FACTURE_PATIENT_FK FOREIGN KEY (patientId)
          REFERENCES PATIENT (patientId)
) ;

-- -----------------------------------------------------
-- Table CONSULTATION
-- -----------------------------------------------------
CREATE TABLE CONSULTATION
(
        patientId            INT             NOT NULL
      , consultationId       INT             NOT NULL AUTO_INCREMENT
      , consultationDate     DATE            NOT NULL
    , CONSTRAINT CONSULTATION_PK PRIMARY KEY (patientId, consultationId)
    , CONSTRAINT CONSULTATION_ID_AK UNIQUE (consultationId) 
 --    , CONSTRAINT CONSULTATION_DATE_AK UNIQUE (patientId, consultationDate)
    , UNIQUE INDEX CONSULTATION_DATE_AK  (patientId, consultationDate)
    , CONSTRAINT CONSULTATION_PATIENT_FK FOREIGN KEY (patientId)
          REFERENCES PATIENT (patientId)
) ;

-- -----------------------------------------------------
-- Table CONSULTATION_PAYANTE
-- -----------------------------------------------------
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_FACTURE_AK UNIQUE (patientId, factureId)
    , CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_FK FOREIGN KEY (patientId, consultationId)
          REFERENCES CONSULTATION (patientId, consultationId)
          ON DELETE CASCADE
    , CONSTRAINT CONSULTATION_PAYANTE_FACTURE_FK FOREIGN KEY (patientId, factureId)
          REFERENCES FACTURE (patientId, factureId)
) ;

-- -----------------------------------------------------
-- Table TRAITEMENT
-- -----------------------------------------------------
CREATE TABLE TRAITEMENT
(
        patientId            INT             NOT NULL
      , consultationId       INT             NOT NULL
      , traitementId         INT             NOT NULL
    , CONSTRAINT TRAITEMENT_PK PRIMARY KEY (patientId, traitementId)
    , CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY (patientId, consultationId)
          REFERENCES CONSULTATION (patientId, consultationId)
) ;

-- -----------------------------------------------------
-- Table TRAITEMENT_PAYANT
-- -----------------------------------------------------
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_FACTURE_AK UNIQUE (patientId, factureId)
    , CONSTRAINT TRAITEMENT_PAYANT_TRAITEMENT_FK FOREIGN KEY (patientId, traitementId)
          REFERENCES TRAITEMENT (patientId, traitementId)
          ON DELETE CASCADE
    , CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY (patientId, factureId)
          REFERENCES FACTURE (patientId, factureId)
) ;

-- -----------------------------------------------------
-- Table INTERVENTION
-- -----------------------------------------------------
CREATE TABLE INTERVENTION
(
        patientId            INT             NOT NULL
      , interventionId       INT             NOT NULL
      , traitementId         INT             NOT NULL
      , interventionDate     DATE            NOT NULL
    , CONSTRAINT INTERVENTION_PK PRIMARY KEY (patientId, interventionId)
    , CONSTRAINT INTERVENTION_DATE_AK UNIQUE (patientId, interventionDate)
    , CONSTRAINT INTERVENTION_TRAITEMENT_FK FOREIGN KEY (patientId, traitementId)
          REFERENCES TRAITEMENT (patientId, traitementId)
) ;

INSERT INTO PATIENT (patientMatricule, patientNom)
VALUES
    (1, 'Fernand Naudin'), (2, 'Raoul Volfoni'), (3, 'Paul Volfoni')
  , (4, 'Me Folace')    
;
SELECT *, '' as 'PATIENT' from PATIENT ;

INSERT INTO FACTURE (patientId, factureNumero, factureDate, factureMontant)
    (SELECT (SELECT patientId FROM PATIENT WHERE patientMatricule = 1), '00000001', '2019-12-07', 100)
;
SELECT *, '' as 'FACTURE' from FACTURE ;

INSERT INTO CONSULTATION (patientId, consultationDate)
    SELECT (SELECT patientId FROM PATIENT WHERE patientMatricule = 1), '2019-12-07'
;
SELECT *, '' as 'CONSULTATION' from CONSULTATION ;

INSERT INTO CONSULTATION_PAYANTE (patientId, consultationId, factureId)
            SELECT x.patientId, y.consultationId, z.factureId 
            FROM   PATIENT as x 
             JOIN  CONSULTATION as y ON x.patientId = y.patientId
             JOIN  FACTURE as z ON x.patientId = z.patientId
            WHERE  patientMatricule = 1
               AND consultationDate = '2019-12-07'
               AND factureNumero = '00000001'
;
SELECT *, '' as 'CONSULTATION_PAYANTE' from CONSULTATION_PAYANTE ;

...

**apprenant16** · 24/02/2020, 22h31

Bonjour fsmrel, merci pour ce retour et mes excuses pour le délai. Des imprévus de mon côté.

Par ailleurs, j'ai une question, vous avez proposez dans ce post Looping, est-ce que cela pourrait être difficile pour moi de faire la modélisation avec Looping afin de générer le script SQL pour ACCESS ?

Sinon, je continuerai avec MySQL. Merci beaucoup

**fsmrel** · 25/02/2020, 04h36

Bonsoir apprenant16,

Envoyé par apprenant16

est-ce que cela pourrait être difficile pour moi de faire la modélisation avec Looping afin de générer le script SQL pour ACCESS ?

Pas de problème ! Voici un MCD avec Looping :

Nom : apprenant16_factures_idRel_Chemin_auto_incr(v2)mcd.png
Affichages : 300
Taille : 24,3 Ko

En notant que pour l’auto-incrémentation, on utilise le type COMPTEUR (COUNTER avec ACCESS).

Pour générer le script SQL, utiliser l’icône (si vous en avez demandé la présence par personnalisation de la barre d’outils) ou utiliser la méthode décrite par Paprick (cf. son post à la suie du mien) : passer par le menu principal et demander "Exporter" -> "Script SQL au format TXT".

Le script généré par Looping :

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

Retouches manuelles pour retrouver l’équivalent de la génération précédente (post #33) :

Table CONSULTATION_PAYANTE : comme ci-dessous, virer la colonne patientId_1 et mettre à niveau, ajouter le ON DELETE CASCADE.

Table TRAITEMENT : comme ci-dessous, sortir la colonne consultationId de la clé primaire.

Etc. pour les autres tables.

Le script équivalent à celui qu’on a utilisé pour MySQL :

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 INT," & _
   "consultationId INT," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId, consultationId)," & _
   "CONSTRAINT CONSULTATION_PAYANTE_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId) ON DELETE CASCADE," & _
   "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, traitementId)," & _
   "CONSTRAINT TRAITEMENT_CONSULTATION_FK FOREIGN KEY(patientId, consultationId) REFERENCES CONSULTATION(patientId, consultationId)" & _
");"

DoCmd.RunSQL "CREATE TABLE TRAITEMENT_PAYANT(" & _
   "patientId INT," & _
   "consultationId INT," & _
   "traitementId INT," & _
   "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) ON DELETE CASCADE," & _
   "CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"

DoCmd.RunSQL "CREATE TABLE INTERVENTION(" & _
   "patientId INT," & _
   "traitementId INT," & _
   "interventionId COUNTER," & _
   "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

Le diagramme ACCESS après exécution du script :

Nom : apprenant16_factures_idRel_Chemin_auto_incr(v2)access_relations.png
Affichages : 309
Taille : 55,0 Ko

A suivre...

**apprenant16** · 25/02/2020, 10h35

Bonjour fsmrel,

Excellent, je ne pourrai pas vous remercier assez.
Je vais bien avancer dans le développement maintenant je pense. Je vous mets au courant avant de passer le fil en résolu.

Bonne journée

**apprenant16** · 25/02/2020, 10h50

Sous ACCESS 2016, pour exécuter le script, j'ai créé un module, mais à l'exécution, message d'erreur

:

Nom : Capture.PNG
Affichages : 249
Taille : 31,9 Ko

Nom : Capture1.PNG
Affichages : 304
Taille : 74,3 Ko

**fsmrel** · 25/02/2020, 14h21

Bonjour apprenant16,

ACCESS est pour le moins léger, pour ne pas dire déficient dans ses explications concernant les erreurs...

Pour ma part, j’utilise Office 365 MSO (16.0.12430.20112) 32 bits. Le type de base de données que j’ai utilisé est MDB. Je viens d’essayer avec le type ACCDB (ACCESS 2007-2016) en reprenant le code SQL du post #35 : tout se passe bien.

Il va falloir identifier la contrainte qui de votre côté fait tousser ACCESS : faire un module ne contenant que les create table PATIENT, FACTURE, CONSULTATION, CONSULTATION_PAYANTE, et voir pour cette dernière si c’est la clé primaire qui ne plaît pas, donc en virant les contraintes concernant les clés étrangères CONSULTATION_PAYANTE_CONSULTATION_FK et CONSULTATION_PAYANTE_FACTURE_FK :

DoCmd.RunSQL "CREATE TABLE CONSULTATION_PAYANTE(" & _
   "patientId INT," & _
   "consultationId INT," & _
   "factureId INT NOT NULL," & _
   "CONSTRAINT CONSULTATION_PAYANTE_PK PRIMARY KEY(patientId, consultationId)," & _
");"

Si ça ne marche pas, virer la contrainte de clé primaire CONSULTATION_PAYANTE_PK et voir si ça passe pour les contraintes concernant les clés étrangères...

Si ça passe avec la contrainte de clé primaire CONSULTATION_PAYANTE_PK, essayer en ajoutant CONSULTATION_PAYANTE_CONSULTATION_FK...

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

Bonsoir,

En exécutant les requêtes une à une, cela passe, mais seule cette dernière pose le soucis :

Code :

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

1
2
3
4
5
6
7
8
9
DoCmd.RunSQL "CREATE TABLE TRAITEMENT_PAYANT(" & _
   "patientId INT," & _
   "consultationId INT," & _
   "traitementId INT," & _
   "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) ON DELETE CASCADE," & _
   "CONSTRAINT TRAITEMENT_PAYANT_FACTURE_FK FOREIGN KEY(patientId, factureId) REFERENCES FACTURE(patientId, factureId)" & _
");"

Avec message d'erreur de syntaxe sur la clause CONSTRAINT.

En supprimant les contraintes sur les clés étrangères, j'ai ceci :

Nom : Capture.PNG
Affichages : 296
Taille : 24,9 Ko

Nom : Capture.PNG
Affichages : 296
Taille : 24,9 Ko

En virant la contrainte de clé primaire également pour voir si ça passe pour les contraintes concernant les clés étrangères…, voici le message :

Erreur de syntaxe sur la clause CONSTRAINT.

Ma base en pièce jointe pour vérification si possible.

Merci

**fsmrel** · 26/02/2020, 02h30

J’ai déjà eu ce genre d’erreurs à force de manips, mais ne se produisant plus après redémarrage à froid du fichier accdb.

Le CREATE TABLE CONSULTATION_PAYANTE contient une colonne consultationId à virer. Il n’y a pas de raison pour que les choses s’améliorent pour autant, mais bon...

Je préfère ne pas ouvrir votre fichier, car j’ai des problèmes avec Windows...

Une bonne âme de passage pourrait-elle le faire ?

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

Schéma

Discussions similaires

Partager

Partager