Date: Thu, 1 Jun 2006 19:44:24 -0400
From: postgres@jal.org
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table design question
Message-ID: <20060601234424.GB1610@clueinc.net>
Mail-Followup-To: Chris Browne <cbbrowne@acm.org>,
	pgsql-sql@postgresql.org
References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
	<e5n90p$1i7g$1@news.hub.org>
	<60r728sl9r.fsf@dba2.int.libertyrms.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <60r728sl9r.fsf@dba2.int.libertyrms.com>
User-Agent: Mutt/1.3.28i
Organization: clue inc

On Thu, 01 Jun 2006, Chris Browne wrote:

> Celko is decidedly *NOT* promoting the notion that you should use a
> 100 byte long "natural key."
> 
> Jamie's comments of "Orthodox versus Reform" seem reasonably
> appropriate in outlining something of the difference between the
> positions.  

Just to be clear, that was all I was trying to do. I probably should
have mentioned that any attempt to use such an attribute as a PK should 
be met with a baseball bat or other shillelagh-ish implement, but was 
interrupted several times during that email drafting.

> I may not care for doing this; you may not either; a company that
> builds auto parts that they want to sell into the automotive industry
> may care about standardizing their part IDs quite a lot.

This is another important point. In some situations, a rigid data model
can be a godsend to coders. If you happen to sit in such an enviable
position, I would encourage you to take advantage of it. (This doesn't
mean picking bad keys, of course.) 

I liberally sprinkle surrogate keys around simply because most of the
projects I work on have transient requirements, so spontaneous rejiggery 
and various pokery are both commonplace, and SKs provide "enough" data
integrity that the cost/benefit curve seems to peak there. Were I doing
projects that had longer release cycles, I'd re-evaluate that position,
and likely see a marginal reduction in bugs.

None of this should be taken as bashing Celko - he's a smart man and an
excellent source of advice.

-j

-- 
Jamie Lawrence                                        jal@jal.org
When I talked to the president, he was loaded.
  - Brent Scowcroft, Kissinger's assistant, 10/11/73