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

[fsmrel]

Salve,



Dans cette affaire, la contrainte de base de données du Modèle Relationnel de Données (Database constraint, faisant l’objet de l’instruction CONSTRAINT) et son pendant SQL, l’assertion, sont là pour garantir les règles de gestion des données, de façon déclarative. Si le SGBD ne nous offre que des triggers pour y parvenir (hélas ! dirons-nous, car un trigger a une raison d’être autre qu’assurer l’intégrité), on fera avec. Je rappelle qu’un trigger spécifie un événement, une condition et une action. Tu me diras que, qui peut le plus peut le moins, on fait avec ce qu’on a, mais c’est un peu le mariage de la carpe et du lapin.

Cela dit, la solution Bouillier et la solution fsmrel sont à mon sens plus une affaire de goût. Dans le 1er cas, il ne faudra pas oublier de créer le doublon dans la table InfoBusiness, et documenter le dossier concernant le message d’anomalie 'Violation de contrainte d'intégrité...', alors que dans le 2e cas, on laisse le système créer la redondance, sous le capot, et s’occuper des injures.

N.B. Ton trigger devrait porter sur la table InfoProduct plutôt que sur la table ProductLine.

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
CREATE TRIGGER InformationBusinessLineAssertion ON InfoProduct
FOR INSERT, UPDATE AS
    IF NOT EXISTS
      ( 
       SELECT  x.id_commercialInformation, y.id_productLine, x.id_businessLine  
       FROM      InfoBusiness AS x
              JOIN
                 Inserted AS y
                    ON x.id_commercialInformation = y.id_commercialInformation
              JOIN
                 ProductLine AS z
                    ON  y.id_productLine = z.id_productLine
                    AND z.id_businessLine = x.id_businessLine
      )
     BEGIN
        RAISERROR ('Violation de contrainte d''intégrité...', 16, 1)
     END

Ne pas oublier non plus de s'occuper de la cohérence des tables InfoBusiness et InfoProduct en cas de suppression ou de modification dans l’une ou l’autre table.

[clement.bouillier]

En effet, c'est bien sur InfoProduct que je voulais mettre le trigger.

Je me souvenais très bien du rappel concernant les assertions au sens du modèle relationnel.
C'est pour cela que le point soulevé était que la solution Bouillier me semblait plus dans l'idée de l'assertion au sens Modèle Relationnelle, puisque je ne fais qu'une vérification via une requête (qu'on trouverait dans une assertion si nos chers moteurs les implémentaient) suivi d'un lancement d'erreur puisqu'il faut bien se substituer à la lacune citée des SGBD...

L'objectif étant bien que le trigger empêche toute tentative d'insertion de données rendant l'ensemble incohérent...on peut donc essayer d'"oublier de créer le doublon dans la table InfoBusiness", le trigger nous insultera .

En tout cas merci pour vos éclairages sur le problème...

[fsmrel]

En fait, le trigger fsmrel va dans le sens de la finalité du trigger qui est la mise à jour (INSERT, UPDATE, DELETE), tandis que le trigger bouiller va plus dans le sens de l’assertion, dont l'objet est d’assurer l’intégrité de la base de données. Autrement dit, si l’on disposait pour de vrai de l’instruction CREATE ASSERTION, dans la série « Ceinture, bretelles et épingle à nourrice », on pourrait très bien envisager de doubler le trigger fsmrel par une assertion façon bouiller (des fois que le trigger fsmrel ne remplisse pas complètement son office, pêche par omission, ce qui est tout à fait possible avec les triggers dans le cas général) :

Code D :

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

1
2
3
4
5
6
7
8
9
10
 CREATE ASSERTION InformationBusinessLineAssertion CHECK
    (NOT EXISTS 
         (SELECT *
          FROM   InfoProduct x JOIN ProductLine AS y
                      ON  x.id_productLine = y.id_productLine
          WHERE  NOT EXISTS
                     (SELECT  *
                      FROM    InfoBusiness AS Z
                      WHERE   x.id_commercialInformation = z.id_commercialInformation
                         AND  y.id_businessLine = z.id_businessLine))) ;

Et on laisse le soin au SGBD de se dépatouiller avec les injures.