Re: dbdebunk 'Quote of Week' comment

"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 Received on Thu Aug 18 2005 - 10:28:31 CEST