SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Normalization and PM's

    I am working on my Private Messaging module, and have re-worked some things after it was pointed out that maybe I had some normalization issues.

    In my new design, I would like the following...

    a.) 3rd Normal Form (3NF).
    b.) Ability to message multiple Users.
    c.) Ability to store things efficiently
    d.) Allow the Sender and Recipients of the PM to "flag", "delete" and "purge" a PM independently of what others do.


    Here are the Relationships...

    Code:
    MEMBER (Sender) --||------|<-- PM_DISTRIBUTION -->|-------||-- PRIVATE_MSG
    
    MEMBER (Recipient) --||------|<-- PM_DISTRIBUTION -->|-------||-- PRIVATE_MSG

    Here is my Table Layout...

    Code:
    MEMBER table
    - id
    - email
    - username
    - first_name
    
    and so on...

    Code:
    PM_DISTRIBUTION table
    - id (PK)
    
    - private_msg_id (UK1)
    * sender_id (UK2)
    - recipient_id (UK3)
    
    * sender_flag
    * sender_deleted_on
    * sender_purged_on
    
    - recipient_flag
    - recipient_read_on
    - recipient_deleted_on
    - recipient_purged_on
    
    - created_on
    - updated_on
    
    (Fields with an asterisk ( )* denote those that used to be in the PRIVATE_MSG table, but were moved here.)

    Code:
    PRIVATE_MSG table  
    - id
    - subject
    - body
    - created_on

    ****************************
    Side Note:
    My PM module works like most Email/PM systems do....

    The Sender sends a PM and it goes into his/her "Sent" folder. If the Sender deletes it, the PM gets a "deleted_on" time-stamp and visually moves to the Trash folder. If the Sender empties his/her Trash, the PM gets a "purged_on" time-stamp and visually disappears.

    Similar logic for Recipients.

    And when *both* Sender and Recipient have "purged" a PM, then the record in PRIVATE_MSG (Parent) plus the record in PM_DISTRIBUTION (Child) are removed from the database.
    ****************************


    Questions:
    1.) Are the Tables above in 3NF?

    2.) Will this new design meet the above stated goals?

    3.) Any design concerns with the Table Layout above?


    I am 95% certain that this new design meets all of my goals and is well designed, however, some confirmation for you Database Gurus would make me feel better!!

    Sincerely,


    Debbie

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,082
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    I would do:

    private_msg
    Code:
    id
    sender_id
    subject
    body
    created_date
    sent_date (if you want draft status?)
    private_msg_recipients
    Code:
    id
    private_msg_id
    member_id
    flagged
    read
    deleted
    purged
    Why do you need created and updated dates on the recipient table? If you are storing an update to the message itself, that belongs on private_msg, same goes for sender_id. You can look up msg create and send date based on the parent.

    Am I missing anything?

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I would do:

    private_msg
    Code:
    id
    sender_id
    subject
    body
    created_date
    sent_date (if you want draft status?)
    private_msg_recipients
    Code:
    id
    private_msg_id
    member_id
    flagged
    read
    deleted
    purged

    Here is what I ended up going with...

    member
    Code:
    id (PK)
    email (UK)
    username (UK)
    first_name
    and so on...

    private_msg_recipient
    Code:
    id (PK)
    private_msg_id (UK1)(FK)
    recipient_id (UK2)(FK)
    recipient_flag
    recipient_read_on
    recipient_deleted_on
    recipient_purged_on
    created_on
    updated_on

    private_msg
    Code:
    id (PK)
    sender_id (FK)
    subject
    body
    sender_flag
    sender_deleted_on
    sender_purged_on
    created_on
    updated_on

    The big thing I was looking for some validation on is this...

    In my original design, I was coerced by others to put these fields in the private_msg_recipient table...
    Code:
    * sender_flag
    * sender_deleted_on
    * sender_purged_on
    That is wrong, because if the Sender sent a PM to 3 people, and the Sender flagged the Sent PM in his/her Message Center, then my code would have to check the "sender_flag" in 3 records!!

    That is denormalized!!


    I would argue that the "sender_id", "sender_flag", "sender_deleted_on", and "sender_purged_on" are all *intrinsically* part of the Private Message!!



    Quote Originally Posted by K. Wolfe View Post
    Why do you need created and updated dates on the recipient table?
    My style is to always have those two fields on any table, because every table has records that are created and usually updated. (It's a back-end auditing thing.)



    Quote Originally Posted by K. Wolfe View Post
    If you are storing an update to the message itself, that belongs on private_msg, same goes for sender_id. You can look up msg create and send date based on the parent.
    Most enterprise systems do what I am doing for auditing purposes.

    Admittedly, it doesn't make as much sense here, because you should not be updating the Recipient table, but the benefits outweigh the storage of one extra field.


    Quote Originally Posted by K. Wolfe View Post
    Am I missing anything?
    I need to store "deleted_on" and "purged_on" for both the Sender and all Recipients. See my design above.

    Sincerely,


    Debbie

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,082
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    I just want to make sure I've got everything straight. What is sender_flag? and recipient_flag? Just like a star?

    If this is the case, I would remove flag, purge and deleted from the private_msg table. When a user sends a message, create an additional record in recipient for them. Ie. If kwolfe pms doubledee, one record in private_msg, two records in recipient (one for kwolfe). You then build the users "sent folder" from an inner join of private msg and recipient table, and they can star, delete and purge just the same. You're clean up would then be done if all recipients for a msg_id are purged.

    This idea only works if you want to prevent user from sending a pm to themselves, but you could allow a draft for convenience as I previously suggested

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I just want to make sure I've got everything straight. What is sender_flag? and recipient_flag? Just like a star?
    A Member can flag a Message of interest and a little flag icon appears next to it in either the Inbox or Sent views.


    Quote Originally Posted by K. Wolfe View Post
    If this is the case, I would remove flag, purge and deleted from the private_msg table.

    When a user sends a message, create an additional record in recipient for them. Ie. If kwolfe pms doubledee, one record in private_msg, two records in recipient (one for kwolfe).
    What is the advantage of that?


    Not to be a pain, but it would be easier to follow you if you could post the layouts of the Member, Private_Msg_Recipient, and Private_Msg tables...



    Quote Originally Posted by K. Wolfe View Post
    You then build the users "sent folder" from an inner join of private msg and recipient table, and they can star, delete and purge just the same. You're clean up would then be done if all recipients for a msg_id are purged.

    This idea only works if you want to prevent user from sending a pm to themselves, but you could allow a draft for convenience as I previously suggested
    You lost me on that last part...

    Sincerely,


    Debbie
    Last edited by DoubleDee; Dec 3, 2013 at 12:21. Reason: Asking for clarification

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,082
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    private_msg
    Code:
    id (PK)
    sender_id (FK)
    subject
    body
    created_on
    updated_on
    private_msg_recipient
    Code:
    id (PK)
    private_msg_id (UK1)(FK)
    recipient_id (UK2)(FK)
    recipient_flag
    recipient_read_on
    recipient_deleted_on
    recipient_purged_on
    created_on (again, unless you are allowing someone to edit this, these aren't needed)
    updated_on (again, unless you are allowing someone to edit this, these aren't needed)
    Example of data after K. Wolfe (1) sending message to DoubleDee (2) as the only "to":

    private_msg
    Code:
    id, sender_id, subject, body, created_on, updated_on
    1, 1, "Hello, DoubleDee", "Test body", 2013-12-03 13:27:23, NULL
    private_msg_recipient
    Code:
    id, private_msg_id, recipient_id, recipient_flag, recipient_read_on, recipient_deleted_on, recipient_purged_on
    1, 1, 1, FALSE, FALSE, FALSE, FALSE
    2, 1, 2, FALSE, FALSE, FALSE, FALSE
    Building a users "sent box":
    Code:
    select * from private_msg pm inner join private_msg_recipient pmr on pm.id = pmr.private_msg_id where pm.sender_id = $userId
    Building list of messages to be purged from system (by your rules, when everyone has purged_on)
    Code:
    select id from private_msg pm left join (select private_msg_id as id from private_msg_recipient pmr where recipient+purged_on = FALSE) t on pm.id = t.id where t.id = NULL
    Again though, all you are saving yourself is 3 columns on pm and enforcing yourself to prevent users from pm'ing themselves. If you want to allow them to pm themselves, then having your flags on pm is the way to go

  7. #7
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,154
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    I'll add that I think there's a lot of merit in the direction that KWolfe is trying to push you in.

    Notice, Debbie, that your private_msg and private_msg_recipient tables currently have a good amount of duplicated functionality, such as flags, deleted on, and purged on. You could try separating the concept of the message itself from the delivery of the message. Here's how I might structure it.

    private_msg
    id (PK)
    sender_id (FK)
    subject
    body
    created_on
    updated_on


    There's no flags and no deleted or purged in this table, because we're just thinking of the private message as an independent thing. We're not yet trying to place it in any kind of context, such as someone's inbox or sent box.

    Then another table can represent the delivery of the message into various mailboxes.

    private_msg_recipient
    id (PK)
    private_msg_id (FK)
    recipient_id (FK)
    folder -- for example, inbox, sent items, or perhaps even a user-defined folder
    flag
    read_on
    deleted_on
    purged_on


    This way, you represent a sent item the same way you represent an inbox item, and functionality such as flagging, read on, deleted on, purged on, etc., all exist in only one place.

    EDIT: Aside from the "folder" column that I introduced -- which would allow user-defined folders but is otherwise optional -- I think this is the exact same setup that KWolfe put together.
    "First make it work. Then make it better."

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    I'll add that I think there's a lot of merit in the direction that KWolfe is trying to push you in.
    What I think is more important, is that our independent designs are more similar than dissimilar...


    Quote Originally Posted by Jeff Mott View Post
    Notice, Debbie, that your private_msg and private_msg_recipient tables currently have a good amount of duplicated functionality, such as flags, deleted on, and purged on.
    "Good amount" is a bit of an exaggeration. Really the only difference between my latest design and your and Kyle's is 3 tables.


    Quote Originally Posted by Jeff Mott View Post
    You could try separating the concept of the message itself from the delivery of the message. Here's how I might structure it.

    private_msg
    Code:
    id (PK)
    sender_id (FK)
    subject
    body
    created_on
    updated_on
    There's no flags and no deleted or purged in this table, because we're just thinking of the private message as an independent thing. We're not yet trying to place it in any kind of context, such as someone's inbox or sent box.
    Okay.


    Quote Originally Posted by Jeff Mott View Post
    Then another table can represent the delivery of the message into various mailboxes.

    private_msg_recipient
    Code:
    id (PK)
    private_msg_id (FK)
    recipient_id (FK)
    folder -- for example, inbox, sent items, or perhaps even a user-defined folder
    flag
    read_on
    deleted_on
    purged_on
    Unless I allowed Custom User Folders, the "folder" field is redundant, because "deleted_on" and "purged_on" are all I need to know whether a Message goes into the "Incoming", "Sent", "Trash" views or disappears completely. (Well, in my design that is true, at least.)


    Quote Originally Posted by Jeff Mott View Post
    This way, you represent a sent item the same way you represent an inbox item, and functionality such as flagging, read on, deleted on, purged on, etc., all exist in only one place.
    True, but here are some things to consider...

    1.) "read_on" doesn't apply to the Sender, so that is a minor half-truth in this design

    2.) "recipient_id" is no longer an accurate field name in your design.

    3.) I suppose your "folder" field does this, but you really need a way to tell what "role" the person in this table is playing (e.g. Sender, Recipient)

    4.) It's a bit confusing if a person PM's themselves with your design.

    5.) Your design requires that I have Database Logic or Application Logic to *prevent* a PM from having multiple Senders.

    6.) What happens when someone wants to "Forward" a Message?

    Now you would have to have multiple "Senders" for a particular Message...

    So which one came first? (Yeah, you could look at the "created_on" field, but that is somewhat messy. (Oh wait, there is no "created_on" field in Kyle's design!)


    If I was going to use your suggestion, I'd probably tweak it like this...

    Code:
    id (PK)
    private_msg_id (FK)
    member_id (FK)
    pm_member_role (e.g. Sender, Recipient)
    flag
    read_on
    deleted_on
    purged_on

    Quote Originally Posted by Jeff Mott View Post
    EDIT: Aside from the "folder" column that I introduced -- which would allow user-defined folders but is otherwise optional -- I think this is the exact same setup that KWolfe put together.
    Yep, it looks like you two are on the same page.

    Sincerely,


    Debbie


Bookmarks

Posting Permissions

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