How would you maintain a history in SQL tables?

Question

Hi all,

I am designing a database to store product informations, and I want to store several months of historical (price) data for future reference. However, I would like to, after a set period, start overwriting initial entries with minimal effort to find the initial entries. Does anyone have a good idea of how to approach this problem? My initial design is to have a table named historical data, and everyday, it pulls the active data and stores it into the historical database with a time stamp. Does anyone have a better idea? Or can see what is wrong with mine?

Thanks a lot!

Jason

Answer 1

It's a much broader topic than it initially seems. Martin Fowler has a nice narrative about "things that change with time".

Answer 2

First, I'd like to comment on your proposed solution. The weak part of course is that, there can, actually, be more than one change between your intervals. That means, the record was changed three times during the day, but you only archive the last change.

It's possible to have the better solution, but it must be event-driven. If you have the database server that supports events or triggers (like MS SQL), you should write a trigger code that creates entry in history table. If your server does not support triggers, you can add the archiving code to your application (during Save operation).

Answer 3

You could place a trigger on your price table. That way you can archive the old price in an other table at each update or delete event.

Answer 4

IMO your approach seems sound if your required history data is a snapshot of the end of the day's data - in the past I have used a similar approach with overnight jobs (SP's) that pick up the day's new data, timestamp it and then use a "delete all data that has a timestamp < today - x" where x is the time period of data I want to keep.

If you need to track all history changes, then you need to look at triggers.

Answer 5

I agree with htaler, I usually also go with the update trigger on the live table. On update insert new record to the historical table.

Then you can have a sql job scheduled to clean up too old historical data.

Answer 6

I would like to, after a set period, start overwriting initial entries with minimal effort to find the initial entries

We store data in Archive tables, using a Trigger, as others have suggested. Our archive table has additional column for AuditDate, and stores the "Deleted" data - i.e. the previous version of the data. The current data is only stored in the actual table.

We prune the Archive table with a business rule along the lines of "Delete all Archive data more than 3 months old where there exists at least one archive record younger than 3 months old; delete all archive data more than 6 months old"

So if there has been no price change in the last 3 months you would still have a price change record from the 3-6 months ago period.

(Ask if you need an example of the self-referencing-join to do the delete, or the Trigger to store changes in the Archive table)