Charlotte Foust
cfoust at infostatsystems.com
Thu Dec 28 11:59:50 CST 2006
OK, I never use autonumbers as anything but pointers. They have no other meaning anyhow, and should not. That still leaves me confused by his denunciation of autonumbers as surrogates, since they are certainly not natural. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Thursday, December 28, 2006 9:50 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Surrogates vs Natural Charlotte, << If not one can see it, how can you use it??>> I think the point he was trying to make is that since it is not verifiable, a user should not be exposed to it. It doesn't relate to anything. As John has said in the past last time we had this discussion, true surrogates function as a physical pointer. Based on that functionality, why would you show it to the user? But if you use an auto number as a natural key (ie. use it as a Purchase Order Number), then you would want it exposed. A lot of this boils down to semantics and what you consider a "natural" key to be. Is a VIN # on a car natural? Certainly not in the same sense that a fingerprint is for a human. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, December 28, 2006 11:50 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Surrogates vs Natural >>Auto-numbering is an exposed physical locator and not a surrogate." The problem I have with that is how you're supposed to be able to hide a surrogate. I don't normally let users see my autonumbers, so does that make them OK? His statement doesn't sound like it, so I'm baffled by what he would consider a "real" surrogate key. If not one can see it, how can you use it?? Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Thursday, December 28, 2006 2:44 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Surrogates vs Natural Hi Marty and Arthur I've found that you always should wear glasses when reading Joe's stuff. But I think he is right here; what is the problem by switching from 10-digit UPC to 14-digit EAN numbers for retail articles? And I guess this is nothing more than a typo: ".. never used in queries, DRI or anything else that a user does." Should read: ".. never used in queries' resultsets, DRI or anything else that a user does." /gustav >>> artful at rogers.com 28-12-2006 06:43 >>> Joe and I have corresponded occasionally for several years. I deeply respect his knowledge and depth but here and there I take issue with him. This is one case in point. Suppose that I create a table Sales with an Autonumber (or Identity if in SQL). What is wrong with exposing this value to the user? Of course, the front end should prevent editing of this value, but that is another question. I do not see the problem with exposing the ANPK to the user. It becomes the SalesID and the printout exposes it and the customer uses this number when calling to question about the Sale. What is wrong with that? Maybe I'm missing something here, but I've been doing it this way for 20+ years and cannot recall a problem with it. I don't care about sequences without gaps. I don't care if you attempt to insert then cancel 100 times in a row. Not my issue. But should you insert and save and then print it (to PDF for email or whatever), then the customer has a unique number to refer to this sale. I don't understand why we should add a new number. What is wrong with the PK? Granted, the systems should not allow this number to be edited, but aside from that, what is the problem? Arthur ----- Original Message ---- From: MartyConnelly <martyconnelly at shaw.ca> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Wednesday, December 27, 2006 5:12:19 PM Subject: [AccessD] Surrogates vs Natural A Bit More on Defining Keys Joe Celko spouts off on one of the basics of database design. This statement should set it off. " This means that a surrogate ought to act like an index; created by the user," managed by the system and NEVER seen by a user. That means never used in queries, DRI or anything else that a user does. Auto-numbering is an exposed physical locator and not a surrogate." -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com