Gestion Facturation et devis

**Benallasiham** · 27/07/2013, 18h53

Bonjour tous le monde je suis débutante en développement et je suis entrain de realiser un petit logiciel de gestion des factures et devis voila mon MCD j'aimerai avoir vos avis et remarques mercii d'avance

Nom : Sans titre.jpg
Affichages : 14762
Taille : 107,8 Ko

**fsmrel** · 28/07/2013, 02h40

Bonjour toute seule, et bienvenue,

Une analyse rapide.

Via les associations CONTENIR et DEMANDER, une occurrence L1 de LIGNE_DEVIS détermine un certain client demandeur de devis, appelons-le Fernand.
Via les associations LIVRER, FACTURER et PAYER, cette même occurrence L1 de LIGNE_DEVIS détermine un certain client payeur qui peut être Raoul.

Si le client payeur et le client demandeur peuvent être différents, votre MCD est correct pour cet aspect particulier des choses. En revanche, si le client payeur et le client demandeur sont nécessairement la même personne, il manque une contrainte dans le diagramme.

L’entité-type LIGNE_FACTURE comporte d’une propriété Quantité : on peut supposer qu’il s’agit d’une quantité d’un certain article A1 (même chose pour le prix). Comment faites-vous pour déterminer l’article A1 ? L’article lui-même n’est pas caractérisé par un prix (à date) ? Si c’est le cas, je suppose que le prix porté par la facture peut être différent.

Quelle est la finalité de l’entité-type AVOIR ? Quelles sont les règles de gestion qui gouvernent sa relation avec l’entité-type LIGNE_FACTURE ? Je note que chaque ligne de facture fait mention d’un avoir.

Selon votre diagramme, chaque client règle au moins une facture. Que faites-vous du cas d’un nouveau client qui pour le moment n’a fait qu’une demande de devis ? Plus généralement, certaines cardinalités 1,N ne sont peut-être que des 0,N.

Sinon, pour un début c'est bien.

P.-S. Merci d’éviter le fond bleu pour le diagramme, ça fait mal aux yeux et ça rend les cardinalités sont difficiles à lire : un fond blanc serait le bienvenu.

**Benallasiham** · 02/08/2013, 21h14

Merciii pour votre analyse c'est très riche d'information et remarque

je dois réfléchir et traiter vos remarques et je vais faire un autre exemplaire qui va être en noir et blanc

a propos de la relation payer facture je peux la supprimer et savoir le client concerné par la facture de l'entité devis ce que je vois pas logique une autre solution au niveau de la réalisation de la base de données je peux faire une contrainte ou vérification si le client demandeur n'est pas le client payeur alors annuler l'ajout

d'une autre part je dois admettre que les cardinalités votre remarque et vrai je dois les corriger

et en fin l'avoir c'est le cas ou le client n'accepte pas un article l'entreprise dois justifier la facture régler par un avoir et ce dernier peut être appliquer sur toute la facture ou juste un article ou deux c'est pourquoi j'ai établit une relation entre l'avoir et ligne de facture en attendant de realiser un autre MCD si vous trouver des erreurs dans se que j'ai dis merci de me le dire

**fsmrel** · 05/08/2013, 05h33

Bonsoir Benallasiham,

Envoyé par Benallasiham

A propos de la relation payer facture je peux la supprimer et savoir le client concerné par la facture de l'entité devis ce que je vois pas logique.

On va chercher une solution avec laquelle on conservera la relation « Payer ».

Envoyé par Benallasiham

je peux faire une contrainte ou vérification si le client demandeur n'est pas le client payeur alors annuler l'ajout.

Le rôle de l’applicatif est de produire et pas de tirer les oreilles : les contraintes sont à sous-traiter au SGBD, vous devez seulement lui décrire ces contraintes. En ce sens, je vous propose deux scénarios.

1er scénario :

Mise en œuvre de triggers au niveau SQL pour effectivement rejeter les opérations (ajout, modification) faisant que le client facturé ne serait pas celui pour qui un devis a été établi.

Au préalable, je fais observer que la cardinalité portée par la patte connectant l’entité-type LIGNE_DEVIS et l’association LIVRER n’est pas 1,1 mais 0,1, car avant de le facturer et le livrer, il faut au moins que le client accepte le devis (si finalement il ne le refuse pas !) :

Les conséquences sont qu’au niveau du MLD (Modèle Logique de Données) l’association LIVRER doit faire l’objet d’une table (nommée BON_DETAIL ci-dessous) pour éviter que le bonhomme NULL vienne ficher la zoubia dans la base de données (SQL) :

