Discussion :

[Nerva]

Bonjour.

Je cherche à réaliser un emploi du temps hebdomadaire (travail purement personnel dans un but pédagogique) mais l'approche est difficile étant donné que j'entrevois plusieurs possibilités, évidemment, sans savoir quelle est la bonne.

Il s'agit d'un planning d'heures de ménage chez des particuliers, avec les règles suivantes.

- Les particuliers sont nommés clients et les personnels de ménage employés.
- Comme aucun attribut ne les distingue, je pense qu'une entité unique avec un attribut différenciant l'un de l'autre s'impose.
- Une employée peut avoir 1 ou plusieurs clients ; 1 client peut avoir 1 ou plusieurs employés.
- Pour qu'une employée se rende chez un client, une affectation doit être créée. Cette affectation peut être durable ou temporaire, dans le cas d'un remplacement (maladie, vacances, etc). À ce propos, il s'agit dont dans l'immense majorité des cas d'affectations "permanentes", définies par un nombre d'heures à effectuer par semaine.
- Une affectation temporaire est supprimée dès que la période est terminée (il n'y a aucun besoin d'historisation).
- Une employée ne peut avoir plus d'une affectation simultanément chez le même client.
- Les plannings sont établis au nom de l'employée. Une employée ne peut avoir qu'un seul planning.

Voici comment j'ai procédé pour le moment...

Une table de créneaux horaires est reliée à une table de plannings. L'enregistrement d'une nouvelle salariée implique la création d'un nouveau planning.

Dans la table des plannings, on trouve :

- Un identifiant.
- La clé étrangère de la table des employées.

Dans la table des créneaux horaires, on trouve :

- Un identifiant.
- La clé étrangère de la table des plannings.
- La clé étrangère de la table des clients.
- La clé étrangère de la table des jours (ceci afin de pouvoir classer par ordre de jour).
- 1 colonne pour l'horaire de début.
- 1 colonne pour l'horaire de fin.

Horaires de début et de fin puisent les valeurs dans une table (non reliée) qui contient les heures de 08:00 à 20:00 par "crans" de 15 minutes.

Actuellement, donc, afin de faciliter la conception et ne pas m'emmêler les pinceaux, j'ai carrément séparé clients et employés en utilisant deux tables.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
CREATE TABLE T_EMPLOYES (
	EMP_ID INTEGER IDENTITY NOT NULL,
	SAL_NOM VARCHAR (32) NOT NULL,
	SAL_PRENOM VARCHAR (32) NOT NULL,
	CONSTRAINT PK_EMP_ID PRIMARY KEY (EMP_ID)
);

CREATE TABLE T_CLIENTS (
	CLI_ID INTEGER IDENTITY NOT NULL,
	CLI_NOM VARCHAR (32) NOT NULL,
	CLI_PRENOM VARCHAR (32) NOT NULL,
	CONSTRAINT PK_CLI_ID PRIMARY KEY (CLI_ID)
);

CREATE TABLE T_AFFECTATIONS (
	AFF_ID INTEGER IDENTITY NOT NULL, 
	EMP_ID INTEGER NOT NULL,
	CLI_ID INTEGER NOT NULL,
	AFF_HR DECIMAL (6,2) NOT NULL,
	CONSTRAINT PK_AFF_ID PRIMARY KEY (AFF_ID),
	CONSTRAINT FK_AFF_EMP FOREIGN KEY (EMP_ID) REFERENCES T_EMPLOYES (EMP_ID),
	CONSTRAINT FK_AFF_CLI FOREIGN KEY (CLI_ID) REFERENCES T_CLIENTS (CLI_ID)
);

CREATE TABLE T_JOURS (
	JOU_ID INTEGER NOT NULL,
	JOU_LABEL VARCHAR (10) NOT NULL,
	CONSTRAINT PK_JOU_ID PRIMARY KEY (JOU_ID)
);

CREATE TABLE T_HEURES (
	HEU_ID INTEGER NOT NULL,
	HEU_HR TIME NOT NULL,
	CONSTRAINT PK_HEU_ID PRIMARY KEY (HEU_ID)
);

