Private messaging system

[eluser]huzzi[/eluser]
Hello experts!!

I need to develop a private messaging system similar to (or clone) facebook's messaging system, just wondering if anyone has created something similar? I've seen other threads on this forum about messaging system but it's not what I want.

I would also appriciate if anyone could suggest how i should design the database for this messaging system.

Many thanks in advance.

[eluser]Unknown[/eluser]
CREATE TABLE `sb_mail` (
`id` int(9) NOT NULL auto_increment,
`user_id` varchar(11) NOT NULL default '0',
`authoruser_id` varchar(11) NOT NULL default '0',
`convo_id` int(9) NOT NULL default '0',
`maildate` int(14) NOT NULL default '0',
`subject` varchar(50) NOT NULL default '',
`body` text,
`status` int(1) NOT NULL default '0',
`outbox` int(1) NOT NULL default '0',
PRIMARY KEY (`mail_id`),
KEY `INDEX` (`mail_user_id`)
)
in this status can have 0,1,2 indicating 1: unread mail in mailbox,0: read mail,2: mail deleted from inbox
outboz would be having 2 value 0,1
1 indicating mail in outbox and 0 indicating mail in outbox deleted
whenever user would be deleting from outbox you would check if message has status 2 than you will delete message else you would change outbox value to 0 for that message , in same manner value of status would be maintained

[eluser]Daniel H[/eluser]
I'm designing something similar but couldn't find any example models. I'm going for a two table approach: message, and message_recipient, which should handle multiple recipients...

Code:

CREATE TABLE `message`
(
`id` BIGINT(20) unsigned  AUTO_INCREMENT  UNIQUE ,
`subject` VARCHAR(255),
`content` TEXT NOT NULL,
`date_sent` DATETIME NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`reply_message_id` BIGINT(20)
);

CREATE TABLE `message_recipient`
(
`id` BIGINT(20),
`user_id` BIGINT(20),
`message_id` BIGINT(20),
`date_read` DATETIME,
`read` TINYINT(1) DEFAULT 0,
`replied` TINYINT(1) DEFAULT 0,
`date_replied` DATETIME,
`deleted` TINYINT(1) DEFAULT 0
);

Only just throwing this together - not sure if it is practical!!

[eluser]huzzi[/eluser]
Thanks cooltarun and Daniel for your suggestions.