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
  #11  
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.

--


Ads
  #12  
Old August 6th, 2007, 12:21 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

Then they have a problem that they might have avoided with a more "natural"
EmployeeID used as the key, until they decide that since everybody is part
of one company everybody's ID should be the same format. There is no single
best answer, but having the number format change within a single
organization is probably the more likely scenario.

"Jamie Collins" wrote in message
oups.com...
On Aug 3, 9:56 pm, Jerry Whittle
wrote:
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.


Now consider the scenario where two companies use auto-increment
values for their personnel data then those companies merge...

Jamie.

--




  #13  
Old August 6th, 2007, 03:50 PM 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 4 Aug, 00:12, "Jeff Boyce" wrote:
I tend to use Autonumber PKs for most of my tables


See:

Business Rules Require Real-World Identifiers
by Dr. John K. Sharp

http://www.inconcept.com/JCM/May1998/sharp.html

"...Let's analyze a simple example of a petty cash reimbursement to an
employee to see how a meaningless unique key is not sufficient for
business rule enforcement..."

Jamie.

--


  #14  
Old August 6th, 2007, 06:08 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

The employee number used in the example is as meaningless as a random or
sequential number (unless the "E" has some special significance). The
example is unclear as to whether the form number is the form identifier
(such as Form 1040) or a sequence number. I will assume the latter. The
author seems to be saying that the only possible natural key comprises the
employee number of the employee making the purchase; the form number, which
is already unique; and the date. Since the form number is already unique,
it is unclear how much more unique the record would be with the addition of
two additional fields to the key. Even if there is an argument in the
article's labored logic that there should be a compound key, it does not
address the question about whether the employee ID should be autonumber or
another format.

"Jamie Collins" wrote in message
ps.com...
On 4 Aug, 00:12, "Jeff Boyce" wrote:
I tend to use Autonumber PKs for most of my tables


See:

Business Rules Require Real-World Identifiers
by Dr. John K. Sharp

http://www.inconcept.com/JCM/May1998/sharp.html

"...Let's analyze a simple example of a petty cash reimbursement to an
employee to see how a meaningless unique key is not sufficient for
business rule enforcement..."

Jamie.

--




  #15  
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]
  #16  
Old August 7th, 2007, 08:38 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 6, 6:08 pm, "BruceM" wrote:
See:

Business Rules Require Real-World Identifiers
by Dr. John K. Sharp

http://www.inconcept.com/JCM/May1998/sharp.html

"...Let's analyze a simple example of a petty cash reimbursement to an
employee to see how a meaningless unique key is not sufficient for
business rule enforcement..."


The
author seems to be saying that the only possible natural key comprises the
employee number of the employee making the purchase; the form number, which
is already unique; and the date. Since the form number is already unique,
it is unclear how much more unique the record would be with the addition of
two additional fields to the key.


I think the article makes it very clear (it's a paradigm shift for
you, I recognize your reluctance). "If an auditor checked to see if
reimbursements were duplicated she/he would look at only the non-key
columns because the key column is by definition unique."

Employee E7654 submitted petty cash form 23416 on April 16, 1998 for a
purchase made at Ace Hardware on April 15, 1998 in the amount of $8.43
that is described and justified as "replaced wrench broken this
morning."

Employee E7654 submitted petty cash form 23417 on April 16, 1998 for a
purchase made at Ace Hardware on April 15, 1998 in the amount of $8.43
that is described and justified as "replaced wrench broken this
morning."

The petty cash form number is the meaningless unique key (let's say
sequential) and the domain expert attested that an auditor would
identify the above as duplicate purchases. "The real-world key allows
us to stop requests from being entered twice by mistake. Enforcing
this auditing rule at the time of submission eliminates a mistake that
could be very embarrassing to an employee. If an employee had several
requests and was distracted during entering them, a request could be
entered twice. Explaining this to the boss or a higher manager would
not be a highlight of the employee's day."

[the article] does not
address the question about whether the employee ID should be autonumber or
another format.


The article is not concerned with the generation of employee numbers.
The meaningless unique key under discussion in the article is petty
cash form number, specifically its usage as a key rather than
considerations of generation algorithm (sequential is a reasonable
assumption) or source.

