Discussion :

[fsmrel]

J'ai répondu trop rapidement, il y avait du rab, je complète donc :

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

J’ai supposé qu’un mot avait un sens, par exemple « artilleur », mais s’il n’y a pas d’utilité, on vire cet attribut.

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

MySQL Workbench ne permet pas de modéliser ce genre de contrainte, mais il est évident qu’il faudra rattraper le coup dans le code SQL, tout en étant conscient qu’avec des triggers, si on s’en sort avec n’importe quel SGBDR pour l’exclusion (X), il en va autrement pour la totalité (T). Dans ce 2e cas, on s’en sort péniblement à coup de DEFERRED (défini par la norme SQL, mais non pris en compte par MySQL), tandis que c’est très simple à assurer dans le cadre de la théorie relationnelle. En tout cas, avec MySQL c’est à garantir manuellement...


Par exemple, concernant l’exclusion, interdire l’ajout d’un mot pour une ligne de projet à laquelle on a déjà affecté un forfait ou une heure :

Code MySQL :

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
CREATE TRIGGER MOT_EXCLUSION_TR BEFORE INSERT ON MOT
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   FORFAIT 
              WHERE  ClientId = NEW.ClientId
                AND  ProjetId = NEW.ProjetId
                AND  LigneProjetId = NEW.LigneProjetId
             ) ;
    IF  @N > 0 THEN
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps un forfait.' ;
    END IF ;

    SET @N = (
              SELECT COUNT(*) 
              FROM   HEURE 
              WHERE  ClientId = NEW.ClientId
                AND  ProjetId = NEW.ProjetId
                AND  LigneProjetId = NEW.LigneProjetId
             ) ;
    IF  @N > 0 THEN
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps une heure.' ;
    END IF ;
END ;

Le code est le même pour contrôler l’UPDATE (curieusement, MySQL exige un trigger supplémentaire). Il faut aussi coder les triggers interdisant qu’un forfait soit en même temps une heure ou un mot, etc.



P.-S. J’ai retrouvé la phrase complète à propos de l’artilleur :

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

Bonne dimanchade !

[Invité]

Bonsoir,

Je mets du temps à répondre, le temps de digérer tout ça !

Pour la question CATEGORIE/MATCH, c'est juste qu'il faut 3 infos pour le MOT, son intitulé (le match), son prix et son nombre. Du coup, catégorie, c'est juste qu'il me fallait un nom pour les champs et pour les retrouver dans les 3 tables que j'avais créé.

Du coup, si MotValeur ne sert pas, est-ce que sa table est justifié ?

Pour les questions :

— Quel est le tarif moyen des matchs ?

Il ne suffit pas d'un AVG en sélectionnait l'ensemble de tarifs ?

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

Un MAX avec un SUM en faisant un lien entre la table MOT et CLIENTS

Sinon, à savoir pour les questions qui peuvent revenir... Y'en a pas tellement, on mets ce qu'on veut sur les statistiques que l'on souhaite sortir.... L'intérêt de détailler le prix au mot, le nombre de mots et son match, c'est que j'ai besoin de toutes ces informations pour la facturation. En effet, les factures sont détaillés pour chaque projet... (ce qui est utilisé actuellement).

Du coup, avec le XT, je suis obligé de faire des TRIGGERS ? ça rejoint la question de ne pas vous suivre pour l'identification relative ?
Après, la base de données n'est pas énorme non plus. Le nombre de projets est environ de 50 par mois (pour tous les clients réunis).

La question est de savoir si cette bdd n'est pas utile dans un cas qui envoie des milliers de lignes par mois ? Est-ce que dans mon cas une solution sans identification relative, sans triggers ne permettra pas d'assurer mes données ?

Merci pour le temps que vous m'accordez ! ;-)

[fsmrel]

Bonsoir orygynz,

Si MotValeur ne sert pas, est-ce que sa table est justifié ?

On peut effectivement simplifier :

Il ne suffit pas d'un AVG en sélectionnait l'ensemble de tarifs ?

Pour s’en assurer, codez et présentez la requête, je la testerai...

