Re: How to save a history of changes to a record?
Date: 1997/01/10
Message-ID: <32D7234D.6DAF_at_worldnet.att.net>#1/1
It sounds to me (if I read properly)
like you have a situation where you have subtypes at the conceptual level. In the
latter approach that you favor, you are simply storing all the subtypes in a single table along
with the supertype. This is called "absorbing" the subtype, and it is completely legitimate, and
actually does not violate normalization rules. Maintaining the trigger code might be a little
bit of a hassle over time, but your potential changes are probably pretty well defined.
I would tend to go with the single table approach because I think there is probably an advantage to keeping all the history in one place. If I had multiple levels of subtypes, I would tend to *not* want to use the single table approach, because I want simple declarative foreign keys to enforce the RI.
Thanks,
Bill
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 Fri Jan 10 1997 - 00:00:00 CET