Re: indexes

I asked this question here awhile ago. It's a fairly common question,
and it's known as the surrogate vs natural key debate.

Using a natural key has the advantage of performance. With a surrogate
key, most RDBMS systems will have to maintain two indexes. Natural keys
can also make your database more readable, and can eliminate the need to
do joins for foreign keys in some cases.

Surrogate keys are useful because you can very easily change your data
structure with a bit less SQL magick. A lot of Object Relational
Mappers always create surrogate keys, too, although I suspect that's
mostly a way to make the ORM more portable by guaranteeing that a
primary key will exist.

The only other time surrogate keys are very useful is when all your
candidate keys have values that change fairly often, since the primary
key ought to be as stable as possible.

Some developers also feel more comfortable with an id field. Having
that metadata feels like a safety net for some reason.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Allison
Sent: Friday, November 24, 2006 9:54 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] indexes

I notice a lot of places where people use the approach of creating an
index and
a unique key like:

CREATE TABLE foo (
idx SERIAL PRIMARY KEY,
name varchar(32) UNIQUE NOT NULL
)

instead of
CREATE TABLE foo (
name varchar(32) PRIMARY KEY
)

If the name is NEVER going to change, is there any advantage to doing
this?
If there are many-to-many reference tables (like name-to-friends) is
this any
different?

I've seen this a lot, but I've always assumed that with the condition
that
'name' would NEVER change, there was no advantage.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings