From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: David Garamond <lists@zara.6.isreserved.com>,
	pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 6 Aug 2004 15:42:04 -0700
User-Agent: KMail/1.5.4
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<200407210001.00102.josh@agliodbs.com>
	<41026D55.3040002@zara.6.isreserved.com>
In-Reply-To: <41026D55.3040002@zara.6.isreserved.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200408061542.04336.josh@agliodbs.com>

David,

> 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)? 

Sure ... *if* it's being used that way.   If, however, your table has that 
Invoice # *and* a seperate surrogate key that's redundant and can cause 
problems.

> 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).

Sure.  But surrogate keys don't fix this problem; only good change management 
does.   This is precisely why I say "use with caution"; all too often project 
leaders regard surrogate keys as a substitute for good change management and 
don't do any further work.

> 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.

Absolutely false.   It's quite possible, it's just a performance/schema/data 
management issue.  This also applies to my comment above.

> 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:

You're right here, both are equally hard to normalize.   What I'm criticizing 
is the tendency of a lot of beginning DBAs -- and even some books on database 
design -- to say: "If you've created an integer key, you're done."

Had I my way, I would automatically issue a WARNING on any time you create a 
table in PG without a key.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco