Re: dbdebunk 'Quote of Week' comment

On Thu, 18 Aug 2005 00:25:57 -0700, Mike Meng wrote:
> ** QUOTE **
> Don't use primary keys that have meaning for the end user, such as
> invoice number or the ISBN value.
[...]
> ** 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.

This is probably an area where Darwen/Date and Celko would agree(!).

It has often been discussed in this group and comp.databases. And it has been written about at various places on the web, eg

http://www.intelligententerprise.com/030320/605celko1_1.jhtml http://c2.com/cgi/wiki?AutoKeysVersusDomainKeys

My position is that generated columns are sometimes needed because the world isn't perfect. But it's a stupid habit when people _automatically_ (by heart) create a generated "ID" column in tables and make it the primary key without thinking. Often, it will contain useless information because columns can be uniquely identified by other column(s) in the table. Sometimes, using such generated values for primary keys will allow wrong, redundant information to accumulate in the non-primary key columns (if the domain key(s) aren't also enforced with UNIQUE constraints).

-- 
Greetings from Troels Arvin