How to store multiple versions of DB data for auditing/merging?

Question

I'm using ASP.net MVC and SQL Server, but I don't think the answer will depend on this architecture, and I'm equally interested in solutions that use other architectures. So...

I have an object (row) within SQL that needs to be: versioned, updated by multiple people, approved, and merged.

OPTION A) For a quick-and-dirty solution, I'm doing the following. It's in a controlled environment where there's one update per quarter and it's done by a finite set of roles in a given order, so while this looks really messy, it actually works decently and I'd continue with it without a better option.

Main Table:

ID & Year <-- combined to form primary key (this fulfills my versioning -- I only need one per year, or one per quarter in some cases)
A handful of fields that can't be edited, like foreign keys
Value1
Value2
...

Editing Table:

Edit Table ID
Main Table ID & Year
Value1 changes by Person A
Value2 changes by Person A
Value3 changes by Person A
Name of Person A
Value1 changes by Person B
Value2 changes by Person B
Value3 changes by Person B
Name of Person B
...

OPTION B) Now I'm getting into an instance where instead of 1 set of updates per quarter, they might be ongoing, and possibly initiated by different people. I've done something like this in the past:

Main Table:

Instance ID (Primary Key)
Main ID (this would be the primary key if there weren't potentially several instances)
Flag (edit, live, archive, delete)
Person
Value1
Value2
Value3

So, I would have the base row (instance id 1, main id 1, flag "live"). Whenever someone updated, I'd create a new row (instance id 2, main id 1, flag "edit"). At some point the reviewer would be able to see the live data and any suggested changes. The reviewer would then choose which changes to incorporate, and I'd create a new row with the live flag, and change all old 'edit's and 'live's to 'archive'.

So... my questions are:

1) Which option is best? B seems better, but is it that much better? Is there another option to handle this that I'm not thinking of? 2) If B is the way to go, are there any libraries / examples that can help me out? This seems like a somewhat complicated solution with a lot of comparing and keeping track of variables. Similarly, how would I best handle the reviewing/comparison within ASP.net MVC. If I have a Models.Main, should I add a exention method called Compare(Models.Main) so that Live_Row.Compare(Edit_Row) would return just the deltas?

Answer 1

I can't comment on your MVC questions but option B looks fairly sensible to me. For our projects we utilize a trigger on the table that is being updated which saves a record of the changes to the most interesting values in the table plus the users login name, a unique identifier and a time stamp into a "shadow" table for revision/edit logging.