DWUTKA at marlow.com
DWUTKA at marlow.com
Thu Jun 10 10:41:09 CDT 2004
Hardee har har. Yep, only lately. Normally I'm rockin and rollin just fine! <grin> Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Thursday, June 10, 2004 10:21 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate Only lately?? <VBG> Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Thursday, June 10, 2004 7:02 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] OT: The Great Primary Debate I think that is what I was trying to say. I think you said it better though. Been a little off my rocker lately. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Scott Marcus Sent: Wednesday, June 09, 2004 4:46 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] OT: The Great Primary Debate Drew, Anything other than a number to a computer would not be a natural key. A computer deals with numbers, any data must be translated to a number. The concept of a natural key is only meaningful in the context it is being used? I would even say that a natural key in English may not be a natural key in German. I think the whole concept of natural key needs perspective. An auto-number, to a computer, is a natural key. 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 DWUTKA at marlow.com Sent: Wednesday, June 09, 2004 5:35 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] OT: The Great Primary Debate What I find so odd in this 'debate', is the assertion that a 'natural' key actually represents something, while an AutoNumber does not. Take, for example, a Social Security Number. Yes, it's a 'natural' key. But it is just as meaningless as an AutoNumber. The number may have some 'traits', such as what state issued it, etc, but in reality, it is a number representing actual data. You can't holler across the street 'Hey 222-55-9999, how's it going?'. If you do, you'll get really wierd looks, I'm sure. Not too mention that saying an AutoNumber is 'invalid' to the real system is like saying 'Of course I want my computer to do all of my work, I just refuse to plug it into the wall, I want it to work by my sheer will!'. The purpose of a key is to be a unique identifier. There is almost always some doubt as to whether or not a 'natural' key is going to truly be unique, therefore, don't risk it, use an AutoNumber. Hope this wasn't just a bucket of gasoline I threw on the fire. If it is, I blame Susan....if she'd get something back to me on the shaped forms article, I'd probably be too busy to send anything to the list! LOL. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Heenan, Lambert Sent: Wednesday, June 09, 2004 4:01 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] OT: The Great Primary Debate Jim, You said: " Absolutely not. An autonumber is not an attribute of anything. It's a meaningless key." Then you said:" 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." Am I missing some subtle point here? What makes an "arbitrary value" so different from a "meaningless key" that one can be described as an "attribute" and the other cannot? After all, an AutoNumber is an arbitrary value, they don't need to be sequential, just unique. Then you said:"Sure a DNA sequence would be the best natural key for identifying a person. " This is getting dangerously close to saying "the best key to use to identify a person is the person". The point of a key is not to give the complete specification of how to _recreate_ a row of data, but rather it's supposed to allow you to _retrieve_ a row of data. Long Live AutoNumbers, IDENTITY columns and GUID's - that's what I say. <huge grin> Lambert > -----Original Message----- > From: Jim Dettman [SMTP:jimdettman at earthlink.net] > 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