relating two tables

[george]

Hi to all,

I work on an insurance project.I have a table,
tblPolicies, which has a triple primary key: BranchID,
CompanyID, PolicyN. To this table I want to relate another
table, tblPolicyDetails. What is the best way to do this?

1. in the second table create an auto number primary key
and a foreign key (BranchID, CompanyID, PolicyN) and then
relate the primary key to the foreign key (BranchID to
BranchID, CompanyID to CompanyID, PolicyN to PolicyN)

2. in the second table create the same triple primary key
as in the first table and then connect the two primary
keys (as before) in a one to one relationship

3. some other possibility?

any help appreciated, george

 

[John Vinson]

Hi to all,

I work on an insurance project.I have a table,
tblPolicies, which has a triple primary key: BranchID,
CompanyID, PolicyN. To this table I want to relate another
table, tblPolicyDetails. What is the best way to do this?

1. in the second table create an auto number primary key
and a foreign key (BranchID, CompanyID, PolicyN) and then
relate the primary key to the foreign key (BranchID to
BranchID, CompanyID to CompanyID, PolicyN to PolicyN)

2. in the second table create the same triple primary key
as in the first table and then connect the two primary
keys (as before) in a one to one relationship

3. some other possibility?

Probably neither of these. You can do it two ways, and different folks
here have strong opinions in favor of both:

1. In tblPolicies, add an autonumber "surrogate key", PolicyID; in
tblPolicyDetails add a Long Integer foreign key field PolicyID and
join the two tables on PolicyID. This will automatically and
appropriately be a one to many relationship, assuming that there is at
least sometimes going to be more than one Detail record for a policy.
With this approach the BranchID, CompanyID, and PolicyN fields would
not exist in tblPolicyDetails. You should set a unique Index on the
three-field combination in tblPolicy (since it's no longer the Primary
Key, uniqueness will not be enforced otherwise.)

2. Use the "natural key" - set up tblPolicyDetails with a *FOUR* field
Primary Key, BranchID, CompanyID, PolicyN, and DetailNo. Use the first
three fields as the Foreign Key and join on the three fields.

In this case I'd recommend option 2, since your three-field key
appears to meet the criteria for a good Primary Key candidate: it's
unique, stable, and reasonably short.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[george]

-----Original Message-----


Probably neither of these. You can do it two ways, and different folks
here have strong opinions in favor of both:

1. In tblPolicies, add an autonumber "surrogate key", PolicyID; in
tblPolicyDetails add a Long Integer foreign key field PolicyID and
join the two tables on PolicyID. This will automatically and
appropriately be a one to many relationship, assuming that there is at
least sometimes going to be more than one Detail record for a policy.
With this approach the BranchID, CompanyID, and PolicyN fields would
not exist in tblPolicyDetails. You should set a unique Index on the
three-field combination in tblPolicy (since it's no longer the Primary
Key, uniqueness will not be enforced otherwise.)

2. Use the "natural key" - set up tblPolicyDetails with a *FOUR* field
Primary Key, BranchID, CompanyID, PolicyN, and DetailNo. Use the first
three fields as the Foreign Key and join on the three fields.

In this case I'd recommend option 2, since your three- field key
appears to meet the criteria for a good Primary Key candidate: it's
unique, stable, and reasonably short.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.

Thanks a lot for your answer. I'll follow the second
method that you propose using an autonumber for DetailNo.
However just for the sake of better grasping the meaning
behind your suggestion, why is it better to use a four key
primary key in the second table instead of a three key
primary key? Is it just to avoid a one to one
relationship? (I suppose that a specific policy will
always have one and only one policydetail record, I don't
see why it would ever need more than one)

thanks again, george

 

[John Vinson]

Thanks a lot for your answer. I'll follow the second
method that you propose using an autonumber for DetailNo.
However just for the sake of better grasping the meaning
behind your suggestion, why is it better to use a four key
primary key in the second table instead of a three key
primary key? Is it just to avoid a one to one
relationship? (I suppose that a specific policy will
always have one and only one policydetail record, I don't
see why it would ever need more than one)

If you have a one to one relationship, and no *strong* reason to have
two tables, then it's usually better just to include the policydetail
fields in the policy table. Why mess around with the overhead of a
second table, joins, etc. if there will only be one record anyway?

But in practice one to one relationships are quite uncommon in the
real world. It's policy detail*S* - plural, right there in the name!
What is actually stored in the PolicyDetails table? Are you sure that
there will always be one, and only one, detail needed per policy?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[george]

I see your point. I'll follow your advice, thank you for
your time and efford.

george