Discussion :

[Crackerz]

Bonjour,

Je viens vous faire part d'une même difficulté qu'il m'arrive de rencontrer dans différents problèmes. Je prend un exemple pour vous expliquer ce dernier.
Un utilisateur peut envoyer des messages à un autres utilisateurs, je modélise cela de cette manière :
[Utilisateur]--- 0,N ---- (envoyerMessage) --- 0,N --- Utilisateur (association réflexive).

Plus concrètement le rôle envoyerMessage devient une table : Message(id_demander,id_demandeur, Etat, DateDemande);
Etat peut prendre comme valeurs : lu,envoyer,brouillon.

Cependant, si par exemple l'utilisateur A supprime son message je veux qu'il reste toujours disponible pour l'utilisateur B. La seule solution que je vois est de mettre la valeur null dans la table Message en fonction de qui supprime le message (le demandeur, ou le demander).

Ma question est donc, est-ce qu'il y a une solution pour éviter la valeur null dans un cas comme celui-ci ?

Merci.

[fsmrel]

Bonjour Crackerz,


Si l’utilisateur U1 supprime un message M1 qu’il a créé, et que celui-ci doit rester disponible pour les utilisateurs U2, ...Um, vous pouvez modéliser ainsi la partie qui concerne l’émission des messages :

[UTILISATEUR]--0,N--------(R1)-------- 1,1--[EMISSION]--(1,1)--------(R2)--------(0,1)--[MESSAGE]

A la création de M1, on effectue un insert dans la table EMISSION (dérivée de l’entité-type EMISSION) et un insert dans la table MESSAGE (dérivée de l’entité-type MESSAGE).

Quand U1 voudra supprimer M1, en fait l’opération ne portera que sur la table EMISSION : M1 ne sera plus présent dans EMISSION mais le sera dans MESSAGE, donc inaccessible pour U1 mais accessible pour U2.

Du côté réception on a quelque chose de semblable :






MLD :







Code SQL

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 CREATE TABLE UTILISATEUR 
(
   UtilisateurId        INT         NOT NULL,
   CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UtilisateurId)
) ;
 
CREATE TABLE MESSAGE 
(
   MessageId            INT         NOT NULL,
   CONSTRAINT MESSAGE_PK PRIMARY KEY (MessageId)
) ;
 
CREATE TABLE EMISSION 
(
   MessageId            INT         NOT NULL,
   UtilisateurId        INT         NOT NULL,
   CONSTRAINT EMISSION_PK PRIMARY KEY (MessageId),
   CONSTRAINT EMISSION_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
      REFERENCES UTILISATEUR (UtilisateurId)
         ON DELETE CASCADE,
   CONSTRAINT EMISSION_MESSAGE_FK FOREIGN KEY (MessageId)
      REFERENCES MESSAGE (MessageId)
) ;
 
CREATE TABLE RECEPTION 
(
   MessageId            INT         NOT NULL,
   CONSTRAINT RECEPTION_PK PRIMARY KEY (MessageId),
   CONSTRAINT RECEPTION_MESSAGE_FK FOREIGN KEY (MessageId)
      REFERENCES MESSAGE (MessageId)
) ;
 
CREATE TABLE UTIL_RECEPT 
(
   MessageId            INT         NOT NULL,
   UtilisateurId        INT         NOT NULL,
   CONSTRAINT UTIL_RECEPT_PK PRIMARY KEY (MessageId, UtilisateurId),
   CONSTRAINT UTIL_RECEPT_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
      REFERENCES UTILISATEUR (UtilisateurId)
         ON DELETE CASCADE,
   CONSTRAINT UTIL_RECEPT_RECEPTION_FK FOREIGN KEY (MessageId)
      REFERENCES RECEPTION (MessageId)
) ;

Quand un message a été supprimé des deux côtés, en fait ce sont les liens avec les utilisateurs qui sont rompus, mais il est toujours présent. A vous de voir dans quelles conditions il peut être supprimé (garbage collector, trigger su delete...)

[Crackerz]

Bonjour,

Votre réponse est toujours aussi claire
Cependant, en évitant la valeur null par le schéma que vous me proposez on passe de 3 tables à 5 tables est-ce que cela à des répercussions sur les performances ? Lorsque nous voulons conserver des entités commune à deux personnes la méthode est-elle dont de décomposé en plus d'étape comme vous l'avez fait ?