Un MAX avec un SUM en faisant un lien entre la table MOT et CLIENTS

A nouveau, codez et présentez la requête...

Du coup, avec le XT, je suis obligé de faire des TRIGGERS ?

Si vous voulez une base de données saine, la moins vérolée qui soit, alors, yes !

ça rejoint la question de ne pas vous suivre pour l'identification relative ?

Non, dans la mesure où, par trigger, il s’agit uniquement de s’assurer que la même valeur de clé ne figure pas à la fois dans les tables MOT, HEURE, FORFAIT.

Est-ce que dans mon cas une solution sans identification relative, sans triggers ne permettra pas d'assurer mes données ?

Non. Errare humanum est, et une gestion de l’intégrité sans le recours aux mécanismes offerts par le SGBD, donc à la main, conduit inévitablement à l’erreur, que les tables comportent quelques dizaines ou quelques centaines de millions de lignes, j'ai pu le vérifier pendant des décennies à l’occasion d’audits et expertises en tous genres chez mes nombreux clients...


En passant, que devient la traduction pour mon artilleur ?

[Invité]

Bonjour,

Désolé pour le délai, des changements persos m'ont un peu ralenti dans mon projet... Mais j'y pense !!!

La traduction

el artillero cogió su fusil, encaró y le sacó la lengua

Du coup, je vais proposer un schéma finale avec vos propositions ! :-)

Effectivement, le AVG ne serait pas simple à mettre en oeuvre, addition ans le select, sum avec avg, etc...

Merci !

[fsmrel]

Effectivement, le AVG ne serait pas simple à mettre en oeuvre, addition ans le select, sum avec avg, etc...

Certes ! Un truc tordu...

Et si vous croisez l'artilleur, tirez-lui la langue de ma part (ou faites-lui un pied-de-nez...)


Bonne route !

[Invité]

I'm back !!

Désolé de ne pas avoir continué, les derniers mois ont été ultra chargés .... Du coup fsmrel, si tu me dis, trop tard, je comprends ! Mais je tente ...




Du coup, sans le XT, cette proposition est ok ????

J'ai juste une question pour la table "MOT", le fait de pas mettre comme avant le Cat_1, Cat_2, etc.... Cest sûr, ça permet de mettre un nombre illimité mais par contre, au lieu d'avoir 2 lignes par projet, je vais en avoir 2, 3, 4 ou 5 !!! la base va pas devenir énorme ???

Merci de ton aide.

[Invité]

Fais pas la tête fsmrel !!! J'ai croisé l'artilleur, il voulait de tes nouvelles !!

[fsmrel]

Ave orygynz,


Il faudra en fait que je prenne le temps de relire toute la discussion et comprendre ce qui s'est échangé, et ça peut prendre un certain temps (comme le refroidissement du canon du fusil de l'artilleur...)

[Invité]

Ok je comprends !!

Mais promis, j'avais de vrai excuses de faire une aussi longue pause !!! Et là, je ne m'arrêterais que quand tu m'auras achevé ...

A bientôt !
Orygynz

[fsmrel]

Bonsoir orygynz,

J'ai juste une question pour la table "MOT", le fait de pas mettre comme avant le Cat_1, Cat_2, etc.... C’est sûr, ça permet de mettre un nombre illimité mais par contre, au lieu d'avoir 2 lignes par projet, je vais en avoir 2, 3, 4 ou 5 !!! la base va pas devenir énorme ???

Reprenons votre exemple :



Cela représente 6 lignes X 7 colonnes, et il y a pas mal de colonnes non renseignées : c’est de la place perdue.

En verticalisant la chose : 5 lignes pour 5 colonnes...

Code :

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

1
2
3
4
5
6
7
MotId    NbMots    Tarif    MatchValeur    Id_ligne_projet
-----    ------    -----    -----------    ---------------
    1      1500      0,2    nouveaux mots                1
    2       250      0,3    répétitions                  1
    1      6850      0,8    new words                    2
    2        30      0,2    duplicates                   2
    3        95      0,4    0% - 75%                     2

Et pour que la base devienne « énorme », il faudrait quelques dizaines de millions de projets...


