hoopplaya4 Posted July 17, 2009 Share Posted July 17, 2009 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! Quote Link to comment Share on other sites More sharing options...
limitphp Posted July 17, 2009 Share Posted July 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
PugJr Posted July 17, 2009 Share Posted July 17, 2009 Does it really matter? Or do you plan on having the next facebook? Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted July 17, 2009 Author Share Posted July 17, 2009 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? Quote Link to comment Share on other sites More sharing options...
Amtran Posted July 17, 2009 Share Posted July 17, 2009 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! ) Quote Link to comment Share on other sites More sharing options...
Philip Posted July 17, 2009 Share Posted July 17, 2009 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) Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted July 17, 2009 Author Share Posted July 17, 2009 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! Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted July 17, 2009 Share Posted July 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
Philip Posted July 17, 2009 Share Posted July 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted July 17, 2009 Share Posted July 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
Philip Posted July 17, 2009 Share Posted July 17, 2009 You're right. Quote Link to comment Share on other sites More sharing options...
corbin Posted July 18, 2009 Share Posted July 18, 2009 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. Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted July 18, 2009 Share Posted July 18, 2009 Hell, even PHP Freaks has a multi MySQL server setup. Quote Link to comment Share on other sites More sharing options...
dadamssg Posted July 19, 2009 Share Posted July 19, 2009 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 Quote Link to comment Share on other sites More sharing options...
Philip Posted July 19, 2009 Share Posted July 19, 2009 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 Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted July 22, 2009 Author Share Posted July 22, 2009 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. Quote Link to comment Share on other sites More sharing options...
gevans Posted July 22, 2009 Share Posted July 22, 2009 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. Quote Link to comment Share on other sites More sharing options...
waynew Posted July 22, 2009 Share Posted July 22, 2009 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. Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted July 22, 2009 Share Posted July 22, 2009 It's not magic Then why does PHP have magic methods?! Quote Link to comment Share on other sites More sharing options...
Nix Posted July 22, 2009 Share Posted July 22, 2009 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 Quote Link to comment Share on other sites More sharing options...
hoopplaya4 Posted July 23, 2009 Author Share Posted July 23, 2009 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? Quote Link to comment Share on other sites More sharing options...
waynew Posted July 23, 2009 Share Posted July 23, 2009 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. Quote Link to comment Share on other sites More sharing options...
waynew Posted July 23, 2009 Share Posted July 23, 2009 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.