Composite Primary Key Design

[Dkline]

I'm trying to figure out the best design for a new database for a composite
primary key (CPK) where one of the fields may be a NULL - a violation of a
primary key. We will be using on SQL Server 2000 with an Access front end.
Existing database is entirely in Access and we are upsizing. The CPK will be
the most fundamental in the database.

I am working on a life insurance database. Each case can have one or more
insureds - if multiple insureds normally two but theoretically could be
more.

We assign a policy number to each case e.g. "VUL100000". If this case has
more than one insured e.g a husband and wife - we currently append an "a" or
a "b" to set up a unique key for each of the insureds. If the policy has
only one insured, then a letter is not appended.

So if VL100000 has two insureds and VL100001 has one insured the keys would
be:

PolicyNumber
VUL100000a (for the husband or first insured)
VUL100000b (for the wife or second insured)
VUL100001 (no letter appended as it is a single insured

The above works as Primary Key. But what we want to do is to leave the
PolicyNumber alone and have a second field distinguish the record:

PolicyNumber PolicyNumberAlpha
VUL100000 a (for the husband or first
insured)
VUL100000 b (for the wife or second
insured)
VUL100001 (no letter appended as
it is a single insured

Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as
part of a CPK.

I suppose one solution would be to assign a "z" to the single insured for
the CPK. His policy number alone makes his record unique so assigning a
suffix in the PolicyNumberAlpha field is unnecessary.

What is the most efficient design to handle this?

 

[Rick Brandt]

I'm trying to figure out the best design for a new database for a
composite primary key (CPK) where one of the fields may be a NULL - a
violation of a primary key. We will be using on SQL Server 2000 with
an Access front end. Existing database is entirely in Access and we
are upsizing. The CPK will be the most fundamental in the database.

I am working on a life insurance database. Each case can have one or
more insureds - if multiple insureds normally two but theoretically
could be more.

We assign a policy number to each case e.g. "VUL100000". If this case
has more than one insured e.g a husband and wife - we currently
append an "a" or a "b" to set up a unique key for each of the
insureds. If the policy has only one insured, then a letter is not
appended.
So if VL100000 has two insureds and VL100001 has one insured the keys
would be:

PolicyNumber
VUL100000a (for the husband or first insured)
VUL100000b (for the wife or second insured)
VUL100001 (no letter appended as it is a single insured

The above works as Primary Key. But what we want to do is to leave the
PolicyNumber alone and have a second field distinguish the record:

PolicyNumber PolicyNumberAlpha
VUL100000 a (for the husband
or first insured)
VUL100000 b (for the wife or
second insured)
VUL100001 (no letter
appended as it is a single insured

Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't
work as part of a CPK.

I suppose one solution would be to assign a "z" to the single insured
for the CPK. His policy number alone makes his record unique so
assigning a suffix in the PolicyNumberAlpha field is unnecessary.

What is the most efficient design to handle this?

You need two tables (IMO). One for Policies and a related one for the
InsuredPersons. There would be a one-to-many relationship between these two
tables. Then every PolicyNumber can be associated with any number of
InsuredPersons. Policy table would have a single-field PK consisting of the
PolicyNumber and the InsuredPersons table would use a composite key of
PolicyNumber and an additional field to identify the person.

 

[Dkline]

Rick - thanks for the reply.

I left out a crucial element. An insured can be in many policies. A policy
can have many insureds. So I believe I will need a linking or union table.
Mea culpa for leaving that vital information out.

Don

 

[Jack MacDonald]

Use a linking (junction) table in a classic many-to-many relation
between people and policies. The bare requirements for the linking
table are foreign keys to the base tables. If required by your
business rules, you could add more fields than the bare linking
requirements into the linking table -- e.g. date when new party was
added to the policy, sub-policy letter ("a", "b", etc)

Rick - thanks for the reply.

I left out a crucial element. An insured can be in many policies. A policy
can have many insureds. So I believe I will need a linking or union table.
Mea culpa for leaving that vital information out.

Don

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Dkline]

So I could have a linking table with its own fields.

How does one build a form that makes sense to an end user - where they have
to pair up the policy number on one side and the insured on the other? I'm
building it and I have to be careful that I am pairing them up properly or
is that an idicator of poor table design?

 

[Jack MacDonald]

The typical interface is to have a "base" form that deals with the
policies. Within that form you create subform for managing the
people. The subform is based on the junction table, and it displays
(at a minimum), the foreign key to the people table, and is usually a
datasheet or continuous form. End result: the form displays the data
for a single policy with a list of the people who belong to that
policy. Access manages the "pairing-up" automatically via the "Linking
fields" properties of the subform.

Typically, the "people" foreign key will be a combo box linked to the
People table so that you deal with people's names, rather than their
ID number.

You could also build a complimentary form using the People table as
the base table, with a subform showing the Policies that are related
to that person. The thought process is identical.

So I could have a linking table with its own fields.

How does one build a form that makes sense to an end user - where they have
to pair up the policy number on one side and the insured on the other? I'm
building it and I have to be careful that I am pairing them up properly or
is that an idicator of poor table design?

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Dkline]

Thanks for the reply. I'm struggling with the linking table/sub form.

I am under the impression that the linking table would have only the fields
needed to pair up the records e.g. Policy # | InsuredID #

Other fields could be added to this table where the data is unique to the
pairing. That is not the case here so the linking table should be just the
two fields.

I guess what I'm missing as showing the rest of the record for each insured
in the subform if the subform is based on the linking table which has only
two fields.

I'm sorry if I'm being dense but I'm still missing a piece to this puzzle.

 

[Jack MacDonald]

See inline comments

Thanks for the reply. I'm struggling with the linking table/sub form.

I am under the impression that the linking table would have only the fields
needed to pair up the records e.g. Policy # | InsuredID #

Other fields could be added to this table where the data is unique to the
pairing. That is not the case here so the linking table should be just the
two fields.

Correct - additional fields in the linking table must pertain only to
the unique pairing of Policy# & Insured#. One such possible field that
comes to mind is InsuredDate -- by placing this field in the linking
table, it will allow each Insured person to have a unique date for
their coverage (may or may not be applicable for your situation)

I guess what I'm missing as showing the rest of the record for each insured
in the subform if the subform is based on the linking table which has only
two fields.

Base the linking table on a query that joins the linking table with
the third table.

Alternatively, and if there is a lot of information from the third
table that you want to display, you can create a second subform for
the third table's information. It would sit on your main form in
parallel with the original subform. It's a bit trickier to keep such a
form "in sync" with the active data, but is feasible
- place an unbound textbox on the main form
- use the OnCurrent event of the subform to populate the textbox with
the current value of the appropriate field
e.g. Me.Parent![Text3] = Me![Insured#]
- use the textbox as the MasterLinkingField into the secondary subform
- hide the textbox

I'm sorry if I'm being dense but I'm still missing a piece to this puzzle.

Not at all. Some of this stuff is not immediately obvious.



**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Dkline]

I now have the main form and the two subforms. I got ambitious and set an
onclick even so that when the user selects an Insured to be with the
selected Policy, it puts the InsuredID into the linking table along with the
PolicyNumber. Putting the values into the junction table works fine.

What I'm stuck on is updating the forms on the screen. I make my selection
in the InsuredsID field. Until I click on the other column [PolicyNumber] in
the subform in which I select the linking InsuredID, the InsuredID field
screen does not get updated.

The other subform has a query to show what insured(s) are selected for the
current policy. That doesn't update until I move the Main Form's record
selector.

I've tried DoCmd.Requery and everything else I can think of. How can I get
these to refresh or repaint or requery?

See inline comments

Thanks for the reply. I'm struggling with the linking table/sub form.

I am under the impression that the linking table would have only the
fields
needed to pair up the records e.g. Policy # | InsuredID #

Other fields could be added to this table where the data is unique to the
pairing. That is not the case here so the linking table should be just the
two fields.

Correct - additional fields in the linking table must pertain only to
the unique pairing of Policy# & Insured#. One such possible field that
comes to mind is InsuredDate -- by placing this field in the linking
table, it will allow each Insured person to have a unique date for
their coverage (may or may not be applicable for your situation)

I guess what I'm missing as showing the rest of the record for each
insured
in the subform if the subform is based on the linking table which has only
two fields.

Base the linking table on a query that joins the linking table with
the third table.

Alternatively, and if there is a lot of information from the third
table that you want to display, you can create a second subform for
the third table's information. It would sit on your main form in
parallel with the original subform. It's a bit trickier to keep such a
form "in sync" with the active data, but is feasible
- place an unbound textbox on the main form
- use the OnCurrent event of the subform to populate the textbox with
the current value of the appropriate field
e.g. Me.Parent![Text3] = Me![Insured#]
- use the textbox as the MasterLinkingField into the secondary subform
- hide the textbox

I'm sorry if I'm being dense but I'm still missing a piece to this puzzle.

Not at all. Some of this stuff is not immediately obvious.



**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security