From: Rod Taylor <pg@rbt.ca>
To: David Clarke <pigwin32@gmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com>
Date: Thu, 01 Jun 2006 09:41:56 -0400
Message-Id: <1149169316.851.782.camel@home>
Mime-Version: 1.0
Subject: Re: Table design question
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

> 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
> criteria for a key. So should I follow Joe's advice and use my natural
> key as the primary key? It sounds reasonable but it will mean at least
> one other table will have the string as a foreign key. My postgres

Unfortunately as a result of the implementation of most commercial
databases (and PostgreSQL), using a string as the primary key will cause
a performance hit.

Yes, normally it is better and for things like status flags and other
constant values that rarely change it is a good approach.


I would tend to add a SERIAL and make it the primary key (using it in
foreign tables) but also making the string column unique and not
null'able.

Essentially it gives the table two primary keys in the hope that some
day PostgreSQL will add a layer of abstraction between primary/foreign
key lookups and presentation that allows for usage of strings directly
without a performance hit.
--