N.B. Concernant les pattes connectant les tables, la notation utilisée pour les cardinalités est dite « patte d’oie » ou « patte de corbeau », Crow’s foot (cf. Outils > Options, « Notation conceptuelle du modèle » chez PowerAMC).

Pour vous montrer que le MCD n’est en fait que la partie émergée de l’iceberg, je vous montre le code SQL correspondant à la définition des tables, ainsi que les triggers nécessaires.

Définition des tables (SGBD : MS SQL Server) :

TABLE ARTICLE

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE ARTICLE
(
   ArticleId               INT                  NOT NULL,
   Designation             VARCHAR(64)          NOT NULL,
   CONSTRAINT ARTICLE_PK PRIMARY KEY (ArticleId)
) ;

TABLE CLIENT

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE CLIENT
(
        ClientId                INT                  NOT NULL,
        CLientNom               VARCHAR(64)          NOT NULL,
    CONSTRAINT CLIENT_PK PRIMARY KEY (ClientId)
) ;

TABLE DEVIS

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE DEVIS 
(
        DevisId                 INT                  NOT NULL,
        ClientId                INT                  NOT NULL,
        DateEnvoi               DATETIME             NOT NULL,
        DateValidation          DATETIME             NOT NULL,
        DevisMontant            INT                  NOT NULL,
    CONSTRAINT DEVIS_PK PRIMARY KEY (DevisId),
    CONSTRAINT DEVIS_CLIENT_FK FOREIGN KEY (ClientId) REFERENCES CLIENT
) ;

TABLE LIGNE_DEVIS

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE LIGNE_DEVIS
(
        LigneDevisId            INT                  NOT NULL,
        DevisId                 INT                  NOT NULL,
        ArticleId               INT                  NOT NULL,
        Quantite                INT                  NOT NULL,
        PrixHT                  INT                  NOT NULL,
        TVA                     DECIMAL(4,2)         NOT NULL,
    CONSTRAINT LIGNE_DEVIS_PK PRIMARY KEY (LigneDevisId),
    CONSTRAINT LIGNE_DEVIS_DEVIS_FK FOREIGN KEY (DevisId) REFERENCES DEVIS,
    CONSTRAINT LIGNE_DEVIS_ARTICLE_FK FOREIGN KEY (ArticleId) REFERENCES ARTICLE
) ;

TABLE FACTURE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE FACTURE 
(
        FactureId               INT                  NOT NULL,
        ClientId                INT                  NOT NULL,
        FactureDate             DATETIME             NOT NULL,
    CONSTRAINT FACTURE_PK PRIMARY KEY (FactureId),
    CONSTRAINT FACTURE_CLIENT_FK FOREIGN KEY (ClientId) REFERENCES CLIENT
) ;

TABLE BON_LIVRAISON

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE BON_LIVRAISON 
(
        BonId                   INT                  NOT NULL,
        FactureId               INT                  NOT NULL,
        DateLivraison           DATETIME             NOT NULL,
    CONSTRAINT BON_LIVRAISON_PK PRIMARY KEY (BonId),
    CONSTRAINT BON_LIVRAISON_FACTURE_FK FOREIGN KEY (FactureId) REFERENCES FACTURE
) ;

TABLE BON_DETAIL

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE BON_DETAIL
(
        BonId                   INT                  NOT NULL,
        LigneDevisId            INT                  NOT NULL,
    CONSTRAINT BON_DETAIL_PK PRIMARY KEY (LigneDevisId),
    CONSTRAINT BON_DETAIL_BON_LIVRAISON_FK FOREIGN KEY (BonId) REFERENCES BON_LIVRAISON,
    CONSTRAINT BON_DETAIL_LIGNE_DEVIS_FK FOREIGN KEY (LigneDevisId) REFERENCES LIGNE_DEVIS
) ;

Un 1er trigger permet de garantir la validité des INSERT dans la table BON_DETAIL, c'est-à-dire que le client facturé est bien celui qui a fait l’objet du devis :

CREATE TRIGGER ASSOCIATION_BON_DEVIS_INSERT_TR ON BON_DETAIL INSTEAD OF INSERT AS
DECLARE @N AS INT ; 

