Database design for threading messages I am not sure whether this question is suitable for this forum, but since it says Database Administrator I hope I am right. So my question is, whats 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.

If anyone could guide me through as to how all this threading message works (like Gmail or Facebook), I will be really grateful.

Edit:

With such a helpful guide and the tables below, I came up with my models. But having just a slight confusion, as to how to start a new thread.

Tables:




My models:

class Thread(models.Model):
    pass

class ThreadParticipant(models.Model):
    thread = models.ForeignKey(Thread)
    user = models.ForeignKey(User)

class Message(models.Model):
    thread = models.ForeignKey(Thread)
    sent_date = models.DateTimeField(default=datetime.now)
    body = models.TextField()
    user = models.ForeignKey(User)

class MessageReadState(models.Model):
    message = models.ForeignKey(Message)
    user = models.ForeignKey(User)
    read_date = models.DateTimeField()


As the Thread class has nothing in it, how do I create a new thread? Do I place an integer in Thread and increment it? Or do I use the Primary Key itself as the attribute to create a new thred? Please kindly explain it to me. Thank you agian!
A very similar question was asked on StackOverflow.  Have a look at my answer there.  I recommend a data model and give specific advice about the SQL to list of all messages in a thread, with read state and get all threads with latest message in each thread for a given user.