Hi guys,

I am developing a website where I have a requirement of "Private Messaging System". Registered user will have an ability to compose messages to any registered users. There is no limit to the number of messages one user can send to another. This looks like a case of "Many To Many" relationship.


My requirement is as follows :

When logged in user in my website clicks on 'Messages Sent', it should list all the messages which logged in user has sent to different members.

When logged in user clicks on 'Inbox', it should list all the messages which other members have sent to the currently logged in user.

It is more like an email system design (where we have concepts like Inbox, compose, sent items etc etc).

Currently the table design which I have is as follows :

Table : Messages

id int(10) NOT NULL auto_increment,
message_from int(10) default NULL,
message_to int(10) default NULL,
subject varchar(66) default NULL,
message varchar(250) default NULL,
message_status varchar(3) default NULL,
replied varchar(3) default NULL,
sent_date bigint(26) default NULL,
read_date timestamp NULL default NULL,
replied_date timestamp NULL default NULL,
PRIMARY KEY (id)

But this table is not able to cater my requirement.

Appreciate if anyone can shed some lights on the DB schema design to cater this requirement.

Thanks
Jameel

a message can be from only one user, and this is accomplished by the FK message_from

however, a message can typically be to multiple users, and thus you would not want the FK message_to in this same table

similarly, when a message is read by one of the users it was sent to, the read_date column that is updated must relate to the recipient, not just the message itself

so you need another table

make sense?

Oops.. I need to redesign a private message table that I've using. It works for a strictly one-sender to one-recipient scenario, but won't if it's one-to-many.

Thanks r937!