CREATE TABLE T_PLANNINGS (
	PLA_ID INTEGER IDENTITY NOT NULL,
	EMP_ID INTEGER NOT NULL,
	CONSTRAINT PK_PLA_ID PRIMARY KEY (PLA_ID),
	CONSTRAINT FK_PLA_EMP FOREIGN KEY (EMP_ID) REFERENCES T_EMPLOYES (EMP_ID),
	CONSTRAINT UK_PLA_EMP UNIQUE (EMP_ID)
);

CREATE TABLE T_CRENEAUX (
	CRE_ID INTEGER IDENTITY NOT NULL,
	PLA_ID INTEGER NOT NULL,
	CLI_ID INTEGER NOT NULL,
	JOU_ID INTEGER NOT NULL,
	CRE_DEBUT TIME NOT NULL,
	CRE_FIN TIME NOT NULL,
	CONSTRAINT PK_CRE_ID PRIMARY KEY (CRE_ID),
	CONSTRAINT FK_CRE_PLA FOREIGN KEY (PLA_ID) REFERENCES T_PLANNINGS (PLA_ID),
	CONSTRAINT FK_CRE_CLI FOREIGN KEY (CLI_ID) REFERENCES T_CLIENTS (CLI_ID),
	CONSTRAINT FK_CRE_JOU FOREIGN KEY (JOU_ID) REFERENCES T_JOURS (JOU_ID),
	CONSTRAINT UK_PLA_JOU_DEB UNIQUE (PLA_ID, JOU_ID, CRE_DEBUT),
	CONSTRAINT UK_PLA_JOU_FIN UNIQUE (PLA_ID, JOU_ID, CRE_FIN),
	CONSTRAINT CK_DEB_FIN CHECK (CRE_FIN > CRE_DEBUT)
);

Testé dans une application LibreOffice Base.

Alors ça fonctionne, je dirai même que ça va tout seul pour la saisie dans un formulaire, mais je ne suis pas du tout sûr que la structure soit bonne et j'aimerais avoir votre avis.

Merci.

[escartefigue]

Bonjour Nerva.

Vous avez communiqué les règles de gestion, c'est très bien.
Il est recommandé de numéroter les règles de gestion, ça facilite les échanges, c'est pourquoi j'ai ajouté ci-dessous un identifiant à chacune de vos règles.

R001 : les particuliers sont nommés clients et les personnels de ménage employés.

- Comme aucun attribut ne les distingue,
je pense qu'une entité unique avec un attribut différenciant l'un de l'autre s'impose.

Fort bien, mais la notion d'entité concerne la modélisation conceptuelle, or vous ne fournissez qu'un modèle tabulaire, de plus, vous avez modélisé deux tables distinctes.
Il est fortement recommandé de commencer par un modèle conceptuel (MCD), les tables n'étant que la conséquence d'un MCD validé

- R002 : une employée peut avoir 1 ou plusieurs clients ; 1 client peut avoir 1 ou plusieurs employés.

Même si dans la vraie vie, on constate qu'il y a en effet bien plus de femmes que d'hommes qui exercent des métiers d'aide à domicile, ne soyons pas sexistes, il convient de généraliser

- R003 : pour qu'une employée se rende chez un client, une affectation doit être créée.
Cette affectation peut être durable ou temporaire,
dans le cas d'un remplacement (maladie, vacances, etc).
À ce propos, il s'agit dont dans l'immense majorité des cas d'affectations "permanentes",
définies par un nombre d'heures à effectuer par semaine.

Il faut préciser si l'affectation est un créneau horaire fixe ou pas.
Par exemple, l'employé E1 se rend-t-il chez le client C1 tous les mardi de 16h à 18h ou bien est il libre d'y aller à raison de deux heures par semaine mais quand il le souhaite ?

- R004 : une affectation temporaire est supprimée dès que la période est terminée
(il n'y a aucun besoin d'historisation).

Admettons, bien que je m'interroge sur la méthode qui sera utilisée pour calculer les salaires en fin de mois si l'historique est supprimé.
Quoi qu'il en soit, cette notion concerne les traitements, elle est donc prématurée à ce stade.

- R005 : une employée ne peut avoir plus d'une affectation simultanément chez le même client.

Que ce soit chez le même client ou pas, un employé ne peut être affecté à un instant "t" qu'une seule fois

