Trigger sur Update, lignes multiples

Version imprimable

12/02/2013, 10h09
FlorianC

1 pièce(s) jointe(s)

Trigger sur Update, lignes multiples

Bonjour,

Dans le cadre d'une appli à développer, j'ai un catalogue de Produits que les commerciaux peuvent utiliser pour leurs chiffrages. Il doit aussi être possible qu'un Produit soit composé de Sous Produits, pour obtenir le détail de sa composition. Jusqu'ici, pas de soucis : la table UsinageProduit permet de représenter cela.

Maintenant, une modification du prix d'un des Sous Produits doit directement calculer le nouveau prix des Produits affectés et être soumis à la validation d'un responsable. Ok, on créée une table ModifPrixProduit et des triggers sur UsinageProduit et SousProduit. Problème : redondance....

Et on complique encore un peu : un Sous Produit peut lui même être composé d'autres SousProduits, d'où la relation SousProduit-(0,n)---(0,n)-SousProduit. Et cette fois, une modif sur le prix d'un Sous Produit de rang inférieur doit automatiquement modifier le prix des Sous Produits affectés, sans confirmation... J'ai donc essayé de créer un trigger, mais cette fois problème : le trigger doit faire un update en calculant les différentes SUM(valeur*quantité) et les affecter à différents produits :aie:

Plusieurs questions se dégagent donc :
1) N'y aurait-il pas une façon plus élégante de gérer la confirmation, et éviter la redondance ?
2) La relation N,N sur une seule table, c'est pas joli joli non ?
3) Si je reste sur cette structure, c'est possible un trigger qui fasse ce que je veux ?
12/02/2013, 10h36
aieeeuuuuu

Bonjour,

Je ne comprend pas bien votre modèle.

