jwcolby
jwcolby at colbyconsulting.com
Fri Jun 11 07:43:21 CDT 2004
Sorry, I appeared to be back at Jim's message - "pick a screw up off the shelf", which I took to mean just some screw laying on someone's desk. Of course a single screw in a bin of that type couldn't be distinguished from any other without a serial number. John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Scott Marcus Sent: Friday, June 11, 2004 8:28 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate John, If you have a bin full of 20,000 screws (all the same, because that's what I was saying) you would be an amazing person if I could pick up anyone of them, show it to you, take it back, put it back in the bin, mix the bin up, and you could find that same exact screw. 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 jwcolby Sent: Friday, June 11, 2004 8:21 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] OT: The Great Primary Debate >My point is that you couldn't distinguish one bolt/nut/screw from >another (in that bin) by looking at it. Not true at all. I worked at a screw manufacturer a few years back. You could hand the owner a screw and he could rattle off the attributes. >All of the attributes are the same. That's like saying all the attributes of people in a group are the same. Simply not true, you are trained to distinguish people ad you could look at a person and rattle off height, weight, hair color, eye color, ethnic background etc. You are just not trained to recognize screws. As for the serial number vs. autonumber... I personally agree, they are equivalent. Just unique numbers used to identify an instance of an object. John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Scott Marcus Sent: Friday, June 11, 2004 7:26 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate <<But it does have attributes; diameter, style of thread, depth of thread, <<type of head, etc that make it is what it is. They sure do. My point is that you couldn't distinguish one bolt/nut/screw from another (in that bin) by looking at it. All of the attributes are the same. If you want to distinguish one screw from another, you must add a serial number (which is not stamped on any of these tiny parts). Why would that serial number be any different than adding an auto-number? According to the reasoning in question, the serial number is a natural key, yet the auto-number is not. Sounds like a double standard to me. It has been said that you can arbitrarily change the auto-number. Couldn't the serial number be arbitrarily changed as well? Before it is said, "but the serial number shouldn't be changed", I would argue that the auto-number shouldn't be changed either. 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 Dettman Sent: Thursday, June 10, 2004 12:07 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate Scott, <<The screw itself looks like any other screw. Same with washers, nuts, bolts, etc...>> But it does have attributes; diameter, style of thread, depth of thread, type of head, etc that make it is what it is. Jim (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Scott Marcus Sent: Thursday, June 10, 2004 9:47 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate Jim, The bin that the screws reside has a part-number label on it. The screw itself looks like any other screw. Same with washers, nuts, bolts, etc... 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 Dettman Sent: Thursday, June 10, 2004 9:38 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate Scott, <<How would I create a natural key for a part if not?>> You see a part sitting on a shelf with no part number. How do you figure out what it is? Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Scott Marcus Sent: Wednesday, June 09, 2004 5:34 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate Jim, What about part-number, is that an attribute of part? I can go in and arbitrarily change it. How would I create a natural key for a part if not? 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 Dettman Sent: Wednesday, June 09, 2004 3:59 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate 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 -- _______________________________________________ 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 -- _______________________________________________ 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