Discussion :

[UrdaUrda]

Bonjour , je viens vers vous car je "bloque" sur un problème au niveau conceptuel.

Voila mon problème , je doit faire un intranet pour une pme qui gère des lancement de campagne publicitaire donc avec des partenaires (entreprise) et des contact dans ces entreprises.

J'ai donc modéliser comme ceci

CAMPAGNE -1,1-- (LANCER) --1,n-CONTACT
|............................................|
1,1.........................................1,1
|............................................|
(PROPOSER)..........................(TRAVAILLER)
|............................................|
1,n.........................................1,n
|............................................|
ENTREPRISE----------------------

Cela vous semble t'il correct ?

Il faut pouvoir gerer les changement d'entreprise d'un contact.

Merci de votre aide.

Urda

[CinePhil]

Un peu léger comme cahier des charges !
Ce que je lis :
- Une campagne est lancée par un contact et un contact lance de une à plusieurs campagnes.
- Un contact travaille dans une entreprise et une entreprise fait travailler de un à plusieurs contacts.
- Une campagne est proposée par une entreprise et une entreprise propose de une à plusieurs campagnes.

Si ces règles de gestion correspondent à votre besoin, votre MCD est correct.

Il faut pouvoir gerer les changement d'entreprise d'un contact.

Qu'entendez-vous par là ?
- Simplement modifier l'entreprise du contact dans la table Contacts ?
- Mémoriser les changements d'entreprise des contacts donc archiver les anciennes informations ?

[fsmrel]

Bonsoir,

Je me joins à CinePhil pour poser la question : Qu’entendez-vous précisément par changement d’entreprise d’un contact ?

Indépendamment de cela, votre MCD comporte une faille. En effet, rien n’interdit que l’entreprise e1 propose la campagne c1, laquelle sera lancée par le contact k1 travaillant pour l’entreprise e2.

Étudions plus avant le problème soulevé. Pour des raisons de commodité, illustrons les choses à l’aide de valeurs dans des tables, au niveau du MLD.

Procédons d’abord à l’abréviation des noms :

E pour ENTREPRISE
C pour CAMPAGNE
K pour CONTACT
Eid pour l’attribut identifiant l’entreprise
Ers pour la raison sociale de l’entreprise
Cid pour l’attribut identifiant la campagne
Clb pour le libellé de la campagne
Kid pour l’attribut identifiant le contact
Knm pour le nom du contact.

En fait, on peut étudier le problème à partir de trois scénarios.

Scénario 1, directement dérivé du MCD (clés primaires soulignées) :

Code :

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

1
2
3
4
5
6
E {Eid, Ers}
   e1   DVP
   e2   HAL

C {Cid, Clb,  Eid, Kid}         K {Kid, Knm,    Eid}
   c1   cmp1  e1   k1              k1   Ockham  e2

Une solution consisterait à ajouter une contrainte (sous forme d’un trigger SQL) permettant de garantir que, lorsqu’on insère ou remplace une valeur du couple {Eid, Kid} dans la table C, cette valeur existe préalablement dans la table K. En l’occurrence, on garantit donc la pertinence des redondances entre tables.

On peut aussi évacuer ce genre de redondance, par exemple en supprimant l’association-type TRAVAILLER :

Scénario 2

Code :

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

1
2
3
4
5
6
E {Eid, Ers}
   e1   DVP
   e2   HAL

C {Cid, Clb,  Eid, Kid}         K {Kid, Knm}
   c1   cmp1  e1   k1              k1   Ockham

Il n’y a plus de redondance de couple {Eid, Kid}. Si l’on a besoin de savoir qui est le contact de la campagne c1 proposée par l’entreprise e1, on sait qu’il s’agit de M. Ockham. Mais il y a un gros inconvénient : tant que M. Ockham n’a pas lancé de campagne, il est impossible de savoir pour quelle entreprise travaille ce contact.

Une autre solution consisterait à supprimer l’association-type PROPOSER :

Scénario 3

Code :

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

1
2
3
4
5
6
E {Eid, Ers}
   e1   DVP
   e2   HAL

C {Cid, Clb,  Kid}         K {Kid, Knm,    Eid}
   c1   cmp1  k1              k1   Ockham  e2

Comme précédemment, on évacue les redondances entre tables, mais pour connaître les campagnes proposées par une entreprise, il faudra d’abord que les contacts aient été créés.

Autour de la 3e forme normale (3NF)

