Maintaining historical data

[soniks]

Hi,

I am designing a data model which needs to be able to model the current state of all the values in each of the tables but also to be able to model what the values were at any specific time. I thought on way to do this is to use effective_from and effective_to date fields indicating the period for which a particular row is valid. This is repeated for all tables in the model. The problem I for see if when having to join tables as this would also require constraining based on a date for every table.

What is the best approach to model historical changes whilst also being able to select data based on the current snapshot of the table.

TIA

Soniks

[yogeshsr11]

You can create a HST database for each of the databases you want to maintain history of. E.g.: If you have a database Sales then create a history database Sales_HST or something similar. Create triggers INSERT, UPDATE, DELETE on individual tables which will move respective rows to HST database with Action type and date. Also have DateCreated and DateUpdated fields in all the tables. Add HistID to all the tables in HST database. This is simple and efficient solution for this scenario. Ofcourse you need to custamize it based on your requirements.