SET @N = (
          SELECT COUNT(*)
          FROM   FACTURE AS x JOIN BON_LIVRAISON AS y ON x.FactureId = y.FactureId 
                              JOIN INSERTED AS z ON y.BonId = z.BonId  
          WHERE  EXISTS
                       (
                        SELECT ''
                        FROM   DEVIS AS t JOIN LIGNE_DEVIS AS u ON t.DevisId = u.DevisId
                        WHERE  z.LigneDevisId = u.LigneDevisId AND x.ClientId <> t.ClientId
                       )
         ) ;
IF @N > 0
    /* --------------------------------
       Tentative d'infraction détectée
    */ --------------------------------
    BEGIN
        RAISERROR ('INSERT BON_DETAIL - Affectation à un client qui n’est pas le bon !', 16, 1)
        --ROLLBACK TRAN
       RETURN
    END
/* -----------------------------------------------------
   Si pas d'infraction, on peut effectuer l’insert
*/ -----------------------------------------------------
INSERT INTO BON_DETAIL (BonId, LigneDevisId)
    SELECT BonId, LigneDevisId
    FROM   INSERTED ;

Un 2e trigger permet de garantir la validité des UPDATE de la table BON_DETAIL :

CREATE TRIGGER ASSOCIATION_BON_DEVIS_UPDARZ_TR ON BON_DETAIL INSTEAD OF UPDATE AS
DECLARE @N AS INT ; 

SET @N = (
          SELECT COUNT(*)
          FROM   FACTURE AS x JOIN BON_LIVRAISON AS y ON x.FactureId = y.FactureId 
                              JOIN INSERTED AS z ON y.BonId = z.BonId  
          WHERE  EXISTS
                       (
                        SELECT ''
                        FROM   DEVIS AS t JOIN LIGNE_DEVIS AS u ON t.DevisId = u.DevisId
                        WHERE  z.LigneDevisId = u.LigneDevisId AND x.ClientId <> t.ClientId
                       )
         ) ;
IF @N > 0
    /* --------------------------------
       Tentative d'infraction détectée
    */ --------------------------------
    BEGIN
        RAISERROR ('UPDATE BON_DETAIL - Affectation à un client qui n’est pas le bon !', 16, 1)
        --ROLLBACK TRAN
       RETURN
    END

/* ----------------------------------------------------------------
    Si pas d'infraction, on peut effectuer l’update
    (on pourra chercher à améliorer la performance de ce qui suit) 
*/ ----------------------------------------------------------------
DELETE FROM BON_DETAIL
    WHERE BonId IN (
                    SELECT BonId
                    FROM   DELETED
                   )
    AND LigneDevisId IN (
                         SELECT LigneDevisId
                         FROM   DELETED
                        )
;
INSERT INTO BON_DETAIL (BonId, LigneDevisId)
    SELECT BonId, LigneDevisId
    FROM   INSERTED 
;

Un début de jeu d’essai :

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
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (1, 'schmilblick') ; 
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (2, 'biglotron') ; 
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (3, 'boulon') ; 
 
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (1, 'Etablissements Naudin Montauban') ; 
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (2, 'Volfoni Frères') ; 
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (3, 'Delafoy, Instruments de ménage') ; 
 
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (1, 1, '2013-08-01', '2013-08-05', 1500) ; 
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (2, 2, '2013-07-14', '2013-08-02', 7000) ; 
 
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 1, 1, 10, 100, 19.6) ; 
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 3, 3, 40, 150, 19.6) ; 
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 5, 3, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 6, 3, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 2, 2, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 4, 3, 20, 200, 19.6) ; 
 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 1, '2013-08-02') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (2, 2, '2013-07-28') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 3, '2013-08-03') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (2, 4, '2013-08-03') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 5, '2013-08-02') ; 
 
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (1, 1, '2013-08-03') ; 
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (1, 2, '2013-08-04') ; 
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (2, 3, '2013-07-29') ; 
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (3, 4, '2013-07-29') ; 
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (4, 5, '2013-07-29') ; 
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (5, 6, '2013-07-29') ; 
 
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (1, 2) ; 
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (3, 3) ;  -- infraction 
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (4, 4) ;  -- infraction
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (5, 6) ; 
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (2, 5) ; 
 
UPDATE BON_DETAIL 
    SET BonId = 4 WHERE BonId =  6 -- pas d'infraction
 
UPDATE BON_DETAIL 
    SET BonId = 1 WHERE BonId =  5 -- infraction
 
