Re: Table links and PKs

Sybrand,

Thank you for your insight.

I see the purity of your logic.

However<s>, in a case like this, relatively few phone numbers will be repeated and the number of repeats for any number will be small. I would therefore tend, in practice, to ignore purity and follow my original inclination for simplicity (of inserts and updates) and speed (less joins in the frequent Select statements for Company->Contacts->ContactPhones).

Do you think I am (very) wrong?

Thanks again,
Clive.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ul0au2tctq4k75_at_corp.supernews.com>...
> "CliveW" <clivew_at_clivewalden.com> wrote in message
> news:a7e7de55.0208061101.51be1569_at_posting.google.com...
> > Design tools I have complain when I try to set up a table with a
> > multi-field Primary Key (PK) with one field only linked as a Foreign
> > Key (FK) to the PK of another table.
> >
> > However this configuration often seems natural to me. Can anyone
> > explain?
> >
> > Example of how I might choose to use it is in a Contacts telephones
> > table:
> >
> > CONTACTS
> > -> Contact_Id PK
> > | ...Other fields
> > |
> > | PHONES
> > | PhoneNumber PK1
> > |_ Contact_Id PK2
> >
> > As multiple contacts may share the same telephone number, PhoneNumber
> > on its own is not an appropriate PK.
> >
> > Because my design tool complains I usually add a surrogate PK and put
> > a unique index on the two fields above.
> >
> > Why is the above design perceived as flawed?
> >
> > Thanks,
> > Clive.
>
> If I revert your statement
> > As multiple contacts may share the same telephone number, PhoneNumber
> > on its own is not an appropriate PK.
> you seem to say
> 'one contact can have only one phone'
> If that would have been true phone number and contact id would have
> constituted a composite key.
> However, as the phone number is unique on it's own, you can't have the
> contact id and the phone details in one table,as it would force you to
> repeat the phone number details. The way I look at it is there is a junction
> table hiding in your phone number table. You need 3 tables
> contact -> contactphone <- phone.
> Using Designer you would have created a n:m relationship between entities
> and Designer would have created the junction table for you.
>
> Hth
Received on Tue Aug 06 2002 - 22:33:12 CDT