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

Bonjour apprenant16,


Je suis obligé de m'absenter, mais d'ici une paire d'heures je vous réponds.

A tout à l'heure

Bonjour apprenant16,


En principe une facture peut faire référence à plusieurs consultations et ou traitements, c’est plutôt la ligne de facture qui ferait référence à une consultation ou à un traitement, mais bon, restons-en à la règle qui veut qu’une facture ne fasse référence qu’à une consultation et/ou à un traitement.

Une idée est de ne mettre en relation avec la table FACTURE que les consultations et traitements payants (procédé de spécialisation), d’où la mise en oeuvre des deux tables CONSULTATION_PAYANTE et TRAITEMENT_PAYANT.

Pour éviter que deux consultations payantes fassent référence à la même facture (même principe pour les traitements payants), l’attribut factureId doit faire l’objet d’une clé alternative pour la table CONSULTATION_PAYANTE (même chose pour la table TRAITEMENT_PAYANT). Avec MySQL Workbench, ces clés alternatives passent par la déclaration d’un indes UNIQUE.

 

Envoyé par apprenant16

Questions :

1. sur les tables Consultations Payantes et Traitements Payants, il n'y a donc pas de Clès Primaires ?
2. s'agissant d'une consultation gratuite ou traitement gratuit, seule la table Consultation sera utilisée ?
3. pour les tables Consultations Payantes et Traitements Payants, est-ce que seuls les deux attributs ConsultationId et FactureID suffiront ?

Concernant la 1re question : les clés primaires sont là ! Simplement elles ont viré au rouge car elles sont aussi clés étrangères (j’utilise MySQL Workbench V6.3 et il procède ainsi).

Concernant la 2e question : ici, seule la table CONSULTATION est porteuse des données concernant les consultations gratuites et seule la table TRAITEMENT est porteuse des données concernant les traitements gratuits.

Concernant la 3e question : si les consultations payantes font l’objet de données ne concernant pas les consultations gratuites, alors ces données feront l’objet d’attributs ad-hoc dans la table CONSULTATION_PAYANTE.
Si les traitements payants font l’objet de données ne concernant pas les traitements gratuits, alors ces données feront l’objet d’attributs ad-hoc dans la table TRAITEMENT_PAYANT.

Par ailleurs, si les consultations gratuites faisaient l’objet de données ne concernant pas les consultations payantes, alors ces données feraient l’objet d’attributs d’une table CONSULTATION_GRATUITE.
Si les traitements gratuits faisaient l’objet de données ne concernant pas les traitements payants, alors ces données feraient l’objet d’attributs d’une table TRAITEMENT_GRATUIT.



 

Bonjour apprenant16,


A propos des cardinalités des associations :

Envoyé par apprenant16

La relation entre la table Consultation et Consultation_Payante est de type 1 à 1 ou bien 1 à plusieurs ?

La relation entre les tables Consultation et Consultation_Payante est de type 1 à 1, c’est bien ce que confirme le bouton « One-to-One » qui est activé.

C’est bien ce que veut exprimer dans mon diagramme précédent la représentation graphique (notation « patte-d’oie ») :

[CONSULTATION]—||—————————O|—[CONSULTATION_PAYANTE]

En notation UML cela donnerait :

[CONSULTATION]—1—————————0..1—[CONSULTATION_PAYANTE]

En passant : les notations patte-d’oie et UML permettent de distinguer une bijection d’une injection, mais ça n’est pas le cas de la notation ACCESS que je n’utilise donc pas (de même, la notation ACCESS ne permet pas de distinguer une surjection d’une autre application).

Sémantiquement parlant, une consultation payante est une consultation (notez l’emploi du verbe être). Si la relation était de type 1 à plusieurs, il faudrait remplacer « être » par « avoir », ce qui serait ici une erreur sur tous les plans...

Techniquement parlant, les données qui sont communes aux consultations payantes et aux consultations gratuites sont regroupées dans la table CONSULTATION, tandis que les données propres aux consultations payantes sont isolées dans la table CONSULTATION_PAYANTE. Si les consultations gratuites étaient dotées elles aussi de données en propre, alors il faudrait mettre en en oeuvre une table CONSULTATION_GRATUITE. On entre alors dans le domaine de la généralisation/spécialisation.


Au sujet de l’attribut FactureId :

Envoyé par apprenant16

Dans les tables Consutation_Payante et Traitement_Payant, est-ce qu'il faut créer manuellement les attributs FactureId ou bien il vont découler de la relation automatiquement ?

