[AccessD] OT: The Great Primary Debate

DWUTKA at marlow.com DWUTKA at marlow.com
Wed Jun 9 16:34:57 CDT 2004


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



More information about the AccessD mailing list