Results 1 to 3 of 3
  1. #1
    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?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    {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));

  3. #3
    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)
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •