[AccessD] OT: The Great Primary Debate

Charlotte Foust cfoust at infostatsystems.com
Wed Jun 9 10:55:29 CDT 2004


Did you notice in that except that he is talking about "relational" keys
rather than "primary" keys?  The "uniquifier" tag is one I haven't
encountered, and I suspect it is made up to provide a handle that
doesn't use PK.

Charlotte Foust

-----Original Message-----
From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca] 
Sent: Wednesday, June 09, 2004 7: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



More information about the AccessD mailing list