Discussion :

[Kropernic]

Bonjour à tous,

Après quelques mois passés à administrer et développer dans mon coin (fort des connaissances acquises via ce forum), je reviens vers vous car j'ai reçu un projet de gestion de planning et que je sais que les composantes temporelles sont particulières à modéliser (il me semble que ce sont les 5NF et 6NF qui s'occupent de cela).

Voici ce que j'ai tiré comme règles de gestion de ce que l'on m'a expliqué (aucun cahier des charges bien entendu ):

Règles de gestion
1. Une personne exerce une fonction et une fonction peut être exercée par plusieurs personnes.
2. Une personne est employée par un magasin et un magasin emploie plusieurs personnes.
3. Un magasin peut ouvrir plusieurs jours et un jour peut « être ouvert » par plusieurs magasins.
4. Un magasin définit son « budget » (prévision du CA) pour chaque jour et le budget de chaque jour est défini par un magasin.
5. Le budget journalier est précisé par plusieurs heures et une heure précise un budget journalier.
6. Un horaire est défini par un type d’horaire et un type d’horaire peut définir plusieurs horaires.
7. Un magasin « possèdent » plusieurs jours de livraisons et un jour de livraison peut être « possédé » par plusieurs magasins.
8. Une personne peut avoir des absences et une absence fait référence à une seule personne
9. Une absence est caractérisée par un type et un type peut caractériser plusieurs absences.
10. Une personne peut travailler plus ou moins d’heures que stipulé dans son contrat pour une semaine précise.
11. Une personne fait partie d’une seule équipe et une équipe se compose d’au moins un membre
12. Une équipe est dirigée par un chef d’équipe et un chef d’équipe dirige au moins une équipe.
13. Des limites d’horaire journalières sont définies par le chef d’équipe.

N.B. : Je ne suis pas arrivé à formuler certaines règles de manière "conventionnelle".

Et voici également (voir pièce jointe), ce que j'en ai tiré comme MLD.

A priori, cela à l'air de tenir la route mais il y a probablement des choses à améliorer pour éviter certains problèmes inhérents à la gestion de données temporelles.
N.B. : Certains me diront que la règle de gestion 12 n'est pas modélisée et qu'il manque la table des chefs d'équipe. J'ai choisi de la modélisée en tant que telle mais de l'intégrer à la table des équipes. La personne référencée par une équipe est donc le chef de la-dite équipe.

P.S. : L'achat de PowerDesigner a été approuvé et je devrais donc bientôt être en mesure de fournir un MCD plutôt qu'un MLD et dans lequel ou pourra distinguer quelles sont les clefs alternatives

[JPhi33]

Bonjour Kropernic,

Certaines règles de gestion méritent des précisions, d'où les questions ci-dessous.

3. Un magasin peut ouvrir plusieurs jours et un jour peut « être ouvert » par plusieurs magasins.

S'agit-il des jours de la semaine (lundi, mardi, ...) ou bien de jours calendaires (23/09/2013, 24/09/2013, etc.) ?

Dans un cas comme dans l'autre, la table T_OUVERTURE_OUV oblige à mettre en place des contrôles pour éviter les redondances. Par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
OUV_ID OUV_DATE   STR_ID
------ ---------- ------
     1 23/09/2013 store1
     2 23/09/2013 store2
     3 24/09/2013 store1
     4 24/09/2013 store2
     5 23/09/2013 store1
     6 23/09/2013 store1

La règle 3 se modélise normalement comme ceci :

Code :

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

1
2
[ STORE ]--0,n----( OUVERTURE )----0,n--[ JOUR ]

dont le MLD dérivé est :

STORE (STR_ID, ...)
JOUR (JOUR_ID, ...)
OUVERTURE (STR_ID, JOUR_ID, ...)

Les attributs OUV_DEBUT et OUV_FIN dépendent de JOUR si tous les magasins ouvrent et ferment à la même heure pour un jour donné. Sinon, ils dépendent d'OUVERTURE.

4. Un magasin définit son « budget » (prévision du CA) pour chaque jour et le budget de chaque jour est défini par un magasin.

Autrement dit, si le magasin store1 définit son budget le 23/09/2013, le magasin store2 ne peut pas définir son budget ce même jour ?

Il existe la même possibilité de redondance dans la table T_BUDGET_BDG que dans la table T_OUVERTURE_OUV :

