Asp.net 线程消息系统数据库架构设计

那你为什么不直接问呢？:)

让我试着说明我对你的要求的理解.在我看来，你看到的线程是两个人之间消息的线性列表(而不是树).我想你可能想让更多的人进来，而不仅仅是两个人.这就像Facebook，只要有人发布一条消息，然后任何人都可以阅读它，然后开始添加 comments .当你添加一条 comments 时，它会把你放到线程中，你开始得到状态更新和邮箱，告诉你线程中的活动等等.假设这就是你想要的，那么我建议的Big Mike的模式并不完全是你想要的.

考虑下列事项:

这里的 idea 是，每当用户启动一个全新的线程/消息时，它都会从线程表中的一条新记录开始.然后将用户添加为线程_参与者，并将消息内容添加到消息中，该消息指向包含的线程.从消息到用户的FK表示消息的作者.

当用户阅读一条消息时，他们会在message_READ_STATE表中得到一个条目，表明他们已显式或隐式地将消息标记为已读，具体取决于您的需求.

当有人对线程中的初始消息发表 comments 时，第二条消息将添加一个FK返回到原始线程，回复作者(用户)将被添加到thread_参与者表中.当消息被一个、两个甚至更多的参与者添加到线程中时，情况就是这样.

要获取任何线程中的最新消息，只需从消息FK所在的感兴趣的线程的创建日期(或身份密钥)上按降序排序的消息中取出顶部1.

要获取用户最近更新的主题，请从创建日期降序排序的消息中获取与前1个主题相关的主题，其中该消息位于该用户是THREAD_PARGMENT的线程中.

恐怕我不能在LINQ中陈述这些事情而不爆发LinqPad.如果你不明白我上面的意思，我可以用表定义和一些SQL来充实答案.只需在 comments 中提问.

EDIT: Clarification of Requirements and Implementation

澄清要求:最初我考虑的是有机会发表 comments 的公开消息，而Shane想要的是更多的直接消息功能.在这种情况下，最初的收件人需要在一开始就包含在THREAD_参与者表中.

为了更清楚，让我们在表格中放几行.下面是一个场景(为了纪念加拿大日):用户1 DMs用户2询问会议的啤wine 事宜.用户2回答一个关于在哪里见面的问题，用户1回答.这些表格看起来是这样的:(可能过于简化了)

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

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

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;

请注意，如果这样称呼是公平的，那么诀窍就是用子 Select 来获取读取状态.这是必要的，因为获取读取状态的部分条件需要一个WHERE子句，而该子句不能满足外部连接.因此，您可以使用子 Select 来确定您希望从MessageReadState子表中 Select 哪个(可能缺少)值.

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

要获取给定用户参与的所有线程的列表，首先按最新消息排序，只显示最新消息(每个线程1条消息)，然后使用与上述查询类似的查询，除了不按FK过滤消息到感兴趣的线程之外，通过子查询筛选消息，该子查询在感兴趣的用户参与的每个线程中查找最新消息.看起来是这样的:

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;