How would you create a threaded messaging system?

Question

I'm wondering about the table structure for a threaded messaging system.

I'm thinking there will be 3 tables:

- message_thread
    - id
    - subject
- message_user
    - thread_id
    - user_id
    - thread_status (unread, read, trash, deleted)
- message
    - date (date sent)
    - sent_user_id
    - message

Each message_thread would have 1 record in message_thread. Each user would have a record in message_user relating them to that thread and also their read/deleted status for that thread. (message_user might also contain some datetime fields as to when the different statuses occured.) The message table would contain the messages, the user that sent them and the time.

The system in the end would work similar to Facebook or looks like Gmail's (although not based on email).

Required:

• more than 2 users involved in the thread
• each user must have their own read/delete status for the thread

Is this the way you'd set it up?

Answer 1

Try one table:

messages
  id (INT)
  user_id (INT, ref to users table)
  subject (CHAR)
  parent_id (INT, ref to messages table, NULL if head of thread)
  content (TEXT),
  (other per-message attributes)

Allows threading, as well as for each message to have it's own subject.

To add read status, add a many-to-many table linking users to messages, where an entry in the table means the user's read that message.

Answer 2

I'd add a status logging table with user_id, thread_id, status_id and the date the status changed.

I would also combine the message and message_thread tables as it appears those will be a 1 to 1 and could be combined.