SELECT '' AS 'ARTICLE', * FROM ARTICLE ;
SELECT '' AS 'CLIENT', * FROM CLIENT ;
SELECT '' AS 'DEVIS', * FROM DEVIS ;
SELECT '' AS 'LIGNE_DEVIS', * FROM LIGNE_DEVIS ;
SELECT '' AS 'FACTURE', * FROM FACTURE ;
SELECT '' AS 'BON_LIVRAISON', * FROM BON_LIVRAISON ;
SELECT '' AS 'BON_DETAIL', * FROM BON_DETAIL ;

Mais on aura beau surveiller que le système soit toujours cohérent, il y aura toujours une faille...

En effet, modifions par exemple le client d’un devis :

Code SQL :

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

1
2
UPDATE DEVIS
    SET ClientId = 1 WHERE ClientId = 2 ;

Et du coup le client 2 (Volfoni Frères) reste le payeur de la facture 2, tandis que son devis a été affecté au client 1 (Établissements Naudin)...

Bref, méfiance, il faudrait des triggers à peu près sur toutes les tables pour assurer la cohérence. On va chercher à mieux blinder le système en remplaçant les triggers (pas toujours simples à programmer !) par la technique dite de l’identification relative.

2e scénario :

On commencera par observer que, dans votre MCD, en partant de l’entité-type CLIENT, on peut atteindre l’association LIVRER par deux chemins différents : l’un qui passe par DEVIS et l’autre par FACTURE. Si le client facturé et livré doit être celui qui a fait l’objet du devis, il y a donc une contrainte, que certains auteurs merisiens nomment depuis un bon moment « contrainte de chemin » (Flory A., Morejon J., Rochfeld A. : Toward a new generation of design tools : some basic ideas. First int conf. on TOOLS – Paris – 1989). Pour garantir la contrainte et éviter les triggers qui ne sont pas la panacée, je propose pour ma part de mettre en œuvre la technique de l’identification relative, permettant une plus grande efficacité tout en évitant les triggers.

Pour illustrer, prenons le cas des entités-types DEVIS et LIGNE_DEVIS. LIGNE_DEVIS est ce qu’on appelle une entité-type faible (weak entity type) parce qu’elle n’a pas d’existence autonome, elle n’a pas de sens en l’absence de DEVIS : une ligne de devis n’est qu’une propriété d’un devis, propriété multivaluée car un devis peut comporter plus lignes de devis. En Merise, il est d’usage de représenter une propriété multivaluée au moyen d’une entité-type, comme dans le système de Chen du reste (cf. The Entity-Relationship Model—Toward a Unified View of Data). Chen représente graphiquement une entité-type faible au moyen d’un rectangle imbriqué dans un rectangle (les cardinalités sont à l’inverse de celles de Merise, mais ça n’est pas un problème, le tout est de le savoir...) :

Avec PowerAMC, on obtient l’équivalent en identifiant LIGNE_DEVIS relativement à DEVIS, ce qui graphiquement est représenté par la mise entre parenthèses de la cardinalité 1,1 :

Avec WinDesign, on fait suivre la cardinalité 1,1 par la lettre « R » (cf. la FAQ Merise) :

Lors du passage au MLD, l’identifiant relatif se traduira par la clé primaire suivante de LIGNE_DEVIS :

{DevisId, LigneDevisId}

Si pour un devis donné l’attribut DevisId prend la valeur 'x', et si pour les lignes de devis de ce devis l’attribut LigneDevisId prend les valeurs 'a', 'b', 'c', etc., alors pour ce devis la clé primaire de la table LIGNE_DEVIS prendra les valeurs <'x', 'a'>, <'x', 'b'>, <'x', 'c'>, etc. En général 'a', 'b', 'c', etc. sont les entiers 1, 2, 3, etc. mais ça n’est pas une obligation, du moment que l’unicité des valeurs soit respectée (doublons interdits).

Généralisons maintenant l’identification relative (PowerAMC) :

Et produisons le MLD correspondant :

L’AGL a propagé l’identifiant du client jusqu’à la table BON_DETAIL par les deux branches (attribut ClientId par la branche Devis, attribut ClientId_2 par la branche Facture), mais il est parfaitement ignorant du fait que le client demandeur du devis est celui qui paye la facture, alors que pour nous qui le savons, la contrainte à mettre en œuvre est la suivante : {ClientId} = {ClientId_2}. Il n’y a rien de plus simple !

De ces deux attributs il suffit d’en faire un seul :

Cette fois-ci, selon la structure de la table BON_DETAIL, on ne peut avoir qu'un seul et même client côté devis et côté facture : le client qui règle la facture est forcément celui du devis. Mais peut-on contourner la contrainte en remplaçant par exemple la valeur de l’attribut ClientId au niveau de la table FACTURE (en admettant qu’il existe un client sans devis et sans facture pour lequel ClientId = la nouvelle valeur) ? La réponse est négative. Celui qui connaît bien les actions de compensation RESTRICT (ou NO ACTION selon le SGBD) et CASCADE des contraintes référentielles, sait que si l’on a codé RESTRICT (qui est l’action par défaut) pour la contrainte référentielle entre FACTURE et BON_LIVRAISON, la modification demandée sera rejetée par le SGBD tant qu’il existera un bon de livraison pour la facture impliquée dans cette affaire. Même principe bien sûr si l’action de compensation de la contrainte référentielle entre BON_LIVRAISON et BON_DETAIL est RESTRICT (ou NO ACTION). Si les actions de compensation sont CASCADE, un stimulus partira de FACTURE et parviendra à BON_DETAIL via BON_LIVRAISON, mais cette fois-ci c’est la contrainte référentielle entre LIGNE_DEVIS BON_DETAIL et LIGNE_DEVIS qui opposera un veto pour tentative de viol d’intégrité référentielle.

Si vous ne connaissez pas ou — pas suffisamment — SQL, tout ceci peut vous paraître compliqué, mais une fois que l’on a manipulé les actions de compensation, ça se clarifie. En tout cas, personnellement j’utilise l’identification relative, car non seulement ça résout le problème posé par la contrainte de chemin en se dispensant des triggers (lesquels n'offrent pas toutes les garanties voulues en matière d'intégrité des données), mais en plus ça simplifie le codage des requêtes dans lesquelles intervient l’attribut ClientId (lister par exemple les factures et les lignes de facture de tel client), et on s’y retrouve au niveau performance. Pour un exemple plus simple, voyez ici.

Le code SQL produit à partir de ce MLD ne nécessite pas de trigger (il est bon de se souvenir que les SGBD ne traitent pas tous des triggers de la même façon

). Essayez quand même de tricoter les devis du client Etablissements Naudin et les factures du client Volfoni Frères...

TABLE ARTICLE

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE ARTICLE
(
   ArticleId               INT                  NOT NULL,
   Designation             VARCHAR(64)          NOT NULL,
   CONSTRAINT ARTICLE_PK PRIMARY KEY (ArticleId)
) ;

TABLE CLIENT

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE CLIENT
(
        ClientId                INT                  NOT NULL,
        CLientNom               VARCHAR(64)          NOT NULL,
    CONSTRAINT CLIENT_PK PRIMARY KEY (ClientId)
) ;

TABLE DEVIS

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE DEVIS 
(
        ClientId                INT                  NOT NULL,
        DevisId                 INT                  NOT NULL,
        DateEnvoi               DATETIME             NOT NULL,
        DateValidation          DATETIME             NOT NULL,
        DevisMontant            INT                  NOT NULL,
    CONSTRAINT DEVIS_PK PRIMARY KEY (ClientId, DevisId),
    CONSTRAINT DEVIS_CLIENT_FK FOREIGN KEY (ClientId) REFERENCES CLIENT
) ;

TABLE LIGNE_DEVIS

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE LIGNE_DEVIS
(
        ClientId                INT                  NOT NULL,
        DevisId                 INT                  NOT NULL,
        LigneDevisId            INT                  NOT NULL,
        ArticleId               INT                  NOT NULL,
        Quantite                INT                  NOT NULL,
        PrixHT                  INT                  NOT NULL,
        TVA                     DECIMAL(4,2)         NOT NULL,
    CONSTRAINT LIGNE_DEVIS_PK PRIMARY KEY (ClientId, DevisId, LigneDevisId),
    CONSTRAINT LIGNE_DEVIS_DEVIS_FK FOREIGN KEY (ClientId, DevisId) REFERENCES DEVIS,
    CONSTRAINT LIGNE_DEVIS_ARTICLE_FK FOREIGN KEY (ArticleId) REFERENCES ARTICLE
) ;

TABLE FACTURE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE FACTURE 
(
        ClientId                INT                  NOT NULL,
        FactureId               INT                  NOT NULL,
        FactureDate             DATETIME             NOT NULL,
    CONSTRAINT FACTURE_PK PRIMARY KEY (ClientId, FactureId),
    CONSTRAINT FACTURE_CLIENT_FK FOREIGN KEY (ClientId) REFERENCES CLIENT
) ;

