Re: Historical data; how?

Tim X <timx_at_spamto.devnul.com> wrote in message news:<87hec7nmk5.fsf_at_tiger.rapttech.com.au>...
> >>>>> "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

i agree with the solution offered by Tim. though we do this a bit differently.

the way we have done this is by adding a RecordVersion column. recordversion 0 means current record and 1 for history record. we do not care about maintaining a chronological history as long as we can differentiate between current and history record. but if you need to maintain a chronological record then you can add a updatedate column to store when the record was inserted or updated. Received on Mon Jan 20 2003 - 17:43:32 CET