View Single Post
  #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.

--