Discussion :

[clement.bouillier]

Bonjour,

J'ai un problème de conception sur lequel je me demande quelle est la solution la plus juste...

Imaginons le cas suivant :
- 1 entité BusinessLine
- 1 entité ProductLine en relation 1..n avec BusinessLine (une ProductLine est nécessairement liée à une BusinessLine) -> cela constitue un référentiel (idée de la volumétrie : ~6/7 BusinessLine et 50/60 ProductLine)
- 1 entité CommercialInformation qui peut être reliée à une BusinessLine et/ou à une ProductLine...dont la signification métier est le périmètre de cette information...

La question est si je relie une Information à une ProductLine, alors la BusinessLine doit être celle de la ProductLine...
Si on passe au modèle physique, dans la table Information, je vais avoir une FK vers ProducLine et une FK vers BusinessLine...et pour la table ProductLine, une FK vers BusinessLine.
La question est comment gérer la colonne sur laquelle est la FK BusinessLine de la table Information : dois-je "dupliquer" la valeur de la BusinessLine référencée dans la table ProductLine si une Information référence une ProductLine ?
Ex :
ProductLine(Id,IdBL)
(1,1)
(2,1)
...
et Information(Id,IdBL,IdPL) :
1) tuple (1,1,1) -> avec une "assertion" (au sens modèle relationnel) pour contraindre IdBL
ou 2) tuple (1,null,1) -> avec des vues (ou via le code) pour récupérer la BusinessLine via la ProductLine

Mon dilemme est que 2) fait qu'on met de la "neige" (terme utilisé par fsmrel...François si tu me reconnais) et qu'on "galère" un peu pour récupérer la BusinessLine...et que 1) fait que j'ai l'impression qu'on duplique de la donnée...

Merci de votre éclairage.

Cdlt,
Clément

[Oishiiii]

Bonjour,

Si j'ai bien tout compris, vous avez actuellement le MCD suivant :
BusinessLine -0,N---()---1,1- ProductLine
CommercialInformation -0,1---()---0,N- BusinessLine
CommercialInformation -0,1---()---0,N- ProductLine

Ce qui donnerai le MLD :
BusinessLine(id)
ProductLine(id, id_businessLine)
CommercialInformation(id, id_businessLine, id_productLine)

Une fois en SQL, pour votre première solution il faudra apparemment effectuer un ordre INSERT avec NULL et ensuite corriger le tir avec un Trigger (Assertion).
Pour la deuxième solution, le travail est simplement reporté sur la vue, mais il y aura de la neige dans votre table.

Vous pourriez peut-être opter pour un MLD comme celui-ci:
BusinessLine(id)
ProductLine(id, id_businessLine)
CommercialInformation(id)
InfoProduct(id_productLine, id_commercialInformation)
InfoBusiness(id_business, id_commercialInformation)

Tout risque d'enneigement et ainsi écarté au niveau des tables.
Mais il faudra quand même passer par une vue si vous voulez à tout pris une représentation de ce type : Information(Id,IdBL,IdPL).

Voilà pour ma proposition, je passe la main aux autres en espérant avoir était utile

[CinePhil]

Effectivement avec des cardinalités 0,1 -- 0,n, il faut normalement une table associative, comme dans le cas 0,n -- 0,n.
Donc ici il en faut deux.

[clement.bouillier]

Merci pour vos retours, en effet, je me souviens qu'il faut une table d'association pour les relations 0,1 et 0,N pour éviter la neige...mais mon problème se posait plus entre une solution nécessitant une jointure et une solution sans jointure (mais avec une sorte de "duplication").

Si je repose le problème avec les tables d'association, cela donne :
1) insertion dans les tables InfoBusiness et InfoProduct, sachant qu'il faut alors un trigger qui vérifie que le tuple ajouté dans InfoBusiness est bien cohérent avec ce qui est ajouté dans InfoProduct...ce que j'appelle une sorte de duplication puisque le lien Information-BusinessLine peut-être déduit des relations Information-ProductLine et ProductLine-BusinessLine lorsqu'il y a une ProductLine
2) mettre contrainte d'exclusion entre InfoProduct et InfoBusiness (c'est l'un ou l'autre ou aucun) et déplacer le problème dans la(les) vue(s)...c'est une solution que j'ai déjà expérimenté et j'en suis moyennement content car les requêtes peuvent devenir plus complexes...

Qu'en pensez-vous ?

