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

I think the second way is the better approach, simply because it scales better to multiple tables. Also, the extra processing would be minimal, as you can create an index to the table based on the 'approved' bit, and you can specialize your queries to either pull approved (for viewing) or unapproved (for approving) entries.

Answer 3

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.

Answer 4

Given the SOx compliance movement that has been shoved in the face of most publically traded companies, I've had quite a bit of experience in this area. Usually I have been using a separate table with a time stamped pending changes with some sort of flag column. The person in charge of administration of this data gets a list of pending changes and can choose to accept or not to accept. When a piece of data gets accepted, I use triggers to integrate the new data into the table. Though some people don't like the trigger method and would rather code this into the stored procs. This has worked well for me, even in rather large databases. The complexity can get a little difficult to deal with, especially in dealing with a situation where one change directly conflicts with another change and what order to process these changes in. The table holding the request data can never be able to be deleted, since it holds the "bread crumbs" so to speak that are required in case there is a need to trace back what happened in a particular situation. But in any approach, the risks need to be assessed, such as what I mentioned with the conflicting data, and a business logic layer needs to be in place to determine the process in these situations.

I personally don't like the same table method, because in the cases of data stores that are constantly being changed, this extra data in a table can unnecessarily bog down the request on the table, and would require a lot more detail to how you are indexing the table and your execution plans.

Answer 5

As this is a web app i'm going to assume there are more reads than writes, and you want something reasonably fast, and your conflict resolution (i.e out of order approvals) results in the same behaviour -- latest update is the one that is used.

Both of the strategies you propose are similar in they both hold one row per change set, have to deal with conflicts etc, the only difference being whether to store the data in one table or two. Given the scenario, two tables seems the better solution for performance reasons. You could also solve this with the one table and a view of the most recent approved changes if your database supports it.

Answer 6

Yet another idea would be to have three tables.

• One would be the main table to hold the original data.
• The second would hold the proposed data.
• The third would hold the historical data.

This approach gives you the ability to quickly and easily roll back and also gives you an audit trail if you need it.

Answer 7

I work in a banking domain and we have this need - that the changes done by one user must only be reflected after being approved by another. The design we use is as below

1. Main Table A
2. Another Table B that stores the changed record (and so is exactly similar to the first) + 2 additional columns (an FKey to C and a code to indicate the kind of change)
3. A third table C that stores all such records that need approval
4. A fourth table D that stores history (you probably don't need this).

I recommend this approach. It handles all scenarios including updates and deletions very gracefully.

Answer 8

I would create a table with an flag and create a view like

 CREATE OR REPLACE VIEW AS 

  SELECT * FROM my_table where approved = 1

It can help to separate dependencies between the aprovement and the queries. But may be is not the best idea if need to make updates to the view.

Moving records might have some performance considerations. But Partitioned tables could do something quite similar.