If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

Right now, when a Member "deletes" a PM, I set a "deleted_on" date and it ends up in the Member's Trash.

If a Member chooses to "permanently" delete the PM, then I set a "purged_on" date and it is removed from all Member views, however the physical PM still resides in one of the two tables described above.

Things get tricky depending whether it is the "Sender" or the "Recipient" who is deleting things.

For instance, if the "Sender" wants to "purge" a PM, then I can't do that if there are other Recipients who still want to keep the PM. (See Design above)

And if a Recipient wants to "purge" a PM, do I really care since the record in the "pm_recipient" table is just "keys" and dates?!

I think this all seems awkward because of how I designed things (i.e. 3rd Normal Form).

Would appreciate some advice on how to not make this more complicated than it needs to be!!

I think this all seems awkward because of how I designed things (i.e. 3rd Normal Form).

3NF as you have it here is valid only by the ownership of the message belonging to the sender. So yes, the sender gets complete control of the message since that is how you designed it and if they choose to delete it than the receiver is SOL.
If you do not want the sender to have absolute control (hint: you do not, so your design is incorrect and not in 3NF for a proper logical dataset), than you have two options: 2NF, and proper 3/BCNF for the correct dataset. These come with a heavy trade off though, so you need to choose which one.
PM systems require that either:

Denormalize to 2NF so as to allow distinct ownership of duplicate data (this would be more similar to how emails operate).
Pro: orphan control; performance gain
Con: Redundant data; 2NF

Normalize to 3/BCNF and create many to many ownership of data and no direct control of data from any ownership object.
Pro: normalized
Con: Orphaned data; more joins

Proper 3/BCNF will also allow many to many so you can have many recipients for a message. Can also be seen as a con since 1 to 1 can only be enforced via software (2NF and 3NF will both require software control, so the only way to make it 1 to 1 controllable is to use a more direct sender -> receiver single tuple entry).

There really isn't another option in this. Either you have to replicate the data so that both objects own it and control only their unique instance OR you need to create the data so that neither object owns it but both can aggregate it, so neither object has direct control over the data (ie: conform to 3NF regulation of 2NF AND all data relies SOLELY on the primary key).
That means you need to deal with orphan control either with a trigger or a clean up script after the fact.

I can't think of a way to get the best of both worlds. Either you'll have to replicate or you need to create a possibility where orphans can be created. Pedant may be able to come up with an idea, but I can't see how data can be tied into control of *multiple* objects without either duplicate or orphans (since the data is now the control point, it could be set to delete the "owner" types, but not the other way around without a trigger). This is why PM systems are a pain since they work backwards from basic normalization logic.

For a simple reference example as well; plenty can be modified or added:

Code would be used to insert a PM and then insert multiple records into mailbox. Orphans can never exist in mailbox, but can exist in PM since the PM is now the control set, but has no direct owner. A trigger or a delete from a join would fix that.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

For the life of me, I don't understand why you want the sender to be able to either delete or purge sent messages, unless it is simply for display purposes.

SO I'd probably do it the way PM's work in this forum: A SENT message can be deleted, in which case it is *MOVED* out of the SENT folder (table) and into the DELETED folder (table). And when a message is purged, it is simply completely removed.

Now, clearly you don't move the actual messages: You just move the reference(s) to them. In other words, K.I.S.S.

And then, when all references to a message--sender or recipient--have been permanently removed you can (if you wish) remove the message, per se.

An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.

For the life of me, I don't understand why you want the sender to be able to either delete or purge sent messages, unless it is simply for display purposes.

Whoa there! (Must be a shifting vertebrae in those comments?!)

Let's go over this again...

A "Sender" ("member" table) creates a "Private Message" ("private_msg" table) and sends it to a "Recipient" ("private_msg_recipient" table)

In the UI, the new PM appears in the Recipient's "Incoming" folder. (On the back-end, it is a record in the "private_msg_recipient" table pointing back to the Sender's "private_msg".)

If the Recipient "deletes" the PM, in the UI it is moved to the Recipient's "Trash" folder. (On the back-end, the record in the "private_msg_recipient" table - which points back to the Sender's original "private_msg" will get a Timestamp in the "deleted_on" field so that my php knows to display it in the "Trash" folder.)

If the Recipient empties his/her Trash, then the PM disappears from the Trash folder. (On the back-end, the record in the "private_msg_recipient" table - which points back to the Sender's original "private_msg" will get a Timestamp in the "purged_on" field so that I know it can be deleted from the "private_msg_recipient" table.)

In the UI, for the Sender, the new PM appears in the "Sent" folder. (On the back end, the PM is stored in the "private_msg" table.)

If the Sender deletes the PM, then in the UI, it goes to the Trash folder. (On the back end, the PM gets a "deleted_on" Timestamp so my php knows to move it to the Trash folder in the UI.)

If the Sender empties his/her Trash, then in the UI the PM disappears. (On the back end, the PM gets a "purged_on" Timestamp so my php knows it can be removed from the "private_msg" table.

(Of course I need to check for orphans in the "private_msg_recipeint" table now as well?!)

I think what I have is very logical, although it doesn't exactly match what appears to happen in the physical world where there is a physical PM for the Sender, and a physical PM for the Recipient.

Originally Posted by Old Pedant

SO I'd probably do it the way PM's work in this forum: A SENT message can be deleted, in which case it is *MOVED* out of the SENT folder (table) and into the DELETED folder (table). And when a message is purged, it is simply completely removed.

Now, clearly you don't move the actual messages: You just move the reference(s) to them. In other words, K.I.S.S.

And then, when all references to a message--sender or recipient--have been permanently removed you can (if you wish) remove the message, per se.

Right, but one of my questions in my OP was "How do I do that?"

Do I create a Trigger or Stored Procedure to make sure both the "private_msg" record (parent) and the "private_msg_recipient" record (child) are both marked as "purged_on" so I can physically delete things?

Or do I do this in my Application Code?

Or do I write a Cron job?

Or do I do whatever Fou-Lu was saying above? (I don't see that happening because I am already immersed in my current 3NF design...)

I removed the deleted option since you could just make a trash mailbox instead so it wasn't necessary to keep it.

It doesn't matter if you keep your current design or if you fix it, in either case you'll need to write a trigger, cron or application method to clean up orphan records. The only way around that is duplication which is what I pointed out as the tradeoffs between the 3NF and 2NF design choices or by using a single tuple to have a single sender and single receiver.

As far your current design is concerned, it simply a glorified message posting system which only specific users can read. The author of the content is free to delete or modify it whenever they desire since they have absolute ownership of it, and because of this design you must now impose an arbitrary limit using the software to accommodate the deletions. This is why you should be decoupling the ownership of the data from the author so the author no longer carries ownership of the data.

Edit:
Silly me, I just realized as well you'll likely want to know whom a message is from. A non-candidate property for the authorid should be added to the message record as well.

Last edited by Fou-Lu; 11-15-2013 at 02:15 PM.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

Sorry, I wasn't thinking of JUST private messages. I was thinking of messages to multiple recipients.

Quite frankly, for private messages I would just denormalize. Keep one copy for the sender, one for the recipient. Either sender or recipient can delete (or move to TRASH) any message without affecting what the other person sees.

Why work harder than that? Eliminates the need for a trigger/cron job. Simplifies the heck out of things at the expense of a minor denormalization.

An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.