|
|
Posted by Tony Rogerson on 05/18/06 10:55
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" <gmusher@gmail.com> wrote in message
news:1147907580.051286.214000@38g2000cwa.googlegroups.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!!!
>
Navigation:
[Reply to this message]
|