I've got a requirement that inside a SQL Server backed website that is basically a sizable CRUD application, the consumer should have the ability to 'go in time' and have the ability to export the information because it was in a given time.
My real question is what's the best technique for this issue? It is possible to systematic approach I'm able to take and put it on across all tables?
Based on what you require this is often relatively simple or hell.
Esasy: Create a history table for each table, copy data there pre update or publish place/update (i.e. new stuff can there be too. Never delte from original table, make logical remove.
Hard: there's a fdb version counting on every change, every data item is correlated to begin and finish. This involves very fancy primary key mangling.
Just give a little comment to previous solutions. If you want to return for those customers you should use snapshots.
The easiest option would be in order to save a duplicate of every row whenever it changes. You can do this most easily having a trigger. Your UI must provide search capabilities to return and discover the information.
This may provide an explosion of information, which will get worse when tables are up-to-date frequently, so the next thing is usually some type of data-based purge of older data.
An implementation you could think about is Team Foundation Server. It is able to perform historic queries (while using WIQL keyword ASOF). The after sales is SQL Server, so there can be some clues there.