Content-Type: text/plain;
  charset="iso-2022-jp"
From: sad <sad@bankir.ru>
Organization: IT
To: <pgsql-sql@postgresql.org>
Subject: Re: surrogate key or not?
Date: Wed, 21 Jul 2004 11:44:00 +0400
User-Agent: KMail/1.4.3
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
	<004101c46ec2$631ca930$7201a8c0@mst1x5r347kymb>
	<200407210001.00102.josh@agliodbs.com>
In-Reply-To: <200407210001.00102.josh@agliodbs.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200407211144.01001.sad@bankir.ru>

Thnx, Josh
you are very helpful.

> There are, in fact, three very good reasons to use surrogate keys, all of
> which are strictly due to limitations of technology; that is,
> implementation and performance issues, NOT business logic.  They are:
>
> 1) Convenience:  It's very annoying to have to refer to a 4-column foriegn
> key whenever you do a join in queries or want to delete a record, as well
> as tracking a 4-element composite in your client software.
>
> 2) Performance:  INT and BIGINT data types are among the most compact and
> efficient stored in most RDBMSs.   So using anything else as a key would
> likely result in a loss of performance on large-table joins.
>
> 3) Mutability:  Most RDBMSs are very inefficient about CASCADE deletes and
> updates.   Some RDBMSs do not support CASCADE, forcing the client software
> to fix all the dependant rows.  This means that DBAs are very reluctant to
> use columns which change frequently as join keys.

> Now, you're probably wondering "why does this guy regard surrogate keys as
> a problem?"   I'll tell you:  I absolutely cannot count the number of "bad
> databases" I've encountered which contained tables with a surrogate key,
> and NO REAL KEY of any kind.   This makes data normalization impossible,
> and cleanup of the database becomes a labor-intensive process requiring
> hand-examination of each row.


The surrogate keys is a real big problem.
But i want to add another two reasons to have a surrogate keys

4) Replication:  to identify an object of ANY type (record of any table=20
regardless to datamodel), to store lists of deleted or modified objects=20
(regardless to datamodel)

5) Making a primary key: if there is no real key at all.

the sentence (5) is debatable. in theory every relation has a real key, but=
 in=20
the practice we have historical datamodels without PK !!! it is impossible=
=20
but it exists.
For example:
Here in Russia we have a lot of different but identically named streets wit=
hin=20
one city. They has absoluetly identical attributes. Historically only human=
=20
not machines work on that datamodel and they all used to call such streets =
by=20
name adding some spechial non-formal explainations, for example:
"deliver this message please to the house 35 on the Green street, that is t=
o=20
the west of the center of the city."=20
"deliver this message please to the house 12 on the Green street, that is=
=20
shortest of the all Green streets in the town."
Another exaple is a table of user messages:
user has a number of messages they have no mean attribute and also could be=
=20
identical, so you force this user to numbering his messages, or number them=
=20
yourself.