Selon votre diagramme MWB, pour une ligne de projet, on a au moins un forfait, au moins une heure au moins un mot, or c’est au plus :

Soit un forfait, soit une heure, soit au moins un mot.

Il faudrait donc mettre les cardinalités à niveau, comme ici (mode d’emploi, voir par exemple la figure 6.4 de l’article) :





Table LIGNE_PROJET : id_tache doit dégager de la clé.

Table MOT : les attributs NbMots, Tarif et MatchValeur doivent être déclarés NOT NULL.

Dans les clés des tables, mettre les attributs dans le bon ordre : id_client en 1er, id_projet en 2e, etc. (cf. mon diagramme ci-dessus).


On reprendra un peu plus tard, bonne journée...

[Invité]

Bonjour,

Pour commencer, merci beaucoup de reprendre le sujet ! Par contre, je ne voudrais pas vous empêcher de dormir

Pour les cardinalités, je pensais avoir précisé sur mon poste que je n'avais pas fais les modifs sur mysqlworkbench, je ne maîtrise pas énormément ce logiciel. Je l'utilise surtout pour avoir une image de la BDD mais effectivement il faut être précis... En tout cas, nous sommes d'accord.

Du coup, pour la table TACHES, je dois inverser pour avoir ça :
TACHES (id_tache, tache, affichage_langue_facture, #id_ligne_projet)

Petite explication pour cette table tâches : la partie mot/forfait/heure c'est le type de projet alors que la tâche, ça va être "traduction" "relecture" "transcription"...
Du coup, cette tâche est dans la plupart des cas de la traduction au mot, au forfait ou à l'heure mais une relecture peut tout aussi bien être au mot, au forfait ou à l'heure. (J'espère être assez clair car c'est très simple...)

Le reste du diagramme vous semble ok ? Ou vous attendiez de finir cette partie pour m'attaquer sur le reste

Encore merci !
Orygynz

[fsmrel]

Bonjour orygynz,


Un diagramme qui ne correspond pas précisément à la réalité est source de quiproquos, de perte de temps et d’objectivité, de conclusions fausses, etc.

Si vous ne maîtrisez pas MySQL Workbench, transmettez-moi le script SQL (CREATE TABLE, INDEX), je ferai une rétro-conception, pour être sûr qu’on est en phase, sinon on va faire des allers-retours incessants et perdre du temps.

Le reste du diagramme vous semble ok ? Ou vous attendiez de finir cette partie pour m'attaquer sur le reste

1) J’attends qu’on en finisse avec la 1ere partie.

2) L’autre partie du diagramme mérite elle aussi que je fasse une rétro-conception, histoire de savoir exactement ce qui est représenté.

[Invité]

Pour l'instant, une base de données version 1 est en production et elle n'a rien à voir, trop d'erreurs de conception.

La V2 n'existe pour l'instant que sur ce topic et sur mon fichier MySqlWorkBench. Du coup, si le problème sur ce diagramme est lié aux cardinalités, je vais apprendre à bien les renseigner sous MySQLworkbench.

Ou alors, vous préférez un script SQL et dans ce cas, je la mets en place sur mysql.

Je vous laisse le choix des armes vu le temps que vous m'accordez WorkBench ou Script SQL ?

Orygynz

[fsmrel]

Je vous laisse le choix des armes vu le temps que vous m'accordez WorkBench ou Script SQL ?

Les deux mon général !

Allons-y pour ceinture et bretelles, Workbench + script SQL...

[Invité]

Alors pour commencer, voici le nouveau diagramme avec les bonnes cardinalités... Par contre, impossible de passer les clés en rouge. Si je veux les mettre en rouge, je dois choisir relation non identifié mais du coup ce n'est plus une clé primaire ....

La table DEVIS a peu d'intérêt comme évoqué plus haut...
La table PROJET_CHANGE n'est pas abouti mais j'aimerais avoir votre avis... J'ai besoin de pouvoir éditer une copie d'une Facture (par défaut en Euro) en dollars... Mais si je souhaite le détail des projets, je vais devoir recréer MOT, HEURE, FORFAIT en version CHANGE comme projet ? Exemple (MOT_CHANGE, HEURE_CHANGE...)

