Re: primary key and existing unique fields

Look at the database design in terms of data retrieval. If I add a sequence
number as my primary key, when I get ready to retrieve that record
"directly" how do I know what that sequence number is. For instance, my
employee number is 123456789, and it is unique within my company and my
sequence number is 375. I will more likely know the employee number to
query than I will the sequence number. Sure saves time in data access. I
know, you can always create a unique index on the employee number as well as
a primary index on the sequence number but WHY would I want to take up room
for a field in the record as well as the useless index space for no purpose.

Codd said "the key, the whole key and nothing but the key." In database
design we cannot always do this for query performance but why add something
to a record that will serve no real purpose. Keys are updateable, hopefully
they are not changed all that often but the ability should be there. If
they are not updateable then the database engine is not one I would choose
for my application.

Duane
-----Original Message-----
From: Mike Mascari [mailto:mascarm(at)mascari(dot)com]
Sent: Tuesday, October 26, 2004 2:26 PM
To: Joshua D. Drake
Cc: Sally Sally; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] primary key and existing unique fields

Joshua D. Drake wrote:
>
>>> Here is a good article on the topic:
>>>
>>> http://www.devx.com/ibm/Article/20702
>>
>>
>> The surrogate key isn't solving the underlying logical inconsistency
>> problem. It is being used as a work-around to cover one up. I suspect
>> the author of being a MySQL user.
>
> Actually he is a software project consultant for IEEE.org and he holds
> a Ph.D. in Theoretical Physics.

<joking>

Apparently gamma functions and string theory have little to do with
understanding the relational model of data.

</joking>

Seriously, my only point was that Date & Darwen and other relational
purists do not use surrogate keys. The surrogate-key vs. speaking-key
debate devolves quickly. I'd only argue that it is wrong as the author
of the article implied that the speaking-key side of the debate is
without merit.

He begins:

"For the purpose of data modeling, the plumbing should be largely
transparent. In fact, purist DB lore makes no distinction between data
and plumbing. However, you will see that it is more efficient for
administration and maintenance, as well as in terms of runtime
performance, to have some additional fields to serve as DB keys."

So he dismisses the speaking-key argument in one sentence as "purist DB
lore." He then proceeds with a poor example:

"The requirements for a primary key are very strict. It must:

Exist
Be unique
Not change over time
Surrogate keys help to mitigate the fact that real business data never
reliably fulfills these requirements. Not every person has a Social
Security Number (think of those outside the U.S.), people change their
names, and other important information."

1. The reason we have ON UPDATE CASCADE is to handle changes in primary
keys.

2. If not everyone has a social security number than the design should
be sufficiently normalized to reflect that fact.

I am not saying that Chris Date and Hugh Darwen are right and that
Philipp Janert is wrong. I am only saying that both sides should be
investigated and judged on the weight of their arguments.

Personally, I've found over time that when I deviate from "purist DB
lore" I get punished in long run.

Could be wrong, though. :-)

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly