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.
You might want to concider datetracking idea, like Oracle uses in Apps.
There are two additional columns in the table, start date and end date,
which show when this record was (is) effective. I don't think Oracle
patented this idea.
Received on Tue Apr 29 2003 - 23:06:30 CDT
