[thelist] best procedure for storing temp data pending approval

Pringle, Ron RPringle at aurora-il.org
Thu Apr 12 15:09:10 CDT 2007


When a user submits changes to existing data in a DB that needs to be approved before the change is committed, where do you store the changes until approval, and how do you handle the approval process?


I have a calendar of events app and DB that I've been rewriting in PHP/MySQL. One of the things I'm building into the new version is an administrative approval layer between the submission and display of any given event.

This is handled by a separate "approval" table that stores: eventId, approvalStatus, approvalNotes, submitDate, approvalDate and approverID.

I am currently working on the section of the admin app where users will be allowed to update existing records that they have previously submitted (regardless of approval status). This is where things get tricky for me.

I have three approval status levels: pending (the default stage), approved, and denied. Changes made to pending and denied submissions can be handled in the current approval table with little disruption, but I'm not sure how to approach the approval process for a change to a submission that was previously approved.

The easy way would be to make the change to the existing record and reset the approval status to pending. This would make the previously viewable event disappear from the site until the changed version was approved, however.

My initial thought is that I need to create another table to hold the changes in until approved. I'm also wondering if I should create a new table just for change approvals or add some more fields to the existing approval table to handle that.

Not being a DBA or a "trained" programmer, I'm not really familiar with how this, most likely common, situation is typically handled.

Any thoughts, opinions or pointers to information on how to handle this would be greatly appreciated.


Ron



More information about the thelist mailing list