TABLE BON_LIVRAISON

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE BON_LIVRAISON 
(
        ClientId                INT                  NOT NULL,
        FactureId               INT                  NOT NULL,
        BonId                   INT                  NOT NULL,
        DateLivraison           DATETIME             NOT NULL,
    CONSTRAINT BON_LIVRAISON_PK PRIMARY KEY (ClientId, FactureId, BonId),
    CONSTRAINT BON_LIVRAISON_FACTURE_FK FOREIGN KEY (ClientId, FactureId) REFERENCES FACTURE
        -- ON UPDATE CASCADE
) ;

TABLE BON_DETAIL

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE BON_DETAIL
(
        ClientId                INT                  NOT NULL,
        DevisId                 INT                  NOT NULL,
        LigneDevisId            INT                  NOT NULL,
        FactureId               INT                  NOT NULL,
        BonId                   INT                  NOT NULL,
    CONSTRAINT BON_DETAIL_PK PRIMARY KEY (ClientId, DevisId, LigneDevisId),
    CONSTRAINT BON_DETAIL_LIGNE_DEVIS_FK FOREIGN KEY (ClientId, DevisId, LigneDevisId) REFERENCES LIGNE_DEVIS,
    CONSTRAINT BON_DETAIL_BON_LIVRAISON_FK FOREIGN KEY (ClientId, FactureId, BonId) REFERENCES BON_LIVRAISON
      -- ON UPDATE CASCADE
) ;

Un début de jeu d’essai :

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
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (1, 'schmilblick') ; 
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (2, 'biglotron') ; 
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (3, 'boulon') ; 
 
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (1, 'Etablissements Naudin Montauban') ; 
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (2, 'Volfoni Frères') ; 
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (3, 'Delafoy, Instruments de ménage') ; 
 
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (1, 1, '2013-08-01', '2013-08-05', 1500) ; 
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (1, 2, '2013-08-01', '2013-08-05', 1500) ; 
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (2, 2, '2013-07-14', '2013-08-02', 7000) ; 
 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 1, 1, 1, 10, 100, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 1, 3, 3, 40, 150, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 1, 5, 3, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 1, 6, 3, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 2, 4, 1, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 2, 7, 3, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 2, 2, 2, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 2, 3, 2, 20, 200, 19.6) ; 
INSERT INTO LIGNE_DEVIS (ClientId, DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 2, 4, 3, 20, 200, 19.6) ; 
 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 1, '2013-08-02') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 3, '2013-08-03') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 5, '2013-08-02') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (2, 2, '2013-07-28') ; 
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (2, 4, '2013-08-03') ; 
 
INSERT INTO BON_LIVRAISON (ClientId, FactureId, BonId, DateLivraison) VALUES (1, 1, 1, '2013-08-03') ; 
INSERT INTO BON_LIVRAISON (ClientId, FactureId, BonId, DateLivraison) VALUES (1, 1, 2, '2013-08-04') ; 
INSERT INTO BON_LIVRAISON (ClientId, FactureId, BonId, DateLivraison) VALUES (1, 3, 4, '2013-07-29') ; 
INSERT INTO BON_LIVRAISON (ClientId, FactureId, BonId, DateLivraison) VALUES (1, 5, 6, '2013-07-29') ; 
INSERT INTO BON_LIVRAISON (ClientId, FactureId, BonId, DateLivraison) VALUES (2, 2, 3, '2013-07-29') ; 
INSERT INTO BON_LIVRAISON (ClientId, FactureId, BonId, DateLivraison) VALUES (2, 4, 5, '2013-07-29') ; 
 
INSERT INTO BON_DETAIL (ClientId, DevisId, LigneDevisId, FactureId, BonId) VALUES (1, 1, 1, 1, 2) ; 
INSERT INTO BON_DETAIL (ClientId, DevisId, LigneDevisId, FactureId, BonId) VALUES (1, 1, 5, 5, 6) ; 
INSERT INTO BON_DETAIL (ClientId, DevisId, LigneDevisId, FactureId, BonId) VALUES (1, 2, 4, 3, 4) ; 
INSERT INTO BON_DETAIL (ClientId, DevisId, LigneDevisId, FactureId, BonId) VALUES (2, 2, 2, 4, 5) ; 
INSERT INTO BON_DETAIL (ClientId, DevisId, LigneDevisId, FactureId, BonId) VALUES (2, 2, 3, 2, 3) ;

