Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24

    Messaging System questions

    My website provides the ability for Registered Members to send each other Private Messages (PM) very similar to DBForums.

    Things are working well, but I want to revisit my design and make sure things are modeled properly. (I am also looking to add additional functionality, so my ERD probably needs to be tweaked.)

    Instead of typing a novel below, I was thinking it might be easy for me to describe a simple scenario, and see what you experts think about my thought-process. Then from there, maybe I can ask some more advanced questions?


    Simple Scenario:
    The website provides the ability for *registered* Members to send and receive PM's. For each PM, there can only ever be One Sender, and One Recipient. Also, PM's cannot be forwarded.

    With such simple requirements, would having just two tables like below be okay?

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

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

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

    Again, I am working towards a fancier design, but if someone could confirm if the "Simple Scenario" is valid, that would be a good start.

    Sincerely,


    Debbie

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    That should work fine
    However id agree one sender but most pm services allow multiple recipients.
    Something that isnt to tricky to implement
    Push recipient id from message and create a new entity messagerecipients with acts as a junction or intersection table that identifies what messages have been sent to what recipients. Id probably also wanr to know when a message has been read by each recipient
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by healdem View Post
    That should work fine

    Quote Originally Posted by healdem View Post
    However id agree one sender but most pm services allow multiple recipients.
    Something that isnt to tricky to implement
    Push recipient id from message and create a new entity messagerecipients with acts as a junction or intersection table that identifies what messages have been sent to what recipients. Id probably also wanr to know when a message has been read by each recipient
    Hang on, I'm getting there...


    So, in the example above, do you agree that the "Sender" and the "Recipient" are intrinsically part of the "Private_Msg" entity?


    Debbie

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by doubledee View Post
    So, in the example above, do you agree that the "Sender" and the "Recipient" are intrinsically part of the "Private_Msg" entity?
    Yes. A message must have both a sender and a recipient.

    Neither should be nullable.

    Can someone send a message to themselves? (sender_id = recipient_id)


    P.S. I wouldn't have columns called "id" as it doesn't really describe what the column contains. message_id, member_id, sender_member_id, recipient_member_id would be more appropriate in my opinion.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Sender definitely
    Recipient only if there is one recipient per message, sonething not found that often out in the wild
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by gvee View Post
    Yes. A message must have both a sender and a recipient.
    I'll go out on a limb and assume healdem might agree with you...



    Quote Originally Posted by gvee View Post
    Can someone send a message to themselves? (sender_id = recipient_id)
    Good question.

    Yes, they may.

    Sincerely,


    Debbie

  7. #7
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by healdem View Post
    Sender definitely
    Recipient only if there is one recipient per message, sonething not found that often out in the wild
    Hang on, I'm getting there...


    Debbie

  8. #8
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Healdem, I know you are "chompin at the bit", but please give me a moment to get to where you are already at.

    (I'm going slow on purpose, to keep the conversation from drifting from where I need it to go. Not that there are any problems so far.)


    -------
    Okay, so asking a few more questions about my "Simple Scenario" - which admittedly isn't the final solution I'm heading towards...

    Current Requirements:
    - Only Registered and Logged-in Members can send a PM.
    - Only Registered Members can receive a PM.
    - A PM can have only One Sender.
    - A PM can only have One Recipient.
    - A Sender can PM his/herself.


    Additional Requirements:
    - Each Member has a Message Center with the following PM Views: Incoming, Sent and Trash.
    - When a Sender sends a PM, the PM appears in the Sender's "Sent" view.
    - When a Sender sends a PM, the PM appears in the Recipient's "Incoming" view.

    - A Sender needs a way to "Flag" a PM.

    - When a Sender deletes a PM, it receives a timestamp in the "sender_deleted_on" field, and the PM appears in the Sender's "Trash" view.

    - When a Sender empties his/her Trash, the PM receives a timestamp in the "sender_purged_on" field, and the PM no longer appears in any of the Sender's views.



    ------
    Focusing on my "Simple Scenario" for now...

    Are the following fields intrinsically part of the "Private_Msg"...
    Code:
    - sender_flag
    - sender_deleted_on
    - sender_purged_on
    Sincerely,


    Debbie

  9. #9
    Join Date
    Jul 2012
    Posts
    40
    I have created this Data Model for Message Handling System on my Database Answers Web Site that you might find interesting :-
    Message Handling Systems Data Model

    HTH

    Barry in London

  10. #10
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by Barry in London View Post
    I have created this Data Model for Message Handling System on my Database Answers Web Site that you might find interesting :-
    Message Handling Systems Data Model

    HTH

    Barry in London
    Nope.


    Debbie

  11. #11
    Join Date
    Jul 2012
    Posts
    40
    Hi Debbie

    If that 'nope' was for me pls tell me why my Data Model wasn't helpful.

    Barry

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by doubledee View Post
    Additional Requirements:
    1) Each Member has a Message Center with the following PM Views: Incoming, Sent and Trash.
    2) When a Sender sends a PM, the PM appears in the Sender's "Sent" view.
    3) When a Sender sends a PM, the PM appears in the Recipient's "Incoming" view.
    4) A Sender needs a way to "Flag" a PM.
    5) When a Sender deletes a PM, it receives a timestamp in the "sender_deleted_on" field, and the PM appears in the Sender's "Trash" view.
    6) When a Sender empties his/her Trash, the PM receives a timestamp in the "sender_purged_on" field, and the PM no longer appears in any of the Sender's views.

    ------

    Are the following fields intrinsically part of the "Private_Msg"...
    Code:
    a) sender_flag
    b) sender_deleted_on
    c) sender_purged_on
    1) I suggest you attempt to write queries for this based on your existing structure. This will flush out the missing fields
    2) As per point 1. Sent items are easy
    3) As per point 1. Except "Incoming" is a bit ambiguous... do you mean "Inbox" or "Unread Messages"? Should you be storing when the recipient reads the message?
    4) Flag with what? One flag per message or multiple flags per message? What is the purpose of this functionality?
    5) I think this gives you the answer to one of your queries in point 1
    6) Once again, remember to include this in your initial queries. I may be tempted to create a view that encapsulates this logic and then write my "Incoming", "Sent" and "Trash" queries on top of this.

    ---

    a) Until I understand sender_flag I can't comment. If it is simply a boolean 1:1 relationship between flag and message then yes.
    b) Storing the datetime for the action is a good idea. This will give you when a sender deletes a message... What about a recipient, shouldn't they also be able to delete messages?
    c) As per point b!
    George
    Home | Blog

  13. #13
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    George,

    Thanks for the response.

    A few things...

    First, unfortunately this thread went dark for over a week, and at the end started getting off topic...

    The best way to help me is to let *me* lead the conversation, and you guys just answer for now...


    Secondly, the requirements provided earlier were for *your* benefit.

    I already wrote all of these requirements and designed the data model myself, and already have a finished working product.

    (I'm just here to see if there are any flaws or things can be improved.)


    Finally, anyone who thinks this is a "homework assignment" should be publicly beaten...





    Quote Originally Posted by gvee View Post
    3) As per point 1. Except "Incoming" is a bit ambiguous... do you mean "Inbox" or "Unread Messages"? Should you be storing when the recipient reads the message?
    The "Incoming" view (aka "Inbox") shows both Read and Unread Incoming PM's.


    Quote Originally Posted by gvee View Post
    4) Flag with what? One flag per message or multiple flags per message? What is the purpose of this functionality?
    Most E-mail systems allow you to "flag" a message. (Especially Client software like MS Outlook.)

    I want the Sender to be able to "flag" a PM that he/she sent. (I do that all of the time to note a message that I sent that I might need to refer to later.)

    Likewise, a Recipient should have the ability to "flag" a PM.

    The Flag relates to the Member-Message combination.

    DoubleDee (Sender) might want to flag the Sent Message, while George (Recipient) might not want to Flag anything.


    Quote Originally Posted by gvee View Post
    5) I think this gives you the answer to one of your queries in point 1
    6) Once again, remember to include this in your initial queries. I may be tempted to create a view that encapsulates this logic and then write my "Incoming", "Sent" and "Trash" queries on top of this.

    We're getting off into the weeds...


    If you don't mind, back to my last questions in Post #8 on November 27...

    Quote Originally Posted by doubledee
    Focusing on my "Simple Scenario" for now...

    Are the following fields intrinsically part of the "Private_Msg"...
    Code:

    - sender_flag
    - sender_deleted_on
    - sender_purged_on

    I'm sort of looking for a "Yes/No" response, followed up by an Explanation as to why you think "Yes/No"...


    (*Note: Remember, for now, there is only One Sender and One Recipient for One PM...)

    Sincerely,


    Debbie

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Please be careful with the tone of your response. I'm not going in to discussion on this here, just be aware that your post in places comes across as rather rude.



    If you're not bothered about knowing when a recipient read a message then the "incoming" messages is just a list of messages for a given recipient. No other metadata is required.

    The flagging is controlled by separate users so I would have a flagged_by_sender and flagged_by_recipient field.

    With regards to my point 5 & 6 this is genuinely how I would (and do!) approach these design questions. But the short answer you are clambering for is "yes".
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The tone of this thread has gone from bad to unacceptable.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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