Logical question on primary keys...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...

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.
 
Hey Rick,

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

John.
 
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 ...
 
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?
Click to expand...

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?
Click to expand...

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
 
Back
Top