[fsmrel]

Bonsoir orygynz,


Reprenons votre diagramme (annoté) :





Pourquoi avoir associé PROJET et TACHE ? L’association était avec LIGNE_PROJET, il fallait seulement rendre cette association non identifiante. Une plus, vous avez établi une bijection entre PROJET et TACHE et du coup une tâche ne peut être associée qu’à un seul projet !


J’avais écrit :

Dans les clés des tables, mettre les attributs dans le bon ordre : id_client en 1er, id_projet en 2e, etc.

Vous avez mangé la consigne : il faut réordonner les colonnes dans les clés.

Etc.

impossible de passer les clés en rouge

Je suppose que vous voulez parler des clés primaires. En l’occurrence, c’est un gadget superflu qui a été ajouté dans une des dernières versions de MWB : si elles sont toutes en jaune, c’est très bien ainsi (si elles sont pertinentes, cf. id_tache, table PROJET...)

Par contre, que les clés étrangères qui ne participent pas aux clés primaires soient en rougeâtre (losanges) , pas de problème :



Pour le reste, on verra plus tard, quand on en aura fini avec les projets.

[Invité]

Bon j'ai bien bossé sans manger la consigne !!

Nouveau diagramme



Du coup, pour LIGNE_PROJET, ok on reprend l'id client, l'id projet mais on est obligé aussi de le reprendre dans les tables HEURES ? MOT ou FORFAIT ?

J'ai remis le diagramme en entier car je me suis rendu compte que je n'avais pas pris en compte toutes vos remarques sur la partie FACTURE, cela évitera pour vous de vous répéter par ma faute !!

Sinon, j'ai commencé à la version SQL et j'ai 2 questions.
Quand on a une table comme LIGNE_PROJET avec 3 clés primaires, je ne peux pas mettre id_ligne_projet en AUTO_INCREMENT ?
Sans l'AI, je suis obligé de connaître le dernier ID du coup donc requête supplémentaire en PHP...

Sinon, je vais me renseigner mais je n'arrive pas à mettre les contraintes en places en SQL, il doit me manquer une notion. Exemple entre CLIENT et PROJET, aucun souci mais entre PROJET et LiGNE_PROJET ça se complique .... Je vais regarder ça pour vous proposer ce script SQL mais je suis dessus !!!

Merci,
Orygynz

