Re: Composite Primary Key Design
From: Dkline (Dkline001_at_comcast.net)
Date: 12/29/04
- Next message: Duane Hookom: "Re: Meging Existing Tables"
- Previous message: Duane Hookom: "Re: Access Design"
- In reply to: Rick Brandt: "Re: Composite Primary Key Design"
- Next in thread: Jack MacDonald: "Re: Composite Primary Key Design"
- Reply: Jack MacDonald: "Re: Composite Primary Key Design"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Dec 2004 12:40:40 -0500
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
"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:33g770F3tdeteU1@individual.net...
> Dkline wrote:
>> 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.
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
- Next message: Duane Hookom: "Re: Meging Existing Tables"
- Previous message: Duane Hookom: "Re: Access Design"
- In reply to: Rick Brandt: "Re: Composite Primary Key Design"
- Next in thread: Jack MacDonald: "Re: Composite Primary Key Design"
- Reply: Jack MacDonald: "Re: Composite Primary Key Design"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
