Design question: How would you design a messaging/inbox system?

Question

Many websites have the concept of sending messages from user to user. When you send a message to another user, the message would show up in their inbox. You could respond to the message, and it would show up as a new entry in that message thread.

You should be able to see if you've read a given message already, and messages that have got a new response should be able to be at the top.

How would you design the classes (or tables or whatever) to support such a system?

Answer 1

user
 id
 name

messages
 id
 to_user_id
 from_user_id
 title
 date

message_post
 id
 message_id
 user_id
 message
 date

classes would reflect this sort of schema

Answer 2

It's a rather simple table structure. A to/from, subject and then the message. Now the important thing is the date fields. The DateSent tells when it was sent, the DateRead tells that the message was read, and the DateDeletedTo tells that the TO user deleted it, and the DateDeletedFROM tells that the FROM user deleted it (these are logical deletes for this example).

tblMessage
ID  BIGINT 
ToUserID GUID/BIGINT
FromUserID GUID/BIGINT
Subject NVARCHAR(150)
Message NVARCHAR(Max)
DateDeletedFrom DATETIME
DateDeletedTo DATETIME
DateSent DATETIME
DateRead DATETIME

Answer 3

I'm actually doing this as part of some internal development at work. Make a table called [Messages] and give it the following columns.

• mID (message ID)
• from_user
• to_user
• message
• time
• tID (thread ID)
• read (a boolean)

Something like that should work for the table design. The classes depend on what system you're designing it on.

Answer 4

Table Message:
id INTEGER
recipient_id INTEGER -- FK to users table
sender_id INTEGER -- ditto
subject VARCHAR
body TEXT

Table Thread
parent_id -- FK to message table
child_id -- FK to message table

Then, you could just go through the Thread table to get a thread of messages.

Answer 5

You might want to extend Owen's schema to support bulk messages where the message is stored only once. Also modified so there's only one sender, and many receivers (there's never more than one sender in this scheme)

user
  id
  name

message
  id
  recipient_id
  content_id 
  date_time_sent
  date_time_read
  response_to_message_id (refers to the email this one is in response to - threading)
  expires
  importance
  flags (read, read reply, etc)

content
  id
  message_id
  sender_id 
  title
  message

There are many, many other features that could be added, of course, but most people think of the above features when they think "email".