I need to store user entered changes to a particular table, but not show those changes until they have been viewed and approved by an administrative user. While those changes are still in a pending state, I would still display the old version of the data. What would be the best way of storing these changes waiting for approval?
I have thought of several ways, but can't figure out what is the best method. This is a very small web app. One way would be to have a PendingChanges table that mimics the other table's schema, and then once the change is approved, I could update the real table with the information. Another approach would be to do some sort of record versioning where I store multiple versions of the data in the table and then always pull the record with the highest version number that has been marked approved. That would limit the number of extra tables (I need to do this for multiple tables), but would require me to do extra processing every time I pull out a set of records to make sure I get the right ones.
Any personal experiences with these methods or others that might be good?
Update: Just to clarify, in this particular situation I am not interested so much in historical data. I just need some way of approving any changes that are made by a user before they go live on the site. So, a user will edit their "profile" and then an administrator will look at that modification and approve it. Once approved, that will become the displayed value and the old version does not need to be kept.
Anybody tried the solution below where you store pending changes from any table that needs to track them as XML in a special PendingChanges table? Each record would have a column that said which table the changes were for, a column that maybe stored the id of the record that would be changed (null if it's a new record), a datetime column to store when the change was made, and a column to store the xml of the changed record (could maybe serialize my data object). Since I don't need history, after a change was approved, the real table would be updated and the PendingChange record could be deleted.
Any thoughts about that method?
Best Answer
Definitely store them in the main table with a column to indicate whether the data is approved or not.
When the change is approved, no copying is required. The extra work to filter the unapproved data is the sort of thing databases are supposed to do, when you think about it. If you index the approved column, it shouldn't be too burdensome to do the right thing.