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

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. Received on Tue Apr 29 2003 - 07:22:39 CDT