User Messaging

[eluser]austintbiggs[/eluser]
I'm currently attempting to build a messaging system for use between users. And I was wondering are there any examples out there? Has anyone already started or developed one? I've done quite a bit of googling, but the results I got were all regarding error messages.

Also do I use emails or another method? I'm still trying to develop the concept of how it would work so I can start programming.

Any and all help is appreciated!

Best Regards,
Austin.

[eluser]theprodigy[/eluser]

I’m currently attempting to build a messaging system for use between users.

By 'messaging system', are you referring to a chat messaging or in-site email messaging?

I can help you somewhat with the in-site email messaging, as I have built several (not too extensive, but they serve their purpose).
If you are talking about a chat messaging system, I'm NOT your guy

[eluser]austintbiggs[/eluser]
Actually yes, I am talking about in-site email messaging NOT a chat system [eg. facebook chat].

[eluser]theprodigy[/eluser]
Your basic db structure will want to store the basics of the message itself in one table, and the recipient in another.
Messages:

Code:

id int(11) unsigned NOT NULL AUTO_INCREMENT //Primary key
from_id int(11) unsigned NOT NULL // user id of the person that sent it
subject varchar(255) (NOT) NULL //your choice if to allow null, I would suggest not
body text NULL
sent datetime NOT NULL
deleted int(1) NOT NULL DEFAULT 0 // whether or not the sender deleted it from Sent box

You may end up with more than that, but that's the basics.

Messages_Sent:

Code:

id int(11) unsigned NOT NULL AUTO_INCREMENT
message_id unsigned NOT NULL //links to id of first table
to_id int(11) unsigned NOT NULL // user id of the person it was sent to
has_read int(1) NOT NULL DEFAULT 0 // whether the recipient has read the mail
deleted int(1) NOT NULL DEFAULT 0 // whether or not the recipient deleted the message
deleted_from_trash int(1) NOT NULL DEFAULT 0 // whether or not the recipient deleted the message from their trash folder

That's the basics for the database setup. I may be forgetting 1 or 2 things, but this is just a basic setup.
It allows:
1. 1 sender, multiple recipients.
2. Sender can delete message from their sent folder without it affecting the recipient's mail.
3. Recipient can delete message from inbox, and it still be retrievable from 'trash' folder.

What it does NOT allow (remember, it's only a basic setup):
1. Separate mail into user defined folder structure.
2. Users creating (and sending to) mail lists (although this would not be hard to implement and integrate with this structure).
3. Probably a few other features I'm overlooking right now.

[eluser]austintbiggs[/eluser]
I like the approach you've taken with the mysql, but how do I even begin to write the code to utilize this structure?

[eluser]jmadsen[/eluser]
The thing I've found about message systems is, they can be much more complicated than they seem, and everyone has their own idea of how they should work.

So my strongest advice is to really sit down with whoever is asking for it and get them to show you their gmail or Outlook or Facebook or whatever their idea is of a "message system", so you understand what they have in their head. Of course, if it is for you, it's easy :-)

@theprodigy's design will work pretty well for a simple back-and-forth system. A similar general design I like to use is:

"messages" table - has body, create_date/time, maybe the subject, and a message_thread_id fk. Sent_id is not necessary, but you'll most likely want to label that so add it here

"thread" table: - to tie messages together as a thread, without having to rely on parent_id's. just join this one-to-many with the messages and order by date. you could move the subject line here if you are using that style and save a lot of db space

"recipients" - anyone associated with the thread. user_id, message_thread_id, status (so each person can have a different read, unread, deleted, etc on the same message)

--

The message_thread table let's you create groups of people easily, and has a "weird" feature that was intentional the first time I used it: once you add a person to a message thread, they can go back and read everything on the thread from before they joined, and get themselves up to date. (you can use datestamps to avoid that as an option)

It has the downside of making your queries a bit uglier, though

[eluser]theprodigy[/eluser]
When a user sends a message:
1. input the message details into the Messages table (using logged in user_id for the 'from_id' field, and current date and time for 'sent' field)
2. get the input id from that record
3. for each recipient (if you allow more than 1), input the message_id (the one you got from step 2) and the user_id of the recipient (you may have to select the user_id from the database users table)

To list all messages in a user's inbox:
1. Select the subject, from_id, sent from the Messages table, join messages_sent table on message_id, where messages_sent.to_id = logged in user_id and messages_sent.deleted = 0
1a. Optionally select messages_sent.has_read if you want to bold the unread ones

To list all messages in user's trash:
1. same as above only messages_sent.deleted = 1

When a user clicks on a message to read it:
1. update messages_sent.has_read to 1, where message_id = the one they clicked on, and to_id = their id
2. select body, subject, from_id, sent from messages where id=message_id (where message_id = the one they clicked on).

When a recipient deletes a message from inbox:
1. update messages_sent.deleted to 1 where messages_sent.message_id = message_id and to_id = their id

when a recipient deletes a message from trash:
1. update messages_sent.deleted_from_trash where messages_sent.message_id = message_id and to_id = their id

When a sender deletes a message (from their sent box):
1. update messages.deleted to 1 where id = message_id and from_id = their id

I think that just about covers it. If I missed anything, I'm sure you should be able to figure it out by browsing through the instructions above and seeing how the tables are connected. If not, just ask (but please give it try first. It's not too difficult ;-) ).