Re: How to save a history of changes to a record?

Glenn,

I have an alternate approach that is actually an offshoot of your second approach. If the need for history is to be able to identify what was true at a single point in time, you will get better performance by abandoning the event type and propagating the unchanged data to the history row. This allows a requestor with a single request to get a consolidated picture of what was true at that point in time, which isn't so easy with the design you proposed. Although this a step away from fully normalized, the programming task is one of overlaying the changed column over the original row image prior to insert. Logic to guarantee the correct event type and null indicatation adds somewhat more complexity (my opinion only!) and nothing worse than looking a date of birth change event that is marked as a name change.

Good luck,

Roland DePratti
Cigna Corp

Edward Bear wrote:
>
> 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
Received on Sat Jan 11 1997 - 00:00:00 CET