Code :

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

1
2
3
4
5
6
7
BDG_ID BDG_DATE   BDG_MONTANT STR_ID
------ ---------- ----------- ------
     1 23/09/2013     5000,00 store1
     2 23/09/2013    10000,00 store1
     3 23/09/2013     3000,00 store1
     4 23/09/2013    23000,00 store1

5. Le budget journalier est précisé par plusieurs heures et une heure précise un budget journalier.

Quel est l'objet de cette précision par heure ? Est-ce un découpage de la prévision du CA dans la journée ? Ne manque-t-il pas un montant dans la table T_BUDGET_HEURE_BDH ?

10. Une personne peut travailler plus ou moins d’heures que stipulé dans son contrat pour une semaine précise.

Ici aussi, la table T_HEURE_SEMAINE_HSE autorise des redondances à contrôler (par trigger ou par l'applicatif). La modélisation classique ci-dessous permettrait de s'en affranchir.

Code :

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

1
2
[ PERSONNE ]--0,n----( ACTIVITE )----0,n--[ SEMAINE ]

donnant le MLD dérivé :

PERSONNE (PRS_ID, PRS_HEURE_CONTRAT, ...)
SEMAINE (SEMAINE_ID, ...)
ACTIVITE (PRS_ID, SEMAINE_ID, nombre_heures_travaillees, ...)

11. Une personne fait partie d’une seule équipe et une équipe se compose d’au moins un membre

Dans cette règle, le terme "membre" ne signifie-t-il pas "personne" ? Si oui, la table T_MEMBRE_MEM est à éliminer.

[Kropernic]

Bonjour Kropernic,

Certaines règles de gestion méritent des précisions, d'où les questions ci-dessous.

Bonjour JPhi33,

Tout d'abord, merci d'avoir pris la peine de regarder à ceci et d'avoir poster vos remarques.

Concernant les règles de gestion, vous avez raison... Quand on est dans le bain, on ne se rend pas toujours compte que, de l'extérieur, ce n'est pas forcément clair.

S'agit-il des jours de la semaine (lundi, mardi, ...) ou bien de jours calendaires (23/09/2013, 24/09/2013, etc.) ?

Il s'agit de jours calendaires. D'où la colonne OUV_DATE dans la table T_OUVERTURE_OUV.

Dans un cas comme dans l'autre, la table T_OUVERTURE_OUV oblige à mettre en place des contrôles pour éviter les redondances. Par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
OUV_ID OUV_DATE   STR_ID
------ ---------- ------
     1 23/09/2013 store1
     2 23/09/2013 store2
     3 24/09/2013 store1
     4 24/09/2013 store2
     5 23/09/2013 store1
     6 23/09/2013 store1

La règle 3 se modélise normalement comme ceci :

Code :

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

1
2
[ STORE ]--0,n----( OUVERTURE )----0,n--[ JOUR ]

dont le MLD dérivé est :

STORE (STR_ID, ...)
JOUR (JOUR_ID, ...)
OUVERTURE (STR_ID, JOUR_ID, ...)

Les attributs OUV_DEBUT et OUV_FIN dépendent de JOUR si tous les magasins ouvrent et ferment à la même heure pour un jour donné. Sinon, ils dépendent d'OUVERTURE.

Le MLD présenté par MySqlWorkBench ne présente malheureusement pas les clefs alternatives "matérialisées" par un index unique sur certaines colonnes (à moins qu'il y ait une option que je n'ai pas encore trouvée).
Sur cette table, il y a une clef alternative sur les colonnes OUV_DATE et STR_ID, ce qui empêche donc la redondance dont vous parler ci-dessus.

Par contre, votre lecture me fait prendre conscience que mon diagramme n'est effectivement pas en phase avec la règle de gestion que j'ai formulée. Cependant, il rend bien compte de ce que j'ai voulu exprimé.
Les différents magasins n'ouvrent pas forcément les mêmes jours (en pratique, cela devrait se limité dans 99% des cas à ce que certains ouvrent certains dimanches et d'autres non) et ont des heures d'ouverture/fermeture différentes les uns des autres et changeantes suivant les jours également.

Autrement dit, si le magasin store1 définit son budget le 23/09/2013, le magasin store2 ne peut pas définir son budget ce même jour ?

