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! Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/ 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-876875 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? Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-876890 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? Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-876892 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! ) Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-876895 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) Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-876896 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! Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877086 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877229 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877239 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877245 Share on other sites More sharing options...
Philip Posted July 17, 2009 Share Posted July 17, 2009 You're right. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877253 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877744 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877757 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 Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877788 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 Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-877802 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-880467 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-880565 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-880669 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?! Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-880691 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 Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-880783 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? Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-881220 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. Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-881246 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? Link to comment https://forums.phpfreaks.com/topic/166278-best-way-to-database-for-messaging/#findComment-881537 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.