Discussion :

[apprenant16]

Bonjour, mon problème est relatif à comment matérialiser la relation entre la table Facture et les tables Consultation et Traitement en partant de ces règles de gestion :

1. une consultation est suivie par zéro ou une seul facture (Zéro pour les consultations gratuites parfois)
2. un traitement est suivi par zéro ou une seul facture (Zéro pour les consultations gratuites parfois)
3. une facture correspond soit à une consultation, soit à un traitement soit les deux en même temps

je vous remercie par avance pour votre aide

[fsmrel]

Bonjour apprenant16,


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

A tout à l'heure

[apprenant16]

Bonjour fsmrel, merci de la réponse. C'est noté.

[fsmrel]

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.

 

[apprenant16]

Bonjour fsmrel, très clair comme explication Merci

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 ?

[fsmrel]

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.



 

[apprenant16]

D'accord, je vais prendre en comptes toutes vos indications pour faire un modèle plus complet que je vais vous présenter. Merci

[apprenant16]

Bonjour,

1. La relation entre la table Consultation et Consultation_Payante est de type 1 à 1 ou bien 1 à plusieurs ? si oui, théoriquement cela signifie quoi ?
   
   
   
2. 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 ?
3. Et la table Intervention, sa place est-elle bonne dans le schéma ci-après :

Voici l'affichage pour ACCESS :

[fsmrel]

Bonjour apprenant16,


A propos des cardinalités des associations :

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 :

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 :

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 :

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

 

[apprenant16]

Bonsoir fsmrel, merci avec vos explications c'est plus clair.

Je vais mettre mon modèle à jour et présenter la nouvelle version.

[apprenant16]

Bonsoir fsmrel,

mes excuses pour le retard, est-ce que le modèle que voici est viable :



Je pense qu'il serait préférable de garder les tables dans une base MySQL, et créer l'interface en ACCESS ( Formulaires, Etats, Requêtes). Vous pensez que c'est bon comme démarche ?

[apprenant16]

Salut,

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 ?

[fsmrel]

Bonjour 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.

 

[apprenant16]

Bonjour fsmrel, je vous remercie pour votre disponibilité encore.

Je vois que le modèle est plus viable avec votre proposition ci-dessus. Je vaois compléter les informations de la table Patient et après ça, je crois que ça devrait être bon pour mise en production, avec votre accord ?

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 ?

Merci

[fsmrel]

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

 

[apprenant16]

Bonjour fsmrel, merci pour votre aide et disponibilité aussi.

Pour transformer le script compatible avec Access 2016, il y a beaucoup de chose à faire ? Un outil à utiliser ?

Bon weekend

[apprenant16]

Une remarque : dans le modèle, j'ai remarqué que les champs clés primaires ne sont pas auto-incrémenté. Est-ce que fait exprès suivant le modèle ou bien on peut changer en AI ?

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

[escartefigue]

Bonsoir,

Une remarque : dans le modèle, j'ai remarqué que les champs clés primaires ne sont pas auto-incrémenté. Est-ce que fait exprès suivant le modèle ou bien on peut changer en AI ?

Pour qu'un attribut utilise l'auto incrément, il faut ajouter AUTO_INCREMENT dans le script
par exemple

Code :

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

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS T1_MATABLE 
      ( T1_MAPK    int     AUTO_INCREMENT PRIMARY KEY,
        T1_MADATE  date    not null,
        T1_MONCHAR char(4) not null,
        etc...
       )

Si vous utilisez MySQL Workbench, il suffit de cocher la case "AI"

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

À partir du moment où le script contient des contraintes avec le mot clef "REFERENCE" c'est que l'intégrité référentielle est gérée
Comme mentionné dans la FAQ, INNODB prend en charge l'intégrité référentielle

[apprenant16]

Bonsoir,

je vous remercie escartefigue pour vos explications.

Le script a bien des contraintes avec le mot clef "REFERENCE", mais ce qui m'a fait douter jusqu'à poser la question, c'est le fait que je vois type MyISAM via phpMyAdmin. Ce n'est donc pas un problème ?



Et dans le volet Concepteur du menu Plus, les relations ne s'affichent pas

[fsmrel]

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

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.

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...

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

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.