Versioning data in SQL Server so user can take a certain cut of the data

Question

I have a requirement that in a SQL Server backed website which is essentially a large CRUD application, the user should be able to 'go back in time' and be able to export the data as it was at a given point in time.

My question is what is the best strategy for this problem? Is there a systematic approach I can take and apply it across all tables?

Answer 1

Depending on what exactly you need, this can be relatively easy or hell.

Easy: Make a history table for every table, copy data there pre update or post insert/update (i.e. new stuff is there too). Never delete from the original table, make logical deletes.

Hard: There is an fdb version counting up on every change, every data item is correlated to start and end. This requires very fancy primary key mangling.

Answer 2

Just add a little comment to previous answers. If you need to go back for all users you can use snapshots.