What's the best way to store changes to database records that require approval before being visible?

Question

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?

Answer 1

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.

Answer 2

Size is your enemy. If you are dealing with lots of data and large numbers of rows, then having the historical mixed in with the current will hammer you. You'll also have problems if you join out to other data with making sure you've got the right rows.

If you need to save the historical data to show changes over time, I would go with the separate historical, table that updates the live, real data once it's approved. It's just all-around cleaner.

If you have a lot of datatypes that will have this mechanism but don't need to keep a historical record, I would suggest a common queue talbe for reviewing pending items, say stored as xml. This would allow just one table to be read by administrators and would enable you to add this functionality to any table in you system fairly easily.