Discussion :

[HABITB0L]

Bonjour,

Je vous demande de l'aide dans l'élaboration d'une base de données. Voici un lien pour vous montrer ce que j'ai fait : http://www.developpez.net/forums/att...1&d=1337823387 ! Le problème, c'est que ceci n'est pas cohérent avec ce que je veux faire.

En effet, un fournisseur peut avoir plusieurs produits. Un produit peut être livré par plusieurs fournisseurs. Jusque là, tout fonctionne ! Le problème, c'est qu'une commande doit contenir plusieurs produits venant d'un SEUL ET UNIQUE fournisseur. Alors que dans le schéma que je vous ai envoyé, une commande peut contenir plusieurs produits de fournisseurs différents ...

Merci d'avance

PS 1 : pouvez-vous m'indiquer si les nommages de mes identifiants sont clairs. Si ce n'est pas le cas, quelles règles de nommage me conseillez-vous ?

PS 2 : ne faites pas attention aux types des données, je les ai saisis vite fait

[Richard_35]

Bonjour HABITB0L,

Le problème, c'est qu'une commande doit contenir plusieurs produits venant d'un SEUL ET UNIQUE fournisseur.

==> il faut stocker "id_fournisseur" dans "commande" (ne surtout pas le stocker dans "commande_produit_fournisseur", qu'il faudrait nommer "commande_detail" ou "commande _produit").

PS 1 : pouvez-vous m'indiquer si les nommages de mes identifiants sont clairs. Si ce n'est pas le cas, quelles règles de nommage me conseillez-vous ?

==> tu auras autant de réponses que de forumeurs !... ou à peu près : fais un tour dans les FAQ et tutos, il me semble que tu pourras trouver des bases. Sinon, Google te les trouvera également.

[CinePhil]

==> il faut stocker "id_fournisseur" dans "commande" (ne surtout pas le stocker dans "commande_produit_fournisseur", qu'il faudrait nommer "commande_detail" ou "commande _produit").

Sauf qu'avec ce modèle simple, on peut commander à un fournisseur des produits qu'il ne fournit pas.

J'avais commencé à y réfléchir ce matin chez moi mais pas eu le temps d'aller au bout de la réflexion et de la rédaction.
J'y reviendrai plus tard.
HABITB0L, tu trouveras sûrement des exemples similaires dans les discussions de ce forum.
Tu verras ainsi qu'il vaut mieux donner un titre plus explicite pour faciliter la recherche !

==> tu auras autant de réponses que de forumeurs !

Un premier article à lire qui donne un standard de nommage des objets d'une BDD.

Je l'ai un peu adapté de cette manière concernant les identifiants :
- code_id pour les identifiants des tables issues des entités du MCD, avec 'code' = le code mnémotechnique de 3 lettres rappelant le nom de la table où se trouve la colonne.
- code_id_autre_table ou code_id_nom_clair pour les portant une clé étrangère.

Exemple :
personne -0,n----diriger----1,1- projet -1,1----concerner----0,n- client

te_personne_prs (prs_id, prs_nom...)
te_projet_prj (prj_id, prj_id_chef, prj_id_client, prj_nom...)

Ce standard peut sembler rébarbatif au début mais on s'aperçois vite qu'on n'a même plus besoin de regarder son MCD ou MLD pour écrire les requêtes quand on a bien son schéma en tête.

J'écrirais un billet de blog un de ces 4 sur les raisons qui m'ont poussé aux adaptations que j'ai faites du standard de SQLPro.

[Richard_35]

Bonjour Philippe,

Sauf qu'avec ce modèle simple, on peut commander à un fournisseur des produits qu'il ne fournit pas.

==> ma réponse n'était, effectivement, pas complète. HABITB0L aura, sans doute, pris soin de la compléter :

==> il faut stocker "id_fournisseur" dans "commande" (ne surtout pas le stocker dans "commande_produit_fournisseur", qu'il faudrait nommer "commande_detail" ou "commande _produit")...

... et modifier les relations en conséquence.

[fsmrel]

Pauvre HABITB0L, confronté à un p... de problème !


En réalité, du fait de la contrainte qui veut que pour une commande le fournisseur soit unique quels que soient les produits, la prétendue clé (candidate) de la table commande_produit_fournisseur est seulement une surclé réductible à une paire :




Pour la suite, pour éviter les maux de tête, permettez-moi d’utiliser des noms moins chargés :



Le problème est qu’après avoir défini la clé authentique (irréductible) de la table, il y a maintenant viol de la deuxième forme normale (2NF). En effet, la contrainte initiale donne lieu à la dépendance fonctionnelle :

DF1 : CommandeId -> FournisseurId

La solution consiste à appliquer le théorème de Heath, en décomposant la table CDE_PROD_FOUR peccamineuse selon :

CPF1 {CommandeId, FournisseurId}, laquelle peut être absorbée par la table COMMANDE ;

CPF2 {CommandeId, ProduitId, Quantite, Montant}.

Malheureusement, comme dit CinePhil, on peut désormais commander des produits non fournis par le fournisseur concerné...
Pour éviter cela, au stade opérationnel il faudra en passer par la mise en œuvre d’un trigger veillant au grain.

Il en va de même si on préfère ne pas décomposer la table : mettre en œuvre un trigger veillant à ce que la dépendance fonctionnelle DF1 soit respectée.

[Richard_35]

Bonjour Fsmrel,

Il me semble préférable de stocker le fournisseur dans l'entête de la commande, puis développer un trigger de contrôle qui vérifie que le couple {Fournisseur(entête), Produit(détail)} existe dans le catalogue fournisseur.

Soit :

Code :

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

1
2
3
Fournisseur ---0,n---[Commander]---1,1--- Commande_Fournisseur ---1,n---[Composer]---1,n--- Produit
    |                                                                                          |
    +------------------------------0,n---[Fournir]---1,n---------------------------------------+

donnant :

Fournisseur(Id_Fournisseur, Nom,...) ;
Produit(Id_Produit, Libellé, ...) ;
Prod_Four(#Id_Fournisseur, #IdProduit, ...) ;
Commande(Id_Commande, #Id_Fournisseur, Date_Commande, ...) ;
Commande_Détail(#Id_Commande, #Id_Produit, Quantité, ...).

Non ?

[CinePhil]

C'est exactement ce qu'il a dit dans sa "relation-langue" !

Et donc => trigger pour vérifier qu'on ne commande pas une fenêtre à deux vantaux chez Microsoft !

[Richard_35]

Merci d'avoir fait le lien, Philippe !... ... et merci à Fsmrel, fabuleux puit de savoir.

[HABITB0L]

Tout d'abord, je tiens à vous remercier d'avoir pris le temps de m'aider pour résoudre mon problème. Si j'ai bien compris, il faut que je développe un trigger qui s'occupe de gérer la DF {commande} -> {fournisseur} !

Hélas, je ne sais pas comment fonctionne un trigger car j'en ai jamais utilisé et je n'ai jamais su à quoi ça servait (enfin grâce à vous, j'ai maintenant une petite idée ) !

Auriez-vous un bon tuto pour apprendre comment fonctionne les triggers ? Dans tous les cas, je vais chercher sur le net. Google est mon ami !

[fsmrel]

Bonsoir Habitbol,

Auriez-vous un bon tuto pour apprendre comment fonctionne les triggers ?

Un trigger est automatiquement déclenché lors des opérations d’insert/update/delete quand on l’a programmé pour. Par exemple, le trigger Lei57_insert_update ci-dessous (qui fonctionne dans un contexte SQL Server et doit être adapté pour d’autres SGBD) contrôle chaque insert et update et provoque une erreur si l’on cherche à enfreindre la règle selon laquelle pour une commande il ne peut y avoir qu’un seul fournisseur.

Le trigger Lei57_insert_update correspond au 1er cas de figure envisagé : viol de la 2NF (donc mise en oeuvre de la table CDE_PROD_FOUR). Le SGBD lui transmet le contenu d’une table nommée INSERTED qui contient les nouvelles données pour la table CDE_PROD_FOUR. Il suffit par jointure avec la table des commandes de vérifier que l’on ne cherchera pas à enfreindre la règle, sinon le trigger se fâchera. Je pense que la lecture du code n’est pas bien compliquée (@@ROWCOUNT contient le nombre de lignes résultant du SELECT) :

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
18
19
20
21
22
23
 
CREATE TRIGGER Lei57_insert_update ON CDE_PROD_FOUR INSTEAD OF INSERT, UPDATE AS
  DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
SELECT x.CommandeId, x.FournisseurId
FROM   INSERTED AS x INNER JOIN CDE_PROD_FOUR AS y
       ON x.CommandeId = y.CommandeId AND x.FournisseurId <> y.FournisseurId  
 
SET @Count = @@ROWCOUNT
 
IF @Count > 0
    BEGIN
          SELECT 'Erreur' AS 'Table CDE_PROD_FOUR : la commande ne doit faire mention que d’un seul fournisseur !', * FROM INSERTED
          RAISERROR (@Err, 15, 1) 
          RETURN
    END
ELSE
    BEGIN
        INSERT INTO CDE_PROD_FOUR (CommandeId, ProduitId, FournisseurId, Quantite, Montant)
            SELECT  CommandeId, ProduitId, FournisseurId, Quantite, Montant
            FROM    INSERTED
    END
GO

Je joins un bout de jeu d’essai. Pour des raisons de lisibilité, les clés primaires sont du type CHAR et non pas des auto-incréments chers à CinePhil.

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
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
CREATE TABLE FOURNISSEUR (
        FournisseurId     CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
  , CONSTRAINT FOURNISSEUR_PK PRIMARY KEY (FournisseurId))
 ;
CREATE TABLE PRODUIT (
        ProduitId         CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
  , CONSTRAINT PRODUIT_PK PRIMARY KEY (ProduitId))
 ;
CREATE TABLE PROD_FOUR (
        ProduitId         CHAR(04)      NOT NULL
      , FournisseurId     CHAR(04)      NOT NULL
      , Etc VARCHAR(48) NOT NULL
  , PRIMARY KEY (ProduitId, FournisseurId)
  , CONSTRAINT PROD_FOUR_FOUR_FK FOREIGN KEY (FournisseurId)
             REFERENCES FOURNISSEUR (FournisseurId)
  , CONSTRAINT PROD_FOUR_PROD_FK FOREIGN KEY (ProduitId)
             REFERENCES PRODUIT (ProduitId))
 ;
CREATE TABLE COMMANDE (
        CommandeId        CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
  CONSTRAINT COMMANDE_PK PRIMARY KEY (CommandeId))
 ;
CREATE TABLE CDE_PROD_FOUR (
        CommandeId        CHAR(04)      NOT NULL
      , ProduitId         CHAR(04)      NOT NULL
      , FournisseurId     CHAR(04)      NOT NULL
      , Quantite          INT           NOT NULL
      , Montant           INT           NOT NULL
  , CONSTRAINT CDE_PROD_FOUR_PK PRIMARY KEY (CommandeId, ProduitId)
  , CONSTRAINT CDE_PROD_FOUR_CDE_FK FOREIGN KEY (CommandeId)
             REFERENCES COMMANDE (CommandeId)
  , CONSTRAINT CCDE_PROD_FOUR_FOUR_FK FOREIGN KEY (ProduitId, FournisseurId)
             REFERENCES PROD_FOUR (ProduitId, FournisseurId))
 ;
INSERT INTO FOURNISSEUR VALUES ('F01', 'un 1er fournisseur') ; 
INSERT INTO FOURNISSEUR VALUES ('F02', 'un 2e fournisseur') ; 
INSERT INTO FOURNISSEUR VALUES ('F03', 'un 3e fournisseur') ; 
INSERT INTO FOURNISSEUR VALUES ('F04', 'un 4e fournisseur') ; 
 
INSERT INTO PRODUIT VALUES ('P01', 'un 1er produit') ; 
INSERT INTO PRODUIT VALUES ('P02', 'un 2e produit') ; 
 
INSERT INTO PROD_FOUR VALUES ('P01', 'F01', 'une 1ère paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P01', 'F02', 'une 2e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P01', 'F03', 'une 3e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P02', 'F01', 'une 4e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P02', 'F03', 'une 5e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P02', 'F04', 'une 6e paire produit / fournisseur') ; 
 
INSERT INTO COMMANDE VALUES ('CD01', 'une 1ère commande') ; 
INSERT INTO COMMANDE VALUES ('CD02', 'une 2e commande') ; 
INSERT INTO COMMANDE VALUES ('CD03', 'une 3e commande') ; 
 
INSERT INTO CDE_PROD_FOUR VALUES ('CD01', 'P01', 'F01', 100, 1000) ; 
INSERT INTO CDE_PROD_FOUR VALUES ('CD01', 'P02', 'F03', 200, 2000) ; 
 
SELECT '' AS FOURNISSEUR, * FROM FOURNISSEUR ;
SELECT '' AS PRODUIT, * FROM PRODUIT ;
SELECT '' AS PROD_FOUR, * FROM PROD_FOUR ;
SELECT '' AS COMMANDE, * FROM COMMANDE ;
SELECT '' AS CDE_PROD_FOUR, * FROM CDE_PROD_FOUR ; 
 
UPDATE CDE_PROD_FOUR 
       SET FournisseurId = 'F02'WHERE CommandeId = 'CD01'
 
SELECT '' AS CDE_PROD_FOUR, * FROM CDE_PROD_FOUR ;

Il me semble préférable de stocker le fournisseur dans l'entête de la commande, puis développer un trigger

Vu de la dunette, ça peut se défendre, mais quand on descend dans la soute, marche arrière toute ! car le SELECT faisant partie du trigger est moins performant (double jointure avec salto arrière contre une jointure simple) :

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
18
19
20
21
22
23
24
25
26
27
 
CREATE TRIGGER Lei57_insert_update ON CDE_PROD INSTEAD OF INSERT, UPDATE AS
  DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
SELECT x.CommandeId, x.ProduitId
FROM   INSERTED AS x 
            INNER JOIN COMMANDE AS z    
                    ON  x.CommandeId = z.CommandeId
            INNER JOIN PROD_FOUR AS t
                    ON  x.ProduitId = t.ProduitId
                    AND z.FournisseurId = t.FournisseurId
 
SET @Count = @@ROWCOUNT
 
IF @Count = 0
    BEGIN
        SELECT 'Erreur' AS 'Table CDE_PROD : la commande doit référencer des produits fournis par le fournisseur qu’elle référence', * FROM INSERTED
        RAISERROR (@Err, 10, 1) 
        RETURN
END
ELSE
    BEGIN
        INSERT INTO CDE_PROD (CommandeId, ProduitId, Quantite, Montant)
               SELECT CommandeId, ProduitId, Quantite, Montant
               FROM   INSERTED
    END
GO

Jeu d’essai correspondant :

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
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
CREATE TABLE FOURNISSEUR (
        FournisseurId     CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
  , CONSTRAINT FOURNISSEUR_PK PRIMARY KEY (FournisseurId))
 ;
CREATE TABLE PRODUIT (
        ProduitId         CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
  , CONSTRAINT PRODUIT_PK PRIMARY KEY (ProduitId))
;
CREATE TABLE PROD_FOUR (
        ProduitId         CHAR(04)      NOT NULL
      , FournisseurId     CHAR(04)      NOT NULL
      , Etc VARCHAR(48) NOT NULL
  , PRIMARY KEY (ProduitId, FournisseurId)
  , CONSTRAINT PROD_FOUR_FOUR_FK FOREIGN KEY (FournisseurId)
             REFERENCES FOURNISSEUR (FournisseurId)
  , CONSTRAINT PROD_FOUR_PROD_FK FOREIGN KEY (ProduitId)
             REFERENCES PRODUIT (ProduitId))
;
CREATE TABLE COMMANDE (
        CommandeId        CHAR(04)      NOT NULL
      , FournisseurId     CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
  CONSTRAINT COMMANDE_PK PRIMARY KEY (CommandeId)
, CONSTRAINT COMMANDE_FOUR_FK FOREIGN KEY (FournisseurId)
             REFERENCES FOURNISSEUR (FournisseurId))
;
CREATE TABLE CDE_PROD (
        CommandeId        CHAR(04)      NOT NULL
      , ProduitId         CHAR(04)      NOT NULL
      , Quantite          INT           NOT NULL
      , Montant           INT           NOT NULL
  , CONSTRAINT CDE_PROD_PK PRIMARY KEY (CommandeId, ProduitId)
  , CONSTRAINT CDE_PROD_CDE_FK FOREIGN KEY (CommandeId)
             REFERENCES COMMANDE (CommandeId)
  , CONSTRAINT CDE_PRODUIT_FK FOREIGN KEY (ProduitId)
             REFERENCES PRODUIT (ProduitId))
;
-----------------------------------------------------------------
INSERT INTO FOURNISSEUR VALUES ('F01', 'un 1er fournisseur') ; 
INSERT INTO FOURNISSEUR VALUES ('F02', 'un 2e fournisseur') ; 
INSERT INTO FOURNISSEUR VALUES ('F03', 'un 3e fournisseur') ; 
INSERT INTO FOURNISSEUR VALUES ('F04', 'un 4e fournisseur') ;
 
INSERT INTO PRODUIT VALUES ('P01', 'un 1er produit') ; 
INSERT INTO PRODUIT VALUES ('P02', 'un 2e produit') ;
 
INSERT INTO PROD_FOUR VALUES ('P01', 'F01', 'une 1ère paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P01', 'F02', 'une 2e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P01', 'F03', 'une 3e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P02', 'F02', 'une 4e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P02', 'F03', 'une 5e paire produit / fournisseur') ; 
INSERT INTO PROD_FOUR VALUES ('P02', 'F04', 'une 6e paire produit / fournisseur') ;
 
INSERT INTO COMMANDE VALUES ('CD01', 'F01', 'une 1ère commande') ; 
INSERT INTO COMMANDE VALUES ('CD02', 'F02', 'une 2e commande') ; 
INSERT INTO COMMANDE VALUES ('CD03', 'F03', 'une 3e commande') ;
 
INSERT INTO CDE_PROD VALUES ('CD01', 'P01', 100, 1000) ; 
INSERT INTO CDE_PROD VALUES ('CD01', 'P02', 200, 2000) ;
 
SELECT '' AS FOURNISSEUR, * FROM FOURNISSEUR ;
SELECT '' AS PRODUIT, * FROM PRODUIT ;
SELECT '' AS PROD_FOUR, * FROM PROD_FOUR ;
SELECT '' AS COMMANDE, * FROM COMMANDE ;
SELECT '' AS CDE_PROD, * FROM CDE_PROD ;
 
UPDATE CDE_PROD
       SET ProduitId = 'P02' WHERE CommandeId = 'CD01'
 
SELECT '' AS CDE_PROD, * FROM CDE_PROD ;

Pour un tuto sur les triggers, voyez les écrits de SQLpro.

[HABITB0L]

Merci fsmrel de m'avoir donné une réponse si complète

J'ai bien compris ce que tu as fait et ça m'a l'air de fonctionner

Par contre, j'utilise une base de données MySQL et le code source du trigger n'a pas la même syntaxe et je n'arrive pas à le retranscrire car MySQL ne dispose pas de la variable @@ROWCOUNT, ni de RAISERROR() entre autre ... J'ai essayé de "bidouiller" un peu mais ça ne fonctionne pas complètement

Pouvez-vous me donner le code MySQL qui fait la même chose ?

[Richard_35]

Bonjour à tous,

C'est exactement ce qu'il (Fsmrel) a dit dans sa "relation-langue" !

==> eh bien, ce n'était pas cela, donc. Car :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE COMMANDE (
        CommandeId        CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
...
CREATE TABLE CDE_PROD_FOUR (
        CommandeId        CHAR(04)      NOT NULL
      , ProduitId         CHAR(04)      NOT NULL
      , FournisseurId     CHAR(04)      NOT NULL
      , Quantite          INT           NOT NULL
      , Montant           INT           NOT NULL
...

Il ne me semble pas, dans ma carrière, avoir vu des tables de type Entête/Détail (commandes clients, commandes fournisseurs, factures clients, factures fournisseurs, etc...) où un attribut commun à toutes les lignes détails soit stocké dans la partie "détail" et contrôlé via un trigger "before insert/before update" : ceci pour l'argument "historique", mais ce n'est pas le plus important.

Je me permets de maintenir que :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE COMMANDE (
        CommandeId        CHAR(04)      NOT NULL
      , FournisseurId     CHAR(04)      NOT NULL
      , Etc               VARCHAR(48)   NOT NULL
...
CREATE TABLE CDE_PROD_FOUR (
        CommandeId        CHAR(04)      NOT NULL
      , ProduitId         CHAR(04)      NOT NULL
      , Quantite          INT           NOT NULL
      , Montant           INT           NOT NULL
...

avec un trigger "before update" sur COMMANDE et "before update/before insert" CDE_PROD_FOUR.

Bref, dans l'esprit, répéter autant de fois le fournisseur que de lignes "détail" ne me paraît pas très judicieux.

Mais, la problématique est intéressante car, dans la solution défendue par Fsmrel, stocker le couple Fournisseur/Produit dans la partie "détail" produit une modélisation "propre" (adaptation de la modélisation), mais une redondance d'information "pas très propre". Vu d'une fenêtre externe, quelqu'un qui étudierait la table "détail" pourrait s'offusquer de constater que toutes les lignes détail d'une même commande comporte le même fournisseur... et de se poser la question : "pourquoi, diable, ne pas avoir stocké le fournisseur dans l'entête de la commande ?"... et le débat repartirait...

A contrario, quelqu'un qui constaterait le stockage du fournisseur dans l'entête a peu de chance de s'offusquer qu'il ne soit pas stocké dans le détail.

En annexe, concernant les triggers, il me semble me rappeler que, sur AS/400 (iSeries), il y a la possibilité de créer des triggers "before/after" pour "insert/update/delete", soit 6 possibilités. Fsmrel, ne serait-ce pas le cas dans tous les SGBD ?
Il ne semble pas voir cette notion dans les triggers que tu décris.

[CinePhil]

==> eh bien, ce n'était pas cela, donc. Car :

Si si , c'est pourtant bien la solution qu'il préconisait :

La solution consiste à appliquer le théorème de Heath, en décomposant la table CDE_PROD_FOUR peccamineuse selon :

CPF1 {CommandeId, FournisseurId}, laquelle peut être absorbée par la table COMMANDE ;

CPF2 {CommandeId, ProduitId, Quantite, Montant}.

Malheureusement, comme dit CinePhil, on peut désormais commander des produits non fournis par le fournisseur concerné...
Pour éviter cela, au stade opérationnel il faudra en passer par la mise en œuvre d’un trigger veillant au grain.

Mais juste après il faisit aussi cette supposition :

Il en va de même si on préfère ne pas décomposer la table : mettre en œuvre un trigger veillant à ce que la dépendance fonctionnelle DF1 soit respectée.

Et c'est cette hypothèse qu'il semble avoir développée dans son dernier message.

Mais comme toi Richard, je préfère la solution "Fournisseur référencé dans la commande". C'est plus logique car cela répond à cette règle de gestion :
"Une commande est passée à un seul fournisseur et un fournisseur peut faire l'objet de plusieurs commandes."

Toujours revenir aux règles de gestion pour lever les doutes !

[Richard_35]

Toujours revenir aux règles de gestion pour lever les doutes !

==> effectivement, les règles de gestion doivent être le juge de paix.

C'est intéressant, d'ailleurs, d'établir les "règles d'adaptation générales" : règles de gestion => modélisation => développement. Autrement dit :

• c'est au développement de s'adapter à la modélisation et non l'inverse => nombreuses discussions sur ce thème, en général par des virtuoses du développement ;
• c'est à la modélisation de s'adapter aux règles de gestion et non l'inverse => cette discussion est, pour moi, la première.

[CinePhil]

c'est au développement de s'adapter à la modélisation et non l'inverse => nombreuses discussions sur ce thème, en général par des virtuoses du développement ;

C'est ce que j'ai compris assez vite en fréquentant les forums SGBD et Conception et c'est ce que je prèche dans les forums SGBD et PHP que je fréquente !

Les développeurs ont davantage une approche de modélisation métier qui va s'implémenter assez naturellement dans leur logiciel.
C'est souvent la passerelle entre modélisation métier et modélisation des données qui pose problème.
Et les ORM sont mal utilisés par les développeurs parce qu'ils attaquent directement les tables au lieu de passer par la couche modèle organisationnel des données (vues dans la BDD).
En plus, d'après ce que j'ai cru comprendre, et constaté quand j'ai dû essayer de jouer un peu avec le Seam Framework, les ORM ont tendance à déplacer la couche organisationnelle dans le programme au lieu de la laisser dans le SGBD.

c'est à la modélisation de s'adapter aux règles de gestion et non l'inverse => cette discussion est, pour moi, la première.

C'est l'objet de mon billet de blog qui commence à être célèbre !
Et c'est la démarche que j'emploie souvent dans mes réponses.

[fsmrel]

Bonsoir,

En annexe, concernant les triggers, il me semble me rappeler que, sur AS/400 (iSeries), il y a la possibilité de créer des triggers "before/after" pour "insert/update/delete", soit 6 possibilités. Fsmrel, ne serait-ce pas le cas dans tous les SGBD ?
Il ne semble pas voir cette notion dans les triggers que tu décris.

On aura beau écarquiller les yeux, force est de constater que, contrairement à ceux d’IBM qui respectent la syntaxe de la norme SQL, les triggers de MS SQL Server s’en départissent. Une fois de plus, voir les écrits de SQLpro. En tout cas, MS SQL Server répond lui aussi à 6 possibilités : "instead of/after" X "insert/update/delete". En consultant la documentation, on constate que DB2 UDB et Oracle ont du reste fini par inclure INSTEAD OF, mais applicable seulement pour les vues (en fait pour que l’on puisse coder au sein du trigger affecté à une vue la mise à jour des tables impliquées dans cette vue et non pas la vue elle-même).

j'utilise une base de données MySQL et le code source du trigger n'a pas la même syntaxe et je n'arrive pas à le retranscrire car MySQL ne dispose pas de la variable @@ROWCOUNT, ni de RAISERROR() entre autre

Évidemment, chaque SGBD propose ses variables et fonctions qui lui sont propres, dont @@ROWCOUNT et RAISERROR sont des représentants typiques en ce qui concerne MS SQL Server.

Pouvez-vous me donner le code MySQL qui fait la même chose ?

Je voudrais bien, mais je n’arrive pas installer MySQL, donc je préfère ne pas vous fournir un code non testé... Il vous reste à vous plonger dans la documentation qui va bien, et au besoin poster dans le forum MySQL...


A propos des contraintes

En passant, je reviens sur la solution où la 2NF est respectée et dont voici un MCD dans le style Merise :



A l’examen de ce MCD, on peut se douter qu’en l’état rien n’empêche :

1) Comme on l’a déjà vu, d’avoir des commandes référençant des produits fournis par des fournisseurs autres que celui qui est référencé par la commande,

2) Par le jeu de mises à jour intempestives de la table PROD_FOUR (dérivée de l’association du même nom), de détruire impunément des liens établis entre cette table et la table CDE_PROD (dérivée de l’association du même nom), donc de rendre orphelines les commandes concernées. A la limite on pourrait vider complètement la table PROD_FOUR...

Pour éviter cela au niveau opérationnel, il faut mettre en œuvre une assertion si le SGBD sait de quoi il s’agit, sinon des triggers pour pallier, comme dans le cas de MS SQL Server. L’équivalent conceptuel de l’assertion pourrait être une contrainte d’inclusion (dans le style Merise/2) :



Ce qui revient à dire que, au niveau SQL, la projection {FournisseurId, ProduitId} de la jointure naturelle des tables COMMANDE et CDE_PROD doit être un sous-ensemble (non strict) de la projection {FournisseurId, ProduitId} de la table PROD_FOUR (voir en fin de message).

En conséquence, les triggers ad-hoc peuvent être les suivants (toujours avec MS SQL Server, désolé...) :

1) Un premier trigger dédié à la mise à jour de la table CDE_PROD :

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
18
19
20
21
22
23
24
25
26
 
CREATE TRIGGER Lei57_Commande_insert_update ON CDE_PROD INSTEAD OF INSERT, UPDATE AS
  DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
SELECT DISTINCT x.CommandeId, x.ProduitId, z.FournisseurId
FROM   INSERTED AS x 
            INNER JOIN COMMANDE AS z    
                    ON  x.CommandeId = z.CommandeId
            INNER JOIN PROD_FOUR AS t
                    ON  x.ProduitId = t.ProduitId
                    AND z.FournisseurId = t.FournisseurId
 
SET @Count = @@ROWCOUNT
 
IF @Count = 0
    BEGIN
        SELECT 'Chaque commande doit référencer des produits fournis par le fournisseur qu’elle référence', * FROM INSERTED
        RAISERROR (@Err, 10, 1) 
        RETURN
    END
ELSE
        BEGIN
            INSERT INTO CDE_PROD (CommandeId, ProduitId, Quantite, Montant)
                SELECT CommandeId, ProduitId, Quantite, Montant
                FROM   INSERTED
        END

Il s’agit en fait d’une révision du trigger Lei57_insert_update que j’avais proposé la fois précédente, lequel toutefois ne répond pas exactement à ce que l’on en attend.

2) Une paire de triggers dédiés à la mise à jour de la table PROD_FOUR, l’un pour contrôler les suppressions et l’autre les modifications.


Contrôle des suppressions :

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
18
19
20
21
22
23
24
25
26
 
CREATE TRIGGER Lei57_PROD_FOUR_delete ON PROD_FOUR INSTEAD OF DELETE AS  
  DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
SELECT DISTINCT x.FournisseurId, x.ProduitId
FROM   DELETED AS x 
            INNER JOIN COMMANDE AS z    
                    ON  x.FournisseurId = z.FournisseurId
            INNER JOIN CDE_PROD AS t
                    ON  x.ProduitId = t.ProduitId
                    AND z.CommandeId = t.CommandeId
 
SET @Count = @@ROWCOUNT
 
IF @Count > 0
    BEGIN
        SELECT 'Delete rejeté : paire(s) fournisseur/commande référencée(s) par des commandes ' AS 'Table PROD_FOUR', * FROM DELETED
        RAISERROR (@Err, 10, 1) 
        RETURN
    END
ELSE
    BEGIN
        DELETE FROM PROD_FOUR
               WHERE FournisseurId IN (SELECT FournisseurId FROM DELETED)
                 AND ProduitId IN (SELECT ProduitId FROM DELETED)
    END

Contrôle des modifications :

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
18
19
20
21
22
23
24
25
26
27
28
29
 
CREATE TRIGGER Lei57_PROD_FOUR_Update ON PROD_FOUR INSTEAD OF UPDATE AS  
  DECLARE @Count AS INT, @Err AS VARCHAR(48)
 
SELECT DISTINCT x.FournisseurId, x.ProduitId
FROM   DELETED AS x 
            INNER JOIN COMMANDE AS z    
                    ON  x.FournisseurId = z.FournisseurId
            INNER JOIN CDE_PROD AS t
                    ON  x.ProduitId = t.ProduitId
                    AND z.CommandeId = t.CommandeId
 
SET @Count = @@ROWCOUNT
 
IF @Count > 0
    BEGIN
        SELECT 'Update rejeté : paire(s) fournisseur/commande référencée(s) par des commandes' AS 'Table PROD_FOUR', * FROM DELETED
        RAISERROR (@Err, 10, 1) 
        RETURN 
    END
ELSE
    BEGIN
        DELETE FROM PROD_FOUR
               WHERE FournisseurId IN (SELECT FournisseurId FROM DELETED)
                 AND ProduitId IN (SELECT ProduitId FROM DELETED)
        INSERT INTO PROD_FOUR
               SELECT *
               FROM   INSERTED 
    END

Dans le scénario où la 2NF est violée ces deux derniers triggers applicables à la table PROD_FOUR sont inutiles, en effet les contraintes à respecter le sont de facto par le jeu des clés étrangères (CDE_PROD_FOUR fait référence à PROD_FOUR). La validité, la robustesse de la base de données et sa performance y trouvent leur compte : tant pis pour cette malheureuse 2NF (à moins que..., voir ci-dessous).

Bref, dans l'esprit, répéter autant de fois le fournisseur que de lignes "détail" ne me paraît pas très judicieux.

Au stade de la modélisation conceptuelle, on s’intéresse à la commande, son en-tête, ses lignes de commande, c’est évident et l’on n’a pas de raison de créer de la redondance. Je répète quand même qu’il ne faut pas mélanger les niveaux, et quand on « descend » au niveau relationnel, on a affaire à des relations (au sens mathématique du terme), manipulables à l’aide de l’algèbre relationnelle, et à des contraintes traduisant les règles de gestion. En conséquence, on ne s'occupe plus d’en-têtes et de lignes de commande, mais de variables X, Y, Z, etc. Les contraintes sont mises en oeuvre sous diverses formes : clés candidates, clés étrangères, assertions, voire triggers quand le SGBD ne sait pas ce qu’est une assertion. Stricto sensu, la théorie de la normalisation ne fait pas partie pour sa part du Modèle Relationnel de Données (aka théorie relationnelle), mais suite aux travaux de Codd, c’est dans ce contexte qu’elle peut pleinement s’exprimer (grâce à la théorie des dépendances, à la validité et la complétude des axiomes et règles d’Armstrong), sans se heurter à des barrières psychologiques. C’est grâce à cette théorie qu’on peut détecter les infractions et juger opportun de les conserver dans la mesure où la validité de la base de données n’est pas remise en cause.

Je suis évidemment partisan de respecter tant que faire se peut les formes normales, et il est rare qu’au cours de ma carrière de DBA j’ai eu à commettre des entorses, car je connais trop bien les risques encourus : enfreindre la 2NF et au-delà ça peut nous exploser à la figure dès lors qu’on n’est pas vraiment artificier. Pour en revenir à l’alternative à laquelle est confronté HABITB0L, peut-être avais-je été trop elliptique la première fois, mais je confirme que ce sont les prototypages de robustesse et de performance qui peuvent conduire à transformer des structures jusqu’ici normalisées (sans rien perdre bien sûr des règles de gestion des données). Sinon, il est délicat de trancher et comme le dit un orfèvre en la matière, à savoir Chris Date (page 371 de An Introduction to Database Systems, eighth edition) :

We are forced to the unpleasant conclusion that the twin objectives of (a) decomposing a relvar into BCNF components, and (b) decomposing it into independent components, can occasionally be in conflict—that is, it is not always possible to satisfy both of them at the same time.

=> Au niveau conceptuel, répéter autant de fois le fournisseur que de lignes "détail" n’est pas judicieux, mais au niveau SQL ça se discute et ça se démontre.


@ HABITB0L,

En conclusion, secouez les deux versions proposées : robustesse, performance et testez les opérations de mise à jour et de consultation de la base de données. Si la performance de la version normalisée s’avère être tout à fait acceptable, vous pouvez la préférer à l’autre.

Par ailleurs réfléchissez au point suivant : supposons que la base de données soit opérationnelle depuis un bon moment, donne toute satisfaction aux utilisateurs, comment réagir quand un beau jour, sans crier gare, la MOA émettra une note rendant caduque la règle qui veut qu’une commande fasse référence à un seul fournisseur ? (Il faut reconnaître que cette règle de gestion est plutôt étrange...)


Quand on en arrive aux tests, on doit notamment éprouver la robustesse des contraintes que l’on a développées et la performance du code qui les hébergent. Comme on a pu s’en rendre compte, dans ce genre d’exercice, il est évident que les structures des tables peuvent être différentes de celles produites par une dérivation brute du MCD. Mais, entre avoir au niveau SQL la copie conforme du niveau conceptuel et quelque chose qui s’en départit, on a le choix ; la seule contrainte fonctionnelle qui persiste à tous les niveaux est que les règles de gestion des données soient respectées, tandis que la base de données en production doit être la plus robuste et la plus performante possible. Cela peut conduire à casser les structures brutes et ne pas respecter les formes normales : par définition de la relation elle-même, seule la 1NF doit impérativement l’être. Je rappelle simplement que pour les autres FN, à moins de bien les maîtriser, on court de grands risques si on ne les respecte pas.

c'est à la modélisation de s'adapter aux règles de gestion et non l'inverse

La modélisation n’a pas à s’adapter mais à prendre en compte les règles de gestion pour garantir qu’elles seront respectées.

Ainsi, dans le cas qui nous intéresse, il n'est pas inutile de représenter des contraintes de type Merise/2 dans les MCD (exclusion, inclusion, totalité, etc.) ; au niveau SQL garantir les règles passe par la mise en œuvre correcte des clés candidates et des clés étrangères, mais aussi d’assertions quand ces règles vont au-delà des possibilités offertes par les clés. Si le SGBD ne permet pas la mise en œuvre des assertions, on se rabat sur des triggers. Cela dit, structurer les données est du ressort du spécialiste : le concepteur au niveau MCD, le DBA au niveau SQL.


A propos des assertions

Il faut reconnaître qu’en termes de contrôle de la validité de la base de données, mettre en œuvre une assertion est quand même moins pénible que d’en passer par des triggers (dont la syntaxe varie d’un SGBD à l’autre). Ainsi pour garantir la contrainte d’inclusion définie plus haut, selon laquelle les paires {FournisseurId, ProduitId} issues de la jointure naturelle des tables COMMANDE et CDE_PROD ne doivent pas être orphelines des paires {FournisseurId, ProduitId} de la table PROD_FOUR :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE ASSERTION Habitbol_Asset01 CHECK
    (NOT EXISTS (SELECT *
                 FROM   COMMANDE AS x INNER JOIN CDE_PROD AS y
                        ON x.CommandeId = y.CommandeId
                 WHERE  NOT EXISTS 
                       (SELECT *
                        FROM   PROD_FOUR AS z
                        WHERE  x.FournisseurId = z.FournisseurId
                          AND  y.ProduitId = z.ProduitId)
     ) ) ;

Quand je pense que ça existait déjà dans le prototype SYSTEM/R d’IBM (années soixante-dix...)

[dba01]

Bonjour.

Il est un détail que je ne comprends pas : pourquoi un fournisseur par commande ?... Parce que c'est le fournisseur lui-même qui livre.

Donc, c'est un problème de groupage, de colisage, et non un problème de commande. Et les deux doivent être gérés séparément. J'ai vu écrit quelque part une notion de délais de livraison. Mais alors si vous facturez par commande alors qu'une des lignes ne sera livrée que dans trois mois, ou peut-être même annulée, vous pouvez attendre quelques réclamations.

Moi, j'aurai volontiers distingué les notions de producteur (qui fabrique, le catalogue) et fournisseur (qui livre). Et la facture ne doit contenir que la livraison, et pas la commande. Et alors plus de problèmes pour évoluer vers les commandes multi-producteurs, tant que les livraisons restent mono-fournisseur. Et il faudra un retour d'info depuis le fournisseur pour savoir quelle livraison contient quoi, et peut donc être facturée, ou mise en suspens.

Que reste-t-il de la commande ? La date de commande pour chaque ligne-produit, permettant de voir ce qui est en attente, Mais la commande elle-même n'a qu'un intérêt très relatif.

[CinePhil]

Revenons un instant au terrain.

Dans les entreprises, pour une commande d'achat, on achète P produits à 1 fournisseur.
Cette commande d'achat entraînera L livraisons de la part de ce fournisseur, même s'il utilise plusieurs transporteurs pour le faire, c'est son affaire.
Le fournisseur de cette commande enverra aussi F factures concernant cette commande d'achat.

Il y a donc bien en premier l'association suivante :
1) commande -1,1----passer_à----0,n- fournisseur

Et comme il y a aussi ceci :
2) commande -1,n----avoir----(1,1)- ligne_commande -(1,1)----concerner----0,n- produit

Ainsi que ceci :
3) fournisseur -0,n----fournir----0,n- produit

Il faut modéliser une contrainte d'inclusion comme l'a fait fsmrel et mettre en oeuvre un mécanisme dans le SGBD pour contrôler cette contrainte, sous peine de commander un jour un plateau-repas à Microsoft !

J'irais même plus loin, me référant à ce que j'ai vu pratiquer dans une entreprise du BTP : une commande d'achat est passée à un seul fournisseur pour une seule affaire. Ceci afin de bien imputer l'achat en comptabilité analytique.

[HABITB0L]

Désolé de répondre si tardivement, j'ai créé un trigger comme vous me l'avez conseillé.

J'ai créé une table erreur pour pouvoir afficher les erreurs car, contrairement à certains SGBD, MySQL ne dispose pas d'une commande permettant de déclencher une erreur personnalisée. La seule solution est donc de faire une requête dont on sait qu'elle va générer une erreur. Voilà le code :

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
-- creation de la table erreur ------
CREATE  TABLE IF NOT EXISTS erreur (
  `id_erreur` TINYINT NOT NULL AUTO_INCREMENT ,
  `text_erreur` VARCHAR(255) NULL ,
  PRIMARY KEY (`id_erreur`) ,
  UNIQUE INDEX `text_erreur_UNIQUE` (`text_erreur` ASC) )
ENGINE = InnoDB;

-- insertion du message d'erreur -----
INSERT INTO erreur (text_erreur) VALUES ('Erreur : Une commande ne peut pas avoir plusieurs fournisseurs.');

-- creation du trigger ----
DELIMITER $$

DROP TRIGGER IF EXISTS before_insert_ligne_commande $$

CREATE TRIGGER before_insert_ligne_commande BEFORE INSERT ON ligne_commande FOR EACH ROW

BEGIN
  DECLARE nb INT;
  SELECT COUNT(fournisseur_id_ligne_commande) INTO nb
  FROM   ligne_commande 
  WHERE commande_id_ligne_commande = new.commande_id_ligne_commande 
  AND fournisseur_id_ligne_commande <> new.fournisseur_id_ligne_commande;
  
  IF nb != 0 THEN
     INSERT INTO erreur (text_erreur) VALUES ('Erreur : Une commande ne peut pas avoir plusieurs fournisseurs.');
   END IF;

END $$

DELIMITER ;

Tout fonctionne comme je le désire, merci vraiment à vous. Mais avant de cliquer sur "résolu", j'aimerais bien que vous me dites si ce que j'ai fait est correct

[fsmrel]

Bonsoir Benjamin,


Je n'ai toujours pas MySQL, donc je ne vérifierai pas, mais en tout cas votre trigger a une bonne tête et je vous fais confiance pour l'exhaustivité des tests