Database Design – Relationship for Threading Messages

algorithmsdatabase-designdesign-patternsdjangomultithreading

What's the database design or business logic for creating an app for messaging between users?

I am having difficulties with choosing how to approach the relationship between each Conversation of User Thread. I show this figure, but it was really hard to grasp the concept

enter image description here

How all this threading message works (like Gmail or Facebook)?

Best Answer

I would compose the thread of messages and then make the subsequent threads subordinate to the messages they are replies to.

For instance:

  • Thread A is started.
  • Thread A receives three messages.
  • Fourth Message is added and replies to message 3, creating a new thread.
  • Thread B's parent is set to message 3.
  • Fourth Message becomes part of ThreadB.
  • Fifth Message is a reply to the original message and is added to ThreadA.
  • Sixth Message is a reply to message three and is added to ThreadB.

Then when you're rendering the thread, after displaying a message query for any threads who's parent message match the message you just displayed. If there are any for the message simply render that thread before going to the next message.

I've included a basic (probably technically flawed) diagram illustrating the relationships in the database.

Given the above sample it would look like this when displayed:

  • (ThreadA)
    1. Message 1
    2. Message 2
    3. Message 3
      • (ThreadB)
        1. Message 4
        2. Message 6
    4. Message 5

Update: Fixed some relationship and multiplicity issues. I updated it to include textual representations of the relationships as well. Any input on validity would be helpful, been a while since I've done one. I've been using Model Based ORM nearly exclusively lately. Also keep in mind while it use the basic format of IDEF1X. It contains specifics, and IDEF1X is a semantics based modelling system. enter image description here

Related Topic