Discussion :

[Nerva]

Hello.

Script simplifié (HSQLDB) :

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
CREATE TABLE T_PERSONNES_TY (
	PER_TY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL, 
	PER_TY_LIB VARCHAR(16) NOT NULL, 
	CONSTRAINT PK_PER_TY_ID PRIMARY KEY (PER_TY_ID)
);
 
CREATE TABLE T_PERSONNES (
	PER_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
	PER_TY_ID INTEGER NOT NULL,
	PER_NOM VARCHAR(32) NOT NULL,
	PER_PRENOM VARCHAR(32) NOT NULL,
	CONSTRAINT PK_PER_ID PRIMARY KEY (PER_ID),
	CONSTRAINT FK_T_PERSONNES_T_PERSONNES_TY FOREIGN KEY (PER_TY_ID) REFERENCES T_PERSONNES_TY (PER_TY_ID)
);
 
CREATE TABLE T_RESIDENTS (
	RES_ID INTEGER NOT NULL,
	CONSTRAINT PK_RES_ID PRIMARY KEY (RES_ID),
	CONSTRAINT FK_RESIDENTS_PERSONNES FOREIGN KEY (RES_ID) REFERENCES T_PERSONNES (PER_ID)
);
 
CREATE TABLE T_CONTACTS (
	CON_ID INTEGER NOT NULL,
	CONSTRAINT PK_CON_ID PRIMARY KEY (CON_ID),
	CONSTRAINT FK_CONTACTS_PERSONNES FOREIGN KEY (CON_ID) REFERENCES T_PERSONNES (PER_ID)
);
 
CREATE TABLE TJ_RESIDENTS_CONTACTS (
	RES_ID INTEGER NOT NULL,
	CON_ID INTEGER NOT NULL,
	CONSTRAINT PK_RES_ID_CON_ID PRIMARY KEY (RES_ID),
	CONSTRAINT FK_TJ_A FOREIGN KEY (RES_ID) REFERENCES T_RESIDENTS (RES_ID),
	CONSTRAINT FK_TJ_B FOREIGN KEY (CON_ID) REFERENCES T_CONTACTS (CON_ID)
);

PER_TY_ID : 1, résident ; 2, contact.

Les tables T_RESIDENTS et T_CONTACTS sont les sous-types de T_PERSONNES.

- Un contact n'existe que si il est charge (administrativement parlant) d'un résident.
- Un contact peut avoir 1 ou plusieurs résidents.
- Un résident peut avoir 0 ou 1 contact.

Tout ceci fonctionne correctement jusqu'à ce qu'un cas particulier vienne tout chambouler : un résident, qui n'a pas de contact, mais qui est en même temps contact d'un autre résident. Ma première idée serait de créer une nouvelle personne avec le même nom est de la définir comme contact dans PER_TY_ID mais j'ai l'impression que ça n'est pas la bonne méthode.

Merci.

[escartefigue]

Bonjour,

Dans la mesure ou les résidents et les contacts n'ont aucun attribut spécifique, il n'est pas utile de créer un sous-type pour chacun d'eux.
Quel est le contexte ? Une maison de retraite, un établissement de soins ?

Si c'est le cas, la notion de "résident" pourrait être la conséquence de l'existence d'une association à date entre la personne et une chambre (si une personne occupe une chambre du jj-mm-aaaa au jj-mm-aaaa, alors c'est une résident).
Et une personne est contact parce qu'elle est en lien d'association "contact" avec une autre.
Ce-faisant, la notion de "type de personnes" n'a peut-être pas d'utilité, à confirmer (que sont les types de personnes ?)

Ce qui donnerait le MCD suivant :




L'entité type fictive [CAL_calendrier] ne deviendra pas une table (d'où son nom entre parenthèses).
Elle ne sert qu'à faire participer la date comme identifiant de la table issue de l'association (occuper)
La flèche partant de (OCC_occuper) à destination de [PER_personne] est une contrainte matérialisant le fait que pour une chambre et une date, il ne peut y avoir qu'une personne (sous réserve que les chambres soient toutes des "single")

Une personne est résidente s'il existe une occurrence d'association OCC_occuper pour cette personne dont la date de début CAL_date et la date de fin OCC_dtfin encadre la date du jour.

Une personne est contact s'il existe une occurrence d'association (CTA_contact) pour cette personne.
Là aussi on pourrait faire participer l'entité-type fictive [CAL_calendrier] à l'association (CTA_contact) si le contact d'un résident peut changer dans le temps (à préciser)

