I'm not sure I understand. Will there be a single message for both members (sender and recipient)??
Here's the model I came up with:
Private Messages acts as a "header" table that points to the actual message in the receipt table. So when Member #1 sends a message to Member #2, there will be two "header" records created BUT they will point to the same receipt. This way, either of the members can delete the message or move into different folders. If BOTH members delete the message, then the actual receipt is also deleted.
Do you see any flaws or suggestions?
CREATE TABLE Folder
(
folder_id INT NOT NULL PRIMARY KEY
member_id INT NOT NULL REFERENCES Members (member_id)
)
CREATE TABLE Private_Messages
(
pm_id INT NOT NULL PRIMARY KEY,
member_id INT NOT NULL REFERENCES Members (member_id),
folder_id INT REFERENCES Folders (folder_id)
)
CREATE TABLE Private_Message_Receipts
(
pm_receipt_id NOT NULL PRIMARY KEY,
pm_id INT NOT NULL REFERENCES Private_Messages (pm_id),
pm_recipient_id INT NOT NULL REFERENCES Members (member_id),
pm_sender_id INT NOT NULL REFERENCES Members (member_id)
)