[AccessD] Surrogates vs Natural

Jim Dettman jimdettman at verizon.net
Thu Dec 28 10:49:02 CST 2006


 Gustav,

<<".. never used in queries' resultsets, DRI or anything else that a user
does.">>

  That's the way I understood it as well; not exposed to the user.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, December 28, 2006 5:44 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Surrogates vs Natural

Hi Marty and Arthur

I've found that you always should wear glasses when reading Joe's stuff.
But I think he is right here; what is the problem by switching from 10-digit
UPC to 14-digit EAN numbers for retail articles?

And I guess this is nothing more than a typo:

".. never used in queries, DRI or anything else that a user does."

Should read:

".. never used in queries' resultsets, DRI or anything else that a user
does."

/gustav

>>> artful at rogers.com 28-12-2006 06:43 >>>
Joe and I have corresponded occasionally for several years. I deeply respect
his knowledge and depth but here and there I take issue with him. This is
one case in point.

Suppose that I create a table Sales with an Autonumber (or Identity if in
SQL). What is wrong with exposing this value to the user? Of course, the
front end should prevent editing of this value, but that is another
question. I do not see the problem with exposing the ANPK to the user. It
becomes the SalesID and the printout exposes it and the customer uses this
number when calling to question about the Sale. What is wrong with that?

Maybe I'm missing something here, but I've been doing it this way for 20+
years and cannot recall a problem with it. 

I don't care about sequences without gaps. I don't care if you attempt to
insert then cancel 100 times in a row. Not my issue. But should you insert
and save and then print it (to PDF for email or whatever), then the customer
has a unique number to refer to this sale. I don't understand why we should
add a new number. What is wrong with the PK? Granted, the systems should not
allow this number to be edited, but aside from that, what is the problem?

Arthur

----- Original Message ----
From: MartyConnelly <martyconnelly at shaw.ca>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Wednesday, December 27, 2006 5:12:19 PM
Subject: [AccessD] Surrogates vs Natural

A Bit More on Defining Keys
Joe Celko spouts off on one of the basics of database design.

This statement should set it off.

" This means that a surrogate ought to act like an index; created by the
user,"
managed by the system and NEVER seen by a user.  That means never used
in queries, DRI or anything else that a user does.
   Auto-numbering is an exposed physical locator and not a surrogate."

http://www.tdan.com/i038ht04.htm 

-- 
Marty Connelly
Victoria, B.C.
Canada

-- 
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