How to implement historical versioning?

Question

We are in the early stages of building a large C# MVC2 app (we also employ Sharp architecture and Nhibernate as part of the ecosystem) on SQL 2008 R2, and one of the requirements is that all database row versions are accessible for a given period of history.

We have toyed with the idea of a layout similar to:

id (PK)
recordId
versionId

and having each edit to a record result in a new record being created with the same recordId and an incremented versionId. Record display would then be done with something along the lines of SELECT ... WHERE recordId = X AND versionId = MAX(versionId)

Snapshotting on every transaction would not work (too many? and not accessible from within the application easily).

But we are curious as to what other implementations have been tried with success, or potential problems with our proposal.

Answer 1

You seem to be alluding to a temporal table. Three approaches:

Valid-state table: appending two 'timestamp' columns (e.g. of type DATETIME), one specifying when the row became valid and one specifying when the row stopped being valid, the intervening time is being the period of validity of the row

Transaction-time state table: associates with each row the period of time that row was present in the monitored table, thereby allowing the state of the monitored table at any previous point in time to be reconstructed.

Bitemporal table: capturing both valid time and transaction time, simultaneously records the history of the enterprise, while also capturing the sequence of changes to the record of that history.

Source: Developing Time-Oriented Database Applications in SQL (Richard T Snodgrass).