Best way to manage update-review-publish workflow?

Question

I manage an online catalog. Currently in-house personnel update it manually, and their changes are immediately visible. Now we want to add a verification step: Tom makes a change, Jerry approves it.

I see two paths, but neither seems elegant.

1. Keep a second, 'working copy' of the whole database.
2. Keep a second set of 'dirty' tables inside the same database.

Both of these seem to require a lot of code just for housekeeping, as well as double the work whenever the structure of a table changes.

Is there a better way? In case it matters, the DBMS is SQL Server, the web app is asp.net.

Edited to add:

1. The two changes I outlined above are both backward-compatible with existing code. I know I'm going to have to make some changes, but I can't alter every query.

2. I think my key constraints prohibit simply cloning rows and marking them 'pending'.

Let's say Supplier with SupplierID 99 has two Products. (Products can belong to only one SupplierID.) The Supplier's phone number has changed, so I clone the Supplier record, change the phone number, and mark it 'pending'. But the new record can't have an ID of 99, so there's no longer a way to connect it to either its Products or even the record it's intended to replace.

I suppose I could add an unconstrained identifier, SupplierPseudoID, but this seems just as complicated and error-prone as the above ideas.

Answer 1

Why do you need a copy of the tables? Why not just add an approved field on the table instead?

---

Answer to the Edit:

If you have a table like

id | name | text    | modified | etc
-----------------------------------
1  | aaaa | blabla  | 20100210 | xxx
2  | bbbb | yadayada| 20100212 | yyy
3  | cccc | asdfkad | 20090102 | zzz

you can just alter it to add a new field called appoved and make the primary key be both id and modified

id | name | text    | modified | etc | approved
-----------------------------------------------
1  | aaaa | blabla  | 20100210 | xxx | 1
2  | bbbb | yadayada| 20100212 | yyy | 1
3  | cccc | asdfkad | 20090102 | zzz | 1
3  | cccc | qwerklj | 20100219 | zzz | 0

You create a view that only brings you

id | name | text    | modified | etc
-----------------------------------
1  | aaaa | blabla  | 20100210 | xxx
2  | bbbb | yadayada| 20100212 | yyy
3  | cccc | asdfkad | 20090102 | zzz

By defining it as something like SELECT id, name, text, modified, etc FROM catalog WHERE approved = 1;, that way you only have to modify the "table" the queries select from. To avoid having to modify the insertion you should give approved a default value of 0 and modify the update queries to do something like

INSERT INTO catalog (id, name, text, modified, etc, approved) 
  VALUES (SELECT id, name, text, NOW(), etc, 0)

which would end up with something like

id | name | text    | modified | etc | approved
-----------------------------------------------
1  | aaaa | blabla  | 20100210 | xxx | 1
2  | bbbb | yadayada| 20100212 | yyy | 1
3  | cccc | asdfkad | 20090102 | zzz | 1
3  | cccc | qwerklj | 20100219 | zzz | 0

and the new bit of interface that you will have to do to "approve a field" would have to

UPDATE catalog SET approved = 1;
DELETE FROM catalog WHERE id = @id AND approved = 1 AND MIN(modified);

which would result in

id | name | text    | modified | etc | approved
-----------------------------------------------
1  | aaaa | blabla  | 20100210 | xxx | 1
2  | bbbb | yadayada| 20100212 | yyy | 1
3  | cccc | qwerklj | 20100219 | zzz | 1

This last bit could be simplified even more if you make a trigger or a stored procedure to do this.

This is a very vague example, adapt to your needs.

Answer 2

I would have an approved field and I would have a trigger on the field that would limit changes to aproved status to come only from users in a specified role of approver (which if you don't have a role or group type thing for your users you will also need so that you know who are authorized users and approvers. That way if Sam triesd to approve his own change, it can't happen. I would probably also have a mechanism for checking to ensure that an approver who makes a change must have his change approved by a different person.

Your application would also have to change to let general users of the catalog only see approved changes unless they are the person who initiated the change or the approvers.

Answer 3

Simply version your important table with statuses.

Same table, just extra rows. Add an "effective date" range to the table.

select * from catalog where item_code = '1234' and status = 'APPROVED' and
today >= start_date and (today <= end_date or end_date is null)

When you wish to change the data, copy the row, change the status to "REVIEW" (or whatever, however many steps you have).

Then your reviewers can see that.

When you "publish", the current "APPROVED" becomes "ARCHIVED", end_date = "today", and the "REVIEW" row becomes "ACCEPTED" with a null end_date and start_date = "today".

The nice part of this is that it's reasonably trivial to quickly "roll back" a change if you like, and you always have a history. Later, you can purge old ARCHIVED data, if you're so inclined.

You can also prestage items that don't go on sale (or whatever) until the first of the month.