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

From: Chris (ganjula_at_hotmail.com)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 11:48:38 -0700

Thanks, John, for the additional overviews and details
last week (I've been out of town for 5 days). That's
the kind of info I was looking for to help me make the
best design decisions, especially the specifics about
Access implementation as opposed to theoretical
considerations.

Considering that this is, apparently, a recurring
question about a significant (and contentious) issue,
it seems it ought to be in a FAQ somewhere... is there
a FAQ associated with these forums to save seekers
and responders from covering the same ground over
and over again? I found it difficult to zero in
on useful messages on this topic by just searching the
forum archives.

Many thanks,

Chris

>-----Original Message-----
>On Wed, 14 Apr 2004 08:22:00 -0700, "Chris"
<ganjula@hotmail.com>
>wrote:
>
>>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?
>
>This can be a contentious topic - flamewars have resulted
from this
>question, though lately we've seen mostly polite
disagreement rather
>than attacks! <let's hope that continues>
>
>>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?
>
>In that case I'd use the textfield... with some caveats.
See below.
>
>>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?
>
>It is the text itself. There's nothing special about a
field that's
>being used as a foreign key - it's *just a field*, like
any other
>field; it's a FK in terms of how it's used, not in terms
of the nature
>of the field.
>
>>Or does Access use internal
>>record numbers or the field indexes as the value for
>>the foreign key?
>
>No. There are no (usable) internal record numbers.
>
>>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.
>
>That's the main argument against them... but what you win
on the
>swings, you lose on the roundabouts. If you have the
actual value in
>the table, then only that table needs to be included in a
Query; you
>don't need to bring the lookup table or any indexes into
memory in
>order to get the value, so search performance can be
better. The
>downside is that the database will be bigger. Access
doesn't store
>trailing blanks, so the allocated size of the field isn't
really
>relevant; but if your categories average 16 bytes, in a
million-record
>table you'll use 16 MByte of foreign key values, vs. 4
MByte for
>4-byte Long Integers.
>
>In practice, if the Primary Key is *unique* - there can
never be
>duplicates; *stable* - it will rarely or never change;
and (ideally)
>short - I'd use a natural key by preference over a
surrogate key such
>as an autonumber. In many cases (tables of People for
instance) there
>is no obvious stable, short, unique key, though; and
sometimes the
>"natural" key might consist of five or six fields. Some
folks (Tom
>Ellison prominent amongst them) prefer to use such
natural keys almost
>exclusively and claim that there is little or no
performance hit;
>others disagree.
>
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.
>

Relevant Pages

  • Re: Storing Foreign Key in a table
    ... StateID AutoNumber ... you don't need the autonumber. ... As an example in the field properties for CityID under lookup I have; ... In datasheet view there is only one column for each foreign key, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Storing Foreign Key in a table
    ... why add an artificial key like an autonumber? ... Many tables have no simple natural key. ... fields combined your primary key, you are unable to enter someone unless you ... As an example in the field properties for CityID under lookup I have; ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Schema Anomalies
    ... Currently your setup only checks for Natural joins (where foreign key ... column names equal the primary key column names). ... beacuse they would only mean extra overhead on the database ... The discussions on single culumn tables (AKA lookup tables) goes long ...
    (comp.databases)
  • Re: multiple related comboboxes in datasheet view
    ... I linked these 2 lookup tables to master table, an to each other, so that in the lookup table where the "subgroup" entries are taken, there is also another column which is linked to the "group" contents. ... lookups for foreign keys, but if your Tables have the primary key as their 1st field and the verbal explanation as the 2nd field, my system will work with a Table, too. ... the right reference format be? ... Autonumber type to "random" for these to emphasize that. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Storing Foreign Key in a table
    ... You do hear that taught sometimes but why must a table have an AutoNumber? ... Many tables have no simple natural key. ... fields combined your primary key, you are unable to enter someone unless you ... As an example in the field properties for CityID under lookup I have; ...
    (microsoft.public.access.tablesdbdesign)