Structure of PM systems

[vineld]

How do you usually design PM systems? I have so far been quite straight-forward, using only one table and setting flags for read, deleted, archived etc. If there are some illegal or otherwise doubtful activities taking place I think it's good to have all messages saved whether they have been deleted or not.

 

There is a big argument for changing the structure a bit though since this structure will build up in size very quickly and not before long I am storing a lot more deleted messages than not which makes the system highly inefficient.

 

Another reason for separating the deleted messages (if you do not delete them for real) from the rest would be that it's much easier to simply export and empty that table once it grows to a certain size.

 

So I am thinking about redesigning my system into three tables actually - one for the inbox, one for deleted messages and one for saved sent copies which will probably not be accessed even near as often and do not need all fields of the inbox table. Does this sound like a good idea or does it have major flaws?

 

This will of course not work well if you want the user to be able to view all sent messages although I rarely ever see a point of doing this. Then you might as well adjust the system for that specific project.

[Philip]

http://www.phpfreaks.com/forums/index.php/topic,260841.0.html

[vineld]

Thanks! Failed to find that thread although there was a lot of air in it and it doesn't quite answer all of my questions. Your multi-recipient argument was a good one though. Had not thought of that. However, I usually don't allow that since it gives spammers a neat tool (that can be solved in various ways though) and there are very few situations where you would want to send a PM to several I'd say. It doesn't hurt to have a flexible system though.

[Philip]

The main thing I forgot on mine was deletion - but keeping the message itself & parent ID. Which would be something like....

 

This one is the one with the sender info & the message itself:

CREATE TABLE IF NOT EXISTS `pm_message` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_parent` int(10) unsigned default NULL,
  `id_sender` mediumint( unsigned NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `date_sent` timestamp NULL default NULL,
  `delete_sender` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
)

 

This is the related table, an entry for each message for each recipient:

CREATE TABLE IF NOT EXISTS `pm_info` (
  `id_message` int(10) unsigned NOT NULL,
  `id_receiver` mediumint( unsigned NOT NULL,
  `read_receiver` tinyint(1) NOT NULL default '0',
  `date_read` timestamp NULL default NULL,
  `delete_receiver` tinyint(1) NOT NULL default '0',
  KEY `message_id` (`id_message`)

[TheFilmGod]

No. You should keep all messages saved in your database. Sounds inefficient, but if you properly configure/build mysql and index your records, there should be no problem in querying 2 million + messages.

 

You could however, use summary tables for speed. Rember, when it comes to performance -> normalization techniques don't apply. Better performance != less diskspace. It's the other way around -> more diskspace you use = better performance. To improve performance, you'll might need to copy the same message 2 or even 3 times. Copy these messages ins ummary tables which could speed up lookups.  :-\