Discussion :

[Invité]

Bonjour,

Suite à la création d'un outil pour la gestion de projets + facturation dédié aux traducteurs (pour l'instant 1 seul ), j'avais créé une base de données un peu au jour le jour... Suite à des soucis de requêtes + discussions (http://www.developpez.net/forums/d14...ion-bdd-mysql/) sur ce forum, j'ai décidé de bien la construire cette fois.
J'envisage de développer une version 2 toujours en PHP/MYSQL mais un peu plus dynamique (avec js notamment). J'ai été formé sur les BDD durant mes études mais depuis, je n'ai jamais travaillé dessus à part pour des projets persos donc vous allez sûrement trouver des erreurs.
Je poste mon schéma + explication pour avoir l'oeil de personnes qui sont habitués aux BDD et qui vont rapidement voir si il y a un manque de logique, ou une possibilité de simplification...

Je ne sais pas si il suffit de lire le schéma pour comprendre, dans ma tête c'est clair , ça me paraît être une BDD simple avec des intitulés claires pour faciliter la compréhension (et pour m'y retrouver personnellement )

Je note quelques phrases "types" pour la BDD:
1 projet a 1 client, 1 tache et 1 type de projet (à l'heure, au forfait, au mot)
1 projet est facturé 1 seul fois dans la grande majorité des cas, suite à la discussion, j'ai laissé la possibilité de le facturer une seconde fois et de mettre un avoir.
Le projet et la facture peuvent être également affiché avec une second monnaie (Euro + dollar)
Si le type de paiement de la facture est paypal, automatiquement, on a une entrée dans la table paypal.
La table "FACTURE_CHANGE" n'est pas attaché à LIGNE_FACTURE tout simplement parce que la facture est obligatoirement de base en Euro, l'affichage dans une autre monnaie est un plus mais c'est la même, il n'y en a pas 2.
La table "PROJET_CHANGE" ne sert pas au calcul de la facture, elle permet d'avoir le détail dans l'autre monnaie. Sans elle, la facture n'aurait que le total dans la 2ème monnaie.

Les mots choisis ne sont peut-être pas les bons, désolé . Mes souvenirs de BDD sont surtout liés au schéma, liens & requêtes.

Merci pour votre aide. N'hésitez pas à me poser des questions pour clarifier certains points...

[fsmrel]

Bonsoir orygyn,



Il y a évidemment des remarques à faire, mais déjà il y a quelque chose de très gênant : selon votre diagramme, rien n'empêche que la facture F1 concernant le client C1 soit à régler par le client C2... : je ne pense pas que ce soit voulu...

Quoi qu’il en soit, selon la structure de votre table LIGNE_FACTURE, la ligne de facture LF1 peut faire référence à une certaine facture F1, à un client C1 et un projet P1. Mais rien n’interdit que le projet P1 fasse référence pour sa part au client C2...

Par ailleurs la ligne de facture LF2 peut faire référence elle aussi à la facture F1, au client C3 et au projet P4 qui fait référence au client C5 : en voilà une salade qu’elle ne manque pas de piquant...

Ne pensez-vous pas qu’il manquerait-quelques contraintes non modélisées dans cette affaire ? Comme dans le cas de Benallasiham, ça sent la contrainte de chemin à plein nez, on peut contrôler ça (à coups de triggers) en partie par le jeu des dépendances fonctionnelles entre attributs de la table LIGNE_FACTURE :

{id_facture} -> {id_client}

{Id_projet} -> {id_client}

Mais ça ne suffira malheureusement pas. Soit on prévoit un trigger supplémentaire pour s’assurer que le client faisant l’objet de la 2e DF est le même que celui qui est déterminé au sein de la table PROJET, soit on évite les triggers en ajoutant une association entre les tables CLIENT et FACTURE, et comme chez Benallasiham on joue l’identification relative à fond.

[Invité]

Effectivement, c'est une possibilité, j'y avais pensé par contre, je comptais mettre les contrôles en place dans le code PHP (peut-être ce qu'il ne faut jamais faire ?). Mais il est clair que si la BDD l'impose aussi, c'est forcément mieux...

A la base, sur la V1 qui tourne actuellement, la facture n'est pas directement lié au client, c'est un projet qui est lié au client et ensuite, on facture en reprenant tous les projets d'un client dans le mois. Mais cf lien dans mon 1er post, il semblerait qu'il ne faille surtout pas utilisé la valeur NULL sur une clé étrangère (cf vos posts )

Du coup, voilà pourquoi j'ai mis en place LIGNE_FACTURE tout en gardant id_client dans la table projet.
Donc si j'ai bien compris, je dois ajouter une table intermédiaire type LIGNE_FACTURE entre FACTURE et CLIENTS ? Il n'y aura pas trop de lien du coup entre CLIENTS, PROJETS, FACTURE et LIGNE_FACTURE ? + la nouvelle.

Merci pour votre aide

[Invité]

Une table LIEN_FACTURE_CLIENT(id_lien_fact_cli (PK), id_client (PK), id_facture (PK)) ?

[fsmrel]

Bonjour orygyn,



Plutôt qu’utiliser le terme « association » :

« en ajoutant une association entre les tables CLIENT et FACTURE »

J’aurais dû utiliser le terme « lien »...

Je modéliserais ainsi (je n’ai pas fait figurer tous les attributs) :




Vous noterez l’utilisation de l’identification relative (liens en traits pleins) : la table PROJET est identifiée relativement à la table CLIENT (après tout, un projet caractérise un client et lui seul), c'est-à-dire que la clé de PROJET n’est pas le singleton {ProjetId} mais la paire {ClientId, ProjetId}.

D’où le code SQL :

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(32)     NOT NULL
    , CONSTRAINT CLIENT_PK PRIMARY KEY (ClientId)
) ;

TABLE PROJET

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE PROJET 
(
          ClientId          INT             NOT NULL
        , ProjetId          INT             NOT NULL
        , ProjetNom         VARCHAR(32)     NOT NULL
    , CONSTRAINT PROJET_PK PRIMARY KEY (ClientId, ProjetId)
    , CONSTRAINT PROJET_CLIENT_FK FOREIGN KEY (ClientId)
          REFERENCES CLIENT (ClientId)
) ;

De la même façon, la table FACTURE est identifiée relativement à la table CLIENT (une facture caractérise un client et lui seul), c'est-à-dire que la clé de FACTURE n’est pas le singleton {FactureId} mais la paire {ClientId, FactureId}.

TABLE FACTURE

Code SQL :

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

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

Dans le même sens, la table LIGNE_FACTURE est identifiée relativement à la table FACTURE (une ligné de facture n’est jamais qu’une propriété d’une facture), c'est-à-dire que la clé de LIGNE_FACTURE n’est pas le singleton {LigneFactureId} mais le triplet {ClientId, FactureId, LigneFactureId}.

Il est important de noter que l’attribut ClientId a été propagé par deux chemins différents jusqu’à LIGNE_FACTURE, mais qu’il n’est présent qu’une seule fois dans l’en-tête de cette table, et fait référence à la fois à une facture d’un client et à un projet qui ne peut appartenir qu’à ce client.

TABLE LIGNE_FACTURE

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_FACTURE
 (
          ClientId          INT             NOT NULL
        , FactureId         INT             NOT NULL
        , LigneFactureId    INT             NOT NULL
        , ProjetId          INT             NOT NULL
    , CONSTRAINT LIGNE_FACTURE_PK PRIMARY KEY (ClientId, FactureId, LigneFactureId)
    , CONSTRAINT LIGNE_FACTURE_FACTURE_FK FOREIGN KEY (ClientId , FactureId)
           REFERENCES FACTURE (ClientId , FactureId)
           ON DELETE CASCADE
    , CONSTRAINT LIGNE_FACTURE_PROJET_FK FOREIGN KEY (ClientId, ProjetId)
          REFERENCES PROJET (ClientId, ProjetId)
) ;

Un jeu d’essai pour illustrer :

Clients

Code SQL :

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

1
2
3
4
5
INSERT INTO CLIENT (ClientId, ClientNom) VALUES (1, 'Ets Naudin') ;
INSERT INTO CLIENT (ClientId, ClientNom) VALUES (2, 'Volfoni SA') ;
INSERT INTO CLIENT (ClientId, ClientNom) VALUES (3, 'Dubicobit') ;
 
SELECT *, '' AS '<= CLIENT' FROM CLIENT ;

Au résultat :

Code :

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

1
2
3
4
5
ClientId    ClientNom    <= CLIENT
-------- 
       1    Ets Naudin
       2    Volfoni SA
       3    Dubicobit

Projets

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (1, 1, 'Projet 1 de Naudin') ;
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (1, 2, 'Projet 2 de Naudin') ;
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (1, 3, 'Projet 3 de Naudin') ;
 
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (2, 1, 'Projet 1 de Volfoni') ;
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (2, 2, 'Projet 2 de Volfoni') ;
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (2, 3, 'Projet 3 de Volfoni') ;
 
INSERT INTO PROJET (ClientId, ProjetId, ProjetNom) VALUES (3, 1, 'Projet dingue de Dubicobit') ;
 
SELECT *, '' AS '<= PROJET' FROM PROJET ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
ClientId    ProjetId    ProjetNom            <= PROJET
--------    --------
       1           1    Projet 1 de Naudin 
       1           2    Projet 2 de Naudin  
       1           3    Projet 3 de Naudin 
       2           1    Projet 1 de Volfoni 
       2           2    Projet 2 de Volfoni 
       2           3    Projet 3 de Volfoni 
       3           1    Projet dingue de Dubicobit

Observez que la numérotation des projets (attribut ProjetId) est relative aux clients : pour chaque client, la numérotation repart à 1.


Factures

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (1, 1, 123456789) ;
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (1, 2, 145320147) ;
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (1, 3, 158500036) ;
 
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (2, 1, 247820143) ;
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (2, 2, 257896022) ;
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (2, 3, 281478943) ;
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (2, 4, 291475478) ;
 
INSERT INTO FACTURE (ClientId, FactureId, FactureNumero) VALUES (3, 1, 347820143) ; 
 
SELECT *, '' AS '<= FACTURE' FROM FACTURE ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
ClientId    FactureId    FactureNumero    <= FACTURE
--------    ---------
       1            1    123456789 
       1            2    145320147 
       1            3    158500036 
       2            1    247820143 
       2            2    257896022 
       2            3    281478943 
       2            4    291475478 
       3            1    347820143

Lignes de factures

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (1, 1, 1, 1) ;
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (1, 1, 2, 1) ;
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (1, 2, 1, 2) ;
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (1, 3, 1, 3) ;
 
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (2, 1, 1, 1) ;
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (2, 2, 1, 1) ;
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (2, 3, 1, 2) ;
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (2, 4, 1, 3) ;
 
INSERT INTO LIGNE_FACTURE (ClientId, FactureId, LigneFactureId, ProjetId) VALUES (3, 1, 1, 1) ;
 
SELECT *, '' AS '<= LIGNE_FACTURE' FROM LIGNE_FACTURE ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ClientId    FactureId    LigneFactureId    ProjetId    <= LIGNE_FACTURE
--------    ---------    --------------
       1            1                 1           1
       1            1                 2           1
       1            2                 1           2
       1            3                 1           3
       2            1                 1           1 
       2            2                 1           1 
       2            3                 1           2
       2            4                 1           3
       3            1                 1           1

Exercice :

Essayez d’enfreindre la règle interdisant qu’une facture d’un client C1 soit affectée à un projet d’un client C2.

[Invité]

Au-delà de la réponse, merci pour le cours !
En lisant, je me suis souvenu rapidement de ça " Observez que la numérotation des projets (attribut ProjetId) est relative aux clients : pour chaque client, la numérotation repart à 1."

Effectivement, je me souviens maintenant de cette solution... Enfin je me souviens de l'avoir vu en cours !

Du coup, je vais relire tout ça et tester avant de proposer un nouveau schéma.

Petite question purement "esthétique" ou pas. Vu que je repars sur une nouvelle bdd, il faut mieux repartir sur des bonnes bases. Comme vous avez pu le voir, mes clés sont de type "id_client" alors que les vôtres sont "ClientId". Est-ce purement une question d'habitude ? S'agit-il d'une nomenclature "officiel" ou bien les "___" ne sont pas conseillés dans les tables ?

Merci à vous.