Que pensez-vous d'une solution consistant tout simplement à ajouter deux attributs de type booléens initialiser à false. Lorsque l'utilisateur demande la suppression du message nous l'initialisons à true, une fois les deux attributs à true le message est automatiquement supprimé.

Enfin, pour utiliser le garbage collector faut-il le préciser de manière explicite ?

Merci pour les choses que vous m'apprendrez

[fsmrel]

Bonjour Crackerz,

est-ce que cela à des répercussions sur les performances ?

En consultation, les conséquences sont quasi nulles, on ne les ressent pas. Lors de la création/suppression des messages, le SGBD a 2 index de plus à mettre à jour (un pour la table EMISSION et un autre pour la table RECEPTION) mais cela devrait rester imperceptible, vous n’êtes pas dans la configuration méridionale...

Lorsque nous voulons conserver des entités commune à deux personnes la méthode est-elle dont de décomposé en plus d'étape comme vous l'avez fait ?

De façon générale, pour éviter l’immixtion du bonhomme Null, une solution est de mettre en œuvre une table à cet effet, comme dans votre exemple.

Que pensez-vous d'une solution consistant tout simplement à ajouter deux attributs de type booléens initialiser à false.

C’est une solution qui du reste est utilisée depuis les débuts de l’informatique, on appelle cela informellement la « suppression logique »...

pour utiliser le garbage collector faut-il le préciser de manière explicite ?

Il faudrait que vous posiez la question dans le forum correspondant à votre SGBD, pour voir comment la procédure chargée de faire le ménage peut être déclenchée automatiquement.


Bonne suite

[Crackerz]

Merci fsmrel, j'aurai une dernière question à ce sujet.

Si vous devriez trancher entre la méthode que vous m'avez proposée et celle de l'utilisation de booléen laquelle prendrez-vous ? Sachant que ce même procédé devra être reporté également sur 5 autres tables.

Merci pour vos conseils

[fsmrel]

Bonsoir Crackerz,


Par précaution je pencherais pour la 1re solution. Dans le cas de la 2e solution, tout dépend de celui qui code les requêtes : que se passe-t-il si en vitesse on code un SELECT mettant en jeu la table MESSAGE en oubliant de tester l’attribut signifiant que tel message est « logiquement supprimé » ? Vous me direz qu’on peut interdire par REVOKE l’accès direct à la table et en contrepartie imposer l'utilisation d'une vue testant cet attribut : c’est alors un problème d’organisation rigoureuse imposée par les DBA, avec l’aval de la direction de la production informatique.

[Crackerz]

Merci fsmrel.
J'ai l'impression que la première méthode est plus "lourde" et plus complexe. Cependant je vais faire confiance a votre expérience est essayer de l'appliquer dans les meilleurs conditions en espérant avoir de bonne performance (plus qu'avec la deuxième méthode en tout qu'a )

[fsmrel]

Bonsoir Crackerz,

J'ai l'impression que la première méthode est plus "lourde" et plus complexe.

Suite à votre réflexion, j’ai relu de près les messages que nous avons échangés, tout en me mettant dans la peau du forumeur découvrant la discussion, c'est-à-dire avec un œil neuf. Je constate qu’en ne modélisant pas l’entité-type on allège le système, sans perdre en fonctionnalités.

Selon le MCD ci-dessous, si on supprime un utilisateur destinataire de messages, on ne perd pas ces messages, on rompt seulement les liens portés par l’association UTIL_RECEPT (même chose en supprimant un message) :

MCD




MLD




Code SQL


TABLE UTILISATEUR

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE UTILISATEUR 
(
         UtilisateurId       INT          NOT NULL
       , UtilisateurNom      VARCHAR(64)  NOT NULL
   ,  CONSTRAINT UTILISATEUR_PK PRIMARY KEY (UtilisateurId)
) ;

TABLE MESSAGE

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE MESSAGE 
(
        MessageId            INT          NOT NULL
      , MessageContenu       VARCHAR(64)  NOT NULL   
      , MessageEtat          VARCHAR(12)  NOT NULL 
   , CONSTRAINT MESSAGE_PK PRIMARY KEY (MessageId)
) ;

