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.

multiple messages will be better, easier to manage them.

Edmhs
– Edmhs

2011-01-20 13:48:09 +00:00
Commented Jan 20, 2011 at 13:48 — Edmhs
– Edmhs, Commented Jan 20, 2011 at 13:48

Phil · Accepted Answer · 2011-01-20 15:13:12Z

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.

answered Jan 20, 2011 at 15:13

Phil

1,1201 gold badge9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick Soares · Accepted Answer · 2014-01-30 23:22:24Z

0

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

answered Jan 30, 2014 at 23:22

Nick Soares

32 bronze badges

Collectives™ on Stack Overflow

User messaging system (PHP / MySQL)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related