Est-ce le prix des produits, ou des sous-produits ou les deux qui peuvent être modifiés (à la main j'entends). Votre prose semble indiquer que les sous produit peuvent voir leur prix modifié, mais pas votre modèle...
Quels prix faut-il ensuite recalculer ?

Je pense que vous ne devriez avoir qu'une seule table Produit (regroupant produit et sous produit) ne comportant aucune colonne de prix.
Avec une table des prix (prix initial et modifications) et une table associant les produits aux sous produit. Donc à partir de votre modèle :
- supprimer la colonne prix de la table produit (mettre les prix dans la table ModifPrixProduit, a qui devrait plutôt s'appeler PrixProduit)
- supprimer les tables SousProduit (mettre les lignes dans la table produit), et UsinageSousProduit (mettre les lignes dans la table UsinageProduit)

Vous pourrez ensuite créer des vues pour avoir vos produits et leur prix respectif... Vous n'aurez plus besoin de trigger pour gérer vos prix, par contre il en faudrait pour gérer l'exclusion mutuelle entre les tables prixProduit et UsinageProduit (un produit est soit un SousProduit "de base" ayant un prix fixé dans la table des prix, soit un produit composé de sous produits, ayant un prix calculé)
12/02/2013, 11h36
FlorianC

1 pièce(s) jointe(s)

Citation:

Envoyé par aieeeuuuuu

Bonjour,
Est-ce le prix des produits, ou des sous-produits ou les deux qui peuvent être modifiés (à la main j'entends). Votre prose semble indiquer que les sous produit peuvent voir leur prix modifié, mais pas votre modèle...
Quels prix faut-il ensuite recalculer ?

Alors, le prix d'un produit peut être modifié par un "admin" (en fait un responsable commercial), et celui d'un sous produit par un commercial, ou un employé du service achat.
Lorsque l'on modifie le prix d'un sous produit, deux types d'actions sont effectués :
- Les prix des sous produits qui en dépendent sont recalculés et modifiés automatiquement.
- Les prix des produits qui en dépendent sont recalculés, mais un "admin" doit valider le prix effectif.

Petit exemple (cf. pièce jointe) :
- On a un Produit A qui a pour prix 100.
- Ce produit est composé des Sous Produits 1 (valeur : 20) et 2 (valeur : 80).
- Le Sous Produit 2 est composé des Sous Produits 3 (valeur : 50) et 4 (Valeur : 30).

On obtient une réduction sur le Sous Produit 4, sa valeur passe à 20 :
- Le Sous Produit 2 verra sa valeur automatiquement descendue à 70.
- Une alerte se déclenchera, expliquant que le Produit A coûte désormais 90, et le responsable sera libre de laisser le prix à 100, ou de fixer une autre valeur.

Mais il peut aussi à tout moment décider de passer le prix du Produit A à 200 si le coeur lui en dit.

Autre point important : seul les Produits peuvent être vendus, contrairement aux sous produits qui sont seulement là pour la gestion interne (pour le calcul des prix, mais aussi pour prévenir le service achat des différentes sorties de matière prévues afin qu'ils puissent gérer leur stock).

Citation:

Envoyé par aieeeuuuuu

Je pense que vous ne devriez avoir qu'une seule table Produit (regroupant produit et sous produit) ne comportant aucune colonne de prix.
Avec une table des prix (prix initial et modifications) et une table associant les produits aux sous produit. Donc à partir de votre modèle :
- supprimer la colonne prix de la table produit (mettre les prix dans la table ModifPrixProduit, a qui devrait plutôt s'appeler PrixProduit)
- supprimer les tables SousProduit (mettre les lignes dans la table produit), et UsinageSousProduit (mettre les lignes dans la table UsinageProduit)

Vous pourrez ensuite créer des vues pour avoir vos produits et leur prix respectif... Vous n'aurez plus besoin de trigger pour gérer vos prix, par contre il en faudrait pour gérer l'exclusion mutuelle entre les tables prixProduit et UsinageProduit (un produit est soit un SousProduit "de base" ayant un prix fixé dans la table des prix, soit un produit composé de sous produits, ayant un prix calculé)

J'ai un peu de mal à me représenter un schéma pareil.
Si j'ai bien compris, on aurait les relations suivantes :
- Produit (idProduit, ref, designation)
- UsinageProduit (idProduitCible, idProduitSource, quantite) avec idProduitCible et idProduitsource clés étrangères de Produit
- PrixProduit (idPrix, idProduit, prix, date) avec idProduit clé étrangère de Produit

ainsi que les associations suivantes :
- Produit-(1,1)---(1,n)-PrixProduit
- Produit-(1,n)---(1,n)-Produit et UsinageProduit en table associative.

Plusieurs questions surgissent :
- Comment différencier les Produits des Sous Produits ?
Un Produit qui n'a pas de "rang supérieur" (qui ne serait pas côté source dans la table UsinageProduit). Mais quid des Sous Produits non utilisés actuellement ? Même si après tout, un simple booléen isProduit réglerait la chose.

- Comment appliquer automatiquement les modifs aux Sous Produits, mais pas aux Produits qui eux doivent passer par une validation ?
Et cette question reste sans réponse pour moi. A la limite, un système d'états (prix à valider, modif effective, modif à annuler...) et l'appli côté client testerait s'il s'agit d'une modif de prix de Produit ou de Sous Produit et rendrait directement effective les modifications pour des Sous Produits ?

Cependant, l'appli n'est pas encore en test et je suis toujours ouvert à des refontes importantes dans le modèle, j'ai simplement créé une BDD pour vérifier si ma première version était applicable. Bonne idée apparemment, vu les problèmes qui surgissent. :)

Je me rends d'ailleurs compte qu'il aurait surement été plus intelligent de poster dans le forum modélisation, pour d'abord discuter de la validité de mon modèle avant tout. Je prépare un post de ce pas !
12/02/2013, 14h18
aieeeuuuuu

Merci pour ces précisions, ça commence à être plus clair. Et je comprend du coup un peu mieux votre modèle

J'ai quand même quelques questions : quel prix doit etre pris en compte pour un produit entre le changement de prix d'un de ses sous produit, et la validation de sont nouveaux prix (recalculé) par un admin ?

et comment gérez vous cette validation ? comment l'admin est notifié, comment valide-t-il le prix...
12/02/2013, 14h45
SQLpro
Votre modèle est une usine à gaz et celui proposé par florian l'est encore un peu...

Considérez simplement qu'un produit fini est un sous produit de lui même... Votre modèle se simplifie donc avec une seule table mélant produit et sous produit. et pour savoir si dans cette table vous êtes sur le produit fini, il suffit de trouvez ceux qui, aux choix boucle sur eux même ou on un père NULL.

Il vous restera donc deux vues à créer :
- celle des produits :
Code:

WHERE ID_produit_pere IS NULL
- et celle des sous produits purs :
Code:

WHERE ID_produit_pere IS NOT NULL

A +
12/02/2013, 14h51
FlorianC

1 pièce(s) jointe(s)
Citation:

Envoyé par aieeeuuuuu

Merci pour ces précisions, ça commence à être plus clair. Et je comprend du coup un peu mieux votre modèle

J'ai quand même quelques questions : quel prix doit etre pris en compte pour un produit entre le changement de prix d'un de ses sous produit, et la validation de sont nouveaux prix (recalculé) par un admin ?

et comment gérez vous cette validation ? comment l'admin est notifié, comment valide-t-il le prix...

En attendant la validation, le prix à prendre en compte reste celui avant modification. Une fois le nouveau prix validé, seuls les articles des chiffrages en cours et à venir sont modifiés. Le prix pour les produits affectés à des commandes déjà validées ne doit pas être affecté (par exemple pour éviter que les objectifs annuels des commerciaux ne soient faussés par un changement de prix).

En ce qui concerne la validation, cela sera géré en dehors de l'interface proposée aux commerciaux.
Le service achats aura accès à une interface pour modifier le prix d'achat des sous produits.
Les responsables auront accès à un catalogue de produit où ils auront accès à un onglet qui recense tous les produits dont le coût a changé, pour modifier le prix en conséquence.

Après, comme je l'ai précisé plus tôt, le projet est seulement en phase de conception, la base de données est encore ouverte aux modifications, alors l'interface utilisateur...

EDIT :
Citation:
Envoyé par SQLpro

Votre modèle est une usine à gaz et celui proposé par florian l'est encore un peu...

Considérez simplement qu'un produit fini est un sous produit de lui même... Votre modèle se simplifie donc avec une seule table mélant produit et sous produit. et pour savoir si dans cette table vous êtes sur le produit fini, il suffit de trouvez ceux qui, aux choix boucle sur eux même ou on un père NULL.

Il vous restera donc deux vues à créer :

celle des produits :

Code:

WHERE ID_produit_pere IS NULL

et celle des sous produits purs :

Code:

WHERE ID_produit_pere IS NOT NULL

A +
Malheureusement, il s'agit dans mon cas d'une relation n:m. Un sous produit peut servir à fabriquer plusieurs produits, et un produit est souvent composé de plusieurs sous-produits !

Petit exemple en pièce jointe.
12/02/2013, 15h12
SQLpro

Donc un produit est aussi un sous produits, ce qui revient au même. Il suffit donc de faire un héritage de produit vers sous produit et le problème est résolu par la jointure...
Jointure = produit.
pas de jointure = sous produit

A +