线程消息系统数据库架构设计 [英] thread messaging system database schema design

问题描述

我正在努力实现这里所解释的内容: 不是您要找的.

请考虑以下情况:

这里的想法是，每次用户启动一个全新的线程/消息时，它都会从 THREAD 表中的一条新记录开始.然后将用户添加为 THREAD_PARTICIPANT，并将消息的内容添加到 MESSAGE，后者指向包含 THREAD.MESSAGE 到 USER 的 FK 表示消息的作者.

当用户阅读消息时，他们会在 MESSAGE_READ_STATE 表中获得一个条目，以表明他们已将消息标记为已读，无论是显式还是隐式，具体取决于您的需求.

当有人对线程中的初始消息发表评论时，第二条 MESSAGE 会添加一个 FK 回到原始 THREAD，并且回复作者(用户)被添加到 THREAD_PARTICIPANT 表中.当一个、两个甚至更多参与者将消息添加到线程时，就会发生这种情况.

要获取任何线程中的最新消息，只需从 MESSAGE 中按创建日期(或身份密钥)降序排序的前 1 条消息 FK 发送到感兴趣的线程.

要获取用户最近更新的线程，请从消息中按创建日期降序排序的前 1 条线程获取相关线程，其中消息位于用户是 THREAD_PARTICIPANT 的线程中.

恐怕我永远无法在不打破 LinqPad 的情况下在 LINQ 中陈述这些事情.如果您无法理解上述内容，我可以用表定义和一些 SQL 来充实答案.只需在评论中提问.

要求和实施的说明

澄清要求:最初我考虑的是公开发布的消息，并有机会发表评论，而 Shane 则追求更多的直接消息功能.在这种情况下，初始收件人需要从一开始就包含在 THREAD_PARTICIPANT 表中.

为了清楚起见，让我们在表格中放置几行.这是场景，(为了纪念加拿大日):用户 1 向用户 2 发送 DM，询问会议是否喝啤酒.用户 2 回答关于在哪里见面的问题，用户 1 回答.表格看起来像这样:(可能过于简化)

编辑 #2:访问 SQL 以获取线程中所有消息的列表，具有读取状态...

使用@OP 的模式，此 SQL 将获取给定线程中的消息列表，并指示给定用户是否已阅读每条消息.消息按最近的第一顺序排列.

SELECT消息.MessageId, 消息.创建日期， 邮件正文, 登录.用户名, (SELECT MessageReadState.ReadDate从消息读取状态WHERE MessageReadState.MessageId = Message.MessageId和 MessageReadState.LoginId = 2) 作为 ReadState发件人(消息内部连接登录消息.SenderLoginId = Login.LoginId)哪里 (((Message.MessageThreadId)=10))ORDER BY Message.CreateDate DESC;

请注意，如果可以公平地称呼它，技巧是通过子选择获取读取状态.这是必要的，因为获取读取状态的部分标准需要外连接无法满足的 where 子句.因此，您可以使用子选择从 MessageReadState 子表中确定您想要的(可能缺少的)值.

编辑 3:SQL 用于获取给定用户的所有线程中的最新消息...

要获取给定用户参与的所有线程的列表，首先按最近的消息排序，只显示最近的消息(每个线程 1 条消息)，然后您可以使用与上面的一个，除了不是通过他们的 FK 过滤到感兴趣的线程的消息，你通过一个子查询过滤消息，在感兴趣的用户参与的每个线程中找到最新的消息.它看起来像这样:

SELECT消息.MessageId, 消息.创建日期， 邮件正文, 登录.用户名, (SELECT MessageReadState.ReadDate从消息读取状态WHERE MessageReadState.MessageId = Message.MessageId和 MessageReadState.LoginId = 2) AS ReadState从消息内部连接登录 Message.SenderLoginId = Login.LoginIdWHERE ( Message.MessageId 在( SELECT Max(Message.MessageId)FROM MessageThreadParticipant INNER JOIN 消息ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadIdWHERE MessageThreadParticipant.LoginId=2GROUP BY MessageThreadParticipant.MessageThreadId))ORDER BY Message.CreateDate DESC;

I'm trying to achieve exactly what's explained here: Creating a threaded private messaging system like facebook and gmail, however i don't completly understand Joel Brown's answer. can any one please explain.

This is what my db tables look like with sample data (I assume i filled it in correctly for demo purposes):

1. I need to display a list of threads based on LoginId (newest on top) what would the query look like in LINQ? (what i'm asking is in a a group of message threads, give me the 1 newest message in each thread) - just like this is done on facebook.

2. I need to display ALL the messages in a message thread (LINQ) -> just like it's done on facebook where you click the message and you would see the whole "conversation" in a tread.

Please help! thanks

EDIT -> continuation Joel, is this correct??

Joel, i'm a bit confused, can you please explain (comments/questions in bold):

The idea here is that every time a user starts a brand new thread/message, it starts with a new record in the THREAD table. The user is then added as a THREAD_PARTICIPANT and the content of the message is added to MESSAGE which points back to the containing THREAD. The FK from MESSAGE to USER indicates the author of the message.

LoginId 1 sends a message to LoginId2 => new record is inserted to MessageThread table. Also a record is inserted to MessageThreadParticipant record with MessageThreadId = 1, LoginId = 1 (the sender). And a new record is inserted into Message table with MessageId =1, MessageThreadid =1, SenderLoginId = 1 (correct??)

this is what i have after that iteration:

I think i'm confused because there is no way for Loginid 2 to know that there is a message for him. ?? OR maybe I need to insert 2 records into MessageThreadParticipant?? (the sender and the receiver)-> this way both can see the whole "conversation"??

EDIT2: Joe, I think I could do this:

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;

Please correct me if i'm wrong :)

解决方案

Well why don't you just ask? :)

Let me try to pin down my understanding of your requirement. It seems to me that you are looking at a thread being a linear list (not a tree) of messages between two people. I would think that you might want to allow more people in than just two. That would be like Facebook insofar as someone posts a message and then any number of people can read it and then start adding comments. When you add a comment it puts you into the thread and you start getting status updates and e-mails telling you about activity in the thread and so forth. Assuming that is what you're after, then the schema I suggested to Big Mike is not exactly what you're looking for.

Consider instead the following:

The idea here is that every time a user starts a brand new thread/message, it starts with a new record in the THREAD table. The user is then added as a THREAD_PARTICIPANT and the content of the message is added to MESSAGE which points back to the containing THREAD. The FK from MESSAGE to USER indicates the author of the message.

When a user reads a message, they get an entry in the MESSAGE_READ_STATE table to indicate that they have marked the message read, either explicitly or implicitly, depending on how your requirements go.

When someone comments on the initial message in the thread, a second MESSAGE is added with an FK back to the original THREAD and the reply author (user) gets added to the THREAD_PARTICIPANT table. And so it goes as messages are added to the thread by one, two or even more participants.

To get the most recent message in any thread, just take the top 1 from MESSAGE sorted descending on create date (or an identity key) where the message FK is to the thread of interest.

To get the most recently updated thread for a user, get the THREAD related to the top 1 from message sorted descending on create date where the message is in a thread in which the user is a THREAD_PARTICIPANT.

I'm afraid I can never state these things in LINQ without breaking out LinqPad. If you are having trouble catching my drift from the above, I could flesh out the answer with table definitions and some SQL. Just ask in the comments.

EDIT: Clarification of Requirements and Implementation

Clarifying the requirements: Initially I was thinking about publicly posted messages with the opportunity for commenting, whereas Shane is after more of the direct message feature. In which case the initial recipient needs to be included in the THREAD_PARTICIPANT table at the outset.

For some clarity, let's put a few rows in tables. Here is the scenario, (in honour of Canada Day): User 1 DMs User 2 to ask about meeting for a beer. User 2 replies with a question about where to meet and User 1 answers. The tables would look something like this: (probably oversimplified)

EDIT #2: Access SQL for list of all messages in a thread, with read state...

Using @OP's schema, this SQL will get a list of messages in a given thread with an indication of whether a given user has read each message or not. Messages are in most recent first order.

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;

Note that the trick, if it's fair to call it that, is that the read state is picked up with a sub-select. This is necessary because part of the criteria for getting the read state requires a where clause that can't be satisfied with an outer join. Therefore you use the subselect to pin down which (possibly missing) value you want from the MessageReadState child table.

EDIT 3: SQL for getting all threads with latest message in each for a given user...

To get a list of all of the threads in which a given user has participated, sorted by most recent message first, with only the most recent message being displayed (1 message per thread) then you would use a similar query to the one above, except instead of filtering messages by their FK to the thread of interest, you filter the messages by a subquery that finds the latest message in each thread that the user of interest participated in. It would look like this:

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;

这篇关于线程消息系统数据库架构设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！