[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!!