If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking data by a primary key
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. |
#2
|
|||
|
|||
Linking data by a primary key
In message ,
RitchieJHicks writes 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. -- Bernard Peek |
#3
|
|||
|
|||
Linking data by a primary key
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. -- Dave Hargis, Microsoft Access MVP "RitchieJHicks" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|