How to Store Historical Data

user_106672 0
Viewed 144169

Some co-workers and I got into a debate on the best way to store historical data. Currently, for some systems, I use a separate table to store historical data, and I keep an original table for the current, active record. So, let's say I have table FOO. Under my system, all active records will go in FOO, and all historical records will go in FOO_Hist. Many different fields in FOO can be updated by the user, so I want to keep an accurate account of everything updated. FOO_Hist holds the exact same fields as FOO with the exception of an auto-incrementing HIST_ID. Every time FOO is updated, I perform an insert statement into FOO_Hist similar to: insert into FOO_HIST select * from FOO where id = @id.

My co-worker says that this is bad design because I shouldn't have an exact copy of a table for historical reasons and should just insert another record into the active table with a flag indicating that it's for historical purposes.

Is there a standard for dealing with historical data storage? It seems to me that I don't want to clutter my active records with all of my historical records in the same table considering that it may be well over a million records (I'm thinking long term).

How do you or your company handle this?

I'm using MS SQL Server 2008, but I'd like to keep the answer generic and arbitrary of any DBMS.

versioning database-design
11 Answers

In SQL Server 2016 and above, there is a new feature called Temporal Tables that aims to solve this challenge with minimal effort from developer. The concept of temporal table is similar to Change Data Capture (CDC), with the difference that temporal table has abstracted most of the things that you had to do manually if you were using CDC.

user_19237330

I Know this old post but Just wanted to add few points. The standard for such problems is what works best for the situation. understanding the need for such storage, and potential use of the historical/audit/change tracking data is very importat.

Audit (security purpose) : Use a common table for all your auditable tables. define structure to store column name , before value and after value fields.

Archive/Historical: for cases like tracking previous address , phone number etc. creating a separate table FOO_HIST is better if you your active transaction table schema does not change significantly in the future(if your history table has to have the same structure). if you anticipate table normalization , datatype change addition/removal of columns, store your historical data in xml format . define a table with the following columns (ID,Date, Schema Version, XMLData). this will easily handle schema changes . but you have to deal with xml and that could introduce a level of complication for data retrieval .

user_5575160
Related