Logical question on primary keys...

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.

Access rookie wrote:
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.

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.


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

Hey Rick,

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

John.

"Rick Brandt" wrote:

Access rookie wrote:
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.

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.


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

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 ...

--
HTH
Van T. Dinh
MVP (Access)





"Access rookie" wrote in message
...
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.

"=?Utf-8?B?QWNjZXNzIHJvb2tpZQ==?="
wrote in
:

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 a Uniqueness, Stability, and Availability.

Hope that helps


Tim F