Let me attempt to clarify "whether the employee ID should be
autonumber" for you: employee number should not be an autonumber (note
that autonumber is not a 'format'). To do so would make the mdb the
trusted source of employee numbers for the enterprise, which would be
an absurdity (problems with autonumber going corrupt notwithstanding).
The trusted source of employee numbers should be the enterprise's
personnel officer or other authorized person with the ability to
verify employees in reality. Neither an mdb nor an autonumber can do
this.

The OP is asking whether they should use EITHER employee number OR
employee number plus autonumber. To use the OP's simple scenario,
consider these natural language examples:

ID 1 is auto-generated for Employee E7654.
ID 2 is auto-generated for Employee E7654.
ID 3 is auto-generated for Employee E7654.

Are these duplicates? Yes. Should there be a unique constraint on
employee number to prevent the above? Of course.

Jamie.

--


  #17  
Old August 7th, 2007, 09:08 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 6, 12:21 pm, "BruceM" wrote:
Then they have a problem that they might have avoided with a more "natural"
EmployeeID used as the key, until they decide that since everybody is part
of one company everybody's ID should be the same format. There is no single
best answer


I agree but I think many people see "autonumber primary key" as always
being the answer, the panacea. I'm not sure whether the blame lies
with the "I tend to use Autonumber PKs for most of my tables" brigade
or whether it's Access's "A table must have a primary key for you to
define a relationship between this table and other tables in the
database [incorrect]. Do you want to create a primary key now?" but,
if proposed designs posted in these groups are anything to go by,
consideration of candidate keys tends to go no further than
"autonumber primary key"

Jamie.

--


  #18  
Old August 7th, 2007, 09:13 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 4, 12:12 am, "Jeff Boyce" wrote:
I tend to use Autonumber PKs for most of my tables, much to the dismay of
the "natural key" purists (heh, heh, heh!).


Dismay? No, I don't think that's it, though I may not be
representative because I'm more respectful of "alternative lifestyle
choices" (e.g. autonumber as surrogate) than the "purist" moniker
implies. My response would depend on whether you'd also put a unique
constraint on the natural key e.g. employee number in the OP's case.
If you'd seemingly given it no serious consideration I'd call you
unprofessional. If you'd considered it and still failed to constrain
the natural key my emotional repose would reflect the contempt you
would have yourself shown for your clients and end users. If you'd
taken steps to put unique constraints on both autonumber and natural
key but had chosen to give the PRIMARY KEY (SQL keywords in uppercase)
designation to the meaningless autonumber column, I'd be slightly
saddened, though remain unsurprised, that an Access MVP was not aware
of the Access/Jet engine's clustered-key compact method that was
introduced in version Jet 3.0 (http://support.microsoft.com/kb/
137039), especially with me banging on about it all this time

Jamie.

--


  #19  
Old August 7th, 2007, 12:49 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


"Jamie Collins" wrote in message
ups.com...
On Aug 6, 6:08 pm, "BruceM" wrote:
See:

Business Rules Require Real-World Identifiers
by Dr. John K. Sharp

http://www.inconcept.com/JCM/May1998/sharp.html

"...Let's analyze a simple example of a petty cash reimbursement to an
employee to see how a meaningless unique key is not sufficient for
business rule enforcement..."


The
author seems to be saying that the only possible natural key comprises
the
employee number of the employee making the purchase; the form number,
which
is already unique; and the date. Since the form number is already
unique,
it is unclear how much more unique the record would be with the addition
of
two additional fields to the key.


I think the article makes it very clear (it's a paradigm shift for
you, I recognize your reluctance). "If an auditor checked to see if
reimbursements were duplicated she/he would look at only the non-key
columns because the key column is by definition unique."

Employee E7654 submitted petty cash form 23416 on April 16, 1998 for a
purchase made at Ace Hardware on April 15, 1998 in the amount of $8.43
that is described and justified as "replaced wrench broken this
morning."

Employee E7654 submitted petty cash form 23417 on April 16, 1998 for a
purchase made at Ace Hardware on April 15, 1998 in the amount of $8.43
that is described and justified as "replaced wrench broken this
morning."

The petty cash form number is the meaningless unique key (let's say
sequential) and the domain expert attested that an auditor would
identify the above as duplicate purchases. "The real-world key allows
us to stop requests from being entered twice by mistake. Enforcing
this auditing rule at the time of submission eliminates a mistake that
could be very embarrassing to an employee. If an employee had several
requests and was distracted during entering them, a request could be
entered twice. Explaining this to the boss or a higher manager would
not be a highlight of the employee's day."


