4

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:

enter image description here enter image description here

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!

1 Answer 1

3

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.

2
  • I am very new to all these database, but thank you so much! I think that is awesome! Just having some confusion, could you please help me understand it better. I have edited the questions in the above. Please have a look at it. Thank you so much again! Nov 30, 2013 at 16:53
  • @Benjamin - Depending on what your database engine is, you probably have some kind of automatically incrementing integer type column which you can use for surrogate keys like THREAD.ID. Look into your database engine's documentation for terms like IDENTITY or AutoIncrement. Thread could have other attributes too, depending on what you want. The sample data I used was deliberately minimal for clarity. The point of the THREAD table is to give a header that all of the messages in the thread can share. A thread class may not have any members other than child collections and an ID.
    – Joel Brown
    Nov 30, 2013 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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