Message-ID: <41026D55.3040002@zara.6.isreserved.com>
Date: Sat, 24 Jul 2004 21:08:21 +0700
From: David Garamond <lists@zara.6.isreserved.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7b) Gecko/20040421
MIME-Version: 1.0
To: josh@agliodbs.com, pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<004101c46ec2$631ca930$7201a8c0@mst1x5r347kymb>
	<200407210001.00102.josh@agliodbs.com>
In-Reply-To: <200407210001.00102.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:
> Given:  Surrogate keys, by definition, represent no real data;
> Given:  Only items which represent real data have any place in
> 	a data model
> Conclusion:  Surrogate keys have no place in the data model

But, once a surrogate key is assigned to a row, doesn't it become a
"real" data? For example, I have a bunch of invoices/receipts and I
write down a unique number on each of them. Doesn't the unique number
become part of the information contained by the invoice/receipt itself
(at least as long as I'm concerned)? Another example is when Adam
(+Eve?) named each animal in the Genesis. At that time the name he gave
for each animal was arbitrary ("surrogate"), but once assigned to each
species, it becomes part of the real data.

> 3) Mutability:  Most RDBMSs are very inefficient about CASCADE deletes and 

Change management IMO is perhaps the main reason of surrogate/artificial
key. We often need a PK that _never_ needs to change (because it can be
a royal PITA or downright impossibility to make this change; the PK
might already be printed on a form/card/document, recorded on some
permanent database, tattoed/embedded in someone's forehead, etc).
Meanwhile, every other aspect of the data can change (e.g. a person can
change his name, sex, age, email, address, even date & place of birth).
Not to mention data entry mistakes. So it's impossible to use any
"real"/natural key in this case.

> Now, you're probably wondering "why does this guy regard surrogate keys as a 
> problem?"   I'll tell you:  I absolutely cannot count the number of "bad 
> databases" I've encountered which contained tables with a surrogate key, and 
> NO REAL KEY of any kind.   This makes data normalization impossible, and 
> cleanup of the database becomes a labor-intensive process requiring 
> hand-examination of each row.

Okay, so surrogate key makes it easy for stupid people to design a
database that is prone to data duplication (because he doesn't install
enough unique constraints to prevent this). But I don't see how a
relation with a surrogate key is harder to "normalize" (that is, for the
duplicates to be removed) than a relation with no key at all. Compare:

 street
 ------
 Green Street
 Green Street
 Green Street

versus:

 id    street
 --    ------
 2934  Green Street
 4555  Green Street
 5708  Green Street

They both contain duplicates and/or ambiguous data. They're both equally
hard to normalize/clean.

-- 
dave