@Oishiiii : merci pour la clarification du MCD, c'est en effet ça.
Pour la solution 1), j'aurais en effet fais un INSERT puis Trigger, mais j'aurais plutôt inséré un idBusinessLine au lieu de NULL, et fait un trigger qui aurait fait seulement une assertion plutôt qu'une mise à jour de la table, c'est-à-dire vérifier qu'une contrainte est bien remplie (celle qui dit que idBusinessLine d'une information doit être la même que idBusinessLine de la productline liée à l'information).

[fsmrel]

Bonsoir,

Bienvenue à toi Clément.

Mon dilemme est que 2) fait qu'on met de la "neige" (terme utilisé par fsmrel...François si tu me reconnais) et qu'on "galère" un peu pour récupérer la BusinessLine...et que 1) fait que j'ai l'impression qu'on duplique de la donnée...

De fait, dans le 1er cas on produit de la redondance et ça mérite a priori une petite punition, tandis que dans le 2e cas le Bonhomme Null fait son apparition, ce qui vaut d’être traduit devant le Tribunal Relationnel...

Vous pourriez peut-être opter pour un MLD comme celui-ci:
BusinessLine(id)
ProductLine(id, id_businessLine)
CommercialInformation(id)
InfoProduct(id_productLine, id_commercialInformation)
InfoBusiness(id_business, id_commercialInformation)

Tout risque d'enneigement et ainsi écarté au niveau des tables.
Mais il faudra quand même passer par une vue si vous voulez à tout pris une représentation de ce type : Information(Id,IdBL,IdPL).

Hugh ! Oishiiii a bien parlé, puisqu’on évite le Bonhomme Null.

N.B. La clé primaire de la table InfoProduct est réductible, puisqu’il existe la dépendance fonctionnelle :

id_commercialInformation → id_productLine

Même chose pour la table InfoBusiness :

id_commercialInformation → id_business

=>

BusinessLine(id)
ProductLine(id, id_businessLine)
CommercialInformation(id)
InfoProduct(id_commercialInformation, id_productLine)
InfoBusiness(id_commercialInformation, id_businessLine)

mettre contrainte d'exclusion entre InfoProduct et InfoBusiness (c'est l'un ou l'autre ou aucun) et déplacer le problème dans la(les) vue(s)...c'est une solution que j'ai déjà expérimenté et j'en suis moyennement content car les requêtes peuvent devenir plus complexes...

Il est un fait que l’utilisation d’une vue conduit à la mise en œuvre d’un trigger d’exclusion. On peut donc chercher plus simple. Dans la mesure où, par trigger, on insère une ligne dans la table InfoBusiness lors de l’insert d’une ligne dans la table InfoProduct, certes on produit de la redondance, mais il n’y a pas péril en la demeure, puisque le SGBD contrôle totalement les opérations.

Exemple avec SQL Server (cas de l’INSERT) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER Cial_Prod_1 ON InfoProduct INSTEAD OF INSERT AS
  INSERT INTO InfoProduct
    SELECT id_commercialInformation, id_productLine
    FROM   inserted
  ;
  INSERT INTO InfoBusiness
    SELECT x.id_commercialInformation, y.id_businessLine 
    FROM     inserted as x JOIN ProductLine as y
               ON x.id_productLine = y.id_productLine             
  ;

Mais il ne faudra pas oublier de traiter du cas de l’UPDATE et du DELETE.

A noter que si l’on cherche à insérer une clé en double dans la table InfoBusiness (via le trigger ou en direct), on aura droit à un code retour de la part du SGBD, ce qui est la moindre des choses...

[clement.bouillier]

Merci François pour ce retour.

Cependant, techniquement, comme dit à Oishiii, j'aurais plutôt fait l'insertion moi-même dans les 2 tables et mis un trigger sur l'insertion dans ProductLine pour faire une assertion qui lève une erreur du type "Violation de contrainte d'intégrité...".
Ca me semble plus dans l'idée de l'assertion au sens modèle relationnel, non ?

ex:

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER InformationBusinessLineAssertion ON [ProductLine]
FOR INSERT, UPDATE
AS
IF NOT EXISTS (SELECT * FROM BusinessLine bl ON
      JOIN INSERTED i ON r.IdInformation = i.IdInformation 
      JOIN ProductLine pl ON pl.IdProductLine = i.IdProductLine
      WHERE pl.IdBusinessLine = bl.IdBusinessLine)
BEGIN
   RAISERROR ('Violation de contrainte d'intégrité...', 16, 1)
END

Y'a-t-il un(des) soucis à faire un RAISERROR dans un trigger ?

Merci.

Clément