5

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)?

1
  • This depends a lot on your database. Some databases have hierarchical data types, which are meant to be used for recursive relationships. You should look this up for whatever platform you're on.
    – Aaronaught
    Commented Nov 30, 2013 at 18:09

1 Answer 1

2

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

7
  • With what gnat said in his comment, I'd like to point out this is a solution for a standard relational database solution. Commented Nov 30, 2013 at 20:13
  • what's the tool you used to draw this UML diagram please ?
    – Turkish
    Commented Dec 1, 2013 at 8:19
  • 1
    Microsoft Visio 2013 Pro. I used the IDEF1X Database Notation template. Commented Dec 1, 2013 at 8:57
  • Thank you so very much. Just having some doubts. So the author here is the starting user? Would you please kindly show the me the table layout too, if possible? But again thank you! Commented Dec 1, 2013 at 10:21
  • This is a prototype of the table layout with the relationships defined using the lines connecting them. The numbers above/below the endpoints represent their multiplicity using IDEF1X. Using my example ThreadA's starting user will also be the author of Message 1. ThreadB's starting user is the person who authored #3. However Only #4 & #6 are stored in the threads Messages, #3 remains a part of ThreadA but is referenced directly in ThreadB. This allows for recursion to be used, and still provides the data to the thread beneath it. Commented Dec 1, 2013 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.