This discussion is archived
1 2 3 4 Previous Next 47 Replies Latest reply: Nov 28, 2013 2:31 AM by rukbat RSS

Need help with Data Model for Private Messaging

Debbie_1004338a Newbie
Currently Being Moderated

Sad to say, but it looks like I just really screwed up the design of my Private Messaging (PM) module...  *sigh*

 

What looked good on paper doesn't seem to be practical in application.

 

I am hoping some of you Oracle gurus can help me come up with a better design!!

 

Here is my current design...

 

member -||-----0<- private_msg_recipient ->0------||- private_msg

 

MEMBER table
- id
- email
- username
- first_name

 

PRIVATE_MSG_RECIPIENT table
- id
- member_id_to
- message_id
- flag
- created_on
- updated_on
- read_on
- deleted_on
- purged_on

 

PRIVATE_MSG table
- id
- member_id_from
- subject
- body
- flag
- sent_on
- updated_on
- sender_deleted_on
- sender_purged_on

 

 

***Short explanation of how the application currently works...

 

- Sender creates a PM and sends it to a Recipient.

- The PM appears in the Sender's "Sent" folder in my website

- The PM also appears in the Recipient's "Incoming" folder.

- If the Recipient deletes the PM, I set "deleted_on" and my code moves the PM from Recipient's "Inbox" to the "Trash" folder.  (Record doesn't actually move!)

- If the Recipient "permanently deletes" the PM from his/her "Trash", I set "purged_on" and my code removes the PM from the Recipient's Message Center.  (Record still in database!)

 

- If the Sender deletes the PM, I set "sender_deleted_on" and my code moves the PM from the Sender's "Sent" folder to the "Trash" folder.  (Record doesn't actually move!)

- If the Recipient "permanently deletes" the PM from his/her "Trash", I set "sender_purged_on" and my code removes the PM from the Sender's Message Center.  (Record still in database!)

 

Here are my problems...

 

1.) I can't store PM's forever.

 

2.) Because of my design, the Sender really owns the PM, and if I add code to REMOVE the PM from the database once it has a "sender_purged_on" value, then that would in essence remove the PM from the Recipient's Inbox as well!!

 

