This question could easily go in the Database forum, but I am posting it here, because I think it is more about 1.) Application Design, 2.) Process Flow, and 3.) User Experience.
Background: (Oh how to describe this in under 10,000 words?!)
I have just about completed my Private Messaging module, and while it look and works great, I just discovered a potentially fatal design flaw?! 
More on that in a minute…
Anyway, my “Message Center” offers pretty standard behavior.
When a Sender sends a PM to a Recipient…
-
The PM goes into the Sender’s “Sent” folder.
-
The PM also appears in the Recipient’s “Incoming” folder.
-
If the Recipient deletes the PM, then I set “deleted_on” with a time-stamp, and my code moves the PM from the “Incoming” to “Trash” folder.
-
If the Recipient empties his/her Trash, then I set “purged_on” with a time-stamp, and my code removes the PM from the User’s Message Center.
(At this point, the PM record is not being moved or removed. Things just appear that way in UI.)
- If the Sender deletes a Sent PM, then I set “sender_deleted_on” with a time-stamp, and my code moves the PM from the “Sent” to “Trash” folder.
- If the Sender empties his/her Trash (of Sent PM’s), then I set “sender_purged_on” with a time-stamp, and my code removes the Sent PM from the User’s Message Center.
(Again, at this point the PM record is not being moved or removed. Things just appear that way in UI.)
Here is my current Database 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
**With this current design, the Sender and the Private Message are really one in the same. Whereas the Recipient is just an entry in the “private_msg_recipient” (junction) table linking the Private Message back to the Recipient in the “member” table.
This design is normalized, and is the most efficient way to store things.
However, as I just discovered this week, it also sorta paints me into a corner… :-/
My ISP charges me for storage, and so I am unwilling to store user’s PM’s indefinitely.
As a result, I need to add some more PHP to physically remove PM’s from the database when the conditions are right.
Originally, my plan was to remove a record from the PRIVATE_MSG_RECIPIENT table when it had a value in the “purged_on” field.
Likewise, I would remove a record from the PRIVATE_MSG table when it had a value in the “sender_purged_on” field.
Seemed easy enough?!
Problems:
1.) If the Sender’s PM is removed from the database (i.e. Record in PRIVATE_MSG table) then it will also delete the Recipient’s PM (i.e. Record in PRIVATE_MSG_RECIPIENT table).
You would be pretty mad if every time the Sender deleted his/her messages, your messages went with them?!
2.) A Sender’s PM’s cannot be removed until the corresponding Recipients clear out their PM’s.
So, a Sender could send out 100 PM’s. The Recipients just sit on them. And then the Sender’s mailbox is full while he/she waits on the Recipients to clear things out?!
(**Note: My website limits Users to 100 PM’s in their Incoming, Sent and Trash folders.)
Desired Outcome:
I need a way to…
a.) Keep my Database lean
b.) Encourage Users to keep their Message Centers clean (i.e. Delete old Sent and Trash PM’s)
c.) Not delete a Recipient’s PM when a Sender deletes the Sent PM?!
Some people might say I am making this too difficult, and worrying about things I shouldn’t. And if I only had 1,000 Users, they would be right.
But after my website has been up for a few years, and I have tens or hundreds of thousands of Users, this entire Private Message management issue could become a crisis if I don’t design things properly upfront!!!
What do I do?
1.) Change my Data Model?
2.) Change my Application Rules & Flow?
3.) Other?
BTW, I have invested an enormous amount of time into my current Database Design (above) and PHP code.
I am willing to scrap most of my work for a better design, BUT, there better be a damn good reason to make Database and Application changes at this point in the game!!! :mad:
Here is hoping that all is not lost, and I just need to “tweak” some things here and there?! :-/
Sincerely,
Debbie