How to save a history of changes to a record?
Date: 1997/01/10
Message-ID: <32D68754.5917_at_100acrewood.com>#1/1
Hi,
I'm designing a database for a medical benefits company. For certain
types of records we need to save a history of changes. I've modeled a
couple of different approaches to this problem. In one case, I set up a
subcategory stucture with a parent "history event" table and associated
category tables for each of the possible types of changes. This was all
nicely normalized to 4NF, but I end up with a lot of tables and some
fairly convoluted queries.
The other approach (which I'm leaning towards now) is to use a single
history table. This table has a list of attributes that correspond to
the attributes that might be changed in the parent entity. For any given
change event, most of the attributes in the history table will be null.
An "event type" attribute is used in conjunction with a case statement
in the Insert/Update triggers to enforce data integrity. For example, if
the event type is "D" then the date of birth attribute must not be null,
and all other attributes are ignored. The advantage is that I can
retrieve a category of changes or the entire history of changes from one
table using one parameterized query. This table will see a fair amount
of activity.
In your experience, which of these solutions will give me the fewest
hassels down the road? Is there another alternative that I haven't
considered? Thanks in advance for your ideas.
Regards,
Glenn Minch
glennm_at_cpts.com
"One noodle, long enough, is a meal." - Sparrow