Re: Design Question - building historic data and audit trail into the database

willy gates wrote:

> Hello all,
>
> I am developing a smallish 9i database running on a smallish server
> for 10 users and have come across a design question which I do not the
> experience to answer.
>
> We are storing policy details for a number of different
> savings/insurance products, the early design decision was made to opt
> for a complex design for the 'policy table' to reduce the redundancy
> and to make the addition of new products easy.
>
> The essence of the design is that an entry in the policy table is
> related to many rows of a policy details table.
>
> Our policy table has about 75,000 rows and the details table has about
> 1.5 million and the performance of the database is adequate.
>
> Now my problem..... Modifications may be made to a policy over the
> years, but I want a facility to look at what a policy looked like at
> any point in its life. I am inclined to add three new columns to the
> policy table and details table:
>
> valid from
> valid to
> person who added the row
>
> However it is possible that a policy may change 10 times in its
> lifetime which will mean that my tables are increasing in size at
> least 10 fold. However this is the most intuative solution so could I
> use date partitioning to make this work effectively?
>
> My colleague suggests having a separate set of tables identical to the
> policy tables to hold the old values of the policy each time it
> changes (along with a date and the id of the modifier) everytime the
> policy is altered. This way the original tables will work as
> previously but when I want to look at historic data I have to check
> these other tables to see if there are any entries. I am not sure
> about this approach as it makes the database more complex.
>
> I will be very grateful to hear opinions on these approaches.

My instinct would be to go for a duplicate table set loaded by an AFTER UPDATE -AFTER DELETE trigger.

That way you have clear physical separation between current data and older data. Then I would build an application into which one entered a single date and that displayed information pertinent only to that date.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
(remove one 'x' from my email address to reply)