I'm currently developing a PM system for a website, in which users must be able to send PM's to multiple recipients. Naturally, this means that if a message has been sent to user A, B and C, user C could delete the message while user A and B won't. The question is what would be the best database table structure for such a system, of course avoiding multiple copies of one message. Currently I've thought of this table structure:
msgid (int),
parentid (int),
timestamp (timestamp),
senderid (int),
recipients (varchar),
subject (varchar)
text (text),
deletedby (varchar),
readby (varchar)
This would be the only table. Threads are created based on the parentid's (if there is no parentid the message is the first in a thread), and ordered by timestamp. Recipients are stored comma-separated in one column and checked by using WHERE userid IN (msg.recipients). The deletedby column contains all id's (comma-separated) of users which have deleted the message, just like the readby column.
However I am not sure if this is an ideal table structure. Before I start coding I would like to hear your thoughts for improvements.