Re: Primary Keys (to autonumber or not ?)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jeff Boyce (JeffBoyce_IF_at_msn.com-DISCARD_HYPHEN_TO_END)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 05:30:15 -0800

Tony

I agree with everything Lynn mentioned.

I fall into the "tends to use Autonumber" camp (but that is only a
tendency). 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. This lets me get on to more interesting issues, like
user interface, without spending extra time/effort discovering candidate
keys and selecting the one I'll use. And Lynn's discussion of "duplicates"
is right on point!

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

Best of luck!

Jeff Boyce
<Access MVP>

"TonyB" <tonyb@kerrisway.freeserve.co.uk> wrote in message
news:OhBZJPi$EHA.3700@tk2msftngp13.phx.gbl...
> I am trying to do something fairly simple in a access db. I have a table
> that contains a record for every sale made. Each sale needs to have an
> unique number of the form yyyy-mm-dd-xx (where xx starts from 01 each day)
> allocated when a new sale is entered, along with other info in other
fields.
> This is the number used to lookup any particular sale.
> So I could create a PK for the record which uses this field directly as
the
> key, which means not using an autonumber field.
> Or I could use a combined key using an autonumber field plus a date field
to
> lookup records when required in a report or query, and convert the
> auotnumber field plus date to the yyyy-mm-dd-xx form for reports, queries
> etc.
> I do want to search for records that have a particular value for
> yyyy-mm-dd-xx, or all records for a particular day etc so I felt it makes
> sense to create this field directly and use it as a natural key for the
> table. But it also could be done using an autonumber field and date
fields.
> However when "googling" this topic there do seem to be strong
contradictory
> opinions as to whether you should use autonumber fields always in Access,
or
> always use a natural key if possible. What are the pros and cons in this
> situation ?
>
> Regards
> Tony
>
>

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: Restart Autonumber
    ... I use Autonumber values as ... tables are in the physical data structure. ... Joe Celko says that autonumbers don't exist in the logical data ... For that reason it's safest to use a natural key if there is one ...
    (microsoft.public.access.gettingstarted)