SQL Server – How to Keep Full History of Values in Each Table

database-designsql servert-sql

How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify a date for which the value was "active" or in "validity".

In a dumb way, I would add to each table a "validity start date" column. And instead of copying every value, I would only copy values that changed, ad set all the other columns to NULL. However, this solution is really horrible, since it complexifies the queries, even if it takes less space.

Is there a specific technology (I am in the Microsoft world) that enables to do that, or a specific way to model the database to do that (except creating a table for each column of each table)?

I would like to mention that I am not a DBA but a dev. KR!

Best Answer

SQL Server has a feature, introduced in SQL Server 2016, called Temporal Tables. Using system-versioned temporal tables is essentially an out-of-the-box alternative to using triggers or application logic to maintain historical record of edits to a table.

Temporal tables do come with a number of limitations and restrictions, which may require alternative options (such as hand-crafted triggers & history tables), depending on your use case. You would need to review your full set of requirements, compared against the set of limitations.

Related Solutions

The best database design to keep track of fields with multiple values

Your new design constraint is such that there's a many-to-many relationship between pages and fields, and a many-to-many relationship between page-fields and field values. That means you'd have to do this:

Page
* Page_Id

Field
* Field_Id
* Field_Type_Id
* Field_Name

Page_Field
* Page_Field_ID
* Page_Id
* Field_Id

Page_Field_FieldValue
* Page_Field_FieldValue_ID
* Page_Field_ID
* FieldValue_Id

FieldValue
* FieldValue_Id
* FieldValue_Value
* Current_Value

"That's really gross" you're thinking. Yes, it is. I'd strongly suggest creating views (indexed or materialized if possible) to make retrieval easier. It doesn't really solve your problem of having to define everything for each new page, but at least you won't be storing the same data repeatedly.

The Field_Type table doesn't change at all. It's relation to Field is the same. I did not list it in order to reduce confusion.

I'd also caution about using the FieldValue.Current_Value field as you seem to be suggesting, as that will not really work if your application allows multiple users. Application state needs to be stored separately from application data, typically.

Sql-server – History table design for supertype/subtype

It sounds like what you want is an implementation of transaction-time temporal tables. It's harder than it sounds.

Valid time, capturing the history of a changing reality, and transaction time, capturing the sequence of states of a changing table, are orthogonal, and can thus be separately utilized or applied in concert. A table supporting both is termed a "bitemporal table".

Reference: Developing Time-Oriented Database Applications in SQL, by Richard Snodgrass, p 20.

This book was the only book I could lay my hands on when I was working on this stuff some years ago. Now you can download a PDF of the book and the accompanying code from the author's web page.

Newer material might be more useful. There are links on his web page.

Best Answer

Related Solutions

The best database design to keep track of fields with multiple values

Sql-server – History table design for supertype/subtype

Related Question