Re: dbdebunk 'Quote of Week' comment

From: John <no_at_email>
Date: Thu, 18 Aug 2005 09:39:52 +0100
Message-ID: <43044956$0$30311$da0feed9_at_news.zen.co.uk>


Roy Hann wrote:
> "Mike Meng" <meng.yan_at_gmail.com> wrote in message
> news:1124349957.125386.120320_at_g49g2000cwa.googlegroups.com...
>
>>Hi all,
>> I'm enjoying reading C. J. Date's new book "Database in Depth", in
>>which I know the www.dbdebunk.com website. I visited the site just now,
>>and find the following statement in its "QUOTE OF THE WEEK" section:
>>
>>** QUOTE **
>>Don't use primary keys that have meaning for the end user, such as
>>invoice number or the ISBN value.
>>
>>--Giuseppe DiMauro & Francesco Balena, PRACTICAL GUIDELINES AND BEST
>>PRACTICES FOR MICROSOFT VISUAL BASIC AND VISUAL C# DEVELOPERS
>>** QUOTE **
>>
>> It seems that they quote this idea to point out how wrong it is. But
>>I myself always regard this idea as a good practice in database design!
>>Am I right? If not, what's wrong with it? Please comment.
>
>
> It might seem like an acceptable way of preventing an incorrect manually
> entered key value from being propagated through the database. If a user
> enters a licence plate number wrong, and that is used as a foreign key in
> dozens of tables, you've got dozens of places to correct the error (meaning
> lots of code has to exist or be written--and added to in perpetuity as the
> database design grows).
>
> The better solution is to create foreign keys with an ON UPDATE CASCADE
> constraint so that when the users enter a wrong value and subsequently
> correct it, the DBMS automatically propagates the correction. This is
> preferable because you don't need a key generator function; you don't need a
> cross-reference table; you don't obfuscate data with surrogate values; and
> most importantly IMO, you don't conceal duplications. The latter allows
> apparently correctly-formed queries to give incorrect answers.
>
> Roy
>
>

I agree.

I often take the following:

"one should not increase, beyond what is necessary, the number of entities required to explain anything"

and modify it to this:

"one should not increase, beyond what is necessary, the number of entities required to store anything"

IME the best designs are always the simple ones.

John Received on Thu Aug 18 2005 - 10:39:52 CEST

Original text of this message