- R006 : les plannings sont établis au nom de l'employée. Une employée ne peut avoir qu'un seul planning.

Dans une gestion de planning, il est nécessaire de connaitre les période de disponibilité ou d'indisponibilité pour ne pas affecter un employé à un client pendant ses congés par exemple.

je ne suis pas du tout sûr que la structure soit bonne et j'aimerais avoir votre avis

Il est bien plus facile et bien plus pertinent de critiquer un modèle conceptuel, remettons les choses dans l'ordre, nous verrons plus tard ce qui concerne les tables

[Nerva]

Précisions...

R001 : présentement j'ai effectivement modélisé 2 tables pour des raisons de praticité (une table commune ou deux tables ne font aucune différence dans la question principale).

R003 : les créneaux horaires sont fixés à l'avance et sont quasiment immuables. Certains changements peuvent intervenir, mais ils impliquent évidemment une modification des créneaux chez les clients de l'employé.

R004 : le calcul des salaires est saisi en fonction du nombre d'heures indiqué manuellement par l'employé (et validé par le client) sur une feuille de travail mensuelle (remplie et signée à chaque intervention), sans aucun rapport avec la planification.

R005 : techniquement, rien n'empêche de créer deux plannings au nom du même employé. C'est pourquoi il y a une contrainte d'unicité dans la table correspondante.

R006 : l'emploi du temps est global, généralisé. Pour les cas particuliers (comme un arrêt maladie), tout se fait de manière informelle. Pour les vacances, les dates sont indiquées (hors application), des plannings de remplacement sont créés de manière temporaire.

[escartefigue]

Il s'agit d'un exercice, mais j'ai essayé de me rapprocher de la réalité.

