Discussion :

[dg78]

Bonjour,

Une petite question concernant des liaisons entre tables (en sql server 2005).

J’ai les tables suivantes :
- Materiel : MaterielId, Nom ..
- Produit : ProduitId, Nom ..

- Tarif : TarifId, libelle, PrixUnitaire, Unite

Chaque matériel peut avoir plusieurs tarifs. Il en est de même pour chaque produit.
Par contre, un produit n’est applicable qu’à un seul matériel ou à un seul produit.

Il y a donc une relation 1-n entre Materiel-Tarif et entre Produit-Tarif.

Que faire ?

1) avoir une table TarifMateriel et une table TarifProduit au lieu d’une seule table tarif.
Il faut alors mettre une clé étrangère MaterielId dans la table TarifMateriel et une clé étrangère ProduitId dans la table TarifId, on a alors :
- TarifMateriel : TarifId, libelle, PrixUnitaire, Unite, MaterielId
- TarifProduit : TarifId, libelle, PrixUnitaire, Unite, ProduitId

2) avoir une seule table Tarif et des tables de jonctions :
- MatérielTarif : Id, MaterielId, TarifId
- ProduitlTarif : Id, ProduitId, TarifId
De telles tables sont ordinairement utilisées dans le cas de relations n-m ce qui n’est pas le cas ici car les relations sont 1-n

3) l’idéal serait de n’avoir qu’une seule table Tarif comprenant deux clé étrangères MaterielId et ProduitId.
- Tarif : TarifId, libelle, PrixUnitaire, Unite, MaterielId, ProduitId
L'une de ces deux clés serait toujours nulle, le tarif étant applicable soit à un matériel soit à un produit.
Mais, dans ce cas, j’ai bien peur que l’intégrité référentielle ne soit pas respectée à moins qu’il n’y ait une astuce.

Comment procédez-vous dans une telle situation qui est assez fréquente, par exemple Client, Fournisseur et table Adresse ?

Merci par avance pour toutes remarques, idées et .. solutions

Dominique

[fsmrel]

Bonjour Dominique,

Vous écrivez :

Un produit n’est applicable qu’à un seul matériel ou à un seul produit.

Je suppose que l’on doit lire :
Un tarif n’est applicable qu’à un seul matériel ou à un seul produit ?

Votre première solution :
Une table TarifMateriel et une table TarifProduit est un scénario tout à fait acceptable. Si cela a un sens que l’utilisateur veuille voir "tous les tarifs" en même temps, vous pouvez définir une vue d’union. A noter que les deux identifiants TarifId gagneraient à être relatifs à MaterielId et ProduitId.

Votre deuxième solution :
Vous dites que "de telles tables [de jonction] sont ordinairement utilisées dans le cas de relations n-m ce qui n’est pas le cas ici car les relations sont 1-n" : vous avez raison dans le cas général. Mais en l’occurrence, vous avez des liens de type 1-N/0,1 et rien n’interdit de mettre en œuvre les tables MatérielTarif et ProduitlTarif. Simplement, elles ont la structure suivante (clé primaires soulignées) :

MatérielTarif (TarifId, MaterielId)
ProduitlTarif (TarifId, ProduitId)

En notant que l’attribut Id disparaît car il n’apporte rien d’essentiel, aucune valeur ajoutée, c’est du bruit pour rien. TarifId, MaterielId et ProduitId sont aussi, bien entendu, clés étrangères.

Votre troisième solution :
"L'une de ces deux clés [étrangères] serait toujours nulle" est à éviter à tout prix, à cause justement des valeurs nulles, qui sont de la dynamite.

Vous avez une autre voie possible à explorer, celle de l’héritage. Par exemple, au niveau conceptuel, vous définissez Tarif comme surtype. Vous définissez deux sous-types : TarifProduit et TarifMateriel, que vous mettez en relation respectivement avec Produit et Matériel. Quelque part, on a une variante de votre deuxième solution (Cf. pièce jointe 1, MCD avec PowerAMC).

Vous pouvez aussi induire un surtype ProdMat (je ne sais pas trop comment le nommer...) à partir de Produit et de Matériel (vous en factorisez les attributs sémantiquement communs), pour aboutir au MCD de la pièce jointe 2. Vous noterez l’identification relative (1,1 entre parenthèses, côté Tarif) : un tarif est celui d’un produit (ou d’un matériel). Si on supprime le produit (ou le matériel), ses tarifs disparaissent eux aussi.

[dg78]

