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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. -- |
#12
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|