La table associative issue de (CTA_contact) vous permet de résoudre votre problème de résident contact d'un autre résident

Le MLD résultant est le suivant (ici généré pour DB2, puisqu'il semble que ce soit votre SGBD) :

[Nerva]

Merci déjà du temps passé pour cette réponse.

Résidents et contacts ont des attributs propres ; j'ai volontairement épuré les tables dans cette base de test afin de ne pas surcharger l'ensemble. D'ailleurs, les résidents sont également dispatchés en 3 "catégories" avec simplement une clé étrangère vers une simple table T_RESIDENTS_TY, puisque ces catégories ne nécessitent pas de sous-typage.

Dans la pratique, il y a également un autre sous-type de personnes : les employés, hors de propos également ici puisque leurs relations avec les résidents sont parfaitement établies et sans cas particuliers (table de liaison 1:N de chaque côté).

Je précise qu'il s'agit de repas et non de logements (mais ça ne change rien à la question). J'avais aussi pensé à une jointure réflexive mais je ne sais pas comment la mettre en oeuvre...

[escartefigue]

Quel est le rôle de la table des types de personnes T_PERSONNES_TY qui semble être une redondance avec la notion de résident et contact ?

Si résidents et contacts ont des attributs spécifiques, alors on a une difficulté : le résident qui est contact d'un autre résident ne peut pas avoir les attributs d'un contact non résident
Pour le reste, pas de difficulté particulière : on peut créer une relation réflexive pour les cas de résidents contact d'autres résidents et qui sera exclusive de la relation entre un contact non résident et un résident. C'est soit une association, soit l'autre, mais jamais les deux. Un trigger devra être codé pour vérifier cette contrainte.

Ce qui donne le MCD suivant (avec des attributs bidons pour montrer que les sous-types sont justifiés) :




Et le MCD correspondant :



J'ai supposé que vous utilisiez DB2 vu la syntaxe des identifiants, c'est bien le cas ?

Voici le script sauce DB2 (il reste à code le TRIGGER pour l'exclusion) :

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
CREATE TABLE TYP_type_personne(
   TYP_ident INT GENERATED BY DEFAULT AS IDENTITY,
   TYP_code CHAR(4) NOT NULL,
   TYP_libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(TYP_ident),
   UNIQUE(TYP_code)
);
 
CREATE TABLE PER_personne(
   PER_id INT GENERATED BY DEFAULT AS IDENTITY,
   PER_nom VARCHAR(50) NOT NULL,
   PER_prenom VARCHAR(50) NOT NULL,
   TYP_ident INT NOT NULL,
   PRIMARY KEY(PER_id),
   FOREIGN KEY(TYP_ident) REFERENCES TYP_type_personne(TYP_ident)
);
 
CREATE TABLE PCT_pers_contact(
   PER_id INT,
   PCT_truc VARCHAR(50) NOT NULL,
   PRIMARY KEY(PER_id),
   FOREIGN KEY(PER_id) REFERENCES PER_personne(PER_id)
);
 
CREATE TABLE PRS_pers_resident(
   PER_id INT,
   PRS_machin VARCHAR(50) NOT NULL,
   PER_id_resident_contact INT,
   PER_id_contact INT,
   PRIMARY KEY(PER_id),
   FOREIGN KEY(PER_id) REFERENCES PER_personne(PER_id),
   FOREIGN KEY(PER_id_resident_contact) REFERENCES PRS_pers_resident(PER_id),
   FOREIGN KEY(PER_id_contact) REFERENCES PCT_pers_contact(PER_id)
);

[Nerva]

Je vais voir ce que donne le script (j'utilise le HSQLDB mais aucun souci pour modifier).

Les attributs des personnes et des résidents sont différents. Pour les personnes, c'est tout ce qui est propre : nom, prénom, date de naissance, titre de courtoisie, etc. Pour les résidents, c'est spécifique : des numéros divers, des dates. Pour les contacts, il y a juste une date. Et il ne faut pas oublier non plus le sous-type Employés (gens de maison des résidents), avec là encore des données spécifiques tel que le numéro de sécu. Et en préparation il y a encore un autre sous-type qui concerne les employés du restaurant, pour totaliser leurs repas (gratuits). Donc, les sous-types sont indispensables.

La table T_PERSONNES_TY définit les "catégories" de personnes : Résident, Contact, Employé, séparés dans des formulaires adéquats.

[Paprick]

Bonjour,

Je me permets de vous recommander de résoudre les problèmes au niveau conceptuel, comme le fait escartefigue dans ses réponses ; le schéma relationnel et les tables se déduisent ensuite automatiquement à partir du modèle conceptuel de données.

Bonne continuation !

[escartefigue]

La table T_PERSONNES_TY définit les "catégories" de personnes : Résident, Contact, Employé, séparés dans des formulaires adéquats.

En ce cas c'est une redondance comme je le craignais : puisque chaque sous-type est identifié par l'héritage et la spécialisation, il ne faut pas y ajouter un lien de typologie, car on risque l'incohérence.
Ce qu'il faut faire, c'est préciser dans les règles de gestion si les différents types sont exclusifs ou pas (peut-on être résident et contact par exemple), puis réaliser la modélisation conceptuelle en conséquence, quand ce modèle conceptuel est validé, on en dérive un modèle tabulaire en quelques clics (un seul clic avec Looping )

[Nerva]

Paprick : ça me semble extrêmement compliqué (d'un point de vue pratique) de redéfinir le modèle.

Escartefigue : au niveau purement base de données, il y a peut-être redondance, mais pas à l'usage. Lorsqu'une nouvelle personne est saisie, faut bien définir si elle est résident ou tuteur. Et dans les formulaires, même si la table T_PERSONNES_TY peut être vue comme superflue (il est plus convivial pour l'opérateur de sélectionner un mot en bon français dans une liste plutôt qu'un ID), le champ PER_TY_ID est indispensable, notamment dans différentes requêtes où les informations du type et du sous-type doivent être extraites.

[escartefigue]

D'un point de vue pratique, il est très simple de savoir si une personne est résidente ou contact sans avoir de table de typologie.
Puisque vous avez un sous-type dédié, il suffit d'aller voir par requête si la personne est présente dans la table des résidents ou des contacts.

Mais surtout, la présence de cette typologie en lien avec les personnes est un risque d'incohérence : rien n'empêche en l'état de définir un lien de type "résident" entre la personne et sa typologie, sans pour autant avoir un sous-type "résident".
La cohérence et l'intégrité des données sont deux principes fondamentaux d'un SGBD.
Il faut donc

• soit utiliser la typologie et uniquement la typologie (en ce cas pas d'attribut spécifique puisque pas de sous-type et pas non plus d'association spécifique pour la même raison)
• soit utiliser l'héritage et la spécialisation et supprimer la typologie (ce qui vous permet d'avoir des attributs spécifiques pour chaque sous-type et des associations spécifiques)

La modification du modèle de données est effectivement souvent lourde de conséquences, c'est la raison pour laquelle il est très important d'y consacrer le plus grand soin avant de se lancer dans les traitements. Malheureusement, cette étape est souvent négligée.
Je ne compte plus le nombre de fois où des personnes sont confrontées à des requêtes excessivement complexes et contre performantes justement à cause d'un modèle de données mal construit.

[Nerva]

Puisque vous avez un sous-type dédié, il suffit d'aller voir par requête si la personne est présente dans la table des résidents ou des contacts.

Je suis bien d'accord. Mais cette personne, avant d'aller voir de quel sous-type elle est avec telle ou telle requête pour tel ou tel résultat, il faut bien la saisir dans la base. Le formulaire affiche les données de la personne (donc, données communes). Selon sélection de PER_TY_ID (1 pour les résidents, 2 pour les tuteurs), le sous-formulaire correspondant s'affiche : la personne est sous-typée. Il n'y a plus qu'à remplir les champs spécifiques et c'est sans ambiguïté. De plus, je doute qu'une simple colonne contenant des 1 ou des 2 puisse plomber les performances.

Pour la conception globale, évidemment que je ne vais pas vous contredire. Même si j'essaie de m'atteler à une nouvelle ébauche, ça sera avant tout dans le but d'apprendre et de faire mieux la prochaine fois, car restructurer entièrement la base en cours, au secours...

Merci du coup de main...

[escartefigue]

Si vous voulez conserver ce mode de fonctionnement coté traitement, pas de souci :
La question Contact/Résident s'affiche à l'écran et le formulaire adapté s'affiche en fonction de la réponse.
Lors de la validation, vous enregistrez les éléments relatifs à la personne (le surtype) et les éléments relatifs au contact ou au résident selon le cas (le sous type), ce faisant, vous typez la personne dans la base, mais sans avoir besoin de stocker dans une table de typologie et donc sans redondance ni risque d'incohérence.

Ce n'est ici pas un problème de performances, effectivement 1 ou zéro dans une table ça ne coute rien, c'est un problème de redondance et du risque d'incohérence qui en découle.