Jim Dettman
jimdettman at earthlink.net
Wed Jun 9 14:59:07 CDT 2004
Lambert, <<As for AutoNumbers not being an attribute of the data. Of course they are. By adding an AutoNumber to a table you are defining a new attribute for the data. That's the purpose. The attribute is the record's uniqueness.>> Absolutely not. An autonumber is not an attribute of anything. It's a meaningless key. If I go into a table and change the autonumber value for any given row, do I now reference a new object? No. The number has no relation to the instance of the entity that's described by the row of the table. If some arbitrary value gets assigned to an instance of some entity, like a fixed assets number, house number, or a UPC code, then it becomes an attribute. I understand what he was trying to say, but I don't think it was said well. <<Does anyone seriously consider using a several billion bit element as a key????>> Actually, I brought this up last time around. Sure a DNA sequence would be the best natural key for identifying a person. Just because we don't have the computer systems to handle it doesn't make it wrong. It's a difference between theory and the real world. Jim Dettman (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Heenan, Lambert Sent: Wednesday, June 09, 2004 1:29 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] OT: The Great Primary Debate Totally agree Scott. This quoted article is a load of bunk IMHO. For example: "A relational key is a subset of attributes that identify a row in a table. Thus, an autonumbering scheme can never be a key by definition: It's not an attribute of anything except the machinery's internal state. " - thorough bunk - What this is saying is that "a relational key has to be a natural key because I've defined a relational key as a natural key. " It's not an argument for natural keys it's a proclamation of natural keys being the "right way" to go. As for AutoNumbers not being an attribute of the data. Of course they are. By adding an AutoNumber to a table you are defining a new attribute for the data. That's the purpose. The attribute is the record's uniqueness. A house number is not part of a house, but is sure as hell is used to identify the house. As you point out, Scott, a UPC is just a number - it has nothing to do with the object that's tied to it other than the agreed convention that everything you might want to sell will have a unique number. "A natural key is a subset of attributes that occur in a table and act as a unique identifier - the classic relational key. Keys are visible, and you can verify them in the external reality. Examples include UPC codes, geographical coordinates, and DNA." Such keys will act as a unique identifier IF the elements of the key have been carefully chosen, but there are lots of ways to make a bad choice, like using the name of an object as part of the key. Us humans seem to delight in changing the names of things. And what's that comment about DNA doing in there? Does anyone seriously consider using a several billion bit element as a key???? This has been yet another chapter in the book of keys, a semi-religious discourse that occupies idle minds endlessly. :-) Lambert > -----Original Message----- > From: Scott Marcus [SMTP:marcus at tsstech.com] > Sent: Wednesday, June 09, 2004 12:02 PM > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] OT: The Great Primary Debate > > << A natural key is a subset of attributes that occur in a table and act > as a > << unique identifier - the classic relational key. Keys are visible, and > you > << can verify them in the external reality. Examples include UPC codes, > << geographical coordinates, and DNA. > > The example of UPC code as a natural key violates the reasoning for saying > an auto-number can't be a key. The UPC code is nothing more than numbers > that represent a grouping of objects (company, product, etc). Am I wrong? > > Scott Marcus > TSS Technologies, Inc. > marcus at tsstech.com > (513) 772-7000 > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence > (AccessD) > Sent: Wednesday, June 09, 2004 11:21 AM > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] OT: The Great Primary Debate > > Hi All: > > And while we are all on the subject of keys, I was reading an interesting > article on keys and the various types of keys...some I had never heard of > or > more accurately differentiated and described. The view of the article > suggests that there is a need for specialized keys but their choice is > dictated by data or requirements. I now take the liberty to post this > information here. Some of the list may be very familiar and some may not. > I > personally prefer the auto-numbering PK because of it's speed and > guaranteed > uniqueness. > > <quote> > A relational key is a subset of attributes that identify a row in a table. > Thus, an autonumbering scheme can never be a key by definition: It's not > an > attribute of anything except the machinery's internal state. Pointers and > other physical implementation details fail as identifiers on the same > principle. > > A natural key is a subset of attributes that occur in a table and act as a > unique identifier - the classic relational key. Keys are visible, and you > can verify them in the external reality. Examples include UPC codes, > geographical coordinates, and DNA. > > An artificial key is a visible attribute added to the table. It doesn't > exist in the external reality but can be verified for syntax or check > digits > inside itself. For example, open codes in the UPC scheme can be assigned > to > a user's own stuff. The check digits still work, but you have to verify > them > inside your own enterprise. > > A "uniqueifier", isn't based on attributes in the data model and is > exposed > to the user. There's no way to predict or verify it. The system obtains a > value through some physical process totally unrelated to the logical data > model. Example: IDENTITY columns, other autonumbering devices. > > A surrogate key is a system generated to replace the actual key behind the > covers where the user never sees it. It may or may not be based on > attributes in the table. Examples: hashing algorithms or pointers from > many > columns back to a common set of domain values > </quote> > > Jim > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Haslett, > Andrew > Sent: Tuesday, June 08, 2004 9:48 PM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] OT: The Great Primary Debate > > > IMO the PK shouldn't be meaningful, and shouldn't be used in any > calculations at all, so it shouldn't matter if its perfectly sequential or > not. > > It's just there to uniquely identify records and used internally in > relationships. An Autonumber PK fits the bill perfectly in Access, just > as > an Identity integer field in SQL Server does. Because its meaningless it > will never need to be changed and hence won't cause the problems that > would > occur when using a natural key. > > My 2 cents... I never really understand what the arguments about, but > hey... 'whatever floats your boat' > > > -----Original Message----- > From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] > Sent: Tuesday, 8 June 2004 9:07 AM > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] OT: The Great Primary Debate > > On 7 Jun 2004 at 13:27, Ken Ismert wrote: > > > > > 3. That's why I said auto-generated! This is where relying on a ANPK can > > cause you problems: you can't extend the table without ruining your > > calculations. > > > > > For regular, sequential data (no interruptions) with a single field > natural > > key, or any data where a unique key can be mathematically calculated (as > > above), you can make an argument that ANPK is redundant, and can > actually > > make the data more difficult to work with. This is where intent of the > data, > > and your data modeling style, plays the deciding role in your PK choice. > > > > In your data dimension table example, you are creating a meaningful field > SequentialDateNumber (which you are calling ID) and are using it in data > calculations. > > Thr real question in this situation is not whether you use this natural > key > as > a PK, but whether you have a PK in the table at all - which comes down to > the > sub-debate about "what is a PK and what is it used for" :-) > > > > > > > > > > > > > > > > > > > -- > Lexacorp Ltd > http://www.lexacorp.com.pg > Information Technology Consultancy, Software Development,System Support. > > > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > IMPORTANT - PLEASE READ ******************** > This email and any files transmitted with it are confidential and may > contain information protected by law from disclosure. > If you have received this message in error, please notify the sender > immediately and delete this email from your system. > No warranty is given that this email or files, if attached to this > email, are free from computer viruses or other defects. They > are provided on the basis the user assumes all responsibility for > loss, damage or consequence resulting directly or indirectly from > their use, whether caused by the negligence of the sender or not. > -- > _______________________________________________ > 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 > > > > > -- > _______________________________________________ > 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