User messaging system

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 threa开发者_StackOverflow中文版d_recipients
=======================
thread_id (PK)
user_id (PK)
read_status
sender_deleted
recipient_deleted

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.

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/

@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.