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.

[fsmrel]

Bonsoir orygynz,

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 ?

Les "____" sont parfaitement légaux. Pour ma part, c’est affaire d’habitude, car j’ai commencé avec les ordinosaures, les ordinateurs de 2e génération, et on avait droit seulement aux lettres majuscules et aux chiffres...


En passant : n’oubliez pas de voter quand une réponse a pu vous rendre service...

[Invité]

Ok ça marche, du coup, je vais continuer avec mes habitudes pour les noms.

J'ai copié/collé toutes les commandes dans une base TEST sur phpmyadmin. C'est bizarre, je n'ai pas de message d'erreur mais les contraintes de clé étrangères n'ont pas l'air d'être prise en compte pour LIGNE_FACTURE. Dans le concepteur, cette table se retrouve sans lien.

Le fait que tout reparte à 1, il n'est pas simple de s'y retrouver dans cette table

[fsmrel]

Je ne connais pas phpmyadmin, mais cet outil comprend-il bien tout ce qui tourne autour des clés étrangères ?

A défaut, pouvez-vous ajouter ces clés à la main ? Elles sont quand même au coeur de la base de données, c'est le filet du trapéziste...

[Invité]

PHPmyadmin, c'est juste un outil graphique pour gérer une base MYSQL.

Du coup, j'ai pu copier/coller les commandes SQL sans souci. Mais c'est bizarre, il ne prends pas en compte les clé étrangères, dans la vue graphiques, on ne peut pas lui dire de mettre (id_client + id_projet) en clé étrangère, il en veut une seule....

Je vais me renseigner et re-poster mon nouveau diagramme.

[fsmrel]

PHPmyadmin, c'est juste un outil graphique pour gérer une base MYSQL.

Dans ce cas-là, pourquoi ne pas en rester à MySQL Workbench ?

[Invité]

Tout simplement car je connais pas trop MySQL Workbench mais pas mal PHPmyadmin !!!

Enfin entre temps, j'ai regardé tout ça, c'est bon sous workbench, si je retourne sus PHPmyadmin, en fait, soit c'est moi qui comprends pas tout, soit c'est une limitation graphique. On peut gérer la double clé étrangère dans l'index en bas....

Enfin, du coup, j'ai testé, il me mets "Duplicate entry". Mais c'est vrai que c'est pas simple de s'y retrouver quand ça repart à 1. Exemple le projet 253 ! Et bien non, là, c'est client 1 projets 65

Du coup, je vais adapter mon MCD et je le remets. Aviez-vous remarquer d'autres problèmes ?

Sinon, sur les UPDATE & DELETE des clés étrangères, je mets toujours RESTRICT, j'ai vu que vous aviez pu mettre CASCADE sur les DELETE. Y'a une règle ou on mets ce qu'on veut ? (Enfin dans la limite de la logique)

Merci.

[fsmrel]

Mais c'est vrai que c'est pas simple de s'y retrouver quand ça repart à 1. Exemple le projet 253 ! Et bien non, là, c'est client 1 projets 65

Certes ! Mais gérer l’identification relative avec MySQL, ça marche, voyez par exemple ici, où l’on constate que ça n’est pas aussi évident avec Access. Regardez aussi le billet de CinePhil.

Aviez-vous remarqué d'autres problèmes ?

Voui. Je constate qu’il y a pas mal d’attributs pouvant accepter la présence du bonhomme Null, lequel est banni du modèle relationnel de données, car dangereux, il porte de la dynamite logique. Je note aussi que vous avez des tableaux fixes à 5 éléments chacun : que ferez-vous quand il faudra passer à plus de 5 ? (ça me rappelle l’histoire du collègue qui avait prévu 7 colonnes dans ses stats réseau (une par jour de la semaine) et à qui un beau jour on a demandé de passer à une colonne par jour de l’année : il a compris qu’il aurait mieux valu verticaliser tout ça. Ça me rappelle aussi une table au cœur d’un système d’exploitation chez IBM (au début des années soixante-dix), prévue pour qu’un ordinateur gère au maximum 256 périphériques : manque de chance, il a fallu passer à 64000...

Pour le reste, il faudrait que j’en sache plus sur les règles de gestion des données. Par exemple, pour un projet et une tâche, peut-on avoir plus d’une ligne projet ? Qu’est-ce qu’un mot ? (mais je ne voudrais pas être indiscret...)

sur les UPDATE & DELETE des clés étrangères, je mets toujours RESTRICT, j'ai vu que vous aviez pu mettre CASCADE sur les DELETE. Y'a une règle ou on mets ce qu'on veut ? (Enfin dans la limite de la logique).

Je vois que vous suivez ! Le choix de RESTRICT ou CASCADE relève du niveau sémantique et de la réflexion plus que de l’émotion. Supposons que quelqu’un cherche à supprimer un client qui nous doit des sous : si on dit que la suppression du client entraîne la suppression de ses factures (CASCADE), c’est le client qui va être content ! Mais plus de trace chez nous de son ardoise, nous sommes refaits... autrement dit, on retiendra l’option RESTRICT dans ce genre de cas de figure. Par contre, si l’on supprime légitimement telle facture, les lignes de cette facture ne peuvent pas s’y opposer (donc CASCADE), puisqu’elles n’en constituent qu’une propriété multivaluée : Au niveau sémantique, LIGNE_FACTURE est ce qu’on appelle une entité-type faible (weak entity type chez Peter Chen, père du modèle entité/relation). Au fond, je vous invite à réfléchir de la pertinence du choix de CASCADE ou RESTRICT pour chaque lien entre les tables, et pourquoi justement ce mot « cascade » plutôt qu’un autre.

[Invité]

Certes ! Mais gérer l’identification relative avec MySQL, ça marche, voyez par exemple ici, où l’on constate que ça n’est pas aussi évident avec Access. Regardez aussi le billet de CinePhil.

J'ai parcouru rapidement les liens, il ne s'agit que de TRIGGERS, c'est justement pour m'en dégoûter ?
J'ai lu il y a quelques années que les TRIGGERS pouvaient amener des problèmes de performance de BDD, après, je n'ai jamais regardé de près tout ça...

Voui. Je constate qu’il y a pas mal d’attributs pouvant accepter la présence du bonhomme Null, lequel est banni du modèle relationnel de données, car dangereux, il porte de la dynamite logique. Je note aussi que vous avez des tableaux fixes à 5 éléments chacun : que ferez-vous quand il faudra passer à plus de 5 ? (ça me rappelle l’histoire du collègue qui avait prévu 7 colonnes dans ses stats réseau (une par jour de la semaine) et à qui un beau jour on a demandé de passer à une colonne par jour de l’année : il a compris qu’il aurait mieux valu verticaliser tout ça. Ça me rappelle aussi une table au cœur d’un système d’exploitation chez IBM (au début des années soixante-dix), prévue pour qu’un ordinateur gère au maximum 256 périphériques : manque de chance, il a fallu passer à 64000...

Alors effectivement, il ya du NULL, il s'agit du commentaires de la table AVOIR, est-ce réellement un problème pour un commentaire ? Après, je peux très bien le rendre obligatoire.
Sinon, il s'agit des 3 tables avec des catégories (MOT/MATCH/TARIF_MOT). En fait, pour donner un exemple, on part d'un projet avec seulement des "nouveaux mots". Du coup, seul la catégorie 1 est rempli avec le nombre de MOT, son MATCH (en l’occurrence Nouveaux mots) et son TARIF, je dis n'importe quoi mais 1 € le mot.
Un 2ème projet peut avoir MATCH de nouveaux mots et MATCH de répétitions qui ne coute que 0.5.... Voilà pourquoi je ne suis obligé de rendre NULL la catégorie de 2 à 5.
Pour répondre à la 2ème question, oui, au-dessus de 5, ça deviendrait problématique et oui ça peut arriver... Je m'étais dis possibilité d'écrire une nouvelle catégorie au besoin sur l'outil (ALTER TABLE... pour nouvelle colonne).
Mais si il y a une autre solution, je suis preneur.

PS: Je pensais qu'il fallait éviter les champs NULL au niveau des clés étrangères seulement.

Pour le reste, il faudrait que j’en sache plus sur les règles de gestion des données. Par exemple, pour un projet et une tâche, peut-on avoir plus d’une ligne projet ? Qu’est-ce qu’un mot ? (mais je ne voudrais pas être indiscret...)

Le mot, c'est le nombre de mots pour le type de projet dit "au mot".
Alors actuellement non, je n'ai pas de table LIGNE_PROJET et un projet peut avoir seulement 1 tache. Pour la V2, un projet pourrait avoir 2 tâches, par exemple, traduire + relire. Dans ce cas, on aurait 2 lignes pour le projet. L'un serait au mot et l'autre à l'heure (par exemple).

Je vois que vous suivez ! Le choix de RESTRICT ou CASCADE relève du niveau sémantique et de la réflexion plus que de l’émotion. Supposons que quelqu’un cherche à supprimer un client qui nous doit des sous : si on dit que la suppression du client entraîne la suppression de ses factures (CASCADE), c’est le client qui va être content ! Mais plus de trace chez nous de son ardoise, nous sommes refaits... autrement dit, on retiendra l’option RESTRICT dans ce genre de cas de figure. Par contre, si l’on supprime légitimement telle facture, les lignes de cette facture ne peuvent pas s’y opposer (donc CASCADE), puisqu’elles n’en constituent qu’une propriété multivaluée : Au niveau sémantique, LIGNE_FACTURE est ce qu’on appelle une entité-type faible (weak entity type chez Peter Chen, père du modèle entité/relation). Au fond, je vous invite à réfléchir de la pertinence du choix de CASCADE ou RESTRICT pour chaque lien entre les tables, et pourquoi justement ce mot « cascade » plutôt qu’un autre.

OK... C'est un peu mon côté "NE FAIS PAS CONFIANCE QUAND C'EST AUTO", plutôt du genre à lancer une commande SQL pour supprimer moi même une entrée... Par exemple, dans l'exemple cité, je contrôle que le client n'a pas de facture. Si il en a et que je suis ok, je les supprime puis je le supprime... tout ça au niveau php.
Dans l'exemple numéro 2, je "cascade" manuellement en lançant de la même façon 2 DELETE FROM.....
Alors en y réfléchissant, c'est vrai que je demande 2 connexions à ma BDD au lieu d'une... Sûrement que ça vient du fait que je ne maîtrise pas complètement ma BDD

[Invité]

Je réfléchissais à un autre problème où tu as peut-être l'habitude.

Sur la notion de devis. En fait, c'est assez étrange mais on a pas de devis pour les clients réguliers, les agences fournissent un "devis". Oui oui c'est le monde à l'envers.
J'ai la réponse pour associer un devis à une facture et client avec le lien que tu m'avais donné tout au début.
Par contre, comment faire pour ceux qui ne sont pas clients ? Certaines personnes demande un devis mais on a plus jamais de retour, ce ne sont pas des CLIENTS, du coup, si je créé une table temporaire pour eux, comment rattacher cette table temporaire à la place d'un client ?

Merci.

[fsmrel]

Bonsoir orygynz,

J'ai lu il y a quelques années que les TRIGGERS pouvaient amener des problèmes de performance de BASE DE DONNEES

Il ne faut pas faire confiance comme si souvent à ce qui est écrit dans la presse du cœur informatique, mais faire ses propres prototypes de performance. Cela dit, un trigger est un objet contrôlé par le SGBD, alors que le code qu’on développe dans un programme échappe à tout contrôle.

Alors effectivement, il y a du NULL, il s'agit du commentaire de la table AVOIR, est-ce réellement un problème pour un commentaire ?

Je commencerai par une remarque préalable à propos du bonhomme Null :

Null nous fait sortir des clous de la logique bivalente ('vrai', 'faux'), on doit se hasarder consciemment ou non dans les sables mouvants de la logique trivalente ('vrai', 'faux', 'on sait pas'), 999 fois sur 1000 sans en connaître les lois... Dans le Null Land, une tautologie n’est plus une tautologie (a> 0 ou a < 0 ou a = 0 n’est plus vrai). Ainsi, le biconditionnel p SI ET SEULEMENT SI p n’est plus une tautologie, l’algèbre relationnelle ne peut plus s’appuyer en aveugle sur la commutativité, l’associativité et consorts, la validité de la jointure n’est plus garantie, on ne peut plus s'appuyer sur le théorème de Heath, d’où l’inhibition des moteurs relationnels qu’on ne veut quand même pas voir dysfonctionner.

Autoriser Null c’est mettre le doigt dans l’engrenage, manipuler de la dynamite sans être artificier. Ça se passera normalement sans bobo dans le cas du commentaire de la table AVOIR, mais disons que c’est une question de discipline, et rien n’empêche qu’un commentaire puisse prendre la valeur ' ' (disons vide)

Alors qu’en général les gens parlent à tort de la « valeur nulle », n’oublions pas que Null n’est pas une valeur, mais un marqueur. Même ceux qui font la norme SQL s’y laissent prendre, et je rappelle la superbe boulette qu’on y trouve :

The data type boolean comprises the distinct truth values True and False. Unless prohibited by a NOT NULL constraint, the boolean data type also supports the truth value Unknown as the null value. This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL <predicate>, <search condition>, or <boolean value expression>; they may be used interchangeably to mean exactly the same thing.

Comme le note malicieusement Peter Gulutzan (SQL-99 Complete, Really), selon la norme il revient au même de dire : « Je ne sais pas » et « Je sais que l’information est absente » !

il s'agit des 3 tables avec des catégories (MOT/MATCH/TARIF_MOT). En fait, pour donner un exemple, on part d'un projet avec seulement des "nouveaux mots". Du coup, seul la catégorie 1 est rempli avec le nombre de MOT, son MATCH (en l’occurrence Nouveaux mots) et son TARIF, je dis n'importe quoi mais 1 € le mot.
Un 2ème projet peut avoir MATCH de nouveaux mots et MATCH de répétitions qui ne coute que 0.5.... Voilà pourquoi je ne suis obligé de rendre NULL la catégorie de 2 à 5.
Pour répondre à la 2ème question, oui, au-dessus de 5, ça deviendrait problématique et oui ça peut arriver... Je m'étais dis possibilité d'écrire une nouvelle catégorie au besoin sur l'outil (ALTER TABLE... pour nouvelle colonne).

Ouch ! Je ne sais toujours pas ce qu’est un mot... En attendant, je vais supposer qu’une ligne-projet est constituée d’un nombre quelconque de mots, qu’elle en est un assemblage (vous me dites quand je suis à côté..). Un mot peut appartenir à 5 catégories (mais le nom de l’attribut correspondant nb_mots_cat(i) est déroutant, de quoi en retourne-t-il réellement ? Merci de donner des exemples).

Si le ième attribut nb_mots_cat(i) est valorisé, doit-il en être de même pour l’attribut tarif_cat(i) ? pour l’attribut match_cat(i) ? Autrement dit, y a-t-il bijection ? Ou bien si seul nb_mots_cat(1) est valorisé, tarif_cat(1), tarif_cat(2), etc. peuvent-ils être simultanément valorisés ?

Tout cela est bizarre autant qu’étrange...

Le mot, c'est le nombre de mots pour le type de projet dit "au mot".

Qu’est-ce qu’un projet ? Qu’est-ce qu’un projet « au mot ? » Un roman de Balzac ?

Comment faire pour ceux qui ne sont pas clients ?

Ce sont des prospects ? Il faudrait peut-être envisager la mise en oeuvre d’une entité-type PERSONNE, avec deux sous-types : clients réguliers et clients potentiels d’autre part, et de toute façon faire apparaître l’entité-type DEVIS, au moins pour ces derniers.


Tout ça reste quand même glauque...

[Invité]

Bonjour,

glauque ??? C'est dur non ?

Pour les valeurs NULL, c'est noté. Du coup, je partirais sur des champs NOT NULL qui seront vide.

Pour la partie DEVIS, oui on peut appeler ça des PROSPECTS. En faite, la partie Devis est très rare. Dans mon cas, 3 en 2 ans...
Voilà pourquoi actuellement, la table DEVIS que j'utilise est autonome... On retape directement un nom client, son adresse, etc... Pour chaque ligne de la table. Je ne sais pas encore si je veux partir dans une table DEVIS lié au reste de la BDD, avec en plus des prospects... Voilà pourquoi je posais la question...

Pour en revenir à notre sujet, je vais essayer d'être plus clair et de donner des exemples pour faciliter la compréhension. Je suis sûrement un peu trop "tête dans le guidon".

Un projet est un projet de traduction. La table TACHE nous indique juste le type du travail (traduction, transcription, relecture... etc..).
Après, pour la partie "tarif du projet", nous avons 3 types de facturation
- FORFAIT : le projet coûte au client 50 €, la durée, le nombre de mots à traduire n'a aucun impact.
- HEURE : le prix est payé à l'heure, il faut 5h, l'heure coûte 5 €. Point barre.
- MOT : C'est le plus utilisé, on fait payer le client au mots. La plupart du temps, c'est le client qui donne les catégories de mots, leur tarif (négocié en amont) et le nombre. Des logiciels spécialisés pour traducteurs traite le document pour ressortir un tableau avec son nombre de mots, les mots répétés, en partie identique, etc...
Exemple :

TABLE MOT:

id_mot	nb_mots_cat1	nb_mots_cat2	nb_mots_cat3	nb_mots_cat4	nb_mots_cat5	id_ligne_projet
1	1500	250	/	/	/	75
2	6580	30	95	/	/	76

TABLE MATCH:

id_match	match_cat1	match_cat2	match_cat3	match_cat4	match_cat5	id_mot
1	Nouveaux mots	Répétitions	/	/	/	1
2	Nouveaux mots	Répétitions	Mots 60%	/	/	2

Table TARIF:

id_tarif	tarif_cat1	tarif_cat2	tarif_cat3	tarif_cat4	tarif_cat5	id_mot
1	0,7	0,1	/	/	/	1
2	0,8	0,2	0,4	/	/	2

Comme tu le vois et pour répondre à ta question, si on part sur un projet au MOT. Dès qu'on a une catégorie, on a forcément 3 infos avec:
- un nombre de mots à traduire
- son "intitulé" (match)
- son tarif (au mot).

J'espère que tu y vois plus clair, étant donné que ce n'est pas mon métier non plus, je ne suis pas forcément super clair dans le choix des mots !

[fsmrel]

Bonsoir orygynz,

Pour la partie DEVIS, oui on peut appeler ça des PROSPECTS. En fait, la partie Devis est très rare. Dans mon cas, 3 en 2 ans...

Bon d’accord, on ne va mettre ce sujet en priorité 1.

glauque ??? C'est dur non ?

Faut pas pleurer, avec vos dernières explications je pense commencer à comprendre le système. ^^


Pour vérifier si je commence à saisir ou à délirer, prenons l’exemple suivant :

Le client Raoul Volfoni a fait traduire puis relire son ouvrage « Comment ventiler les dingues façon puzzle ». Une ligne de la table CLIENT contient les informations caractérisant ce client. Une ligne de la table PROJET contient les informations caractérisant l’ouvrage en question. Une ligne de la table LIGNE_PROJET caractérise la 1re tâche effectuée, disons la traduction de l’ouvrage, et une autre ligne la 2e tâche effectuée, à savoir la relecture de l’ouvrage.

Qu’il s’agisse de traduire, relire, transcrire, etc., le mode de tarification peut être le suivant : à l’heure, au forfait, au mot. Dans les deux premiers cas, les choses sont simples, ça coûte tant : par exemple, pour la relecture de l’ouvrage de Raoul, si ça a été au forfait ça aura coûté, disons, 100 euros, si ça a été à l’heure, ça aura coûté 5 euros de l’heure : comme vous dites : « point barre ».

Dans le cas de sa traduction, le mode de tarification du dit ouvrage a été au mot, tandis que sa relecture a fait l’objet d’un forfait.


Pour la partie traduction, je reprends votre tableau, mais en l’arrangeant autrement :

Code :

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

1
2
3
4
5
6
7
 
MotId    CategorieId    NbMots    Tarif    Match
    1              1      1500      0,7    nouveaux mots
    1              2       250      0,1    répétitions
    2              1      6850      0,8    nouveaux mots
    2              2        30      0,2    répétitions
    2              3        95      0,4    60%

Question : Est-ce que la catégorie détermine le match ? (en effet, pour chaque catégorie vous avez le même match, est-ce une obligation ou un hasard ?)

Pour le moment, si je n’ai pas déraillé, on aurait le diagramme suivant :





La contrainte de partitionnement (XT), c'est-à-dire exclusion et totalité (ajoutée à la main, avec PAINT...) est là pour signifier, d’une part que la tarification est soit forfaitaire, soit horaire, soit au mot, et d’autre part qu’il n’y a pas d’autre type de tarification.


Pour la petite histoire, auriez-vous la bonté de traduire en espagnol (ou autre langue) la phrase :

« L’artilleur prit son fusil, épaula et tira... »

Sachant qu’elle n’est pas complète et que je dois retrouver la suite : je vous la fournirai dès que je peux.

[Invité]

Alors pour la traduction, ça donne :

el artillero cogió su fusil, encaró y disparó

J'ai du mal à comprendre la question :

Question : Est-ce que la catégorie détermine le match ? (en effet, pour chaque catégorie vous avez le même match, est-ce une obligation ou un hasard ?)

Pour moi, dans mon exemple, une catégorie, son intitulé c'est le match.

Les catégories n'ont pas de valeurs dans le diagramme que je propose. C'est juste pour les reconnaître sur les 3 tables... Mais on aurait pu les appeler "ligne1, ligne2".
1 MATCH = 1 TARIF = 1 NB DE MOTS (à multiplier)
Mais si je comprends bien la question, un client fonctionne avec son propre logiciel qui sort les catégories (match).
Exemple, un client 1, il aura dans 99% des cas toujours :
"Nouveaux Mots" à 0,2
"Répétitions" à 0.3

Un client 2 aura également toujours la même chose :
"New Words" à 0.3
"Duplicates" à 0.5
"0% - 75%" à 0.4

Là, les catégories sont juste traduites en Anglais donc on pourrait très bien réutiliser les mêmes MATCH en modifiant le tarif. Mais des fois, certains vont de 0% à 60% alors que d'autres vont de 0% à 70%, etc....
Je sais pas si je suis très clair... Ce qu'il faut retenir, c'est que chaque client est différent mais qu'il utilise dans la majorité des cas toujours les mêmes catégories pour ses projets... Mais sur quelques projets de l'exemple du client 2, il aura une 4ème catégorie... (ils sont pas simples !! )


Pourquoi toujours reprendre tous les clés primaires des autres tables ? cf MOT_CATEGORIE.
En gros, la solution que je propose est plus simple donc pas viable sur la distance ?

Et sinon, à quoi correspond "MotValeur" dans MOT ?

Le XT ne sert que pour le schéma ? Il ne se traduit pas quelque part dans la BDD ?

(ça fait bcq de question )

[fsmrel]

Insatiable orygynz, ave !

Les catégories n'ont pas de valeurs dans le diagramme que je propose.

Pourtant j’avais lu ceci :

La plupart du temps, c'est le client qui donne les catégories de mots

Mais pas de problème, je supprime la table CATEGORIE (et je renomme en passant la table MOT_CATEGORIE en MOT_MATCH).


Dernière mouture du diagramme :




Maintenant, comme pour un mot il peut y avoir plusieurs matchs (cf. votre diagramme initial), on est bien obligé de distinguer le 1er match, le 2e, le 3e, etc. C’est le rôle dévolu à CategorieId, que j’ai renommé en MatchId (mais on peut utiliser tout autre nom convenant mieux).

Code :

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

1
2
3
4
5
6
7
8
ClientId    ProjetId    LigneProjetId    MotId    MatchId    NbMots    Tarif    MatchValeur
--------    --------    -------------    -----    -------
       1           1                1        1          1      1500      0,2    nouveaux mots
       1           1                1        1          2       250      0,3    répétitions

       2           1                1        1          1      6850      0,8    new words
       2           1                1        1          2        30      0,2    duplicates
       2           1                1        1          3        95      0,4    0% - 75%

Pourquoi toujours reprendre tous les clés primaires des autres tables ? cf MOT_CATEGORIE.

Je l’ai fait pour les factures et les projets parce que cela permettait de résoudre de façon simple une contrainte de chemin : éviter qu’un client C1 ait à régler injustement les factures d’un client C2 :




Si l’attribut ClientId n’avait pas été propagé jusqu’à la table LIGNE_FACTURE, dans le cas des SGBD SQL il aurait fallu programmer des triggers pour assurer le coup.


J’ai donc utilisé la technique de l’identification relative (même MySQL Workbench nous l’offre). Plus généralement, on s’en sert pour identifier une entité-type faible relativement à une entité-type plus forte, comme je l’ai déjà écrit dans le message #13. A noter qu’on retrouve la relation de composition d’un diagramme de classes UML.


Partons de l’hypothèse suivante :

Un projet P1 appartient à un client C1, il n’a aucune autonomie, c'est-à-dire que P1 n’existe que parce que C1 existe. Si, sémantiquement parlant, ça n’a pas de sens (il est donc interdit) de transférer P1 à un client C2, alors PROJET est une entité-type faible, et il est naturel d’identifier PROJET relativement à CLIENT. (Si la DGI acceptait de ne plus m’importuner et de vous transférer la charge de régler ce que je lui dois, vous m’en verriez ravi...)

De même :

Un mot M1 appartient à une ligne d’un projet L1, il n’a aucune autonomie, c'est-à-dire que M1 n’existe que parce que L1 existe. Si, sémantiquement parlant, ça n’a pas de sens de transférer M1 à une ligne L2, alors MOT est une entité-type faible, et il est naturel d’identifier MOT relativement à LIGNE_PROJET (entité-type elle -même identifiable relativement à PROJET, vu son absence d’autonomie, sa propre « faiblesse »).


Ainsi, l’attribut ClientId est propagé jusque dans le tréfonds des mots ; c’est une conséquence de la nature sémantique, ontologique de votre projet, ça a un côté mécanique que vous n’êtes évidemment pas obligé de suivre. En tout cas, suite à mes nombreux travaux de prototypage des performances (essentiellement avec DB2 for z/OS), en corrélation avec l’effet cluster, j’ai toujours observé que c’était juteux. Voyez par exemple le cas des engagements sur lignes de facture et des livraisons (4e exemple), sans parler de la consommation en ressources physiques.

S’il s’agit de répondre à des questions du genre :

Quel est le tarif moyen des matchs ? C’est simple :

Code SQL :

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

1
2
SELECT AVG(Tarif) AS TarifMoyen
FROM   MOT_MATCH ;

Quel est le client pour qui on a traité le plus grand nombre de mots ? C’est encore simple, malgré la « distance » qui dépare les tables CLIENT et MOT_MATCH :

Code SQL :

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

1
2
3
4
5
6
7
8
SELECT ClientNom, MAX(SumNbMots)
FROM
    (
     SELECT y.ClientId, ClientNom, SUM(NbMots) AS SumNbMots
     FROM   CLIENT AS x JOIN MOT_MATCH as y ON x.ClientId = y.ClientId 
     GROUP BY y.ClientId
    ) AS z
;

Mais je le répète, vous n’êtes pas obligé de me suivre sur le principe de l’identification relative.

En gros, la solution que je propose est plus simple donc pas viable sur la distance ?

Quod est demonstrandum. Vu du modèle relationnel de données (et même de SQL), je ne vois pas en quoi votre solution serait plus simple. En tout cas, comme je vous l’ai fait remarquer, elle vous bride du fait du nombre de colonnes limité à 5 par type d’élément, colonnes par ailleurs trop souvent vides. Qui plus est, il vous faudra contrôler que si la ième colonne match_cat(i) est renseignée, alors les colonnes nb_mots_cat(i) et tarif_cat(i) le sont aussi, tandis qu’avec la table MOT_MATCH le problème ne se pose pas. Votre solution n’est pas dans l’esprit du modèle relationnel de données qui privilégie la verticalité, tandis que l’horizontalité que vous préférez fait, par exemple, que les fonctions d’agrégation (MAX, SUM, AVG, etc.) y sont plus compliquées à mettre en œuvre. N’oubliez pas que non seulement on définit la structure des données, mais on doit pouvoir les manipuler et en garantir l’intégrité.

Accessoirement, à partir de votre diagramme, quelles requêtes SQL proposez-vous pour répondre aux deux questions posées ci-dessus :

— Quel est le tarif moyen des matchs ?

— Quel est le client pour qui on a traité le plus grand nombre de mots ?

Au-delà, quels genres de questions reviendront le plus souvent ?