Discussion :

[champomy62]

Bonjour,

Clavier qwerty! Desole...

Voici les regles de gestion:

Assignements:
Permet de suivre l'evolution d'une tache. Je n'ai pas mis l'integralite du schema puisque mon probleme se situe ailleurs.

Metric qualities:
Permet de signaler un probleme survenue durant l'analyse d'un assignement.
A tout moment pour un assignement, on est toujours interesse par la derniere metric quality produite. Plusieurs peuvent etre produite pour un meme assignement mais a tout moment la seule consideree valide sera la derniere cree. Le reste peut etre consideree comme un archivage.
Une metric quality est toujours liee a un assignement.

Metric quality comments:
Pour faciliter la resolution des problemes, des commentaires peuvent etre associes soit a un assignement, soit a une MQ.




Donc deux soucis:

Pour les metric qualities

Je voudrai que le schema puisse containdre de n'avoir qu'une seule MQ a la fois pour un assignement mais avec la possibilite de garder un historique des precedentes MQs produites.
Donc pour ce faire, dans current_investigation je me suis dis que pour forcer qu'une seule MQ par assignement, j'ai juste a changer l'identifiant primaire. Mais au final j'ai l'impression que cela fait pas tres propre et je demandais si je devais avoir une seconde entite qui contiendrait l'archivage ? Ou une autre solution existe ?

Dans le schema que j'ai produit j'ai laisse les attributs dans l'entite Metric qualities. Est-ce correct?



Pour les commentaires
J'aimerai pouvoir ne garder qu'une seule entite pour faciliter la gestion mais je ne suis pas sur que la maniere dont j'ai modelise est correct. Je pense meme que c'est faux puisque si MQ est une lie a un commentaire, cela fait duplication d'information et je devrai avoir du code pour m'assurer que l'assignment ID de la metric qualitie liee est le meme que l'assignment ID du commentaire.

Dois-je obligatoirement avoir deux tables ?


Merci de votre temps,

[champomy62]

J'ai refais un deuxieme schema tellement je suis motive!

J'ai pris mes propres commentaires en compte pour peaufiner mon schema. Personnellement ca m'a l'air plus propre mais je me demande toujours si c'est possible d'arriver a la meme construction mais plus facilement ?

[escartefigue]

Bonjour,