L’attribut FactureId est automatiquement généré par MySQL Workbench dès qu’on établit la relation entre les tables, c’est-à-dire quand on clique sur CONSULTATION_PAYANTE puis sur FACTURE (même principe évidemment avec TRAITEMENT_PAYANT). Il faudra ensuite décocher « Identifying Relationship ».


Au sujet des interventions :

Envoyé par apprenant16

Et la table Intervention, sa place est-elle bonne dans le schéma ci-après :

Oui, la table Intervention, est bien à sa place, c’est une conséquence de vos règles de gestion :

Envoyé par apprenant16

R3a : un traitement entraîne une ou plusieurs interventions
R3b : une intervention est entraînée par un seul traitement

 

Bonjour apprenant16,

Envoyé par apprenant16

J'ai pu générer toutes les tables dans MySQL avec le script issu du modèle.
Je crois que je vais continuer avec MySQL comme dorsale et créer les interfaces et requêtes avec ACCESS pour déployer après.
Vous êtes d'accord avec cette idée ?

N’allons pas trop vite. Le choix MySQL / ACCESS est certes totalement de votre ressort, mais de mon côté j’ai des remarques concernant le blindage du modèle...


Selon les schémas des posts précédents :

Supposons que la facture F1 concerne le client C1 par le canal FACTURE > CONSULTATION_PAYANTE > CONSULTATION > PATIENT ;

Rien n’empêche que cette même facture F1 puisse concerner le client C2 par le canal FACTURE > TRAITEMENT_PAYANT > TRAITEMENT > CONSULTATION > PATIENT !

Ça fait désordre. Pour empêcher cela, grâce à l’identification relative (Identifying Relationship de MySQL Workbench) on peut propager l’attribut PatientId dans l’ensemble des tables concernées, y-compris FACTURE, l’astuce consistant dans la foulée à n’avoir qu’un seul attribut patientId dans chaque table :

Quelques remarques supplémentaires

Clés primaires

Pour réduire l’encombrement des index, la clé primaire initiale {patientId, consultationId, traitementId} de la table TRAITEMENT peut être réduite comme ci-dessus à {patientId, traitementId}, cela n’a aucune incidence sur la validité du de la base de données.

Pour les mêmes raisons, la clé primaire de la table INTERVENTION peut être réduite à {patientId, interventionId}


Clés alternatives (contraintes d’unicité)

Table PATIENT : {patientMatricule}.

Table CONSULTATION : {patientId, consultationDate} (sauf si un patient peut consulter plus d’une fois au cours d’une journée).

Table FACTURE : {factureNumero}.

Table CONSULTATION_PAYANTE : {patientId, factureId} (puisqu’une facture joue en même temps le rôle de ligne de facture, c’est-à-dire ne concerne qu’une seule consultation).

Table TRAITEMENT_PAYANT : même principe, {patientId, factureId}.

Table INTERVENTION : {patientId, interventionDate}.


Code SQL :

