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?

This might be helpful: http://blog.schauderhaft.de/2009/11/29/versioned-data/ — Jens Schauder, Mar 07 '11 at 14:18
Related questions about **temporal databases** (that's the name for what you're trying to achieve): http://stackoverflow.com/q/3875078/95 and http://stackoverflow.com/q/800331/95 — Marek Grzenkowicz, Mar 07 '11 at 14:23

score 5 · Accepted Answer · edited Jun 11 '14 at 10:51

5

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.

edited Jun 11 '14 at 10:51

Montag451

1,168
3
14
30

answered Mar 07 '11 at 14:26

TomTom

61,059
10
88
148

Thanks TomTom - I like the sound of the 'easy' approach! – Mark Robinson Mar 07 '11 at 14:39

score 1 · Answer 2 · answered Mar 07 '11 at 14:22

1

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

answered Mar 07 '11 at 14:22

ceth

44,198
62
180
289

Interesting link. Thanks. As the system does not have a great deal of users this could work. Concerned about the long term viability though. – Mark Robinson Mar 07 '11 at 14:37

score 0 · Answer 3 · answered Mar 07 '11 at 14:18

0

The simplest solution is to save a copy of each row whenever it changes. This can be done most easily with a trigger. Then your UI must provide search abilities to go back and find the data.

This does produce an explosion of data, which gets worse when tables are updated frequently, so the next step is usually some kind of data-based purge of older data.

answered Mar 07 '11 at 14:18

Ken Downs

4,707
1
22
20

1

This explains how to store the data, but how do you query for it? – RQDQ Mar 07 '11 at 14:26
The same way you query for anything else -- assuming the UI has query-by-form or other related technology (OP did say this was CRUD), two additional date boxes can be put in specifying date range. – Ken Downs Mar 07 '11 at 18:13

score 0 · Answer 4 · answered Mar 07 '11 at 14:18

0

An implementation you could look at is Team Foundation Server. It has the ability to perform historical queries (using the WIQL keyword ASOF). The backend is SQL Server, so there might be some clues there.

answered Mar 07 '11 at 14:18

RQDQ

15,461
2
32
59

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

4 Answers4