En fait, on retombe sur le même problème qu'avec la règle 3. Autant l'avouer tout de suite, j'ai formulé les règles de gestion à postériori alors que je gribouillais mon MLD sur papier pendant que l'équivalent de la MOA m'expliquait ce qu'il fallait et répondait à mes questions. Car lui (la MOA) formuler une règle de gestion et lui de la valider n'aurait servi à rien.

Il existe la même possibilité de redondance dans la table T_BUDGET_BDG que dans la table T_OUVERTURE_OUV :

Code :

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

1
2
3
4
5
6
7
BDG_ID BDG_DATE   BDG_MONTANT STR_ID
------ ---------- ----------- ------
     1 23/09/2013     5000,00 store1
     2 23/09/2013    10000,00 store1
     3 23/09/2013     3000,00 store1
     4 23/09/2013    23000,00 store1

Comme pour la table ouverture, il existe ici aussi une clef alternative sur les colonnes BDG_DATE et STR_ID. Exit donc la redondance possible.

Quel est l'objet de cette précision par heure ? Est-ce un découpage de la prévision du CA dans la journée ? Ne manque-t-il pas un montant dans la table T_BUDGET_HEURE_BDH ?

Mais où donc passé cette colonne montant ?! Il s'agit effectivement d'une précision du budget de la journée en fonction de l'heure du jour. Et il manque donc bien sûr une colonne pour le montant. C'est pas mal ça... J'ai probablement du faire une fausse manoeuvre qui l'aura effacée.

Ici aussi, la table T_HEURE_SEMAINE_HSE autorise des redondances à contrôler (par trigger ou par l'applicatif). La modélisation classique ci-dessous permettrait de s'en affranchir.

Et encore ce problème de clef alternative non-visualisée sur le diagramme... Je suis vraiment navré pour cela... Il y a donc ici une clef alternative sur les colonnes HSE_NUMERO et PRS_ID afin d'éviter la redondance de semaine pour une même personne. Mais plus important, il manque une colonne pour l'année !!! Sinon on va des problèmes après la première année de production . Chose que j'ai remarquée lorsque j'ai commencé à créer la DB dans SQL Serveur.

Code :

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

1
2
[ PERSONNE ]--0,n----( ACTIVITE )----0,n--[ SEMAINE ]

donnant le MLD dérivé :

PERSONNE (PRS_ID, PRS_HEURE_CONTRAT, ...)
SEMAINE (SEMAINE_ID, ...)
ACTIVITE (PRS_ID, SEMAINE_ID, nombre_heures_travaillees, ...)

Dans cette règle, le terme "membre" ne signifie-t-il pas "personne" ? Si oui, la table T_MEMBRE_MEM est à éliminer.

En effet. Je ne devais pas avoir les yeux en face des trous à ce moment là. Mais du coup, je vais me retrouver avec deux entités qui seront reliées deux fois. En pratique, je vais donc avoir la colonne PRS_ID dans la table T_EQUIPE_EQU afin de renseigner qui est le chef d'équipe et la colonne EQU_ID dans la table T_PERSONNE_PRS afin de renseigner de quelle équipe fait partie la personne. C'est une situation que je n'ai encore jamais rencontrée. Y a-t-il des spécificités dues à cette double relation ?


N.B. : Dès que je trouve 5 min, je apporterai des précisions quand aux clefs alternatives. Car vous en avez relevez quelques unes mais il y en a bien d'autres... Vivement que ma licence de PowerAMC arrive !

[Kropernic]

Juste un rapide message concernant les équipes. J'ai finalement préféré ajouter une entité CHEF qui HERITE de PERSONNE afin que les choses soient clair et d'éviter d'avoir une double relation entre les entités EQUIPE ET PERSONNE. Les trucs circulaires, ça sent pas bon en général.

En pièce jointe, la partie du diagramme concernant cela.

[fsmrel]

Bonjour,

Le MLD présenté par MySqlWorkBench ne présente malheureusement pas les clefs alternatives "matérialisées" par un index unique sur certaines colonnes (à moins qu'il y ait une option que je n'ai pas encore trouvée).

Contrairement à PowerAMC qui permet au 1er coup d’oeil de connaître les clés alternatives (symbole <ak>), avec MWB il n’y a effectivement rien d’immédiat pour les signaler. Le mieux est d’en passer par l’onglet « indexes » :



Désolé de ne pas rester en votre compagnie et celle de Jean-Philippe, mais j’ai plein de fers au feu.

A bientôt j’espère,

François

[Kropernic]

Bonjour,




Contrairement à PowerAMC qui permet au 1er coup d’oeil de connaître les clés alternatives (symbole <ak>), avec MWB il n’y a effectivement rien d’immédiat pour les signaler. Le mieux est d’en passer par l’onglet « indexes » :



Désolé de ne pas rester en votre compagnie et celle de Jean-Philippe, mais j’ai plein de fers au feu.

A bientôt j’espère,

François

Je les indique effectivement au moyen d'un index unique mais elles ne sont pas représentée sur le diagramme....

Pas de souci, je sais que vous êtes fort occupé ;-)

[fsmrel]

Un petit logiciel du genre PAINT peut aider...



Bon, j’arrête de blaguer.

[Kropernic]

C'est pas bien de se moquer

[JPhi33]

Bonjour Kropernic,

La présence des clés alternatives rend évidemment caduques mes remarques portant sur les redondances. Cependant, pourquoi ne pas choisir directement ces clés alternatives uniques comme clés primaires ?

Prenons l'exemple de la table T_OUVERTURE_OUV. Une clé primaire composée de {STR_ID, OUV_DATE} serait parfaitement valide, me semble-t-il. Elle garantirait autant l'unicité de chaque ligne (et pour cause puisqu'elle est clé alternative unique) que la clé OUV_ID. De plus, le MLD pourrait être considéré comme une dérivation d'une modélisation conceptuelle "classique" comme ci-dessous.

Code :

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

[ STORE ]--0,n----( OUVERTURE )----0,n--[ DATE ]

DATE étant une pseudo-entité temporelle (appelée "propriété spatio-temporelle" par certains auteurs) ne génère pas de table dans le MLD.

Tu y gagnerais... une colonne, et une (très légère) amélioration de la lisibilité du schéma. Tu n'y gagnerais ni ne perdrais rien en terme de complexité des requêtes. En revanche, je ne saurais évaluer l'impact sur les performances (la gestion interne des dates dans la base de données y étant probablement impliquée), et là, je m'en remets à ton expertise de DBA.

J'ai finalement préféré ajouter une entité CHEF qui HERITE de PERSONNE

La multiplicité côté CHEF devrait donc être 0..1 et non pas 1..*.

[Kropernic]

Bonjour Kropernic,

La présence des clés alternatives rend évidemment caduques mes remarques portant sur les redondances. Cependant, pourquoi ne pas choisir directement ces clés alternatives uniques comme clés primaires ?

Prenons l'exemple de la table T_OUVERTURE_OUV. Une clé primaire composée de {STR_ID, OUV_DATE} serait parfaitement valide, me semble-t-il. Elle garantirait autant l'unicité de chaque ligne (et pour cause puisqu'elle est clé alternative unique) que la clé OUV_ID. De plus, le MLD pourrait être considéré comme une dérivation d'une modélisation conceptuelle "classique" comme ci-dessous.

Code :

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

[ STORE ]--0,n----( OUVERTURE )----0,n--[ DATE ]

DATE étant une pseudo-entité temporelle (appelée "propriété spatio-temporelle" par certains auteurs) ne génère pas de table dans le MLD.

Tu y gagnerais... une colonne, et une (très légère) amélioration de la lisibilité du schéma. Tu n'y gagnerais ni ne perdrais rien en terme de complexité des requêtes. En revanche, je ne saurais évaluer l'impact sur les performances (la gestion interne des dates dans la base de données y étant probablement impliquée), et là, je m'en remets à ton expertise de DBA.

Oui tu as raison... C'est juste que le fait d'ajouter un identifiant non significatif est tellement devenu un réflexe que je le fais partout .

Par contre, niveau performance, je n'ai pas encore mis les mains assez dans le cambouis pour pouvoir me prononcer plus que toi :-/

Mais puisqu'on aborde le côté physique, à ce niveau-là, travaillant avec sql serveur, on y gagnerait également un index.

La multiplicité côté CHEF devrait donc être 0..1 et non pas 1..*.

Bien vu ! J'oublie tjs de mettre à les relations dans mysqlworkbench...