Re: FAQ? factors influencing choice of data type for primary key

From: Brendan Reynolds (brenreyn)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 20:04:44 +0100

This tends to be one of those 'religious war' issues with some people, who
have very strong views for or against the use of AutoNumbers.

In the past, I tended to use AutoNumbers in all tables, for consistency.
Knowing that the primary key of all my tables was a single long integer
field made it easier to write re-usable code that could be used with any
table.

Lately, however, I'm more likely to use a natural key if it is a) simple (a
single field) and b) rarely changes. The advantage is that queries require
far fewer joins. For example, in a current project, a table 'tblAccident' is
related to four other tables, tblAccidentLevel, tblAccidentLocation,
tblAccidentStatus, and tblAccidentType. Using AutoNumbers, queries look
something like this ...

SELECT tblAccidentWithAutoNumbers.AccidentNumber,
tblAccidentWithAutoNumbers.AccidentDate, tblAccidentLocation.LocationCode,
tblAccidentType.TypeCode, tblAccidentLevel.LevelCode,
tblAccidentStatus.StatusCode, tblAccidentWithAutoNumbers.AccidentCost,
tblAccidentWithAutoNumbers.AccidentNote
FROM tblAccidentStatus INNER JOIN (tblAccidentLevel INNER JOIN
(tblAccidentType INNER JOIN (tblAccidentLocation INNER JOIN
tblAccidentWithAutoNumbers ON tblAccidentLocation.LocationNumber =
tblAccidentWithAutoNumbers.LocationNumber) ON tblAccidentType.TypeNumber =
tblAccidentWithAutoNumbers.TypeNumber) ON tblAccidentLevel.LevelNumber =
tblAccidentWithAutoNumbers.LevelNumber) ON tblAccidentStatus.StatusNumber =
tblAccidentWithAutoNumbers.StatusNumber
WITH OWNERACCESS OPTION;

Using natural keys, the equivalent query can be reduced to ...

SELECT tblAccident.*
FROM tblAccident
WITH OWNERACCESS OPTION;

The primary key of tblAccident itself is still an AutoNumber, however, as
the natural key would have to include at least two fields (AccidentDate and
LocationCode) and possibly more.

In short, in my opinion both AutoNumbers and natural primary keys are useful
tools when used appropriately. What exactly constitutes appropriate use
under specific circumstances is a judgement call that becomes easier with
experience. 

--
Brendan Reynolds (MVP)
"Chris" <ganjula@hotmail.com> wrote in message
news:1c6d501c42234$3b86a4a0$a501280a@phx.gbl...
> If this topic is addressed in a FAQ or other online
> resource, I'd be glad to have a pointer to it.  I've
> tried to find it addressed in several books and by
> searching these newsgroups...
>
> What factors, if any - performance? db size? other? -
> are important in choosing the data type for a primary
> key?  Since one always has the option of using, for
> example, an Autonumber as a primary key, are there
> times when that choice is prefered even though there
> is some other unique attribute in the data itself?
>
> For example, a simple single-attribute table of allowed
> categories (to be used as a lookup table for assigning
> records from another table to different categories)
> could use the category name itself as a primary key -
> all category names will be unique.  But does it make
> sense to use this text field as a foreign key in
> another table, or is it better to create an additional
> autonumber field in the category table?
>
> I think what I'm really wondering about is the
> implementation of primary/foreign keys: if there is
> only a single text attribute in a table, used as a
> primary key, what gets stored in the other table where
> it is a foreign key?  Is it the text itself, which
> could be quite sizable?  Or does Access use internal
> record numbers or the field indexes as the value for
> the foreign key?  I would expect performance and/or
> storage size penalties if the actual primary key
> value (text, in this example) is stored in the other
> table as the foreign key.
>
> I have this feeling I'm missing some fundamental
> concepts here, and appreciate any help in putting
> me on the path towards clearer understanding.  Thanks
> in advance,
>
> Chris
>

Relevant Pages

  • Re: FAQ? factors influencing choice of data type for primary key
    ... >In the past, I tended to use AutoNumbers in all tables, ... >Knowing that the primary key of all my tables was a ... table 'tblAccident' is ... >FROM tblAccidentStatus INNER JOIN (tblAccidentLevel INNER ...
    (microsoft.public.access.tablesdbdesign)
  • Re: null but not null
    ... I agree with you on the use of autonumbers. ... they prefer to use meaningful Unique data. ... primary key. ... we are properly normalized and the [SuperVisor] field is an FK to ...
    (microsoft.public.access.forms)
  • RE: Autonumbering Primary Keys
    ... Try a groups search for "increment number ... primary key" or "increment autonumber" or "simulated autonumber" or something ... > Access suggests that I have a separate column for OrderID using Autonumbers. ...
    (microsoft.public.access.gettingstarted)
  • Re: automatic consecutive numbering
    ... Keep in mind that the Primary Key can consist of more than ... >I will go to your website and study your example. ... >> Autonumbers should never be used for anything other ... >> assigned to equipment located in my facility. ...
    (microsoft.public.access.gettingstarted)
  • Re: AutoNumber Question
    ... Autonumbers in Access are intended as unique row identifiers, ... using the primary key from table1 ... your table2 relationship! ...
    (microsoft.public.access.tablesdbdesign)