pour conserver l'historique, il faut une association à date. On fait donc intervenir une classe d'entité fictive (que j'ai appelée calendrier dans le modèle ci-dessous).
On ne produira pas de table à partir de cette classe d'entité, mais elle permet d'ajouter la date comme composante de la PK de la table associative.
Dans looping, il y a une case à cocher "classe d'entité fictive" dans le bas de la boite de dialogue, qui est disponible dès qu'une entité-type ne possède qu'un seul attribut : l'identifiant.

Pour la deuxième question, il faut modéliser une contrainte d'exclusion entre les deux associations pour montrer que le commentaire concerne AS ou MQ mais pas les deux

Ce qui donne :

Pièce jointe 587324

et le script correspondant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE AS_assignment(
   AS_ident INT IDENTITY,
   PRIMARY KEY(AS_ident)
);

CREATE TABLE MQ_metric_qual(
   MQ_ident INT IDENTITY,
   PRIMARY KEY(MQ_ident)
);

CREATE TABLE CO_comment(
   CO_ident INT IDENTITY,
   MQ_ident INT,
   AS_ident INT NOT NULL,
   PRIMARY KEY(CO_ident),
   FOREIGN KEY(MQ_ident) REFERENCES MQ_metric_qual(MQ_ident),
   FOREIGN KEY(AS_ident) REFERENCES AS_assignment(AS_ident)
);

CREATE TABLE AS_MQ(
   AS_ident INT,
   MQ_ident INT,
   CA_date DATE,
   PRIMARY KEY(AS_ident, MQ_ident, CA_date),
   FOREIGN KEY(AS_ident) REFERENCES AS_assignment(AS_ident),
   FOREIGN KEY(MQ_ident) REFERENCES MQ_metric_qual(MQ_ident)
);

[champomy62]

Donc si je comprends bien votre schema, je ne garde qu'une seule entite pour les metric qualities et la plus recente dans AS_MQ sera celle consideree "en cours de traitement" ?

[escartefigue]

l'association est n-aire, donc, pour un même "assignment" et une même date, il peut y avoir plusieurs occurrences de "Metric_Qual"
Mais je crois que j'ai lu un peu vite votre énoncé : mon raisonnement ne tient que si un MQ peut s'associer à plusieurs AS (c'était le cas dans votre premier schéma mais pas dans le second)
Qu'en est il du coup ?

[CinePhil]

Pour la deuxième question, il faut modéliser une contrainte d'exclusion entre les deux associations pour montrer que le commentaire concerne AS ou MQ mais pas les deux

En l'occurrence, dans ton schéma et dans le script de la table, l'association avec AS_assignement est obligatoire !

Dans ma manière de modéliser, j'aurais mis 0.1 sur les deux pattes partant de CO_comment, ce qui aurait chez moi entraîné deux tables associatives, comme je le recommande dans mon billet de blog.

Une table CO_comment avec deux clés étrangères dont l'une sera mise à NULL sur chaque ligne, ce n'est vraiment pas top et fsmrel va sortir sa sulfateuse pour éradiquer cet invasion de bonshommes NULL !

[CinePhil]

Je voudrai que le schema puisse containdre de n'avoir qu'une seule MQ a la fois pour un assignement mais avec la possibilite de garder un historique des precedentes MQs produites.

Ne serait-ce pas le rôle de la colonne "Status" de dire que telle MQ est valide ; les autres devant être dans un autre statut ?

Il faudrait alors afficher dans le schéma une contrainte (textuelle, a priori) spécifiant cela et le code de la BDD comportera une procédure assurant que lors du passage d'une MQ à l'état valide, les autres soient passées à un autre état. Ou mieux, un trigger, si votre SGBD vous permet de modifier les valeurs dans les autres lignes d'une table que celle qui est en cours d'insertion ou de mise à jour (donc pas MySQL/MariaDB).

[escartefigue]

Bonsoir Cinephil

En l'occurrence, dans ton schéma et dans le script de la table, l'association avec AS_assignement est obligatoire !

Boulette de ma part ! la CIF d'exclusion est incompatible avec cette cardinalité minimale de 1 bien entendu !
à remplacer par 0,1 bien sûr, j'ai fait le schéma entre la poire et le fromage sans prendre le temps de me relire, désolé

Quant aux tables associatives, c'est bien sûr possible et recommandé, même si, je l'avoue, je suis plutôt laxiste sur ce chapitre.

[Paprick]

Bonsoir,
En cas de cardinalité 0,1 Looping produit par défaut une clé étrangère pouvant être NULL... et du coup, mettre fsmrel en colère .
Mais, depuis la version 3.0, il est possible dans ce cas de demander la génération d'une table de correspondance reléguant le bonhomme NULL aux oubliettes !

[champomy62]

Merci de vos reponses.

Voici les regles de gestion proprement faites:

Un assignement peut se voir affecter plusieurs metric qualities.
Une metric quality sera toujours affectee a un seul assignement.
A tout moment pour un assignement il peut y avoir qu'une seule MQ a investiguer. Donc si un operateur decide de creer une nouvelle MQ peut importe la raison, la precedente sera mise a jour avec un status FERME/OUVERT et un autre status RESOLU/NON-RESOLU. Il est possible d'avoir deux MQs avec les deux aux status RESOLU ET FERME. (Plusieurs investigations ont pu etre menes mais ce qui compte c'est de garder une trace). Toujours la derniere a etre prise en compte donc la date est importante.

Un commentaire peut cibler soit un assignement soit une metric quality en particulier mais jamais les deux a la fois.

Je vais re-travailler mon schema mais je commence a voir plus clair.

SGBD: Postgres

[champomy62]

Apres mur reflexion voici mon schema. J'ai pris en compte vos remarques:

Voici le MCD:


Voici le MLD:


Pour valider le passage d'un etat a un autre d'une MQ, conseillez-vous une fonction ou un trigger ? Actuellement toute la base utilise des procedures stockees donc j'irai vers la fonction mais peut-etre que je manque quelque chose!

[CinePhil]

J'essaierais le trigger qui a l'avantage d'être à déclenchement automatique alors que la procédure, il faut explicitement la lancer. Dans le cas de la procédure, il reste possible de faire une insertion sans utiliser la procédure et donc passer outre la contrainte.

[fsmrel]

Bonsoir,


Manifestement une MQ est une propriété multivaluée d’un assignement : autant identifier la 1re relativement au second, ça pourra toujours servir. Et pourquoi ne pas faire participer la date à l’identification des MQ ?


Concernant les commentaires : d’accord avec Paprick et sa solution sioux de derrière les fagots, mais vous pouvez aussi cacher les cardinalités 0,1 sous le tapis en utilisant l’héritage...

[fsmrel]

J'essaierais le trigger qui a l'avantage d'être à déclenchement automatique alors que la procédure, il faut explicitement la lancer. Dans le cas de la procédure, il reste possible de faire une insertion sans utiliser la procédure et donc passer outre la contrainte.

Avec les tables présentes dans le code SQL généré à partir de mon MCD, si je crée une fonction chargée de contrôler la contrainte d’exclusion entre les commentaires (appelons-la COMMENT_EXCLUSION_FN), et si à l’instar de SQL Server PostgreSQL accepte que cette fonction garantisse l’exclusion, on se couvrirait alors automatiquement aussi bien qu’avec un trigger lors des inserts/updates :

ALTER TABLE COMMENT_ASS
ADD CONSTRAINT COMMENT_ASS_EXCLU CHECK (COMMENT_EXCLUSION_FN() = 0) ; 

ALTER TABLE COMMENT_MQ
ADD CONSTRAINT COMMENT_MQ_EXCLU CHECK (COMMENT_EXCLUSION_FN() = 0) ; 

Par contre, s’il y a des millions de commentaires, gare aux performances, il faut évidemment préférer un trigger...

[CinePhil]

Comme la contrainte CHECK n'existe toujours pas chez MySQL / MariaDB (à ma connaissance), j'avais fini par oublier son existence !

[champomy62]

Manifestement une MQ est une propriété multivaluée d’un assignement : autant identifier la 1re relativement au second, ça pourra toujours servir. Et pourquoi ne pas faire participer la date à l’identification des MQ ?

Donc ca veut dire que l'identifiant primaire de l'entite devient {ASSIGNMENT_ID;DATETIME} et que cet identifiant se verra propage pour MMQ_COMMENTS.


J'ai aussi refait votre schema par curiosite et cela me donne:



Ce que je suis pas sur de comprendre c'est pourquoi il y a creation de l'entite COMMENT_MQ{id_comments}. Je comprends qu'il y a heritage a partir de l'entite {COMMENTS} mais en pratique ne pourrait-on pas s'en tenir a {MMQ_COMMENTS} ?

[fsmrel]

Bonjour,


champomy, je suis désolé, sur la patte connectant COMMENT_MQ et COMM_MQ, j’ai laissé traîner une cardinalité 1,n qui doit évidemment être remplacée par 1,1.

[champomy62]

Merci de vos reponses. Je marque comme resolu

[fsmrel]

En passant, un début de jeu d’essai concernant l’exclusion des commentaires ASS et MQ :

SET SCHEMA 'temp' ;

DROP FUNCTION IF EXISTS COMMENT_EXCLUSION_FN() CASCADE ;
DROP TABLE IF EXISTS COMMENT_MQ CASCADE ;
DROP TABLE IF EXISTS COMMENT_ASS ;
DROP TABLE IF EXISTS COMMENTAIRE CASCADE ;
DROP TABLE IF EXISTS METRIC_QUALITY ;
DROP TABLE IF EXISTS ASSIGNEMENT CASCADE ;

CREATE TABLE ASSIGNEMENT
(
   assId SERIAL,
   assCode CHAR(4) NOT NULL,
   CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(assId)
);

INSERT INTO ASSIGNEMENT (assCode)
VALUES
   ('a1'), ('a2'), ('a3'), ('a3') ;

CREATE TABLE METRIC_QUALITY
(
   assId INTEGER,
   mqDate TIMESTAMP,
   mqClosed BOOLEAN NOT NULL,
   mqResolved BOOLEAN NOT NULL,
   CONSTRAINT METRIC_QUALITY_PK PRIMARY KEY(assId, mqDate),
   CONSTRAINT METRIC_QUALITY_ASSIGNMENT_FK FOREIGN KEY(assId) 
       REFERENCES ASSIGNEMENT(assId)
);

INSERT INTO METRIC_QUALITY (assId, mqDate, mqClosed, mqResolved)
VALUES
    ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'), '2020-01-01 00:01:00', FALSE, FALSE)
  , ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'), '2020-02-01 00:02:00', FALSE, FALSE)
  , ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'), '2020-03-01 00:03:00', FALSE, FALSE)

  , ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a2'), '2020-01-01 00:01:00', FALSE, FALSE)
  , ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a2'), '2020-02-01 00:02:00', FALSE, FALSE)
;

CREATE TABLE COMMENTAIRE
(
   commentId SERIAL,
   commentaire VARCHAR(96) NOT NULL,
   CONSTRAINT COMMENTAIRE_PK PRIMARY KEY(commentId)
);

CREATE TABLE COMMENT_ASS
(
        commentId INTEGER,
        assId INTEGER NOT NULL,
      CONSTRAINT COMMENT_ASS_PK PRIMARY KEY(commentId),
      CONSTRAINT COMMENT_ASS_COMMENTAIRE_FK FOREIGN KEY(commentId) 
          REFERENCES COMMENTAIRE(commentId) ON DELETE CASCADE
    , CONSTRAINT COMMENT_ASS_ASSIGNMENT_FK FOREIGN KEY(assId) 
          REFERENCES ASSIGNEMENT(assId)
);

CREATE TABLE COMMENT_MQ
(
        commentId INTEGER,
        assId INTEGER NOT NULL,
        mqDate TIMESTAMP NOT NULL,
      CONSTRAINT COMMENT_MQ_PK PRIMARY KEY(commentId),
      CONSTRAINT COMMENT_MQ_COMMENTAIRE_FK FOREIGN KEY(commentId) 
          REFERENCES COMMENTAIRE(commentId) ON DELETE CASCADE
    , CONSTRAINT COMMENT_MQ_METRIC_QUALITY_FK FOREIGN KEY(assId, mqDate) 
          REFERENCES METRIC_QUALITY(assId, mqDate)
);

CREATE FUNCTION COMMENT_EXCLUSION_FN()
    RETURNS TRIGGER AS
$$
    DECLARE n INT ;
	DECLARE Erreur  VARCHAR ;
    DECLARE leCoupable VARCHAR ;

BEGIN

    leCoupable = 
           (
            SELECT commentaire 
            FROM   COMMENT_ASS as x
              JOIN COMMENT_MQ as y ON x.commentId = y.commentId
              JOIN commentaire as z ON x.commentId = z.commentId
            WHERE  x.commentId = NEW.commentId
           ) ;

    IF leCoupable IS NOT NULL
        THEN
            erreur = 'Le commentaire ''' || leCoupable || ''' ne peut pas être à la fois ASS et MQ' ;
            RAISE EXCEPTION SQLSTATE '45002' USING MESSAGE = erreur ;
    END IF ;

    RETURN NEW ;  
END
$$
LANGUAGE plpgsql ; 

CREATE TRIGGER COMMENT_EXCLUSION_ASS_TR after INSERT OR UPDATE ON COMMENT_ASS
    FOR EACH ROW EXECUTE PROCEDURE COMMENT_EXCLUSION_FN() ;

CREATE TRIGGER COMMENT_EXCLUSION_MQ_TR after INSERT OR UPDATE ON COMMENT_MQ
    FOR EACH ROW EXECUTE PROCEDURE COMMENT_EXCLUSION_FN() ;

INSERT INTO COMMENTAIRE (commentaire)
VALUES 
    ('comment 1')
  , ('comment 2') 
;

INSERT INTO COMMENT_ASS (commentId, assId)
VALUES
    ((SELECT commentId FROM COMMENTAIRE WHERE commentaire = 'comment 1')
   , (SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'))
;
INSERT INTO COMMENT_ASS (commentId, assId)
VALUES
    ((SELECT commentId FROM COMMENTAIRE WHERE commentaire = 'comment 2')
   , (SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'))
;

SELECT * FROM  COMMENT_ASS ;
 

/* Exclusion des commentaires => ça doit planter ! */

INSERT INTO COMMENT_MQ (commentId, assId, mqDate)
VALUES  
    ((SELECT commentId FROM COMMENTAIRE WHERE commentaire = 'comment 1')
   , (SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1')
   , '2020-01-01 00:01:00')
;

SELECT * FROM  COMMENT_MQ ;