TABLE EMISSION

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 EMISSION 
(
   MessageId            INT         NOT NULL,
   UtilisateurId        INT         NOT NULL,
   CONSTRAINT EMISSION_PK PRIMARY KEY (MessageId),
   CONSTRAINT EMISSION_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
      REFERENCES UTILISATEUR (UtilisateurId)
         ON DELETE CASCADE,
   CONSTRAINT EMISSION_MESSAGE_FK FOREIGN KEY (MessageId)
      REFERENCES MESSAGE (MessageId)
         ON DELETE CASCADE
) ;

TABLE UTIL_RECEPT

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 UTIL_RECEPT 
(
   MessageId            INT         NOT NULL,
   UtilisateurId        INT         NOT NULL,
   CONSTRAINT UTIL_RECEPT_PK PRIMARY KEY (MessageId, UtilisateurId),
   CONSTRAINT UTIL_RECEPT_UTILISATEUR_FK FOREIGN KEY (UtilisateurId)
      REFERENCES UTILISATEUR (UtilisateurId)
         ON DELETE CASCADE,
   CONSTRAINT UTIL_RECEPT_RECEPTION_FK FOREIGN KEY (MessageId)
      REFERENCES MESSAGE (MessageId)
         ON DELETE CASCADE
) ;

A votre avis ?

[fsmrel]

En complément.


Pour faciliter la vie de l’utilisateur, vous pouvez lui définir des vues cachant au besoin la complexité. Quelques exemples très simples :

Une vue permettant de fournir la liste des messages sans créateur :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE VIEW MESSAGES_SANS_CREATEUR (MessageId, MessageContenu, MessageEtat)
AS
SELECT MessageId, MessageContenu, MessageEtat
FROM   MESSAGE AS x
WHERE  NOT EXISTS 
      (SELECT ''
       FROM   EMISSION AS y
       WHERE  x.MessageId = y.MessageId) ;

Une vue permettant de fournir la liste des messages sans récepteur :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE VIEW MESSAGES_SANS_RECEPTEUR (MessageId, MessageContenu, MessageEtat)
AS
    SELECT MessageId, MessageContenu, MessageEtat
    FROM   MESSAGE AS x
    WHERE  NOT EXISTS 
          (SELECT ''
           FROM   UTIL_RECEPT AS y
           WHERE  x.MessageId = y.MessageId) ;

Pour fournir la liste des messages sans créateur ni récepteur :

Code SQL :

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

1
2
3
4
CREATE VIEW MESSAGES_SANS_CREATEUR_NI_RECEPTEUR (MessageId, MessageContenu, MessageEtat)
 AS
    SELECT x.MessageId, x.MessageContenu, x.MessageEtat
    FROM   MESSAGES_SANS_CREATEUR AS x JOIN MESSAGES_SANS_RECEPTEUR AS y ON x.MessageId = y.MessageId ;

Etc.

[Crackerz]

Bonjour fsmrel je ne m’attendais pas à ce que vous développiez plus la réflexion après le passage en résolu

Selon le MCD ci-dessous, si on supprime un utilisateur destinataire de messages, on ne perd pas ces messages, on rompt seulement les liens portés par l’association UTIL_RECEPT (même chose en supprimant un message)

Les messages sont toujours là mais on ne sait plus à qu'ils appartiennent (été destiné), non ?

Je vous remercie pour votre solution car elle est beaucoup plus évolutif que celle des booléens. Ne me donner pas tout votre savoir car je finirai par vous surpasser =P

Bonne journée.

[fsmrel]

Bonsoir Crackerz,

Les messages sont toujours là mais on ne sait plus à qu'ils appartiennent (été destiné), non ?

Quel intérêt de faire référence à quelqu'un qui n'existe plus dans la base de données ?

Ne me donner pas tout votre savoir car je finirai par vous surpasser

C'est tout ce qu'on peut vous souhaiter...

[Crackerz]

Quel intérêt de faire référence à quelqu'un qui n'existe plus dans la base de données ?

Aucun et nous sommes complétement d'accord.
Je pensai que par le précédent passage citer vous vouliez dire qu'un utilisateur supprimé à qui des messages ont été destiné sont conservés MAIS qu'on ne peut plus savoir à qui ( puisque la liaison avec l'utilisateur a été rompu ).

C'est tout ce qu'on peut vous souhaiter...

Dans encore pas mal de temps (années ^^ ).

Bonne soirée.