Bruce said:
As a relatively new Access user I perhaps don't have the diverse experience
of some who post here, but I have to disagree with the contention that it is
"rarely a good idea" to use an autonumber PK. The most commonly used fields
are often subject to change: people and company names change, addresses
change, etc. I question the usefulness of the phone book analogy, since any
comparison to a paper document is more in the nature of a thought experiment
than an actual situation.
Compound field PKs have their uses, but can make relationships difficult to
manage. The PK does not need to be useful to the user. I have made a
training records database with an autonumber PK in the parent table. I
relate that field to a corresponding number field in the child table, but I
never see it or work with it directly. I could probably combine enough
fields to come up with a unique combination, but I can't think of a reason
for doing so. I use queries to customize the order in which records appear.
Bruce, Thank you for your comments. I'm glad you've correctly seen the
phone directory as a thought process/analogy rather than an example as
such.
In case I didn't make my point clear, I'll say a few more words. I'm
talking about performance. If this paper copy directory was sorted by
telephone number and I asked you, a human, to extract all the phone
numbers for people whose last name began with an 'S', you would have
to look at every entry and it would take a long time (bad
performance). Even if said book had an index on last name that told
you the page number, it wouldn't make your task much easier. If the
list was by last name order, it would take you mere seconds to rip out
the relevant pages (vastly better performance).
A Jet database's logical index works more effectively than a paper
equivalent but it hold true that having the data in logical *and*
physical order will give a distinct advantage. You may customize the
order in queries but there will be less work to be done if the data is
already in the correct order i.e. your quires will run faster.
I can't think of a better example, so I'll break my own rule and
continue with the telephone directory <g>. As a key, you are proposing
(phone number)
and I'm proposing
(last name, first name, phone number)
You have your reasons (to make 'relationships' easier to manage) and I
have mine (to make queries run faster). So we'll have both! We'll
create a unique index for each. They are both equally 'powerful'.
But how do we decide which shall be the *primary* key (PK)? In
theory, it makes no difference. In practice, we should look for the
'special' meaning of the PK in the product's implementation.
There is no advantage in making your key the PK. You can still define
your 'relationships' on the unique index and maintain referential
integrity plus you've only got one column for the simplicity you
desire.
However, if we make mine the PK we get the performance advantage of a
sensible physical order (clustered index).
So it's a win-win situation. The mistake you made is assuming the
wrong 'special' meaning of the PK in Jet.
Jamie.
--
