User messaging system (PHP / MySQL)

Question

I'm currently developing a user messaging system (similar to Facebook) for my existing site. I already have a users table and just need to integrate a messaging system now.

I started a thread on this yesterday (User messaging system) and made some progress but the structure has changed quite a bit so I'm starting a new question.

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

• Messages are exchanged between users, and a sender can send a message to multiple recipients.

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

• Once a thread is created it will appear in the senders 'sent messages' folder - it will not appear in their inbox unless the recipient sends a reply.

• 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, (or 'sent messages' folder). The other user will still have access to the thread if he/she hasn't deleted it from his/her inbox (or 'sent messages' folder).

• When a message or reply is sent, the 'read status' is set to 'unread' - but that should not affect the sender. i.e. only the recipient should see it as 'unread'.

Here is what I have at the moment:

Table messages
==============
id
thread_id
from_user_id
subject
body
sent_date

Table message_threads
=====================
id
message_id
to_user_id
from_user_id
read_status

Something else to think about is when sending the same message to multiple recipients, should we store a seperate message for each recipient, or just one message?

Any guidance would be highly appreciated.

Answer 1

$query = 'CREATE TABLE IF NOT EXISTS ' . $db->prefix . 'pm (
    `post_id` => $post->ID,
    `role_id` => $role_id,
    `sender` varchar(60) NOT NULL,
    `recipient` varchar(60) NOT NULL,
    `date` datetime NOT NULL,
    `read` tinyint(1) NOT NULL,
    `deleted` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`)
) COLLATE utf8_general_ci;';

I believe having something structured in this nature would work.

Answer 2

I agree with @Eddsstudio: when the user sends a message put a copy of it in the "sent folder" and copies in the recipients inbox's. For any replies: create a new message in the primary senders inbox, not linked to the previous messages (i.e. no link to the message in the sent folder).

You can make the messages look more like a thread conversation in the way they're displayed on the screen to the user but a multiple message back-end will create far fewer headaches.