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

I am a little late to this party...

I have implemented pretty much of what Roland has described below. My requirement is for point in time record representation. I use only 1 table, with each record being timestamped with both a begin date and an end date. Every query into this table must be date qualified. I have arbitrarily defined the table's minimun date and its maximum date. Records are never deleted, but rather marked as inactive. Of course, unwanted data of a certain age can be purged by moving the min date forward and removing the unwanted records.

This design has so far served me well in most situations and was overall, pretty easy to code. The coding requires somewhat of a 2 dimensional representation of data within the table.

The fundamental assumption of this implementation is that a record, if it exists, exists for all time(from min to max).

Where I am having problems is in preserving this relationship accross tables(parent to child) where there is basically a many to 1 relationship between the parent and its children. Where as the parent table is 2 dimensional in nature, this child table is somekind of an array of 2 dimensional items. Its at this point that I have a hard time thinking about the problem and I go find some other problem to solve.

I know that there is a huge gap in my explanation the problem I am trying to solve here. I am an SQL novice, so the obvious may not be apparent to me. Hopefully somebody has wrestled with this problem before and has some suggestions.

Thanks!

> 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

-- 
Mike Landmeier
PosLogic
http://www.primenet.com/~poslogc