J'ai été un peu bavard dans ce message, mais j'espère qu’il vous aura été utile (ainsi qu'à d'autres).

**fsmrel** · 05/08/2013, 05h39

En complément :

Envoyé par Benallasiham

L'avoir c'est le cas ou le client n'accepte pas un article, l'entreprise doit justifier la facture réglée par un avoir et ce dernier peut être appliqué sur toute la facture ou juste un article ou deux c'est pourquoi j'ai établi une relation entre l'avoir et ligne de facture.

Bien sûr, mais selon votre modélisation, rien n’interdit que le même avoir fasse référence au client Naudin et au client Volfoni, ce qui ferait désordre... Il faut blinder, revoir cela de telle sorte que le mélange des clients ne puisse se produire. Ça rappelle quelque chose....

**Benallasiham** · 06/08/2013, 02h06

MERCIII pour le temps que vous m'avez considéré je vais lire attentivement les remarques et les corrigé mercii pour votre réponse détaillée

**fsmrel** · 06/08/2013, 20h16

Et n'oubliez pas de voter pour les réponses qui vous ont été utiles...

**Benallasiham** · 07/08/2013, 04h54

Le 1 er scenario m a donné une idée très détaillée sur la direction de mon analyse avec un éclairage sur les erreurs et les diffeculté que je vais avoir en gardant les mêmes tables et laisser les triggers gerer les failles
pour le 2 scenario
alors là chapeau bas pour votre methode d'anlayse très claire

et j'ai fini par une conclusion que je suis très debutante et je dois enrichir mes connaissances au niveau d'analyse mercii bcp pour votre réponse détaillé et je dois admittre que quand j'ai réaliser mon mcd j'ai était très simple dans mon analyse et je connais pas plusieurs notions que vous avez decter comme <<contrainte de chemin>> et <<’identification relative>> et aussi j'ai conctaté que j'avais pas de problèmes dans la detection des entités et les relations mais comment les rendre plus logiques et cohérantes

mercii infinimant .

**fsmrel** · 11/08/2013, 21h20

Bonsoir Benallasiham,

Vos commentaires font plaisir.

Envoyé par Benallasiham

je vais faire un autre exemplaire qui va être en noir et blanc

Il sera le bienvenu.

Je repose ma question :

L’entité-type LIGNE_FACTURE comporte d’une propriété Quantité : on peut supposer qu’il s’agit d’une quantité d’un certain article A1 (même chose pour le prix). Comment faites-vous pour déterminer l’article A1 ? L’article lui-même n’est-il pas caractérisé par un prix (à date) ? Si c’est le cas, je suppose que le prix porté par la facture peut être différent.

Bon courage.

**Benallasiham** · 23/09/2013, 13h30

la solution que j'ai obtenu actuellement là voila mais j'ai d'autre cas que je ne sais pas comment les représenter alors voila :
MCD
Nom : Sous-modèle1.jpg
Affichages : 10611
Taille : 53,6 Ko

Nom : Sous-modèle1.jpg
Affichages : 10611
Taille : 53,6 Ko

MLD

Nom : Sous-modèle2.jpg
Affichages : 10905
Taille : 64,0 Ko

mais le 1 problème est : le cas ou le client ne passe pas par la phase devis car quand on est des clients fidèles on a pas besoin d'un devis on fait la commande par téléphone et on reçoit le bon de livraison directement alors dans ce cas il doit y avoir une relation direct entre l'entité bon de livraison article et client en conséquence une redendance de la clé client .
je ne sais pas comment traiter ça

le 2 problème c'est que j'ai effacé la ligne de facture car en fin de conte la facture c'est un ensemble de bon de livraison alors je peux savoir les articles via la table ligne de devis et bon livraison mais j'ai un autre probleme c'est l'avoir sur une facture je sais pas comment le gérer comme vous allez le constater j'ai pas mis la entité avoir comme l'autre fois car honetement je ne sais pas ou la placer dans l'antien model c'était facile car il y a l'entité ligne de facutre ce que je voix comme une repetition du meme donné ligne devis
a la fin je ne sais pas est que je suis dans la bonne voie ou je me suis perdu par les cas particulier

**fsmrel** · 23/09/2013, 18h20

Bonsoir Benallasiham,

Est-ce que cette partie du MCD fonctionne pour les clients fidèles c'est-à-dire ceux pour lesquels il n’y a pas de devis ?

**Benallasiham** · 23/09/2013, 18h27

