Jump to content

Best Way to Database for Messaging


hoopplaya4

Recommended Posts

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!

Link to comment
Share on other sites

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! :D)

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.