Re: pervasiveness of surrogate (also called synthetic) keys

Any system that generates transactional data has to use some kind of
synthetic key. I guess you could rely upon some form of timestamp but from
a usability standpoint that is not really a good decision. Inventory also
requires synthetic keys - whether you decide what they are or someone else
does. A serial field is the common form of a synthetic key but any assigned
identifier can be defined in the same way. You can even argue that a field
that concatenates the parts of the natural key qualifies as synthetic.

To actually use the data in the database it is often helpful to be able to
uniquely identify a row using a single field.

My personal take is that normalization is important. Once you have
normalized you will find tables with compound natural primary keys. If
these tables are going to be queried in such a way as to return a single
record (WHERE clause) it is likely that a synthetic key would be beneficial.
If they are normally going to be used only with the context of other core
tables (basically JOIN ON clause) then it is less important to have a single
representative value.

Justifying anything simply based upon what other people do is superficial.

As for the colleague; the basic response is "what is your source". If they
are trying to convince you to include synthetic keys on specific tables ask
them to explain what specific benefit will be gained by doing so and let
them know that "you should always have a synthetic key" is not a helpful
response.

David J.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
Sent: Thursday, April 28, 2011 1:44 PM
To: Jim Irrer
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pervasiveness of surrogate (also called synthetic)
keys

On 4/28/2011 12:29 PM, Jim Irrer wrote:
> A colleague of mine insists that using surrogate keys is the common
> practice by an overwhelming margin in relational databases and that
> they are used in 99 percent of large installations. I agree that many
> situations benefit from them, but are they really as pervasive as he
> claims?
>
> Thanks,
>
> - Jim

I dont see how you could know unless you went to all the "large
installations" and asked. But since its a good idea, and you "should"
do it that way, and because I'm pessimistic, I'd say only 5% of RDB users do
it that way.

Oh! Joke: Why do DB Admins make better lovers? They use surrogates!

Anyway, I'm not a large install, but I use em. That's gotta count for
something.

Really, how could you count? Was there a poll someplace? Ask for some
data. Otherwise seems like BS to me.

-Andy

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general