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?