[EDIT]
Vu que tu travailles de nuit, je te mets ça pour info, voici l'exemple d'un blocage sur mysql pour la table LIGNE_PROJET :

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
CREATE TABLE IF NOT EXISTS `BDD`.`LIGNE_PROJET` (
`id_client` INT NOT NULL ,
`id_projet` INT NOT NULL ,
`id_ligne_projet` INT NOT NULL ,
`id_tache` INT NOT NULL ,
PRIMARY KEY ( `id_client` , `id_projet` , `id_ligne_projet` ) ,
INDEX `fk_LIGNE_PROJET_1` ( `id_projet` ASC , `id_client` ASC ) ,
INDEX `fk_LIGNE_PROJET_2` ( `id_tache` ASC ) ,
CONSTRAINT `fk_LIGNE_PROJET_1` FOREIGN KEY ( `id_projet` , `id_client` ) REFERENCES `BDD`.`PROJET` (
`id_projet` ,
`id_client`
) ON DELETE RESTRICT ON UPDATE RESTRICT ,
CONSTRAINT `fk_LIGNE_PROJET_2` FOREIGN KEY ( `id_tache` ) REFERENCES `BDD`.`TACHE` (
`id_tache`
) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB;

MySQL a répondu: Documentation

#1005 - Can't create table 'INTRADIDEO.LIGNE_PROJET' (errno: 150)

Je me suis renseigné sur le net, problème de clé étrangère dans la majorité des cas... Est-ce lié à id_client alors que le lien s'effectue sur la table PROJET et non CLIENT.
Pour info, ce code sql est tiré à partir de mysqlworkbench pour tester mon problème sur la prépa de la nouvelle base que j'ai effectué par l'intermédiaire de phpmyadmin... Mais j'ai une erreur similaire (celle dont je parle plus haut).

Merci !

[fsmrel]

Bonsoir orygynz,


A la bonne heure, pour la partie droite du diagramme, c’est bon ! Je regarderai un peu plus tard la partie gauche.

pour LIGNE_PROJET, ok on reprend l'id client, l'id projet mais on est obligé aussi de le reprendre dans les tables HEURES ? MOT ou FORFAIT ?

Voui ! On use de l’identification relative à gogo !

Quand on a une table comme LIGNE_PROJET avec 3 clés primaires, je ne peux pas mettre id_ligne_projet en AUTO_INCREMENT ?

On n’a pas 3 clés primaires, car une table n’a qu’une clé primaire, mais celle-ci est composée en l’occurrence d’un triplet d’attributs, nuance.

Sans l'AI, je suis obligé de connaître le dernier ID du coup donc requête supplémentaire en PHP...

Pas forcément ! comme dirait Raoul Volfoni. Il suffit de laisser MySQL incrémenter non pas de façon absolue, mais relative, en lui fournissant le trigger qui va bien :

TABLE LIGNE_PROJET

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_PROJET 
(
          ClientId          INT             NOT NULL
        , ProjetId          INT             NOT NULL
        , LigneProjetId     INT             NOT NULL
        , TacheId           INT             NOT NULL
    , CONSTRAINT LIGNE_PROJET_PK PRIMARY KEY (ClientId, ProjetId, LigneProjetId)
    , CONSTRAINT LIGNE_PROJET_PROJET_FK FOREIGN KEY (ClientId, ProjetId)
          REFERENCES PROJET (ClientId, ProjetId)
    , CONSTRAINT LIGNE_PROJET_TACHE_FK FOREIGN KEY (TacheId)
          REFERENCES TACHE (TacheId) 
) ;

Le trigger pour incrémenter la colonne LigneProjetId (table LIGNE_PROJET)

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
COMMIT ;
DELIMITER GO
 
CREATE TRIGGER  LIGNE_PROJET_INCREMENT_TR BEFORE INSERT ON  LIGNE_PROJET 
FOR EACH ROW
    BEGIN 
        SET NEW.LigneProjetId = (SELECT COALESCE(MAX(LigneProjetId) + 1, 1)  
                                 FROM   LIGNE_PROJET 
                                 WHERE  ClientId = NEW.ClientId AND ProjetId = NEW.ProjetId) ;
    END
 
GO
DELIMITER ;
COMMIT ;

Quelques inserts, avec LigneProjetId à 0, c’est le trigger qui mettra la bonne valeur :

Code SQL :

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

1
2
3
4
5
 
INSERT INTO  LIGNE_PROJET (ClientId, ProjetId, LigneProjetId, Tacheid) VALUES (1, 1, 0, 1) ;
INSERT INTO  LIGNE_PROJET (ClientId, ProjetId, LigneProjetId, Tacheid) VALUES (1, 1, 0, 2) ;
INSERT INTO  LIGNE_PROJET (ClientId, ProjetId, LigneProjetId, Tacheid) VALUES (2, 1, 0, 3) ;
INSERT INTO  LIGNE_PROJET (ClientId, ProjetId, LigneProjetId, Tacheid) VALUES (2, 1, 0, 4) ;

Au résultat, MySQL a fait son boulot, tout seul, comme un grand :

Code :

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

1
2
3
4
5
6
7
 
ClientId    ProjetId    LigneProjetId    Tacheid
--------    --------    -------------    -------
       1           1                1          1
       1           1                2          2
       2           1                1          3
       2           1                2          4

Même principe pour les tables MOT, HORAIRE, FORFAIT (et PROJET !)


Par exemple, pour la table MOT, on enrichit le trigger défini dans le post #21 :

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
 
CREATE TRIGGER MOT_EXCLUSION_TR BEFORE INSERT  ON MOT
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   FORFAIT 
              WHERE  ClientId = NEW.ClientId
                AND  ProjetId = NEW.ProjetId
                AND  LigneProjetId = NEW.LigneProjetId
             ) ;
    IF  @N > 0 THEN
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps un forfait.' ;
    END IF ;	
     SET @N = (
               SELECT COUNT(*) 
               FROM   HEURE 
               WHERE  ClientId = NEW.ClientId
                 AND  ProjetId = NEW.ProjetId
                 AND  LigneProjetId = NEW.LigneProjetId
             ) ;
    IF  @N > 0 THEN
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps une heure.' ;
    END IF ;
 
    -- partie identification relative 
 
    SET NEW.MotId = (SELECT COALESCE(MAX(MotId) + 1, 1)  
                     FROM   MOT 
                     WHERE  ClientId = NEW.ClientId AND ProjetId = NEW.ProjetId AND LigneProjetId = NEW.LigneProjetId) ;
