A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

PK - To AutoNumber or Not To AutoNumber - That is the Question! :-



 
 
Thread Tools Display Modes
  #1  
Old August 2nd, 2007, 10:36 PM posted to microsoft.public.access.tablesdbdesign
Dee
external usenet poster
 
Posts: 644
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

Seriously... if I have, for example, employee numbers in an Employees table,
should I use the employee number (will never change) or should I add an
autonumber PK?
--
Thanks!

Dee
  #2  
Old August 2nd, 2007, 10:56 PM posted to microsoft.public.access.tablesdbdesign
mray29
external usenet poster
 
Posts: 61
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

Dee:
If your employee numbers are unique, there's no reason to add another
autonumer PK. Just make the employee numer your primary key for the table and
you'll be fine.

"dee" wrote:

Seriously... if I have, for example, employee numbers in an Employees table,
should I use the employee number (will never change) or should I add an
autonumber PK?
--
Thanks!

Dee

  #3  
Old August 2nd, 2007, 11:43 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

The only thing an Access Autonumber is designed to do is provide a unique
record/row identifier. If you already have one, why bother with another?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"dee" wrote in message
...
Seriously... if I have, for example, employee numbers in an Employees
table,
should I use the employee number (will never change) or should I add an
autonumber PK?
--
Thanks!

Dee



  #4  
Old August 3rd, 2007, 02:29 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

On Thu, 2 Aug 2007 14:36:01 -0700, dee wrote:

Seriously... if I have, for example, employee numbers in an Employees table,
should I use the employee number (will never change) or should I add an
autonumber PK?