J'ai donc imaginé qu'un client pouvait commander une prestation de ménage, de soins ou autre
C'est pourquoi j'ai ajouté la notion de compétences (savoir faire le ménage n'est pas la même chose que savoir administrer des soins par exemple)
La prestation sera répétée autant de fois que souhaité, c'est l'objet de l'entité-type [intervention]

Et j'ai considéré que chaque employé déclare ses périodes de disponibilité, un traitement pourra automatiser la recopie des lignes si pour un employé les plages horaires sont les mêmes tous les jours.
Evidemment, un employé ne peut être affecté à une intervention que s'il est disponible pour la plage horaire considérée.
En cas d'impondérable, l'employé pourra être remplacé par un autre, d'où l'association REM_

Ce qui donne le MCD suivant




Qui donne le script suivant pour SQL server (puisqu'il semble que ce soit votre SGBD) :

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
CREATE TABLE EMP_employe(
   EMP_ident INT IDENTITY,
   EMP_matricule CHAR(6) NOT NULL,
   EMP_nom VARCHAR(50) NOT NULL,
   EMP_prenom VARCHAR(50) NOT NULL,
   PRIMARY KEY(EMP_ident),
   UNIQUE(EMP_matricule)
);
 
CREATE TABLE CAL_calendrier(
   CAL_date DATE,
   CAL_numjour TINYINT NOT NULL,
   CAL_libjour CHAR(8) NOT NULL,
   CAL_numsem TINYINT NOT NULL,
   CAL_ouvrable BIT NOT NULL,
   PRIMARY KEY(CAL_date)
);
 
CREATE TABLE CLI_client(
   CLI_ident INT IDENTITY,
   CLI_nom VARCHAR(50) NOT NULL,
   CLI_prenom VARCHAR(50) NOT NULL,
   PRIMARY KEY(CLI_ident)
);
 
CREATE TABLE COM_commande(
   CLI_ident INT,
   COM_seq SMALLINT,
   PRIMARY KEY(CLI_ident, COM_seq),
   FOREIGN KEY(CLI_ident) REFERENCES CLI_client(CLI_ident)
);
 
CREATE TABLE CMP_competence(
   CMP_ident INT IDENTITY,
   CMP_code CHAR(4),
   CMP_libelle VARCHAR(128) NOT NULL,
   PRIMARY KEY(CMP_ident),
   UNIQUE(CMP_code)
);
 
CREATE TABLE PRS_prestation(
   CLI_ident INT,
   COM_seq SMALLINT,
   PRS_seq SMALLINT,
   CMP_ident INT NOT NULL,
   PRIMARY KEY(CLI_ident, COM_seq, PRS_seq),
   FOREIGN KEY(CLI_ident, COM_seq) REFERENCES COM_commande(CLI_ident, COM_seq),
   FOREIGN KEY(CMP_ident) REFERENCES CMP_competence(CMP_ident)
);
 
CREATE TABLE PLA_plage(
   CAL_date DATE,
   PLA_seq SMALLINT,
   PLA_hdeb TIME,
   PLA_hfin TIME NOT NULL,
   PRIMARY KEY(CAL_date, PLA_seq),
   FOREIGN KEY(CAL_date) REFERENCES CAL_calendrier(CAL_date)
);
 
CREATE TABLE INT_intervention(
   CLI_ident INT,
   COM_seq SMALLINT,
   PRS_seq SMALLINT,
   INT_seq SMALLINT,
   CAL_date DATE NOT NULL,
   PLA_seq SMALLINT NOT NULL,
   EMP_ident_remplace INT,
   EMP_ident_affecte INT,
   PRIMARY KEY(CLI_ident, COM_seq, PRS_seq, INT_seq),
   FOREIGN KEY(CLI_ident, COM_seq, PRS_seq) REFERENCES PRS_prestation(CLI_ident, COM_seq, PRS_seq),
   FOREIGN KEY(CAL_date, PLA_seq) REFERENCES PLA_plage(CAL_date, PLA_seq),
   FOREIGN KEY(EMP_ident_remplace) REFERENCES EMP_employe(EMP_ident),
   FOREIGN KEY(EMP_ident_affecte) REFERENCES EMP_employe(EMP_ident)
);
 
CREATE TABLE DSP_etre_dispo(
   CAL_date DATE,
   PLA_seq SMALLINT,
   EMP_ident INT,
   PRIMARY KEY(CAL_date, PLA_seq, EMP_ident),
   FOREIGN KEY(CAL_date, PLA_seq) REFERENCES PLA_plage(CAL_date, PLA_seq),
   FOREIGN KEY(EMP_ident) REFERENCES EMP_employe(EMP_ident)
);
 
CREATE TABLE POS_posseder(
   EMP_ident INT,
   CMP_ident INT,
   PRIMARY KEY(EMP_ident, CMP_ident),
   FOREIGN KEY(EMP_ident) REFERENCES EMP_employe(EMP_ident),
   FOREIGN KEY(CMP_ident) REFERENCES CMP_competence(CMP_ident)
);
 
alter table INT_intervention
ADD CONSTRAINT FK_001
FOREIGN KEY(CAL_date, PLA_seq, EMP_ident_affecte)
REFERENCES DSP_etre_dispo(CAL_date, PLA_seq, EMP_ident);

À noter que la contrainte d'inclusion entre AFF_ et POS_ ne pourra pas être vérifiée par une FK, il faudra mettre en jeu un trigger
Script à compléter également de quelques contraintes CHECK pour les dates début/fin
Il faut certainement y ajouter l'adresse du client, ses différents moyens de contact et d'autres infos

[Nerva]

Eh bien je te remercie, je vais étudier ça attentivement.

Je rebondis quand même là-dessus :

Et j'ai considéré que chaque employé déclare ses périodes de disponibilité

Dans la grande majorité des cas, les heures disponibles suivent les heures d'intervention, dans la fourchette Lundi-Vendredi de 8:00 à 19:00. La disponibilité devra plutôt être considérée comme les créneaux libres durant cette fourchette, pendant laquelle un employé pourra se voir proposer d'intervenir chez untel. Bien souvent, ce n'est pas un travail à temps plein et des heures en plus sont les bienvenues.

Il peut y avoir néanmoins des cas particuliers où des heures peuvent avoir lieu le samedi. Là effectivement, définir la disponibilité pour ce jour (ou pour le mercredi après-midi pour celles qui ont des enfants, ou pour le travail entre 12:00 et 13:30) peut être intéressante.

[escartefigue]

Parmi les raisons de déclarer ses dispos auxquelles j'ai pensé, il y a le travail à temps partiel et les congés

[Nerva]

Ton modèle est très complet mais bien trop pour l'usage prévu.

Les plannings sont purement hebdomadaires, quasi-statiques. Les affectations ne sont pas temporaires, sauf remplacements et vacances.

Un (futur) client s'adresse à l'organisme pour qu'un employé intervienne à son domicile. Il demande un certain nombre d'heures par semaine mais ne peut décider des horaires avec exactitude puisqu'il faut tenir compte des disponibilités des différents employés. Par disponibilités, il faut comprendre les creux existants dans l'emploi du temps tel jour de telle heure à telle heure.

À partir du moment où des créneaux sont convenus et contentent les deux parties, ils ne changent généralement plus. Par exemple, Mme Dupont et affectée chez Mr Dupuy le lundi de 14:00 à 17:00.

Chaque mois, chaque employé se voit remettre une feuille de travail (sous forme de tableau, vierge) par client chez qui elle intervient. Ces feuilles sont nominatives (employé/client) et sont remplies au jour le jour à la fin de la période de travail. On y trouve : la date, l'heure de début, l'heure de fin, le total d'heures, la signature du client et de l'employé. À la fin du mois, le salarié remet ses feuilles de travail au service compétent de l'organisme qui établira les fiches de paie et les factures des clients.

Comme les mois ne font pas 28 jours, il peut arriver que d'un mois à l'autre, le nombre d'heures varie : chez Dupuy, Dupont fera 15 heures en mars si le lundi tombe le 1er, par exemple. Tout ceci est stipulé à la signature du contrat entre le client et l'organisme et découle du bon sens.

Après il y a les jours fériés. Ceux-ci sont chômés et rémunérés par l'organisme et ne sont bien entendu pas facturés aux clients, qui ne sont pas des employeurs. Lorsqu'un créneau journalier/horaire tombe un jour férié, les heures ne sont tout simplement pas effectuées. Ne pas oublier qu'il ne s'agit pas d'un organisme gérant des personnes qui ont absolument besoin d'une intervention régulière, comme le feraient des aides-soignantes, mais d'un "simple" service d'aide à domicile (ménage, repassage...).

Cependant, d'un commun accord, client et salarié peuvent décider d'un créneau de remplacement si le salarié a des disponibilités. Ces heures seront inscrites sur la feuille de travail, payées au salarié et facturées au client.

On peut évoquer aussi les arrêts maladie. Si ceux-ci sont de longue durée (au moins une semaine), de manière temporaire, les heures des différents clients seront dispatchées chez d'autres salariés : inscrites dans leurs plannings et supprimées lorsque le salarié en question reprendra le travail. Si il s'agit d'un arrêt de courte durée (un jour ou deux), les heures pourront être rattrapées ou simplement non effectuées cette semaine-là, en fonction, encore, des demandes des clients et des disponibilités du salarié.

Mais que ce soit pour les jours fériés et/ou les heures de "substitution", des arrêts maladie, tout est informel et n'entre pas dans l'établissement des plannings, qui demeurent hebdomadaires, sans tenir compte de la longueur des mois, des jours fériés, des dates ou des périodes de vacances. Car c'est justement une vue d'ensemble claire et précise du planning de chaque salarié qui permet de voir en un coup d'oeil quelles sont les disponibilités de chacun. Et même chose lorsqu'un nouveau client s'inscrit : selon sa localisation (son quartier, sa commune) et connaissant celle des employés, on peut en un instant proposer à tel ou tel employé une nouvelle affectation tel jour de telle heure à telle heure.

Au niveau du temps partiel, connaissant très bien ce milieu, cette "notion" n'existe pas vraiment. La plupart des employées de maison sont à temps partiel et peu refusent des heures en plus lorsqu'on leur en propose. C'est le côté humain de la chose qui ne peut se paramétrer dans une table des disponibilités, qui n'a pas le sens de celui que tu avais suggéré.

Il peut évidemment y avoir des exceptions. Là encore, quand on gère un tel service, on connaît chaque employé et on n'a nullement besoin d'indications écrites pour savoir qui travaille le samedi matin, qui travaille le soir jusque 20 heures ou quelle salariée a absolument besoin de son mercredi après-midi pour ses enfants en bas âge.