Re: dbdebunk 'Quote of Week' comment
Date: Thu, 18 Aug 2005 12:43:55 GMT
Message-ID: <fo%Me.9177$Je.2459_at_newsread2.news.atl.earthlink.net>
"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 depends. There are times when it's wise to use keys like ISBN and
invoice number, and times when it's not.
The important factor is not whether the user understands the keys or does
not, but whether new instances of real world entities come with dependable
keys or not.
Examples: depending on Social Security # to identify persons is a bad idea,
if you are going to be faced with a significant number of people that don't
have an account with Social Security. example: the prison inmate
population.
Depending on ISBN to key books in a library is a bad idea if you are going
to have a significant number of books that come without an ISBN, like
certain government publications.
And, of course, the generator has to avoid duplicates, among other issues.
There are other cases where surrogate keys are a good idea.
But, in general, Date is right. Natural keys are better, as long as they
are dependable.
It's not a dogmatic question. There are situation where one choice is
right, and others where the other choice is right.
And that's what I don't like about dogmatic responses. They tend to assume
that design judgements fall on a simple linear scale from badness to
goodness. Goodness is not a one dimensional measure. And that's why I stay
way from dbdebunk.
At a deeper level, they don't get it.
Received on Thu Aug 18 2005 - 14:43:55 CEST