Merci fsmrel (désolé je n'ai que votre pseudo) pour votre réponse détaillée.

Je suppose que l’on doit lire :
Un tarif n’est applicable qu’à un seul matériel ou à un seul produit ?

Bien vu, effectivement c'était une "coquille".

Si cela a un sens que l’utilisateur veuille voir "tous les tarifs" en même temps

Lors de la création d'un nouveau matériel ou produit (en ado.net), il est important que l'utilisateur puisse voir et créer la totalité des tarifs du matériel ou du produit, de même, lors de la facturation, il est utile qu'il puisse voir les différents tarifs (du matériel ou du produit à facturer) afin de choisir celui à appliquer.

A noter que les deux identifiants TarifId gagneraient à être relatifs à MaterielId et ProduitId.

Comment faire ?

Je ne connais pas la notation MCD, pourriez vous indiquer, pour les solutions 4 et 5, les différentes tables avec leurs clés primaires et étrangères comme vous l'aviez fait pour la solution 2 ?

Il est dommage que la solution 3 ne puisse pas s'appliquer car elle optimise le nombre de tables. N'y a t il pas une astuce pour remplacer les valeurs nulles par des valeurs fictives ?

Votre solution 5 me séduit car je pense qu'il faut factoriser au maximum les attributs communs.

Encore merci

[fsmrel]

Bonsoir Dominique,

En réponse à mon observation :
"Si cela a un sens que l’utilisateur veuille voir tous les tarifs en même temps", vous écrivez :

il est important que l'utilisateur puisse voir et créer la totalité des tarifs du matériel ou du produit

En fait, ce que j’ai écrit doit se lire ainsi :
Si cela a un sens que l’utilisateur veuille voir simultanément l’ensemble des tarifs, qu’ils soient ceux des matériels aussi bien que des produits.
A mon tour, j’interprète votre réponse de la façon suivante : à un instant donné, l’utilisateur traite d’un matériel (ou d’un produit, mais pas les deux à la fois) et de l’ensemble des tarifs qui s’y rattachent. Si mon interprétation est correcte, considérez que votre première solution ne me paraît pas poser de problème.

Je ne connais pas la notation MCD

Un MCD est un modèle conceptuel de données. La production d’un MCD est incontournable pour édifier l’architecture d’une base de données. Ne pas en passer par un MCD revient à construire un immeuble sans en faire le plan. Je ne saurais trop vous conseiller de consulter les tutoriels Merise.
http://merise.developpez.com/faq/

Vous pouvez aussi charger l’ouvrage de Michel Diviné
http://michel.divine.free.fr/Livre.htm

Faites-vous offrir par votre entreprise l’ouvrage le plus complet, celui de Dominique Nanci et Bernard Espinasse : "Ingénierie des systèmes d'information : Merise - Deuxième génération".

Il est dommage que la solution 3 ne puisse pas s'appliquer car elle optimise le nombre de tables.

La réduction du nombre de tables est un argument purement émotionnel. A contrario, j’ai procédé à des audits suite auxquels le nombre de tables a été considérablement revu à la hausse (modèles trop "repliés"). J’ai mis en œuvre des bases de données comportant de mille à mille cinq cents tables : aucun problème. Évidemment, le système de sauvegarde mérite une gestion extrêmement rigoureuse, mais du point de vue des applications : ça marche parfaitement.

Prenons l’exemple des tarifs : Supposons que vous ayez cent mille tarifs pour les matériels et autant pour les produits. Si vous n’avez qu’une table, tous les utilisateurs accèdent à cette table alors qu’à un instant donné untel met à jour les tarifs d’un matériel et son voisin ceux d’un produit (cf. hypothèse ci-dessus) : avec deux tables distinctes, que les tarifs des matériels soient isolés de ceux des produits est en l’occurrence une bonne chose (contentions moins fréquentes). En outre, les table spaces et les index seront radicalement moins volumineux (mieux vaut une table de cent mille lignes qu’une table de deux cents mille lignes), argument qui a son intérêt (hauteur des B-trees plus réduite, donc moins d’entrées/sorties, sauvegardes en parallèle, etc.)

En revanche, votre argument "je pense qu'il faut factoriser au maximum les attributs communs" est recevable et dans ces conditions, à vous de voir si vous privilégiez la réduction de la volumétrie des tables et la réduction des contentions ou bien la non redondance des structures (cas de la solution 5).

A la question :

A noter que les deux identifiants TarifId gagneraient à être relatifs à MaterielId et ProduitId.

Comment faire ?

Je réponds en prenant l’exemple des matériels :

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
 
Create Table Materiel
  (MaterielId     Integer,
   Nom            Varchar(48),
   ...                       ,
  Primary Key (MaterielId)
  ) ;
 
Create Table MaterielTarif
  (MaterielId     Integer,
   TarifId        Integer,
   Libelle        Varchar(48),
   PrixUnitaire   Integer,
   ...                   ,
  Primary Key  (MaterielId, TarifId,)
  Foreign Key  (MaterielId) References Materiel
                            On Delete Cascade

La clé primaire de la table MaterielTarif est composée des colonnes MaterielId et TarifId. TarifId est en fait un séquenceur, dont la numérotation commence à 1 pour chaque matériel. Pour sa part, la colonne MaterielId de la table MaterielTarif est à la fois clé étrangère et composant de la clé primaire de cette table.

N'y a t il pas une astuce pour remplacer les valeurs nulles par des valeurs fictives ?

Concernant le cas qui vous intéresse, c’est-à-dire celui des clés étrangères, il y a bien une bidouille, consistant à créer un matériel et un produit fictifs. Mais, le Modèle Relationnel de Données et le bricolage ne font pas bon ménage. La seule solution valable : ne pas donner l’occasion aux valeurs nulles de se manifester. Je reconnais que ça n’est pas toujours facile.

Voyez à ce sujet la discussion ouverte par nat-0-0, "Entre la théorie et la pratique"
http://www.developpez.net/forums/sho...d.php?t=267568


Traduction des MCD

Solution 4

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
 
Create Table Materiel (
   MaterielId           Int                  Not null,
   Libelle              Char(48)             Not null,
   Constraint PK_Materiel Primary Key  (MaterielId)
)
;
 
Create Table Produit (
   ProduitId            Int                  Not null,
   Libelle              Char(48)             Not null,
   Constraint PK_Produit Primary Key  (ProduitId)
)
;
 
Create Table Tarif (
   TarifId              Int                  Not null,
   Libelle              Char(48)             Not null,
   PrixUnitaire         Int                  Not null,
   Constraint PK_Tarif Primary Key  (TarifId)
)
;
 
Create Table TarifMateriel (
   TarifId              Int                  Not null,
   MaterielId           Int                  Not null,
   AttributSpecifMat    Char(48)             Not null,
   Constraint PK_TarifMateriel Primary Key  (TarifId),
   Constraint FK_TarifMat_Materiel Foreign Key (MaterielId)
      References Materiel (MaterielId)
         On Delete Cascade,
   Constraint FK_TarifMat_Tarif Foreign Key (TarifId)
      References Tarif (TarifId)
         On Delete Cascade
)
;
 
Create Table TarifProduit (
   TarifId              Int                  Not null,
   ProduitId            Int                  Not null,
   AttributSpecifProd   Char(48)             Not null,
   Constraint PK_TarifProduit Primary Key  (TarifId),
   Constraint FK_TarifPro_Produit Foreign Key (ProduitId)
      References Produit (ProduitId)
         On Delete Cascade,
   Constraint FK_TarifPro_Tarif Foreign Key (TarifId)
      References Tarif (TarifId)
         On Delete Cascade
)
;

[Solution 5

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
 
Create Table ProdMat (
   ProdMatId            Int                  Not null,
   Libelle              Char(48)             Not null,
   Constraint PK_ProdMat Primary Key  (ProdMatId)
)
;
 
Create Table Produit (
   ProdMatId            Int                  Not null,
   Libelle              Char(48)             Not null,
   AttrSpecifProd       Char(48)             Not null,
   Constraint PK_Produit Primary Key  (ProdMatId),
   Constraint FK_Produit_ProdMat Foreign Key (ProdMatId)
      References ProdMat (ProdMatId)
         On Delete Cascade
)
;
Create Table Materiel (
   ProdMatId            Int                  Not null,
   Libelle              Char(48)             Not null,
   AttrSpecifMat        Char(48)             Not null,
   Constraint PK_Materiel Primary Key  (ProdMatId),
   Constraint FK_Materiel_ProdMat Foreign Key (ProdMatId)
      References ProdMat (ProdMatId)
         On Delete Cascade
)
;
 
Create Table Tarif (
   ProdMatId            Int                  Not null,
   TarifId              Int                  Not null,
   Libelle              Char(48)             Not null,
   PrixUnitaire         Char(48)             Not null,
   Constraint PK_Tarif Primary Key  (ProdMatId, TarifId),
   Constraint FK_Tarif_ProdMat Foreign Key (ProdMatId)
      References ProdMat (ProdMatId)
         On Delete Cascade
)
;

[dg78]

Merci beaucoup pour vos précieux conseils.

Cordialement