ui je pense que ui il est capable de satisfaire le client

j'espère que je ne suis pas une idiote

**fsmrel** · 24/09/2013, 00h34

Bonsoir Benallasiham,

Hum... Voilà un oui qui n’est pas franc et massif... Mais bon, on va faire comme si...

Partons sur les règles de gestion suivantes :

Une facture fait l’objet d’au moins un bon de livraison ;
Un bon de livraison fait référence à une seule et une seule facture ;
Pour un client considéré comme fidèle, un bon de livraison ne fait pas nécessairement l’objet d’un devis ;
Pour un client non considéré comme fidèle, un bon de livraison fait l’objet d’un devis.

Si ces règles vous convient, un aménagement possible du MCD est le suivant (avec PowerAMC) :

En rouge, figure une contrainte de partitionnement (totalité + exclusivité) signifiant qu’un bon de livraison est soit avec devis soit sans devis.

Un bon avec devis fait l’objet de lignes détail, chacune faisant référence à une ligne de devis.

Un bon sans devis fait l’objet de lignes détail, chacune faisant référence à un article.

MLD correspondant :

La contrainte de partitionnement est toujours là, elle fera l’objet d’un trigger.

A noter que l’attribut LigneDevisId n’est pas strictement nécessaire, la clé de la table LIGNE_DEVIS est alors la suivante : {ClientId, DevisId, ArticleId}. Il en va donc de même pour la table BON_DETAIL_AVEC_DEVIS. Pour les lignes des bons de livraison sans devis, on procède de la même façon, d’où la structure de la table BON_DETAIL_SANS_DEVIS : {ClientId, FactureId, BonId, ArticleId}.

Est-ce que cela vous convient ? Commencez-vous à sécher vos larmes ? Quand on sera d’accord sur cette partie, on passera aux avoirs.

Bon courage...

**Benallasiham** · 24/09/2013, 02h44

bon mme si j'ai pas aimé le fait que vous repondez ui mais je l'accepte maintenant car j'ai bcp de chose à apprendre . et je suis ici juste pour ça et chaqu'un de nous commence du point 0 = idiot

retournant a notre sujet il me parait que c'est logique mercii

**fsmrel** · 24/09/2013, 20h37

Bonsoir Benallasiham,

Dans le MCD que je propose ci-dessous, on branche un avoir soit sur une facture, soit sur un bon, soit sur un détail de bon (on est alors au niveau de l’article). En ce qui concerne le détail de bon, soit celui-ci correspond à une ligne de devis (entité-type AVOIR_DETAIL_AVEC) ou non (entité-type AVOIR_DETAIL_SANS). Est-ce que cela vous convient ?

MCD Avoirs

MLD Avoirs

Quant à ma réponse précédente, je n’ai pas voulu vous froisser, j’ai simplement crû que vous attendiez l’avis de l’utilisateur, donc qu’on pouvait encore être dans le doute, en attente d’un contre-ordre.

**Benallasiham** · 24/09/2013, 21h45

bonsoir mon prof d'analyse

vraiment votre analyse est forte je suis totalement convaincu par cette reponse et je pense que j'ai pas d'autre question a poser mes problèmes sont résolu merci infiniment pour votre aide
une dernère question comment vous avez pu acquerir tout ça ?

c'est juste pour rigoler , je pense que problème résolu

**fsmrel** · 24/09/2013, 22h03

Je suis content que la solution vous satisfasse. Comment j'ai acquis tout ça ? Plus de quarante années chez les clients de la SSII qui m'a employé (banques, assurances, industriels, caisses de retraite, monde de la distribution, de la VPC, armée, hôpitaux, organismes publics, etc., etc.) à modéliser, administrer les bases de données, apprendre, enseigner, engager mon entreprise sur la performance des applications, donc prototyper encore et encore, conseiller, soigner, répondre aux appels au secours... It's a long way

Dans quarante ans peut-être ferez-vous un bilan à votre tour et aiderez aussi les plus jeunes ?
En passant, n'oubliez pas de voter pour les réponses qui ont pu vous aider.

**Benallasiham** · 25/09/2013, 12h28

j'espère que un jour je serai comme vous , en attendant je suis très contente

que j'ai poser ce sujet car j'ai appris bcp de chose de vous et j'éspère pour vous la réussite dans tous ce que vous faites et pour moi la patience et le courage et la volonté pour acquerir la comptence quelle me faut

Gestion Facturation et devis [MCD]

Schéma

Discussions similaires

Partager

Partager