Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Need help with Old PM's

    This may sound like a funny question, but I'm confused how I should go about deleting old Private Messages from my Members.

    Here is my Database Design...

    member
    Code:
    - id
    - username
    and so on...

    pm_recipient
    Code:
    - member_id_to
    - pm_id
    - created_on
    - read_on
    - deleted_on
    - purged_on (This is when the Recipient empties his/her Trash)

    private_message
    Code:
    - id
    - member_id_from
    - subject
    - body
    - sent_on
    - sender_deleted_on
    - sender_purged_on  (This is when the Sender empties his/her Trash)

    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!!

    Sincerely,


    Debbie

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by doubledee View Post
    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:
    1. 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
    2. 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:
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    
    CREATE TABLE IF NOT EXISTS `mailbox` (
      `memberid` int(11) unsigned NOT NULL,
      `pmid` int(11) unsigned NOT NULL,
      `mailbox` enum('inbox','sent') NOT NULL,
      `read` bit(1) NOT NULL,
      `softdeleted` bit(1) NOT NULL,
      PRIMARY KEY (`memberid`,`pmid`,`mailbox`),
      KEY `pmid` (`pmid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `mailbox` (`memberid`, `pmid`, `mailbox`, `read`, `softdeleted`) VALUES
    (1, 1, 'inbox', b'1', b'1'),
    (1, 1, 'sent', b'1', b'0'),
    (3, 3, 'sent', b'1', b'0'),
    (7, 3, 'inbox', b'0', b'0');
    
    CREATE TABLE IF NOT EXISTS `pm` (
      `pmid` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `subject` varchar(255) NOT NULL,
      `body` text NOT NULL,
      `created` datetime NOT NULL,
      PRIMARY KEY (`pmid`),
      KEY `subject` (`subject`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
    
    INSERT INTO `pm` (`pmid`, `subject`, `body`, `created`) VALUES
    (1, 'this is a pm', 'this is the first pm', '2013-11-14 07:04:31'),
    (3, 'This is a reply to message  #2', 'A reply', '2013-11-14 07:37:34');
    
    
    ALTER TABLE `mailbox`
      ADD CONSTRAINT `mailbox_ibfk_1` FOREIGN KEY (`pmid`) REFERENCES `pm` (`pmid`);
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    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 ;)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    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.

  • #4
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Fou-Lu,

    Sorry, I had a really hard time following what you were saying. (Which is strange, because I thought I was pretty good with Database Design.)

    It would help if you could post some ERD's of what you wrote about above...


    Something like this...
    Code:
    member -||----0<- pm_recipient ->0-----||- private_message

    Sincerely,


    Debbie

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.



    Quote Originally Posted by Old Pedant View Post
    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...)

    Sincerely,


    Debbie

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Code:
    +---------------+           +--------------+
    | mailbox       |           | message      |
    +---------------+           +--------------+
    | memberid [pk] |>o-------+<| pmid    [PK] |
    | pmid     [pk] |           | subject      |
    | mailbox  [pk] |           | body         |
    | read          |           | created      |
    +---------------+           +--------------+
    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 01: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 ;)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    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.


  •  

    Posting Permissions

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