[AccessD] 2 quick questions

 > I refuse to participate in this conversation. Apparently, few if any of you (I reserve one 
exception) have never read Codd or Date etc.

Hmm... I have to assume that there is some upper class / lower class thing going on here.  You 
apparently count yourself in the upper class.

 >but given another case such as serial-numbered automobile parts, then non-autonumbered PKs make 
serious sense.

LOL.  No it doesn't.  Reality still exists.  Comparing a long integer PK to a long integer FK takes 
a single machine instruction.  Comparing a 30 character serial number to another 30 character serial 
number takes eons (in computer time).  Doing so a billion times requires some serious supercomputer 
power to solve a trivial problem that a surrogate never creates.

Apparently some of us have never studied pattern matching algorithms and machine instructions...  ;)

Surrogates exist for many reasons.  One of them is sheer speed.

Why on God's green earth would I use a highly efficient surrogate in most cases and yet choose a 
grossly inefficient natural key just because it is "guaranteed to work and not cause problems" (in 
this one specific case).  If a surrogate key doesn't work, then it should never be used.  If it does 
work, then why would I not use it.  Why would I be storing (and indexing) a 30 character string as 
my PK (and FK) instead of a long integer?

Personally I think Codd would cringe at having his name used as a club to win such a silly argument.

John W. Colby
www.ColbyConsulting.com

On 5/31/2011 9:15 PM, Arthur Fuller wrote:
> I refuse to participate in this conversation. Apparently, few if any of you
> (I reserve one exception) have never read Codd or Date etc. Yes, it is
> convenient to use an AutoNumber (or in SQL parlance Identity, or in Oracle
> parlance Sequence) to uniquely identify rows within a relation. Of course it
> is, and that's why most of us use it, but is it correct? Actually, I think
> not, atlthough sometimes it shall suffice: given the case of thousands of
> eggs hatched by hundreds of chickens daily, it may not make sense to give
> them Intelligent Keys, but given another case such as serial-numbered
> automobile parts, then non-autonumbered PKs make serious sense.
>
> I am not on one side or the other of this discussion. Rather, I am on both
> sides, and can see the sense in both sides of this discussion. When we are
> discussing eggs, autonumber may seem correct; when discussing fuel
> injectors, then serial numbers and batch numbers are important, and hence
> PKs should identify these objects intelligently, not autonumerically.
>
> A.