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.



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



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