Linking data by a primary key

[RitchieJHicks]

I am designing an insurer database and am struggling to get my head around
one issue.

When the policy is set up, it has a primary key which is set by the
AutoNumber option. It also contains customer and vehicle details.

The problem starts when there is an amendment. Say, for example, the
customer calls to change their vehicle. Changing the details isn't an issue,
but my client wishes the database to "remember" the previous details (so in
effect having a historical list of amendments per policy)

Can anyone tell me the correct method in dealing with this? I really can't
understand it.

Thanks.

 

[Bernard Peek]

I am designing an insurer database and am struggling to get my head around
one issue.

When the policy is set up, it has a primary key which is set by the
AutoNumber option. It also contains customer and vehicle details.

The problem starts when there is an amendment. Say, for example, the
customer calls to change their vehicle. Changing the details isn't an issue,
but my client wishes the database to "remember" the previous details (so in
effect having a historical list of amendments per policy)

Can anyone tell me the correct method in dealing with this? I really can't
understand it.

One approach is to copy the details from the old record, make the
changes, save the new record and flag the old record as 'stale.'

The PK issue is a bit of a red herring here. If you create a new record
in a table with an autonumber it will be given a new ID. If you consider
this to be a transaction number rather than an identifier for a
particular account then it makes more sense.

 

[Klatuu]

Your basic relational design is incorrect. The vehicle information should
not be in the same table as policy table or the customer table. You really
need 3 tables here. - A Customer table, a Policy table, and a Vehicle table.
Since the vehicle is related to a Policy, a vehicle record should carry the
the primary key of the policy record it relates to as a foreigh key.

This gives you optimim flexibility. A Customer can have multiple policies
and a policy can cover multiple vehicles.

The only other isssue is knowing whether the vehicle is currently covered,
or was previously covered. That you can do with coverage begin and end dates.