>>>>> "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.