In order to remove a PM from the database, I would have to make sure that *both* the Recipient has "purged_on" value and the Sender has a "sender_purged_on" value.  (Lot's of Application Logic for something which should be simple?!)

 

 

I am wondering if I need to change my Data Model to something that allows my autonomy when it comes to the Sender and/or the Recipient deleting the PM for good...

 

 

One the other hand, I believe I did a good job or normalizing the data.  And my current Data Model is the most efficient when it comes to saving storage space and not having dups.

 

Maybe I do indeed just need need to write application logic - or a cron job - which checks to make sure that *both* the Sender an Recipient have deleted the PM before it actually flushes it out of my database to free up space?!

 

Of course, if one party sits on their PM's forever, then I can never clear things out of my database to free up space...

 

What should I do??

 

Some expert advice would be welcome!!

 

Sincerely,

 

 

Debbie

  • 1. Re: Need help with Data Model for Private Messaging
    sb92075 Guru
    Currently Being Moderated

    >Of course, if one party sits on their PM's forever, then I can never clear things out of my database to free up space..

    Correct.


    If the application had a policy that messages older than ????? days will be removed, then the maintenance code is a one liner.

     

    DELETE FROM PRIVATE_MSG WHERE SENT_ON < TRUNC(SYSDATE)-????????

  • 2. Re: Need help with Data Model for Private Messaging
    JohnWatson Guru
    Currently Being Moderated

    I'm a bit nervous about replying, because I remember once before that I offended you. Let's make a fresh start.

    Can you not store your messages in one table? I would have thought that was a normalized structure. The entity is the message, and it has a set of attributes, including:

         body

         to

         from

         sender's folder

         recipient's folder

    You can delete the message when both sender and recipient have placed it in their "purged" folder. THis structure will not handle the situation where a message goes to several recipients. You would have to normalize that with a distribution list.

  • 3. Re: Need help with Data Model for Private Messaging
    Mark D Powell Guru
    Currently Being Moderated

    It took me so long to write this due to interruptions (work) that John stole my main idea: Why not save just one copy of the message and have a table which lists each receipient (and the owner) along with a message deleted indicator and maybe the sent date.  Then when a user signs on the application scans for all messages addressed to the user and lists the messages.  Once all receipients have marked the message as deleted and the sender has marked it as deleted or once a maximum hold period has passed then the message is physically deleted.

    - -

    HTH -- Mark D Powell --

  • 4. Re: Need help with Data Model for Private Messaging
    Debbie_1004338a Newbie
    Currently Being Moderated

    JohnWatson wrote:

     

    I'm a bit nervous about replying, because I remember once before that I offended you. Let's make a fresh start.

    Can you not store your messages in one table? I would have thought that was a normalized structure. The entity is the message, and it has a set of attributes, including:

         body

         to

         from

         sender's folder

         recipient's folder

    You can delete the message when both sender and recipient have placed it in their "purged" folder. THis structure will not handle the situation where a message goes to several recipients. You would have to normalize that with a distribution list.

     

    John,

     

    Thanks for the reply.

     

    Pardon me for saying so, but I'm not seeing where your suggestion is any different from what I have.

     

    I am storing the message in one table.  Look back to my OP and look at the table called PRIVATE_MESSAGE.  That represents the message in its entirety.  (The only difference is that it only describes the Sender, i.e. "member_id_from").

     

    Can you please explain to me what you are trying to do with "sender's folder" and "recipient's folder" in your schema above?

     

     

    To you second point, you say, "You can delete the message when both sender and recipient have placed it in their "purged" folder."

     

    Well, that is sort of the same problem I have now...

     

    In my PRIVATE_MSG table, I have these fields...

     

    - member_id_from
    - sender_purged_on
    

     

     

    And in my PRIVATE_MSG_RECIPIENT table I have these fields...

    - member_id_to
    - purged_on
    

     

    In my current design, if the Sender moves the Sent PM to his/her Trash and then chooses "permanently delete", then the PRIVATE_MSG record gets a time-stamp in "sender_purged_on".

     

    Likewise, if the Recipient moves the Received PM to his/her Trash and then chooses "permanently delete", then the PRIVATE_MSG_RECIPIENT record gets a time-stamp in "purged_on".

     

    When *both* of those conditions are true, THEN I can delete the PRIVATE_MSG record - which in essence represents the PM.

     

     

    But what happens if you send me a PM and I never "purge" it from my Message Center?

     

    Why?

     

    Because, if I didn't check that the Recipient had a time-stamp in "purged_on", then I would delete the PRIVATE_MSG record, in essence wiping out the Recipient's PM as well.

     

    (Remember, in my design, a record in PRIVATE_MSG_RECIPIENT is just a pointer back to the physical PM in the PRIVATE_MSG table.  Follow me?)

     

    I don't really see how your design is any different, but maybe I'm not understand all of the fields or the larger picture.

     

    What is my larger point of discussing where my design seems to fail?

     

    Well, a few people I know have said that when User-A sends User-B a PM, that there should be TWO RECORDS?!  (I don't think I like that, but at least it would allow people to "purge" things independently!!)

     

    Like most things in IT, I fear there is no "silver bullet", and that each design has its pro's and con's...

     

    But maybe you gurus know of a way for me to "Have my cake and eat it too"?! 

     

    Hope you are following me?!

     

    Sincerely,

     

     

    Debbie

  • 5. Re: Need help with Data Model for Private Messaging
    rp0428 Guru
    Currently Being Moderated
    Can you please explain to me what you are trying to do with "sender's folder" and "recipient's folder" in your schema above?

    Unfortunately his design is just as denormalized as yours

    PRIVATE_MSG table  

    • - id  
    • - member_id_from  
    • - subject  
    • - body  
    • - flag  
    • - sent_on  
    • - updated_on  
    • - sender_deleted_on  
    • - sender_purged_on 

    The last two columns have NOTHING to do with the message itself so do NOT belong in a normalized table.

     

    And his design:

    body 

         to

         from

         sender's folder

         recipient's folder

    Same comment - those last two columns also have NOTHING to do with the message itself.

     

    The message table should just have columns directly related to the message. It is a list of unique messages: no more, no less.

     

    Mark gave you hints to the proper normalized design using an INTERSECT table. that table might list: sender, recipient, sender_delete_flag, recipient_delete_flag. As mark suggested you could also have one or two DATEs related to when the delete flags were set. I would just make the columns DATE fields.

     

    Once both date columns have a value you can delete the message (or delete all messages older than 30+ days).

     

     

    When both flags are set you can delete the message itself that references the sender and the message sent.

  • 6. Re: Need help with Data Model for Private Messaging
    Debbie_1004338a Newbie
    Currently Being Moderated

    MarkDPowell wrote:

     

    It took me so long to write this due to interruptions (work) that John stole my main idea: Why not save just one copy of the message and have a table which lists each receipient (and the owner) along with a message deleted indicator and maybe the sent date.  Then when a user signs on the application scans for all messages addressed to the user and lists the messages.  Once all receipients have marked the message as deleted and the sender has marked it as deleted or once a maximum hold period has passed then the message is physically deleted.

    - -

    HTH -- Mark D Powell --

     

    Mark,

     

    Thanks for the reply also!

     

    You said, "Why not save just one copy of the message."

     

    Well, I do have that.  A PM is stored as one record in my PRIVATE_MSG table as described in my OP.

     

    --------

    Then you went on to say, "and have a table which lists each recipient (and the owner) along with a message deleted indicator and maybe the sent date."

     

    Again, I think I have that.  All Recipients are stored in my PRIVATE_MSG_RECIPIENT table.  (The only difference, is that my design tightly binds the Sender to the PM, both of which are stored in the PRIVATE_MSG table, which may be my design flaw?!)

     

    I think there is a disconnect with what you and John may be suggesting, and my understanding.  (It sounds to me like I have exactly what both of you are describing, but maybe not?!)

     

    --------

    Then you went on to say, "and have a table which lists each recipient (and the owner) along with a message deleted indicator and maybe the sent date."

    Finally, you said, "Once all receipients have marked the message as deleted and the sender has marked it as deleted or once a maximum hold period has passed then the message is physically deleted."

     

    Okay, but that is the entire problem I am having right now.

     

    With my design, if the Sender has a time-stamp in "sender_purged_on" field in the PRIVATE_MSG table, AND the Recipient has a time-stamp in the "purged_on" field (think "recipient_purged_on") in the PRIVATE_MSG_RECIPIENT table, THEN I can successfully remove a record from the PRIVATE_MSG table and from the PRIVATE_MSG_RECIPIENT table, thus simulating physically removing the PM from the system.

     

    Follow me?

     

    The key is that BOTH Sender and Receiver have to "purge" their (logical) PM in their Message Centers in order for me to be able to physically do so in the database.

     

    Well, here is the problem...

     

    I currently have a business rule that says, "You can only have 100 (logical) Messages between your Incoming, Sent and Trash folders."

     

    So, let's say that I go out and send 100 PM's to people here on Oracle Forums telling them how much I appreciate their time and help.

     

    I just maxed out my PM Quota.

     

    But worse, let's say that all 100 of the Recipients of my PM are gracious, BUT are also bums who never clean out their Message Centers?!

     

    Because the Sender (Debbie) is bound to the PRIVATE_MSG record, one of two things can happen...  (Neither desirable)

     

    1.) My system let's Debbie "purge" the 100 Sent PM's she sent, in turn removing the 100 PRIVATE_MSG records from the database, and *poof* there goes everyone's "Thank You" PM's from their Inboxes, because Referential Integrity just cascaded things.

     

    2.) Poor Debbie is frozen out of Sending or Receiving PM's until some of you bums clean out your Message Centers?!  *wink*

     

    The point?

     

    My current design is not very well thought out, because of the two aforementioned issues.

     

    Follow me?

     

    Sincerely,

     

     

    Debbie

  • 7. Re: Need help with Data Model for Private Messaging
    rp0428 Guru
    Currently Being Moderated
    The key is that BOTH Sender and Receiver have to "purge" their (logical) PM in their Message Centers in order for me to be able to physically do so in the database.

    That is because you have CHOSEN to implement that as the rule.

     

    You certainly don't have to do that. You are free to delete a PM whenever you choose to. If you want to you can delete it the day after it is sent whether it has been read or not and even if neither sender nor receiver has marked it for purge.

     

    You have to set the business rules. As I mentioned in my reply in the intersect table you can track when a message was sent and when the sender and/or receiver maked it purged.

     

    If a quota is reached you can, if you choose, simply delete the oldest message marked by both sender/receiver for purge. If there are none you can delete the oldest message marked by the receiver only for purge.

     

    In short you have to decide what rules you want to use to cleanup older messages and you have to decide if you want to delete messages that have not been flagged for purge by one or both parties.

  • 8. Re: Need help with Data Model for Private Messaging
    Debbie_1004338a Newbie
    Currently Being Moderated

    rp0428,

     

    I think I am starting to see my evil ways and where I went wrong... 

     

    > Unfortunately his design is just as denormalized as yours

     

    I see that now.  My bad!!

     

     

    > the last two columns have NOTHING to do with the message itself so do NOT belong in a normalized table.

    > And his design:

    >

    > Same comment - those last two columns also have NOTHING to do with the message itself.

     

    Right.

     

     

    > The message table should just have columns directly related to the message. It is a list of unique messages: no more, no less.

     

    Right.

     

    > Mark gave you hints to the proper normalized design using an INTERSECT table.

    > that table might list: sender, recipient, sender_delete_flag, recipient_delete_flag.

    > As mark suggested you could also have one or two DATEs related to when the delete flags were set. I would just make the columns DATE fields.

    >

    > Once both date columns have a value you can delete the message (or delete all messages older than 30+ days).

    >

    > When both flags are set you can delete the message itself that references the sender and the message sent.

     

     

    Okay, how does this revised design look...

     

    MEMBER --||-----0<-- PM_DISTRIBUTION -->0-------||-- PRIVATE_MSG
    

     

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

     

    PM_DISTRIBUTION table (Maybe you can think of a better name??)
    - id
    - private_msg_id
    - sender_id
    - recipient_id
    - sender_flag
    - sender_deleted_on
    - sender_purged_on
    - recipient_flag
    - recipient_read_on
    - recipient_deleted_on
    - recipient_purged_on
    

     

    PRIVATE_MSG
    - id
    - subject
    - body
    - sent_on
    

     

    Is that what you were describing to me?

     

    Quickly reflecting on this new design...

     

    1.) It should now be in 3rd Normal Form, right?

     

    2.) It should allow the Sender and Recipient to freely and independently "delete" or "purge" a PM with no impact on the other party, right?

     

     

    Here are a few Potential Issues that I see, though...

     

    a.) What is to stop there from being TWO SENDERS of a PM?

     

    In retrospect, that is why I originally stuck "member_id_from" in the PRIVATE_MSG table!!  The logic being, that a PM only ever has *one* Sender.

     

    I guess I would have to add either Application Logic, or Database Logic, or both to ensure that a given PM never has more than one Sender, right?

     

     

    b.) If the design above is what you were hinting at, and if it is thus "correct", then is there any conflict with my Business Rule: "Any given User shall only be allowed 100 Messages between his/her Incoming, Sent and Trash folders."

     

    Because the Sender is no longer "tightly bound" to the PRIVATE_MSG, in my scenario above...

     

    Debbie could send 100 PM's, hit her quota, then turn around and delete and purge all 100 Sent PM's and that should in no way impact the 100 PM's sitting in other Users' Inboxes, right??

     

    I think this works like I want...

     

    Sincerely,

     

     

    Debbie

  • 9. Re: Need help with Data Model for Private Messaging
    rp0428 Guru
    Currently Being Moderated
    a.) What is to stop there from being TWO SENDERS of a PM?

    You and the implementation you use to control the data - as I already suggested

    Because the Sender is no longer "tightly bound" to the PRIVATE_MSG, in my scenario above...

    Only because you seem to have chosen to 'ignore' what I suggested. As I said above, and you quoted:

    that table might list: sender, recipient, sender_delete_flag, recipient_delete_flag.

    When you create a new msg that message has one, and only one sender. So the first entry in the INTERSECT table uses that 'sender' id as the 'sender' in that list I suggested above. Sure seems to be 'tightly bound' to me.

     

    The 'thing' that prevents 'TWO SENDERS of a PM' is the code that creates that first entry in the INTERSECT table when a new message is created. One new message => one sender.

  • 10. Re: Need help with Data Model for Private Messaging
    Debbie_1004338a Newbie
    Currently Being Moderated

    rp0428,

     

    How did I 'ignore' what you said??

     

    I think my latest revisions should match *exactly* what you said.

     

    It would help if you'd confirm my latest ERD and Table Layouts...

     

    Sincerely,

     

     

    Debbie

     

    Message was edited by: Debbie_1004338a

  • 11. Re: Need help with Data Model for Private Messaging
    JohnWatson Guru
    Currently Being Moderated

    I m not going to give up on my design yet, because I think it IS normalized, in pretty much the way Mark described. One member can send many messages, and one member can receive many messages. My messages table acts as the intersection between the members. A message's attributes would include its locations: the sender's folder and the recipient's folder (as I said but you ignored, if a message can have mutiple recipients then this would not be correct). Whether a message is unread, read, deleted, or purged by sender and recipient is just an update to the relevant folder column. The folder within which the message resides is not dependent upon anything other than the message id; it as much an attribute of the message as its creation date.

    Consider your entities pm_distribution and private message: is there not a one-to-one relationship between them? So why two entities?

  • 12. Re: Need help with Data Model for Private Messaging
    Mark D Powell Guru
    Currently Being Moderated

    I may have misread your posting as it looked to me that you had a copy of the message itself in both the senders and recipients tables.  Sometimes just from the table column list it can be hard to tell what is there when you only have a few minutes to glace at the material.


    The answer depends.  Do you want to support forwarding.  If yes, then you have two approaches: one is to copy the message to a new message id then you manage the original message using your standard rules.  The copy is treated as a new message and managed as an independent new unique message.  The other choice is to just update the sender/recipient table to have a second sender.  If you think about it a sender and a recipient are really both just message users.  So you could use a type user_type or msg_type (S,R) to identify which.   A sent/received date or transition id can be used to associate the senders/recipients for a set of messages.  Your clean up process would recognize that the message itself is still used by a set of transactions but could remove all entries from the sender/recipient table when all users in a specific transaction marked the message as deleted.

     

    Exactly what columns you need depend on functionality like when a message gets old and is auto-purged do you want to notify the sender that the purged message was never read by recipient X?  If yes, then you have to track the read action.  I not, you don't really care.

     

    HTH -- Mark D Powell --

  • 13. Re: Need help with Data Model for Private Messaging
    Debbie_1004338a Newbie
    Currently Being Moderated

    So rp0428, how does my updated design in Post #8 look?

     

    I believe it is what you suggested...

     

    Sincerely,

     

     

    Debbie

  • 14. Re: Need help with Data Model for Private Messaging
    Debbie_1004338a Newbie
    Currently Being Moderated

    JohnWatson wrote:

     

    I m not going to give up on my design yet, because I think it IS normalized, in pretty much the way Mark described. One member can send many messages, and one member can receive many messages. My messages table acts as the intersection between the members. A message's attributes would include its locations: the sender's folder and the recipient's folder (as I said but you ignored, if a message can have mutiple recipients then this would not be correct). Whether a message is unread, read, deleted, or purged by sender and recipient is just an update to the relevant folder column. The folder within which the message resides is not dependent upon anything other than the message id; it as much an attribute of the message as its creation date.

    Consider your entities pm_distribution and private message: is there not a one-to-one relationship between them? So why two entities?

     

    A few things...

     

    a.) This wasn't specified in my OP, but, yes, it would be nice to allow One Sender to send the same PM to Many Recipients.

     

    b.) I have to say that I think rp0428's design seems the best so far.  (I typed out my understanding of that design in Post #8... )

     

    c.) I believe the status of a PM is a function of the Member and not the PM, and so as MarkDPowell's hint plus rp0428's further hints seem to be more accurate.

     

    d.) To your question, "Is there not a one-to-one relationship between PM_DISTRIBUTION and PRIVATE_MSG?"

     

    No, there isn't.

     

    In my updated design in Post #8...

     

    One and only one PRIVATE_MSG has one or more PM_DISTRIBUTIONs (i.e. Sender and Recipients).

     

    Sincerely,

     

     

    Debbie

1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points