END ;

voici l'exemple d'un blocage sur MySQL pour la table LIGNE_PROJET

J’aurais été à la place de MySQL, comme lui j’aurais rouspété, mais j’aurais été plus explicite, en expliquant pourquoi, au lieu d'afficher un code d'erreur fourre-tout.
Il se trouve que la clé primaire de la table PROJET est le couple {ClientId, ProjetId} et je rappelle qu’un couple est ordonné...

Comme dans la table LIGNE_PROJET vous proposez un couple différent, à savoir {ProjetId, ClientId} pour la clé étrangère référençant [la clé primaire de] PROJET, MySQL se dit que, tel Dagobert, orygynz a mis sa clé à l’envers, et il faudra lui remonter les bretelles tant qu’il n’aura pas remise à l’endroit : {ClientId, ProjetId} dans cet ordre et rien d'autre ! Il faut le faire non seulement dans le code SQL généré, mais aussi dans Workbench, car l’ordre des attributs dans les clés étrangères et (et dans les index) n'a manifestement pas été revu. Cela vaut bien sûr pour toutes les autres tables.

Quant à l’index fk_LIGNE_PROJET_1, non seulement c’est la même chose, mais en plus cet index ne sert strictement à rien, il faut le supprimer, car il est la copie de l’index primaire... (ne pas oublier que mettre à jour un index ça coûte cher...) Disons que lors de la génération d’un script, il est préférable de ne pas demander la génération des index (ou à tout le moins demander qu’ils ne soient pas déclarés au sein des tables) : après génération du script, on vire tous les index et lors du codage des requêtes, on vérifie s’il est opportun d’en créer. Il faut être parcimonieux à ce sujet, ça coûte la feau des pesses ces petites bêtes.

Je me suis renseigné sur le net, problème de clé étrangère dans la majorité des cas...

Serait-ce que dans leur majorité, les gens jouent les Dagobert ?

Et n'oubliez pas de voter pour la minorité !

[Invité]

Bonjour,

J'ai bien aimé la référence à Dagobert

Je vais effectuer toutes ces modifs, merci pour tout ça. Pour l'erreur SQL, c'est sûr qu'il pourrait le dire ... Moi je cherchais une "vrai" erreur, pas ça ... Mais bon, maintenant je le sais

Au niveau des TRIGGERS, on en a déjà un peu parlé mais ça ne tue pas les performances de la bdd d'en mettre trop ??
Avec le blocage "XT", on en serait à 6 TRIGGERS !!!

J'espère avoir compris votre dernière phrase

[fsmrel]

Bonsoir orygynz,


Je n’ai pas encore eu le temps de revoir la partie gauche du diagramme, mais causons déjà des triggers...

Au niveau des TRIGGERS, on en a déjà un peu parlé mais ça ne tue pas les performances de la bdd d'en mettre trop ??

Vous avez raison de vous poser des questions, mais il faut que vous arriviez par vous-même à répondre objectivement à la question. EXPLAIN sera en l’occurrence un auxiliaire précieux.

Reprenons le trigger dédié aux inserts dans la table MOT, et voyons voir ce que peut coûter le SELECT vérifiant s’il existe un forfait ayant la même valeur de clé primaire :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   FORFAIT 
              WHERE  ClientId = NEW.ClientId
                AND  ProjetId = NEW.ProjetId
                AND  LigneProjetId = NEW.LigneProjetId
             ) ;
    IF  @N > 0 THEN
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps un forfait.' ;
   END IF ;

