Let's say that I have a record in the database and that both admin and normal users can do updates.
Can anyone suggest a good approach/architecture on how to version control every change in this table so it's possible to roll back a record to a previous revision?
Best Answer
Let's say you have a
FOO
table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.Then, I would create a
FOO_HISTORY
table. This has all the columns of theFOO
table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key fromFOO_HISTORY
toFOO
. You might also add columns related to the revision such as the UserId and RevisionDate. Populate the RevisionNumbers in an ever-increasing fashion across all the*_HISTORY
tables (i.e. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second (i.e. do not putRevisionDate
into the primary key).Now, every time you update
FOO
, just before you do the update you insert the old values intoFOO_HISTORY
. You do this at some fundamental level in your design so that programmers can't accidentally miss this step.If you want to delete a row from
FOO
you have some choices. Either cascade and delete all the history, or perform a logical delete by flaggingFOO
as deleted.This solution is good when you are largely interested in the current values and only occasionally in the history. If you always need the history then you can put effective start and end dates and keep all the records in
FOO
itself. Every query then needs to check those dates.