conception de schéma de base de données de système de messagerie de thread

Question

Je suis en train de réaliser exactement ce qui est expliqué ici: La création d'un filetée système de messagerie privée comme facebook et gmail, toutefois, je n'ai pas complètement comprendre Joel Brown réponse. quelqu'un peut-il expliquer.

C'est ce que mon db tables avec les données de l'échantillon (je suppose que je l'ai rempli correctement à des fins de démonstration):

1. J'ai besoin d'afficher une liste de threads basé sur l'identifiant de connexion (les plus récents en haut) ce qui serait la requête ressemble à LINQ? (ce que je vous demande est dans un groupe de fils de message, donnez-moi la 1 nouveau message dans chaque thread) - tout comme cela est fait sur facebook.

2. J'ai besoin d'afficher TOUS les messages dans un fil de messages (LINQ) -> tout comme c'est fait sur facebook où vous cliquez sur le message et vous verrez l'ensemble de la "conversation" dans la bande de roulement.

S'il vous plaît aider! merci

EDIT -> poursuite Joel, est-ce correct??

Joel, je suis un peu confus, pouvez-vous nous expliquer (les commentaires/questions en gras):

L'idée ici est que chaque fois qu'un utilisateur démarre une nouvelle marque thread/message, il commence avec un nouveau record dans le FIL de la table. L'utilisateur est ensuite ajouté comme une THREAD_PARTICIPANT et le contenu du message est ajouté à un MESSAGE qui renvoie à l'contenant THREAD. La FK à partir d'un MESSAGE à l'UTILISATEUR indique l'auteur du message.

LoginId 1 envoie un message à LoginId2 => nouvel enregistrement est inséré à MessageThread table. Également un enregistrement est inséré à MessageThreadParticipant enregistrement avec MessageThreadId = 1, LoginId = 1 (l'expéditeur). Et un nouvel enregistrement est inséré dans la table Message avec MessageId =1, MessageThreadid =1, SenderLoginId = 1 (exact?)

c'est ce que j'ai après cette itération:

Je pense que je suis confus car il n'existe aucun moyen pour Loginid 2 à savoir qu'il y a un message pour lui. ?? OU peut-être que j'ai besoin d'insérer 2 enregistrements dans MessageThreadParticipant?? (l'émetteur et le récepteur)-> de cette façon, peut voir l'ensemble de la "conversation"??

EDIT2: Joe, je pense que je pourrais faire ceci:

SELECT
  Message.MessageId, Message.CreateDate, Message.Body, Login.Username, Message.SenderLoginId
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     ) as ReadDate
FROM Message 
    INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
    INNER JOIN MessageThreadParticipant mtp on mtp.MessageThreadId = Message.MessageThreadId 
AND ( Message.MessageId in 
        ( SELECT Max(Message.MessageId)
          FROM MessageThreadParticipant INNER JOIN Message 
            ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
          GROUP BY MessageThreadParticipant.MessageThreadId
        )
      )
Where mtp.LoginId = 2
ORDER BY Message.CreateDate DESC;

S'il vous plaît corrigez-moi si je me trompe :)

Answer 1

Eh bien pourquoi ne pas simplement demander? :)

Permettez-moi de tenter de fixer le ma compréhension de vos besoins. Il me semble que vous êtes à la recherche à un thread d'une liste linéaire (pas un arbre) de messages entre deux personnes. Je pense que vous pourriez vouloir pour permettre à plus de personnes que seulement deux. Ce serait comme Facebook, dans la mesure où quelqu'un poste un message et ensuite n'importe quel nombre de gens puissent le lire et puis commencer à ajouter des commentaires. Lorsque vous ajoutez un commentaire, il vous met dans le thread et que vous commencez à obtenir des mises à jour de statut et les e-mails vous informant sur l'activité dans le fil et ainsi de suite. En supposant que c'est ce que vous êtes après, alors le schéma, j'ai suggéré à Big Mike n'est pas exactement ce que vous cherchez.

Considérons plutôt la suivante:

L'idée ici est que chaque fois qu'un utilisateur démarre une nouvelle marque thread/message, il commence avec un nouveau record dans le FIL de la table. L'utilisateur est ensuite ajouté comme une THREAD_PARTICIPANT et le contenu du message est ajouté à un MESSAGE qui renvoie à l'contenant THREAD. La FK à partir d'un MESSAGE à l'UTILISATEUR indique l'auteur du message.

Lorsqu'un utilisateur lit un message, ils obtiennent une entrée dans le MESSAGE_READ_STATE table pour indiquer qu'ils ont marqué le message lu, que ce soit explicitement ou implicitement, en fonction de vos exigences.

Quand quelqu'un commentaires sur le message initial dans le fil, un second MESSAGE est ajouté avec un FK retour vers le THREAD d'origine et la réponse de l'auteur (de l'utilisateur) est ajouté à la THREAD_PARTICIPANT table. Et il en va de messages sont ajoutés au fil à un, deux ou même plus de participants.

Pour obtenir le message le plus récent dans n'importe quel thread, il suffit de prendre le top 1 de MESSAGE triés par ordre décroissant dans la date de création (ou une clef d'identité) où le message FK est le thread de l'intérêt.

Pour obtenir le plus récemment mis à jour le fil d'un utilisateur, d'obtenir le FIL vers le haut à 1 à partir d'un message triés par ordre décroissant dans la date de création où le message est dans un thread dans lequel l'utilisateur est un THREAD_PARTICIPANT.

Je crains que je ne peux jamais l'état de ces choses dans LINQ sans sortir LinqPad. Si vous éprouvez de la difficulté à reprendre ma dérive à partir de ci-dessus, j'ai pu étoffer la réponse avec les définitions de table et de quelques SQL. Il suffit de demander dans les commentaires.

EDIT: Clarification des Exigences et la mise en Œuvre

Clarifier les exigences: d'Abord, je pensais publiquement affiché des messages avec la possibilité pour les commentaires, alors que Shane est après plus d'un message direct fonctionnalité. Auquel cas le bénéficiaire initial doit être inclus dans le THREAD_PARTICIPANT table dès le début.

Un peu de clarté, nous allons mettre quelques lignes dans les tables. Voici le scénario, (en l'honneur de la fête du Canada): l'Utilisateur 1 DMs de l'Utilisateur 2 à poser à propos de la réunion pour une bière. L'utilisateur 2 réponses à une question sur l'endroit où se rencontrer et de l'Utilisateur 1 réponses. Les tables ressemblerait à quelque chose comme ceci: (probablement simpliste)

EDIT #2: Accès SQL pour la liste de tous les messages dans un fil de discussion avec l'état de lecture...

À l'aide de @OP schéma, ce SQL obtiendrez une liste de messages dans un fil avec une indication de savoir si un utilisateur donné a lire chaque message ou pas. Les Messages sont dans le plus récent en premier ordre.

SELECT 
  Message.MessageId
, Message.CreateDate
, Message.Body
, Login.Username
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     and MessageReadState.LoginId = 2) as ReadState
FROM (Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId) 
WHERE (((Message.MessageThreadId)=10))
ORDER BY Message.CreateDate DESC;

Notez que le truc, si c'est juste de l'appeler comme ça, c'est que l'état de lecture est ramassé avec une sous-sélection. Cela est nécessaire parce qu'une partie des critères pour l'obtention de l'état de lecture exige une clause where qui ne peuvent pas être satisfaits avec une jointure externe. Par conséquent, vous utilisez celle-ci pour déterminer qui (éventuellement manquantes) la valeur que vous voulez à partir de la MessageReadState table enfant.

EDIT 3: SQL pour obtenir tous les threads avec le dernier message de chaque pour un utilisateur donné...

Pour obtenir une liste de tous les threads dans laquelle un utilisateur donné a participé, triés par plus récent message tout d'abord, avec seulement les plus récents afficher de message (1 message par thread) alors vous devez utiliser une requête similaire à celui ci-dessus, sauf qu'au lieu de filtrage des messages par leur FK le thread de l'intérêt, permet de filtrer les messages par une sous-requête qui trouve le dernier message de chaque thread que l'utilisateur d'intérêt participé. Il devrait ressembler à ceci:

SELECT
  Message.MessageId
, Message.CreateDate
, Message.Body
, Login.Username
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     and MessageReadState.LoginId = 2) AS ReadState
FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
WHERE ( Message.MessageId in 
        ( SELECT Max(Message.MessageId)
          FROM MessageThreadParticipant INNER JOIN Message 
            ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
          WHERE MessageThreadParticipant.LoginId=2
          GROUP BY MessageThreadParticipant.MessageThreadId
        )
      )
ORDER BY Message.CreateDate DESC;

Answer 2

Selon Joel Brown'answer, vous pouvez ajouter une colonne LAST_MESSAGE_ID à la table THREAD, puis obtenir tous les fils avec les derniers messages Le code SQL est devenu très simple. Vous devez mettre à jour cette colonne lorsque chaque message est envoyé.

Obtenir tous les sujets avec le dernier message dans chacun pour un utilisateur donné

 SELECT *
FROM THREAD T
INNER JOIN MESSAGE M ON T.LAST_MESSAGE_ID=M.MESSAGE_ID
INNER JOIN USER SENDER ON M.USER_ID=SENDER.USER_ID
LEFT JOIN MessageReadState MRS ON M.MESSAGE_ID=MRS.MESSAGE_ID AND MRS.USER_ID=2