The same reimbursement request made on successive days could be a duplicate.
Rather than a wrench, let's say it is an expendable item such as a can of
paint. It is purchased in the morning. Toward the end of the work day it
is discovered that more paint is needed. It is purchased, and the request
is submitted the following day. Same item purchased on the same day, with
the reimbursement request submitted on successive days. Would the auditor
reject the second purchase because it was made on the same day as the first,
or accept it because the requests were submitted on different days? Should
time of day (most receipts these days contain this information) be part of
the request? Should there be a field for supervisor approval? Should there
be a comments field so that "Didn't have enough paint to finish the job" is
included in the record for the second purchase? I think the whole petty
cash example was rather poorly chosen. EmployeeID, FormID, and Date are not
sufficient in combination to verify uniqueness.

[the article] does not
address the question about whether the employee ID should be autonumber
or
another format.


The article is not concerned with the generation of employee numbers.
The meaningless unique key under discussion in the article is petty
cash form number, specifically its usage as a key rather than
considerations of generation algorithm (sequential is a reasonable
assumption) or source.

Let me attempt to clarify "whether the employee ID should be
autonumber" for you: employee number should not be an autonumber (note
that autonumber is not a 'format'). To do so would make the mdb the
trusted source of employee numbers for the enterprise, which would be
an absurdity (problems with autonumber going corrupt notwithstanding).
The trusted source of employee numbers should be the enterprise's
personnel officer or other authorized person with the ability to
verify employees in reality. Neither an mdb nor an autonumber can do
this.


My point about EmployeeID is not that the number used to punch in (or
whatever) should be autonumber, but that if it is the field involved in the
relationships it can be quite inconvenient when the EmployeeID number format
changes. By the way, I mean "format" generically. I realize that
autonumber is a type of long integer. In my company some of the EmployeeID
numbers went up by 12 (or something like that). Employee 8888 is now
Employee 8900. I don't know what became of 8888, or if it is assigned to
somebody else, or any of that. I just know that it is a nuisance. If they
decide to add a letter prefix to the number (which is now long integer),
there is yet another hassle in that the related field also needs to change
to a text field. The EmployeeID number should be unique. This can be
enforced. However, it is another matter to say that because it is unique it
should be the linking field. I think I prefer to keep EmployeeID unique,
but to have another unique (meaningless) field for linking.


The OP is asking whether they should use EITHER employee number OR
employee number plus autonumber. To use the OP's simple scenario,
consider these natural language examples:

ID 1 is auto-generated for Employee E7654.
ID 2 is auto-generated for Employee E7654.
ID 3 is auto-generated for Employee E7654.

Are these duplicates? Yes. Should there be a unique constraint on
employee number to prevent the above? Of course.


Again, I am not arguing against a unique constraint on EmployeeID, but
rather in favor of a separate field for linking.


Jamie.

--




  #20  
Old August 7th, 2007, 04:04 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

It would appear you've "read in" a lot more than I stated (or feel).

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jamie Collins" wrote in message
ups.com...
On Aug 4, 12:12 am, "Jeff Boyce" wrote:
I tend to use Autonumber PKs for most of my tables, much to the dismay of
the "natural key" purists (heh, heh, heh!).


Dismay? No, I don't think that's it, though I may not be
representative because I'm more respectful of "alternative lifestyle
choices" (e.g. autonumber as surrogate) than the "purist" moniker
implies. My response would depend on whether you'd also put a unique
constraint on the natural key e.g. employee number in the OP's case.
If you'd seemingly given it no serious consideration I'd call you
unprofessional. If you'd considered it and still failed to constrain
the natural key my emotional repose would reflect the contempt you
would have yourself shown for your clients and end users. If you'd
taken steps to put unique constraints on both autonumber and natural
key but had chosen to give the PRIMARY KEY (SQL keywords in uppercase)
designation to the meaningless autonumber column, I'd be slightly
saddened, though remain unsurprised, that an Access MVP was not aware
of the Access/Jet engine's clustered-key compact method that was
introduced in version Jet 3.0 (http://support.microsoft.com/kb/
137039), especially with me banging on about it all this time

Jamie.

--




 




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 09:08 AM.


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