From: sad <sad@bankir.ru>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Wed, 21 Jul 2004 11:44:00 +0400
Message-ID: <200407211144.01001.sad@bankir.ru> (raw)
In-Reply-To: <200407210001.00102.josh@agliodbs.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
<004101c46ec2$631ca930$7201a8c0@mst1x5r347kymb>
<200407210001.00102.josh@agliodbs.com>
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
regardless to datamodel), to store lists of deleted or modified objects
(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
the practice we have historical datamodels without PK !!! it is impossible
but it exists.
For example:
Here in Russia we have a lot of different but identically named streets within
one city. They has absoluetly identical attributes. Historically only human
not machines work on that datamodel and they all used to call such streets by
name adding some spechial non-formal explainations, for example:
"deliver this message please to the house 35 on the Green street, that is to
the west of the center of the city."
"deliver this message please to the house 12 on the Green street, that is
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
identical, so you force this user to numbering his messages, or number them
yourself.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sad@bankir.ru
Subject: Re: surrogate key or not?
In-Reply-To: <200407211144.01001.sad@bankir.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox