database schema design for thread messaging system

asp.net database database-design entity-framework linq

Question

I'm attempting to do precisely what is said here: establishing a private threaded messaging platform a la Facebook and Gmail, but I don't really understand Joel Brown's response. Could someone kindly explain?

The example data in my database tables looks like this (I presume I entered it in properly for demonstration purposes):enter image description here

  1. A list of threads based on LoginId must be shown (newest on top) In LINQ, how would the query look? Similar to how it works on Facebook, i'm asking for the newest message in each of a set of message threads.

  2. I need to use LINQ to show EVERY message in a message thread, exactly as Facebook does, where you can click on a message to see the whole "conversation" in a thread.

Please assist! thanks

33-ZZZ-ZZZ -> Continue Joel, Is this accurate?

enter image description here

Joel, Can you clarify anything for me? (Comments and/or questions in bold)

The concept behind this is that each time a user creates a brand-new thread or message, a new entry in the THREAD database is created. The message's content is then added to MESSAGE, which refers back to the thread that contains it, and the user is added as a THREAD PARTICIPANT. The message's author is shown by the FK from MESSAGE to USER.

A new entry is added to the MessageThread table when LoginId 1 delivers a message to LoginId 2. Additionally, a record with the details MessageThreadId = 1, LoginId = 1 is added to the MessageThreadParticipant record (the sender). And a new record with the values 1 for MessageId, 3 for MessageThreadid, and 1 for SenderLoginId is added to the Message database.

After that iteration, I have this:enter image description here

I believe my confusion stems from the fact that Loginid 2 has no means of knowing that he has a message waiting for him. OR maybe I should add two entries to MessageThreadParticipant? Both parties can view the whole "conversation" in this fashion (the sender and the receiver)?

EDIT2: Joe, I believe I can complete 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;

If I'm incorrect, do let me know:)

1
40
5/23/2017 12:25:51 PM

Accepted Answer

Why don't you simply ask, though?

Let me clarify what I think I understand about your request. I believe that you are considering a thread to be a series of messages between two persons that is linear in nature rather than a tree. You need to let in more guests than simply two, in my opinion. Insofar as anybody can write a message and everyone can read it and start contributing comments, that would be similar to Facebook. When you make a remark, you are automatically added to the discussion and begin receiving emails and status updates about thread activity. If that's what you want, then the schema I gave to Large Mike isn't precisely what you need.

Instead, think of this:

Schema

The concept behind this is that each time a user creates a brand-new thread or message, a new entry in the THREAD database is created. The message's content is then added to MESSAGE, which refers back to the thread that contains it, and the user is added as a THREAD PARTICIPANT. The message's author is shown by the FK from MESSAGE to USER.

Depending on your needs, a user who reads a message receives an item in the MESSAGE READ STATE database to indicate that they have marked the message as read, either explicitly or implicitly.

When a user replies to the thread's opening message, a second message is added to the thread with an FK back to it, and the user who replied is added to the THREAD PARTICIPANT table. And so it continues as one, two, or even more individuals continue to submit messages to the thread.

Simply pick the top message from MESSAGE ordered descending on creation date (or an identification key) where the message FK relates to the thread of interest to find the most current message in any thread.

When a message is in a thread in which the user is a THREAD PARTICIPANT, find the THREAD linked to the top 1 message that is arranged descendingly on the message's creation date.

I'm worried I'll never be able to express these ideas in LINQ without using LinqPad. I could elaborate on the response with table definitions and some SQL if you are having difficulties understanding what I'm getting at from the above. Just make a remark and ask.

EDIT: Clarification of Implementation and Requirements

To be clear, Shane wants more of the direct messaging functionality; initially, I was envisioning publicly posted messages with the option for commenting. In such situation, the THREAD PARTICIPANT table must initially include the original receiver.

Let's make a couple rows into tables to help with clarity. In honor of Canada Day, here is the scenario: User 1 DMs User 2 to inquire about meeting up for a drink. User 1 responds to User 2's enquiry concerning the meeting location. The tables would resemble the following: (probably oversimplified)

Sample Data Part 1Sample Data Part 2

EDIT #2: Use SQL to get a list of every message in a thread, along with their read status.

This SQL will get a list of messages in a particular thread along with a notation of whether a certain user has read each message or not using @OP's schema. The most recent messages are shown first.

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;

The read state is selected using a sub-select, which is the trick, if you can call it that. This is required because one of the requirements for obtaining the read state calls for a where clause that cannot be met by an outside join. In order to determine which (potentially missing) value you want from the MessageReadState child table, you utilize the subselect.

EDIT 3: SQL query to get all threads for a certain user that include the most recent post...

The query would be similar to the one above, but instead of filtering the messages by their FK to the thread of interest, you would filter the messages by a subquery that finds the latest message in each thread that the user of interest participated in. This would give you a list of all 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). It would seem as follows:

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;
77
5/23/2017 11:54:31 AM

Popular Answer

In Joel Brown's response, you may addLAST_MESSAGE_ID field into the THREAD database, it becomes quite easy to use SQL to fetch all threads with the most recent messages. This column must be updated when each message is sent.

obtaining every thread with the most recent message for a certain person

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow