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