On demande à SQL d’effectuer un comptage qui ne portera en fait que sur une ligne au plus de la table FORFAIT : ça va vite, ce que confirme un EXPLAIN de la requête (en notant que MySQL n’a même pas besoin d’accéder à la table, l’index primaire suffit pour obtenir la réponse, c’est que veut dire « Extra = Using index ») :

Ce que dit l’EXPLAIN :

Code :

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

1
2
3
4
select_type    table       possible_keys    key      key_len     rows    Extra
-----------    -------     -------------    -------  -------     ----    -----
SIMPLE         FORFAIT     PRIMARY          PRIMARY       12        1    Using index

On peut préférer l’utilisation d’EXISTS à celle de COUNT, et coder ceci :

Code SQL :

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

1
2
3
4
5
6
7
8
9
 
    IF EXISTS (SELECT ''    
               FROM   FORFAIT 
               WHERE  ClientId = NEW.ClientId
                AND  ProjetId = NEW.ProjetId
                AND  LigneProjetId = NEW.LigneProjetId)
        THEN
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = 'Un mot ne peut pas être en même temps un forfait.' ;
    END IF ;

Mais le verdict de l’EXPLAIN est évidemment le même.

Il en va de même pour l’identification relative, l’accès à l’index primaire suffit :

Code SQL :

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

1
2
3
4
EXPLAIN
    SELECT COALESCE(MAX(MotId) + 1, 1)
    FROM   MOT 
    WHERE  ClientId = 1 AND ProjetId = 1 AND LigneProjetId = 2 ;

Il n’y a donc pas lieu de s’inquiéter...


Tout ça me fait revenir en mémoire l’attitude des informaticiens des années quatre-vingts : l’IR (intégrité référentielle, avec son armée de clés étrangères) n’était pas encore sous le contrôle du SGBD (DB2 pour ma part), on devait donc la garantir de manière applicative, et on avait beau faire attention, il y avait toujours une paille, j’ai un échantillon d’exemples caractéristiques. Tout le monde attendait donc avec impatience les fameuses clés étrangères, les réclamait sur l’air des lampions, mais bizarrement quand enfin on en a disposé, les intéressés se sont fait la réflexion qu’après tout ça pourrait tuer les performances (comme la jointure du reste)... Pour ma part, ayant été un des tous premiers en France à avoir l’occasion de mettre en œuvre l’IR (juin 1988, avec DB2 V2), pour en avoir le cœur net j’ai bâti des prototypes de performance : j’ai été pleinement rassuré, d’autant que ça se passait chez un client particulièrement exigeant, auprès duquel mon patron s’était engagé à ce que l’on tienne N transactions à l’heure, pour un temps de réponse inférieur à 3 secondes (prise de commandes, tenue des stocks et gestion des en-cours).

Si de votre côté vous avez des doutes quant aux triggers et tout le fourbi, montez votre propre prototype de performance ! Envoyez des rafales de création de projets, bien remplis de lignes de projet, de mots et tout ça, vous verrez bien et n’oubliez pas de commencer par faire des EXPLAIN !

Par ailleurs, quand on fait des inserts , ce sont surtout les index qui paradoxalement peuvent plomber les performances (voyez l’exemple de la banque de la ville méridionale où il fait bon vivre), s’ils sont bien utiles en mode lecture, on oublie trop souvent qu’eux aussi sont mis à jour. Ainsi, si vous conserviez l’index totalement inutile fk_LIGNE_PROJET_1, ça ne concourrait pas à améliorer la performance, au contraire ! De même, l’index fk_LIGNE_PROJET_2 est a priori inutile : si vous ne découvrez pas de requête pour laquelle il servirait effectivement, autant ne pas s’en encombrer.

Il ne faut pas se tromper de cible, Roller n’avait pas manqué de rappeler ce qu’il en coûtait de ne pas modéliser avec rigueur.