Several keys in one table

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

Guest

Hi everyone

I use Access 2003 and have seen several sample databases which have at least
one table with multiple keys. I understand that there can be only one
Primary Key per table and what I'm seeing is a situation of several fields
contributing to the Primary Key and each having the key symbol next to it in
Design View.

I suppose that if I were creating the table, I'd create an absolutely unique
Primary Key (such as an Autonumber, Social Security Number, Staff ID Number
etc.). As I'm fresh to this, is there some good reason why the designers of
these databases preferred to have several fields contributing to the Primary
Key, rather than add another field which could be a single unique Primary
Key? I know that this may mean having an extra field but is that the only
reason to do it this way. I'd add that the samples that I've seen have been
in "reputable" areas/websites etc., rather than having been created by
someone who doesn't know what they're doing (like me!).

Thanks in advance.
 
I always create an autonumber primary key. However, there may be a good
reason to create a multi-field primary key for a junction table where there
would be no child records in another table. For instance you might have
tables:

tblEmployees
==============
empEmpID autonumber primary key
.....

tblInterests
==============
intIntID autonumber primary key
intTitle values like "hiking", "cooking", "bridge", "MS Access",...

tblEmployeeInterests
==============
emiEmpID part of two field primary key
links to tblEmployees.empEmpID
emiIntID part of two field primary key
links to tblInterests.intIntID

There would probably not be a child table off tblEmployeeInterests. At the
very least the two fields described in tblEmployeeInterests should be set as
a unique index.
 
You need a multi-field key if you allow an employee only one entry per day.
This may not be the primary key field of the table but a multi-field index
set to unique.
 
Thank you Duane and Karl.

These answers are a bit deep for me, but I'll do some homework! I just came
across the concept of a junction table and the possibility of a many-many
relationship so am getting my head around that.

I thought that there must be a good reason why the designer had used several
fields to create a compound primary key.
 
As I'm fresh to this, is there some good reason why the designers of
these databases preferred to have several fields contributing to the
Primary Key, rather than add another field which could be a single
unique Primary Key?
Click to expand...

Probably more lives and reputations are at risk in this particularly Holy
War than all the crusades put together... Look up Google groups on
comp.databases.theory for l...o...n...g threads on exactly this subject.

To the theorist: artificial keys have no place in R theory. The idea of a
PK is to identify exactly one row in the table and therefore exactly one
real-life instance of something. Calling one row 10298 and another row
22845 does nothing to prevent both of them referring to the same person
(or register entry, or widget supplier code, or whatever). The only
choice for a PK should be some combination of attributes that will always
identify a single instance of something.

To put it pragmatically: it's just not convenient for the DBMSs we use
nowadays to key tables on (FirstName, LastName, MiddleInitial,
DateOfBirth, FullAddress, DateOfEmplyment) and even then you _might_ run
into twin brothers who live together and signed up on the same day.
Therefore an artificial key is a practical solution to the limitations of
real-life implementations.

For the software marketer: my program does really nifty Autonumbers, so
I'll tell all my customers to use Autonumbers on every table they ever
create. I want to sell my software to as many people as possible,
regardless of whether they know how to use it as possible, and I can
easily make a wizard to add an autonumber key if the user doesn't know
how to design a table properly.

For the teacher: I don't have the time to go through all my students'
messy real life problems and I have to get onto reports and queries as
soon as possible. Putting an autonumber on every table means I can get
onto the 'real' Access work more quickly.

For the responsible designer: PKs have to be unique, knowable, and
stable. Artificial keys are practical and efficient, and can solve
problems that would otherwise make create bottlenecks in parts of db
design. The unthinking, blanket use of artificial keys will likely lead
to an incorrect design that fails to meet its business needs.


Just a thought... <g>


Tim F
 
Thank you Tim.

It seems that I might have opened a hornet's nest! I'll check out the
threads that you've mentioned. I must admit that my novice approach would
have been to have an autonumber field which I'd set as the Primary Key
because I'd already thought about twins, with the same eye colour, working
together etc.! Having said that, I appreciate what others have said about
multiple fields contributing to the Primary Key. I've already read a bit
about this in relation to a junction table.

More homework for me :-)
 
Back
Top