"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.

--