-- -----------------------------------------------------
-- Table PATIENT
-- -----------------------------------------------------
CREATE TABLE PATIENT
(
        patientId            INT             NOT NULL
      , 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
      , factureNumero        CHAR(8)         NOT NULL
      , factureDate          DATE            NOT NULL
      , factureMontant       INT             NOT NULL
    , CONSTRAINT FACTURE_PK PRIMARY KEY (patientId, 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
      , consultationDate     DATE            NOT NULL
    , CONSTRAINT CONSULTATION_PK PRIMARY KEY (patientId, consultationId)
    , CONSTRAINT CONSULTATION_DATE_AK UNIQUE (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)
) ;

A noter l’action de compensation CASCADE retenue pour les clés étrangères CONSULTATION_PAYANTE_CONSULTATION_FK et TRAITEMENT_PAYANT_TRAITEMENT_FK. En effet une consultation payante P1 n’est qu’une consultation particulière C1 et la suppression de C1 entraîne forcément celle de P1. Même principe pour les traitement payants.

 

Envoyé par apprenant16

Après, je vais conserver la base MySQL et interfacer avec ACCESS.
Sinon il me faudra nettoyer le code pour le créer directement dans ACCESS ?

Au besoin, le code que j’ai fourni est directement utilisable sous ACCESS (Office 365).

 

Bonsoir,


Tout d’abord je précise que pour le code fourni dans les posts précédents et ci-dessous j’ai utilisé MySQL 5.7, sans jamais préciser le nom du moteur dans les CREATE TABLE ; quoi qu’il en soit, dans mon cas, le moteur par défaut est InnoDB.

Maintenant qu’en est-il de MyISAM ?

Au paragraphe 1.8.2.4, Foreign Key Differences du manuel « MySQL 5.7 Reference Manual », on lit ceci :

« For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it »

Conclusion : fuyons MyISAM ! (Je suppose qu'Escartefigue convient ^^)

Envoyé par apprenant16

Une remarque : dans le modèle, j'ai remarqué que les champs clés primaires ne sont pas auto-incrémentés.

J’ai eu la responsabilité de la modélisation des bases de données de production chez nombre de mes clients (banques, assurances, industrie, distribution, etc.), donc pénalités financières en cas de non performance...

Si au lieu de patients d’un cabinet nous parlons de clients d’une banque et que les utilisateurs y sont nombreux à mettre à jour la table CLIENT et les tables associées, dans cette situation invariablement mon constat fut que les index étaient victimes de poses de verrous par le SGBD (DB2), d’où des temps d’attente insupportables pour les utilisateurs, et pour éviter cela, une bonne solution consistait à utiliser des valeurs aléatoires pour la clé primaire de la table CLIENT, afin d’éparpiller dans l’espace physique les lignes de cette table et les clés dans les index et ainsi éviter au mieux les contentions.

Si vous aviez une centaine d’utilisateurs mettant à jour la table PATIENT, l’auto-incrémentation risquerait d’être redoutable... En utilisant des valeurs aléatoires le risque serait très sensiblement réduit. L’identification relative fait que les autres tables (FACTURE, CONSULTATION, etc.) seront à l’abri si la table PATIENT l’est.

Vous aurez compris pourquoi je n’utilise pas l’auto-incrémentation, mais vous pouvez bien sûr vous en servir dans la mesure où vous n’avez pas une foule d’utilisateurs mettant à jour les tables. D’un point de vue technique, pour la table PATIENT, pas de problème. Pour les autres tables, c’est un peu délicat, car MySQL est plutôt du genre rustique (opinion personnelle, car n’en étant pas utilisateur, je suis peut-être un peu subjectif). Par exemple, dans le cas de la table FACTURE, une seule colonne étant autorisée à être auto-incrémentée, l’identification relative pose un petit problème, car on est contraint à rendre {factureId} clé candidate. En l’occurrence, je définis pour ma part une contrainte d’unicité rendant {factureId} clé candidate sans pour autant être clé primaire, laquelle reste {patientId, factureId}, ouf !

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)
) ;

Remarque concernant la création d’une facture.

A priori, pour créer une facture, on doit connaître la valeur affectée par MySQL à la colonne patientId (table PATIENT) à l’occasion de la création du patient concerné. J’ai pour principe que, quel que soit le SGBD, dans les requêtes SQL je ne fais jamais référence à cette valeur, elle doit rester sous le capot, invisible (j’évite ainsi les problèmes engendrés par la réorganisation des bases de données, et autres servitudes). Par contre je me sers des propriétés naturelles telles que le matricule affecté au patient (cf. CREATE TABLE PATIENT du post #13). Exemple :

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

Pour les consultations : comme pour les factures, l’auto-incrémentation conduit à l’ajout d’une clé candidate, à savoir {consultationId} :

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)
) ; 

Pour créer une consultation, on utilise à nouveau les seules valeurs des propriétés naturelles :

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

Cas des consultations payantes :

On n’a pas à toucher à la structure de la table CONSULTATION_PAYANTE.

Pour l’insertion d’une consultation payante, on utilise une fois de plus les seules valeurs 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 = 1
               AND consultationDate = '2019-12-07'
               AND factureNumero = '00000001'
; 

Remarque : si un patient peut avoir plus d’une consultation dans une journée, il faudra prévoir l’ajout d’une colonne heure pour la table CONSULTATION, car dans le cas du cabinet en question, il est certain que pour un jour et une heure donnés, un patient ne peut pas avoir plus d’une consultation.

Pour les autres tables, les principes formulés jusqu’ici sont valables : les seules valeurs utilisées sont celles des propriétés naturelles.

Envoyé par apprenant16

Suite à cette entrée de faq, je me demande est-ce que le modèle actuel, gère l'intégrité et la cohérence des relations ?

Comme expliqué plus haut, le moteur doit être InnoDB...

Envoyé par apprenant16

Après, je vais conserver la base MySQL et interfacer avec ACCESS.
Sinon il me faudra nettoyer le code pour le créer directement dans ACCESS ?

Au besoin, le code que j’ai fourni est directement utilisable sous ACCESS (Office 365), avec au besoin les aménagements consécutifs à l’auto-incrémentation (type COUNTER).

Exemple :

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

Envoyé par apprenant16

je vois type MyISAM via phpMyAdmin.

J’ignore tout de PHP et de phpMyAdmin, mais il y a manifestement une variable default-storage-engine à changer.