Your Professional Advice Please - Design

[vbnetguy]

Hi All, I have read MANY posts on how to track changes to data over
time

It appears there are two points of view

1. Each record supports a Change Indicator flag to
indicate the current record
(would this be EVERY table?)
2. Each table is duplicated as an archive table and
triggers are used to update archive
Can someone give me some guidance based on REAL world experience
which works best for them?
My scenario - I have insurance policies and must track history as
policies are updated by customer service reps.
Imagine many tables Policy>LifePol>LifePolRiders

AccidentPol >etc...
DIPol>DIPolRiders

To me the archive table scenario does not seem scalable at all....some
guidance on design would be aprreciated...Thanks!!!

[Tony Rogerson]

It really depends what you mean by history.

I use duplicate table and audit triggers to capture any update/insert/delete
activity on the row and hold it along with mac address and user id to track
stuff but that might be overkill for your situation, the audit tables are
there in case we need to recover something or track who changed what and to
what, it alows me to not worry about history in queries because they don't
contain extra syntax to get rid of the baggage in order to get the current
record.

In the same system we have other implementations of history, for instance
coverage (research analyst covering a sector) - I store this in the same
table with start/end dates because its true history rather than auditing,
and yes - the audit triggers and duplicate table exist on that too....

We need more depth, one possible answer would be your central policies table
that holds the current information and you have a seperate notes table
holding the notes that user made when dealing with the policy.

tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"vbnetguy" <gm*****@gmail.com> wrote in message
news:11**********************@38g2000cwa.googlegro ups.com...

Hi All, I have read MANY posts on how to track changes to data over
time

It appears there are two points of view

1. Each record supports a Change Indicator flag to
indicate the current record
(would this be EVERY table?)
2. Each table is duplicated as an archive table and
triggers are used to update archive
Can someone give me some guidance based on REAL world experience
which works best for them?
My scenario - I have insurance policies and must track history as
policies are updated by customer service reps.
Imagine many tables Policy>LifePol>LifePolRiders

>AccidentPol >etc...
>DIPol>DIPolRiders

To me the archive table scenario does not seem scalable at all....some
guidance on design would be aprreciated...Thanks!!!

[rcamarda]

This wont be much help on how to, but here it goes:
In data warehouse speak, you are talking about a slowly changing
dimension also know as a type 2 dimension. (Ralph Kimbal).
I use this type of data in my datawarehouse. As example, I need to keep
track of a students home campus over time. As we open new campuses, or
students move around the area, students will change their 'home
campus'.
I need to associate student's registrations to a version of their
student record that reflects their home campus at the time of the
registration. Without this, reports would show the students current
campus for a registration from 5 years ago. If the campus opened in
spring 2006, the report would show the student registering for classes
5 years in a campus that didnt exist.

The way I track these changes is with an ETL tool from Cognos: Data
Manager. Once I have built the model, I indicated which fields to
track, and the tool does the rest.
It adds fields to the data.
1. Surrogate key
2. Current Flag indicator
3. Create Date
4. End date
5. effective date
6. update date
I'm glad I dont have to tackle this problem w/o a tool.
I would have a separate table to track the changes. A trigger that
would check for the changes you are looking for. If a change is found:
1. End the current record by putting date in the end_date field.
2. set current_ind field from Y to N
3. get next surrogate key (this is just a int field starting at 1)
4. Insert new record with new surrogate key
5. set current indicator to Y
the tricky bit is how to know when a change happend, after that its
pretty straight forward.
HTH
Rob