A primary key candidate should meet three criteria: it should be unique within
the table; it should (preferably) be stable, not something that will get
edited after entry; and (ideally) it should be reasonably small (8 bytes is
the size of an Autonumber so that's a benchmark).

If your employee number meets those criteria it's a good "natural key" and you
should by all means use it.

John W. Vinson [MVP]
  #5  
Old August 6th, 2007, 09:05 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

On Aug 3, 2:29 am, John W. Vinson
wrote:
A primary key candidate should meet three criteria: it should be unique within
the table; it should (preferably) be stable, not something that will get
edited after entry; and (ideally) it should be reasonably small (8 bytes is
the size of an Autonumber so that's a benchmark).


Do you mean PRIMARY KEY (SQL keywords in uppercase)? If so you can
add: cannot include the NULL value; what special meaning is implied by
such designation (hint: clustering on compact of file).

If you mean primary key (general meaning in lowercase) then you've
missed out some important ones (by accident or design?): trusted
source (hint: you don't want to get into the situation where your mdb
is the trusted source of a key and you must expose your autonumber
value to database users), familiarity, validation (e.g. a check digit
can reduce keying errors), verifiability (e.g. an ISBN can be verified
by looking on the back cover, on Amazon, etc), simplicity (contrast
'The United Kingdom of Great Britain and Northern Ireland' with ISO
4217 alpah-3 country code = 'GBP'). Also note that 'stable' (not
subject to frequent change) is not the same as 'immutable' (not
subject to change).

Jamie.

--


  #6  
Old August 6th, 2007, 09:33 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

On Mon, 06 Aug 2007 01:05:15 -0700, Jamie Collins
wrote:

Do you mean PRIMARY KEY (SQL keywords in uppercase)? If so you can
add: cannot include the NULL value; what special meaning is implied by
such designation (hint: clustering on compact of file).

If you mean primary key (general meaning in lowercase) then you've
missed out some important ones (by accident or design?): trusted
source (hint: you don't want to get into the situation where your mdb
is the trusted source of a key and you must expose your autonumber
value to database users), familiarity, validation (e.g. a check digit
can reduce keying errors), verifiability (e.g. an ISBN can be verified
by looking on the back cover, on Amazon, etc), simplicity (contrast
'The United Kingdom of Great Britain and Northern Ireland' with ISO
4217 alpah-3 country code = 'GBP'). Also note that 'stable' (not
subject to frequent change) is not the same as 'immutable' (not
subject to change).



Thanks, Jamie - all excellent points. Hadn't even thought of the trusted
source issue.

John W. Vinson [MVP]
  #7  
Old August 3rd, 2007, 12:44 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

I am in a situation where they changed the format of the EmployeeID number.
Perhaps updating thousands of records in related tables for a hundred
employees is simpler than I imagine by using Cascade Update Related Fields,
but I am not anxious to attempt the project. I am left either with doing
the update (and verifying that it has gone as planned) or using the old
number as the PK (incrementing it by 1 for new employee records) and adding
another field for the new ID number.

"dee" wrote in message
...
Seriously... if I have, for example, employee numbers in an Employees
table,
should I use the employee number (will never change) or should I add an
autonumber PK?
--
Thanks!

Dee



  #8  
Old August 3rd, 2007, 04:36 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default PK - To AutoNumber or Not To AutoNumber - That is the Question

I think BruceM has a good point. Too many times I have heard "This will
never change". You can never bank on anything staying the same. I had a
similar instance where there was a code convention for a data item that was
set up to satisfy or ERP system. One a littler over a year, they changed ERP
systems twice. The coding system had to change both times.
--
Dave Hargis, Microsoft Access MVP


"BruceM" wrote:

I am in a situation where they changed the format of the EmployeeID number.
Perhaps updating thousands of records in related tables for a hundred
employees is simpler than I imagine by using Cascade Update Related Fields,
but I am not anxious to attempt the project. I am left either with doing
the update (and verifying that it has gone as planned) or using the old
number as the PK (incrementing it by 1 for new employee records) and adding
another field for the new ID number.

"dee" wrote in message
...
Seriously... if I have, for example, employee numbers in an Employees
table,
should I use the employee number (will never change) or should I add an
autonumber PK?
--
Thanks!

Dee




  #9  
Old August 3rd, 2007, 09:56 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default PK - To AutoNumber or Not To AutoNumber - That is the Question

I once had the 'pleasure' of trying to fix a problem where a company was
bought by another and, guess what, the employee numbers were all changed!
Since they didn't have Referential Integrity enabled (actually couldn't as
there were some orphaned records) couldn't use Cascade Update.

I'm a firm believer in autonumbers for PKs. If it doesn't have a meaning,
you won't be tempted to change it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Klatuu" wrote:

I think BruceM has a good point. Too many times I have heard "This will
never change". You can never bank on anything staying the same. I had a
similar instance where there was a code convention for a data item that was
set up to satisfy or ERP system. One a littler over a year, they changed ERP
systems twice. The coding system had to change both times.
--
Dave Hargis, Microsoft Access MVP


"BruceM" wrote:

I am in a situation where they changed the format of the EmployeeID number.
Perhaps updating thousands of records in related tables for a hundred
employees is simpler than I imagine by using Cascade Update Related Fields,
but I am not anxious to attempt the project. I am left either with doing
the update (and verifying that it has gone as planned) or using the old
number as the PK (incrementing it by 1 for new employee records) and adding
another field for the new ID number.

"dee" wrote in message
...
Seriously... if I have, for example, employee numbers in an Employees
table,
should I use the employee number (will never change) or should I add an
autonumber PK?
--
Thanks!

Dee




  #10  
Old August 4th, 2007, 12:12 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default PK - To AutoNumber or Not To AutoNumber - That is the Question

I tend to use Autonumber PKs for most of my tables, much to the dismay of
the "natural key" purists (heh, heh, heh!). And to build on your
suggestion, Jerry, the OP can simply add another field that is the
"CurrentCompanyEmployeeIdentifier", and THAT can get changed for any reason!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jerry Whittle" wrote in message
...
I once had the 'pleasure' of trying to fix a problem where a company was
bought by another and, guess what, the employee numbers were all changed!
Since they didn't have Referential Integrity enabled (actually couldn't as
there were some orphaned records) couldn't use Cascade Update.

I'm a firm believer in autonumbers for PKs. If it doesn't have a meaning,
you won't be tempted to change it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Klatuu" wrote:

I think BruceM has a good point. Too many times I have heard "This will
never change". You can never bank on anything staying the same. I had a
similar instance where there was a code convention for a data item that
was
set up to satisfy or ERP system. One a littler over a year, they changed
ERP
systems twice. The coding system had to change both times.
--
Dave Hargis, Microsoft Access MVP


"BruceM" wrote:

I am in a situation where they changed the format of the EmployeeID
number.
Perhaps updating thousands of records in related tables for a hundred
employees is simpler than I imagine by using Cascade Update Related
Fields,
but I am not anxious to attempt the project. I am left either with
doing
the update (and verifying that it has gone as planned) or using the old
number as the PK (incrementing it by 1 for new employee records) and
adding
another field for the new ID number.

"dee" wrote in message
...
Seriously... if I have, for example, employee numbers in an Employees
table,
should I use the employee number (will never change) or should I add
an
autonumber PK?
--
Thanks!

Dee





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.