Logical question on primary keys...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Access rookie - 08 Jan 2005 03:16 GMT

Hello,

This is a logical question on my understanding of primary keys. If a primary
key is to uniquely identify records in a table and that same key can be used
to tie together various aspects of that same entity, why have two primary
keys?
For example, I have a table that contains bio information, another with
medical history, another with drug plans for that individual, etc. I guess
the wisest thing to do is tie them all together with one key, right?
The way banks operate made me ask this question: if my social security
number uniquely identifies me (and I think they're not duplicated...emphasis
on I think), why do I need an account number?

Thinking like a rookie,

John.

Reply to this Message

Rick Brandt - 08 Jan 2005 03:38 GMT

> Hello,
>
[quoted text clipped - 13 lines]
>
> John.

You can have more than one account at the same bank right? So the field that
identifies *you* is not enough. The PK needs to identify the entity that each
row in the table represents.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Access rookie - 08 Jan 2005 04:03 GMT

Hey Rick,

Thanks for your reply and for putting up with my rookiness. Happy New Year!

John.

> > Hello,
> >
[quoted text clipped - 17 lines]
> identifies *you* is not enough. The PK needs to identify the entity that each
> row in the table represents.

Reply to this Message

Van T. Dinh - 08 Jan 2005 04:12 GMT

1. You can have more than 2 bank accounts ...

2. An account may be in more than 1 names ...

3. Some people may not have S.S.N. (depending on the country) ...

4. Someone may turn up at the bank ith $10 million deposit and forgets his
/ her S.S.N. The bank obviously doesn't want to turn the potential client
away ...

Signature

HTH
Van T. Dinh
MVP (Access)

> Hello,
>
[quoted text clipped - 12 lines]
>
> John.

Reply to this Message

Tim Ferguson - 08 Jan 2005 11:26 GMT

"=?Utf-8?B?QWNjZXNzIHJvb2tpZQ==?="
<Accessrookie@discussions.microsoft.com> wrote in
news:4C3BA53C-1C49-4BAD-A4AD-7A97C55FA73C@microsoft.com:

> If a
> primary key is to uniquely identify records in a table and that same
> key can be used to tie together various aspects of that same entity,
> why have two primary keys?

A table cannot have two primary keys: it may have one PK made up of more
than field; or it may have several candidate keys of which only one must be
Primary.

> For example, I have a table that contains bio information, another
> with medical history, another with drug plans for that individual,
> etc. I guess the wisest thing to do is tie them all together with one
> key, right? The way banks operate made me ask this question: if my
> social security number uniquely identifies me (and I think they're not
> duplicated...emphasis on I think), why do I need an account number?

A medical history table is likely to have several records for each patient;
so it would probably have a PK made up of the PatientID and some kind of
episode identifier, e.g. PresentationDate or FileNumber etc.

DrugPlans is definitely many-to-one.

As I understand it, SSNumbers do not uniquely identify an individual --
there are mistakes and duplicates and people who don't have one at all. And
bank accounts have a complex relationship with people: some people have
lots of accounts; others don't have any at all; and some accounts belong to
institutions rather than people anyway. Then, of course, there are joint
accounts that belong to several people at once.

The golden rules for PKs are: Uniqueness, Stability, and Availability.

Hope that helps

Tim F

Reply to this Message