User messaging system

Question

I'm looking at creating a user messaging system (similar to Facebook).

What I want to know is essentially what should the table structure look like? The requirements I have are as follows:

• Messages are exchanged between users - a sender can select multiple users to send a message to.

• Messages are displayed in a thread-style layout, as a 1-1 conversation. i.e. each recipient's reply will appear in it's own thread.

• Individual messages cannot be deleted, however a thread can be deleted. Deleting a thread doesn't delete any messages, it just removes that thread from the user's inbox. However the other user can still access the thread if he/she hasn't deleted it from his/her inbox.

Here is what I have at the moment:

Table messages
==============
id (PK)
user_id (from)
subject
body
sent_at


Table message_recipients
========================
message_id (PK)
user_id (PK)
read_status

EDIT: What about the following:

Table messages
==============
id (PK)
thread_id   
user_id (from)
body
sent_at


Table threads
=============
id (PK)
user_id (from)
subject


Table thread_recipients
=======================
thread_id (PK)
user_id (PK)
read_status
sender_deleted
recipient_deleted

Answer 1

@Angelo R. Would like to know why we require the Thread Table? This isn't any discussion board. If you want to retrieve the whole thread/conversation of messages you can simply query by source AND recipient ID. Plus, if you use Thread, nothing bad. But what in this situation If new message - new thread_id (automatically created), If replied to existing conversation, you have the thread_id, but what if you are creating a new message (say like facebook popup), you don't know whether there was any previous conversation or if thread_id is available or not unless you execute a special query to it. This was my thought. Tell me if I might be wrong somewhere.

Answer 2

I would suggest having the following at least:

Users, Threads, Messages

• All messages would have a thread
  • foreign key: thread_id
• All threads would have at least one message and at least one recipient (as well as sender)
  • foreign key: to_user_id, from_user_id, message_id

From there you could simply assign a couple flags to your thread (to_user_deleted, from_user_deleted) that would be updated accordingly.

There a lot more things to consider of course, such what kinds of things you want to account for. For example:

• Do you want to display the current message as opposed to the starting message?
• Do you want to allow users to mark individual messages as read, or just threads?

You need to take all of these into account while designing your database.

Answer 3

Why not use something like Jabber (example: OpenFire or Web Client)

If you need PHP to interact with it you could use something like:

http://code.google.com/p/xmpphp/ or http://code.google.com/p/jaxl/