Re: Historical data; how?

>>>>> "Karel" == Karel Miklav <karel_at_inetis.spppambait.com> writes:

 Karel> Hi folks, I have some tables, where I should remember
 Karel> histories of every column. Ie. I should track how a person's
 Karel> (licence id) changes over time and be able to find him later
 Karel> by any of his (licence ids) shown. I could create related
 Karel> tables for _every_ column but... there must be a better way?

 Karel> Thanks, Karel Miklav

One way I've seen this done is to have a date_changed column which contains the date the table details were changed. The idea is you keep all records - when something changes, you set the date_changed column of their current record to the current date, insert a new record and set the date_changed column of the new record to null.

Using this technique, you can always find the current record by selecting the record which has null for the date_changed field and you can create a report showing the change history by ordering on the date_changed field. You can also restrict changes using triggers etc - for example if you are only allowed to change your details once every X days, or 10 times or whatever, its quite easy to create a trigger to enforce these restrictions.

Of course, this solution is not very good if you have frequent changes or if only one attribute in an entity is ever changed and the entity has a lot of attributes - in either case, I would probably consider breaking the entity up into multiple entities or possibly keep a audit table which is populated by an after update trigger which records which entity was changed and its old value.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!