Best Way to Database for Messaging

[hoopplaya4]

Hi All,

 

I'm currently working on building a messaging system, similar to a typical email system or Facebook's messaging system, complete with an Inbox, Sent Items, and basic features such as "Unread" and "Read."

 

Do you have any tips/advice on how to properly structure a MySQL database table for this type of endeavor?  For example, what types of columns should I be sure to include, etc.

 

If you need more information, let me know.

 

Thanks in advance for your help!

[limitphp]

I'm curious myself.

Facebook seems to be able to do massive amounts of database stuff...all without any major slowdown.

 

it seems if i did that much database triggering, it would slow my site down to a crawl.

[PugJr]

Does it really matter? Or do you plan on having the next facebook?

[hoopplaya4]

Yeah, actually it does.  I'm not trying to copy facebook, I'm just trying to create something for myself.  Thus, I want to be the best that I can be, I want to know the best way to structure my db, so why not learn from the best? 

[Amtran]

Yeah, actually it does.  I'm not trying to copy facebook, I'm just trying to create something for myself.  Thus, I want to be the best that I can be, I want to know the best way to structure my db, so why not learn from the best? 

 

Woo hoo! Hooray for great comebacks to snide remarks! (I think you've hit the nail on the head there - everyone should have the same philosophy as you do! )

[Philip]

I was actually just reading up on facebook's engineering page today.

 

If you have an account go to their page at:

http://www.facebook.com/FacebookEngineering

and then click on the notes tab, and they have a lot of cool info. A lot of their design wouldn't be for you until your site got large enough (I don't think you're going to have 1/4 a billion people anytime soon)

[hoopplaya4]

Thanks for the replies everyone. 

 

This is what I have as a basic idea thus far:

 

private_messages tbl:
id
date_sent
title
content
status ENUM ('unread', 'read') DEFAULT 'unread'

private_message_relation tbl:
id
message_id
sender_id
receiver_id

 

Anything that you can think of that I might be missing and/or should remove?

 

Thanks!

[Daniel0]

Make the status a tinyint instead and call it something like is_read. Integer comparisons are faster than string comparisons.

 

Your relations table is redundant. Just put the FKs directly in the private_messages table.

[Philip]

Honestly, this is how I'd do it, but I'm no database engineer by any means.

 

CREATE TABLE `pm` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `date_sent` timestamp NOT NULL default '0000-00-00 00:00:00',
  `sender_id` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `pm_info` (
  `message_id` int(10) unsigned NOT NULL,
  `receiver_id` mediumint( unsigned NOT NULL,
  `read` tinyint(1) NOT NULL default '0',
  `date_read` timestamp NOT NULL default '0000-00-00 00:00:00',
  KEY `message_id` (`message_id`)
)

 

This way, you could send a PM to multiple people and see when they first read it.

[Daniel0]

Wouldn't it make sense to then make date_read a NULL field. Unless it has been read there is no valid value for the read_date.

[Philip]

You're right.

[corbin]

By the way, this is essentially pointless to this thread since it's kind of off topic now, but if I remember right (about to go read that engineering page), facebook has a crap load of MySQL mirrors, and a bunch of memcached servers.  In fact, perhaps it's another site I'm thinking of, but iirc, facebook relies heavily on memcached.

[Daniel0]

Hell, even PHP Freaks has a multi MySQL server setup.

[dadamssg]

I designed a super simple message system into my database. i have the fields

messageid int(11);
created DATETIME;
sentby varchar(20);
sentto varchar(20);
subject varchar(50);
content text;
status tinyint(1); ///status would be if it were read or not

 

Mine doesn't have a way to look at your sent messages though. To do that, i would create another identical table and when a message is sent have it inserted into both tables. That way you can have a Sent table and a Received table. With the two tables if the person who received the message decided to delete it, it would be deleted from the Received table but still in the Sent table for the Sender to look at and keep track of what they sent.

 

If you decided on having one table for messages youd have to have like a Received_delete field and a Sent_delete field. Then when one of them deletes the message it puts a '1' or something in that field and then you could run a cron to pull up and delete all tables where both of those fields have 1's...meaning that both the sender and receiver have deleted the message.

 

Hope some of that makes since haha

[Philip]

In fact, perhaps it's another site I'm thinking of, but iirc, facebook relies heavily on memcached.

They are the largest, a quote from a post in Dec '08 (and I know it has grown rapidly since):

Facebook, we're likely the world's largest user of memcached. We use memcached to alleviate database load. memcached is already fast, but we need it to be faster and more efficient than most installations. We use more than 800 servers supplying over 28 terabytes of memory to our users

[hoopplaya4]

Honestly, this is how I'd do it, but I'm no database engineer by any means.

 

CREATE TABLE `pm` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `date_sent` timestamp NOT NULL default '0000-00-00 00:00:00',
  `sender_id` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `pm_info` (
  `message_id` int(10) unsigned NOT NULL,
  `receiver_id` mediumint( unsigned NOT NULL,
  `read` tinyint(1) NOT NULL default '0',
  `date_read` timestamp NOT NULL default '0000-00-00 00:00:00',
  KEY `message_id` (`message_id`)
)

 

This way, you could send a PM to multiple people and see when they first read it.

 

Back on topic, sort of, I do have one question when working with the example provided above.  With the pm_info table, how would I insert the "message_id" in my MySQL statement?

 

Currently, I'm using:

 

<?php
mysql_query("INSERT INTO `pm` (title, content, sender_id) VALUES ('$subject', '$message', '$sender')" );

 

I notice that in 'pm' the id is auto-increment.  Should I set the 'pm_info' to auto increment as well?

 

Thanks.

[gevans]

I designed a super simple message system into my database. i have the fields

messageid int(11);
created DATETIME;
sentby varchar(20);
sentto varchar(20);
subject varchar(50);
content text;
status tinyint(1); ///status would be if it were read or not

 

Mine doesn't have a way to look at your sent messages though. To do that, i would create another identical table and when a message is sent have it inserted into both tables.

 

It does have a way to look at the sent messages, look up the sentby id (or varchar as you've used). There aren't many occasions when data is completely deleted from my databases anymore, most tables have a field called active which is either set to 1 or 0. have an 'active' field for the sending and receiving user, then they can both use the data from one table.

[waynewex]

Also; remember that mail messages are usually threaded. So starting a mail message would be much like starting a forum topic, except for the fact that you have to change the "thread" from read to unread, based on who replies. I've done this before. It's not magic, once you get your head around it.

[Daniel0]

It's not magic

 

Then why does PHP have magic methods?! 

[Nix]

Don't know if this is going to help, but I found this on the net:

http://www.pixel2life.com/publish/tutorials/608/simple_private_messaging_system/

 

Regards!

Nix

[hoopplaya4]

Also; remember that mail messages are usually threaded. So starting a mail message would be much like starting a forum topic, except for the fact that you have to change the "thread" from read to unread, based on who replies. I've done this before. It's not magic, once you get your head around it.

 

That's a good point, I hadn't thought about having it "threaded" when one user replies. 

 

How would I go about doing that?  Because the 'id' field is 'incremental', how would I give it the same id?  Any ideas on that?

[waynewex]

It's not magic

 

Then why does PHP have magic methods?! 

 

PHP reserves all function names starting with __ as magical.

 

I smirked when I read that.

[waynewex]

Also; remember that mail messages are usually threaded. So starting a mail message would be much like starting a forum topic, except for the fact that you have to change the "thread" from read to unread, based on who replies. I've done this before. It's not magic, once you get your head around it.

 

That's a good point, I hadn't thought about having it "threaded" when one user replies. 

 

How would I go about doing that?  Because the 'id' field is 'incremental', how would I give it the same id?  Any ideas on that?

 

How would you implement a forum topic?