Primary Key Question

[harperwork18]

Hello, I'm still trying to grapple with primary keys. I have many
tables that, through the help of these forums, are normalized. But many
of them do not have--at least I do not think they have--natural keys.
So I'm using autonumbers all over the place, which makes me queasy. I
know there are debates on using autonumbers and please, I'd like to not
see a repeat here; I'd just like it if an expert can look at a few
tables (below) and tell me if indeed it seems I can't help but use
autonumbers...thank you, harper

tblComputerTests
TestID autonumber PK
TestName (like "CompTest" and "TechSelfAssess"
UseBegan (date)
UseEnded (date) -- over time we've retired some tests and developed new
ones

tblComputerTestAreas (tests have sections that receive scores, then the
scores are tallied)
TestID FK
TestAreaID autonumber PK
TestAreaName(i.e. MSWord, MSPowerPoint--these names duplicate: the
tests have areas that are the same--for example, virtually all computer
assessments we've used test MSWord skills)

tblEmployeesandTestScores
EmployeeID PK
TestAreaID
Score

Questions: 1) do you see any way that the first two tables can get rid
of autonumbers? 2) what is the value of having 2-field primary keys and
would the first table be a candidate for that (TestID and TestName) 3)
Is it generally a bad idea to use text for a primary key?

Thank you!!

 

[jahoobob via AccessMonster.com]

The "thing" about autonumbers is that they should be significant only to the
database, not the user. A social security number or employee badge number
could be PKs and they have uses other than communication between tables and
are not autonumbered. Your use of autonumber PKs looks fine to me. They are
being used in creating the relationships in a relational database.

 

[Pat Hartman\(MVP\)]

I use natural keys if I have them and if I am certain that they are not
changeable. I only use multi-field primary keys if the table does not have
any children. This arises primarily with junction tables where the keys
from tblA and tblB become the multi-field primary key. However, if the
junction table will have a related many-side table, I add an autonumber and
use a unique index to enforce the business rule that the combination of
tblAkey and tblBkey occur only once.

 

[Steve Schapel]

Regarding Social Security Numbers as Primary Keys - this assumes:
- everybody has one
- they are unique
My understandinng is that in practice these assumptions are not true, so
SSN should never be used as a PK.

 

[Steve Schapel]

Harperwork,

It wouldn't make sense to use a composite primary key (involving more
than one field) if one of them was an Autonumber. TestID and TestName
would not be a candidate for this in your first table. Here's the
question I would ask... will the data in the TestName field *always* be
unique? Or is it possible that you could want two Test records with the
same name (maybe repeating the name of a previously "retired" test)? If
they will always be unique, I would delete the TestID field from this
table, make the TestName the primary key, and use the TestName in the
TestAreas table as the FK. In my opinion, the *only* reason for adding
an Autonumber field to the Tests table is if there will be repeated
values in the TestName. But then, as you noted, there are those who
will take an opposing viewpoint.