If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Private Message Data Model

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-24-09, 19:25
sqlguru sqlguru is offline
Registered User
 
Join Date: Jun 2009
Posts: 66
Private Message Data Model

Members can have multiple folders besides the standard "inbox" and "sent" folders. If Member 1 sends a message to Member 2, the message should be in Member 1's sent box and Member 2's inbox. Member 1 should be able to see if Member 2 read it etc. This is standard functionality.

Any ideas on how to model this?
Reply With Quote
  #2 (permalink)  
Old 06-25-09, 02:52
dportas dportas is offline
Registered User
 
Join Date: Dec 2007
Location: London, UK
Posts: 732
{MessageId, FolderId} KEY {MessageId, FolderId}

In SQL:

CREATE TABLE MessageFolder
(MessageId INTEGER NOT NULL REFERENCES Message (MessageId),
FolderId INTEGER NOT NULL REFERENCES Folder (FolderId),
PRIMARY KEY(MessageId, FolderId));
Reply With Quote
  #3 (permalink)  
Old 06-25-09, 07:10
sqlguru sqlguru is offline
Registered User
 
Join Date: Jun 2009
Posts: 66
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)
)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On