Date: Thu, 1 Jun 2006 12:44:50 -0400
From: postgres@jal.org
To: David Clarke <pigwin32@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table design question
Message-ID: <20060601164450.GA24982@clueinc.net>
Mail-Followup-To: David Clarke <pigwin32@gmail.com>, pgsql-sql@postgresql.org
References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
User-Agent: Mutt/1.3.28i
Organization: clue inc

On Thu, 01 Jun 2006, David Clarke wrote:

> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the

The use of surrogate keys is a mostly religious issue. Celko is an
Orthodox, many others are Reform. Where you want to align yourself
is a personal choice. 

In defense of the Reform movement, I'd note that modern DBs are more
performant with ints than varchars. More importantly, I've found it
much easier to modify DBs designed with surrogate keys than natural
keys, especially when natural keys span columns. It allows a rather
simple convention for coders to write against, and avoids some messy
modification issues when the spec changes.

It is not my intention to bash the purists, and there are good arguments
on the Ortho side, too. I'm merely giving a bit of advice from the
point of view of someone who lives in the constant evolution side
of DB usage.

-j

-- 
Jamie Lawrence                                        jal@jal.org
When I was a boy I was told that anybody could become President. 
Now I'm beginning to believe it.
   - Clarence Darrow