Revenons au 1er scénario. La table K comporte notamment la DF suivante : {Kid} → {Eid} et du point de vue de la normalisation, celle-ci est tout à fait légale, puisque {Kid} est clé candidate (même chose dans le cas du scénario 3).
Par contre, dans le cas des scénarios 1 et 2, la table C comporte elle aussi cette DF, mais Kid n’y est pas clé candidate, c'est-à-dire que l'on enfreint la 3NF. Dans le cas de l’exemple 1, l’infraction est acceptable, car couverte si l’on a prévu la contrainte garantissant la cohérence des couples {Eid, Kid} entre les tables C et K. Dans le cas du scénario 2, l’infraction n’est acceptable que si l’on prévoit la mise en œuvre d’une contrainte garantissant la DF {Kid} → {Eid}. A défaut, il faut normaliser et l’application du théorème de Heath¹ conduit en fait au scénario 3.

Dans tout cela, à vous d’étudier quelle solution vous paraît satisfaisante, sachant que le respect de la 3NF n’est pas obligatoire, dès lors que l’on met en œuvre la contrainte (trigger SQL) garantissant la cohérence des redondances.

¹ Théorème de Heath

Soit la table T {A, B, C} dans laquelle A, B et C sont des ensembles d’attributs de T. Si T satisfait à la dépendance fonctionnelle A → B, alors T est égale à la jointure de ses projections sur {A, B} et {A, C}.

[CinePhil]

C'est sûr que quand il y a des 1 partout en cardinalité minimale, on est un peu coincé ! Même s'il existe parait-il des systèmes pour implanter ça (je n'ai encore jamais eu à le faire).

Pour créer une nouvelle campagne, il faut qu'un contact existe.
Pour que le contact existe, il faut que son entreprise existe.
Pour que l'entreprise existe, il faut que la campagne existe !

C'est le serpent qui se mord la queue à force de se demander qui, de l'oeuf ou de la poule, a commencé !

[fsmrel]

Bonne remarque, des cardinalités 0,N seraient les bienvenues, mais acceptons que les 1,N soient effectivement à mettre en oeuvre.
Selon la norme SQL, pour faire respecter la loi, on peut coder des choses comme :

Code :

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

1
2
3
4
5
6
7
CREATE ASSERTION A1 CHECK 
    (NOT EXISTS (SELECT * 
                 FROM   E 
                 WHERE NOT EXISTS (SELECT *   
                                    FROM  K
                                    WHERE E. Eid = K. Eid))) 
      DEFERRABLE INITIALLY DEFERRED ;

Maintenant, si votre SGBD n’est pas à niveau...


Ensuite, appliquée aux contraintes référentielles, la clause DEFERRABLE de la norme SQL/92 est disponible avec certains SGBD (Oracle, PostgreSQL, ...) et permet de résoudre le problème de l’œuf et de la poule en les mettant dans le même panier. SQLpro pourra tout vous expliquer à ce sujet.

Maintenant, si votre SGBD n’est pas à niveau...

Pour ce qu'il en est du Modèle Relationnel de Données, il y a longtemps là aussi que le problème est résolu, et de façon archi simple.
Pour reprendre mon scénario 1, on code par exemple (peu importe l’ordre des instructions, on est dans un contexte ensembliste...) :

Code :

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

1
2
3
4
5
6
7
INSERT E RELATION {TUPLE {Eid CHAR 'e1', Ers CHAR 'DVP'}} ,
INSERT E RELATION {TUPLE {Eid CHAR 'e2', Ers CHAR 'HAL'}} ,
INSERT C RELATION {TUPLE {Cid CHAR 'c1', Clb CHAR 'cmp1', Eid CHAR 'e1', Kid CHAR 'k1'}} ,
INSERT C RELATION {TUPLE {Cid CHAR 'c2', Clb CHAR 'cmp2', Eid CHAR 'e2', Kid CHAR 'k2'}} ,
INSERT K RELATION {TUPLE {Kid CHAR 'k1', Knm CHAR 'Loulou', Eid CHAR 'e2'}} ,
INSERT K RELATION {TUPLE {Kid CHAR 'k2', Knm CHAR 'Ockham', Eid CHAR 'e1'}} ;

Les virgules (en rouge) sont utilisées pour séparer les instructions et le point-virgule pour marquer la fin d’un ensemble d’instructions. C'est ce point-virgule qui indique au SGBD qu’il peut, seulement à partir de là, effectuer tous les contrôles d’intégrité qu’il veut en relation avec ce paquet d’INSERT. La virgule sépare donc indifféremment les œufs et les poules, le point-virgule marquant pour sa part le fond du panier. L’omelette finale {E, K, C} est à la charge du SGBD.