Re: Primary Keys (to autonumber or not ?)

From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 07:42:28 -0600

> I agree with everything Lynn mentioned.
>

I knew you were a smart guy, Jeff... <g>

> In dbs with multiple related tables, I've found it more
> efficient (read "lazy") to use autonumber primary keys to save effort in
> relating the tables.

I'm not so sure it's a matter of "laziness" as it is more effecient for the
database engine. Of course, there are people, who will remain unnamed until
they name themselves, that will disagree with that.

> But I also use a "natural" primary key when it serves no purpose to
> include
> an artificial (autonumber) key.

That should be standard practice, in my view. A table storing department
information for a company has a perfectly good natural key of Department
Name, which should never be duplicate. Using an AutoNumber here does nothing
to speed up the processing, especially since the Department Name field would
have a primary index on it. 

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Relevant Pages

  • Re: Storing Foreign Key in a table
    ... or not you use an AutoNumber for PK you MUST always declare the Natural Key ... Defining the Natural Key as the Primary Key will create just such an index ... Whilst many frown upon the indescriminate use of AutoNumber ... As an example in the field properties for CityID under lookup I ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Multi-Field Primary Key
    ... A natural key can be verified in the reality being modelled (hint: ... will have a trusted source who will handle the rare ... "Is phone number better than autonumber as ... I can find all sorts of arguments pro and con on any topic, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table Design
    ... I guess I don't see the fundamental difference, ... the natural key ensures data integrity but for some ... I take the main point to be that an autonumber ... should not be seen as a substitute for indexing and enforcing uniqueness. ...
    (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: Composite Primary Key (CPK) and relationships
    ... >>> Use the PayrollID or an autonumber as your primary key and add a status ... >>> Duane Hookom ... >>> MS Access MVP ...
    (microsoft.public.access.tablesdbdesign)