Any *real* reason to choose a natural, composite PK over a surrogate, simple PK?

From: dananrg(at)yahoo(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Any *real* reason to choose a natural, composite PK over a surrogate, simple PK?
Date: 2006-06-08 12:48:57
Message-ID: 1149770937.379165.21830@f6g2000cwb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In the book "Practical Issues in Database Management", Fabian Pascal
notes three reasons for choosing one PK over another - familiarity,
stability, and simplicity.

He notes further that those influenced by OO db design tend to use
simple, surrogate keys for all PKs in all databases they design; that
this is not *precluded* by relational theory, but that there's somehow
something illicit about it.

Today at least, and why I ask, I think it's a good rule of thumb to
create surrogate keys for almost all tables.

"Familiarity" seems like a spurious concern, and a poor tradeoff
against both stability (guaranteeing you are uniquely identifying rows)
and simplicity (with queries, and others intuiting your design).

What am I missing? Why use a composite key *ever* aside from
"familiarity?" Could someone give a real-world example where
"familiarity" is a compelling reason to choose a composite PK, and
trumps stability and simplicity?

Stability seems to be the single-most important factor to consider. If
the database can't uniquely identify a row, what's the point? Choosing
a surrogate key guarantees stability.

Dana

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dananrg 2006-06-08 14:00:25 Re: Any *real* reason to choose a natural, composite PK over a surrogate, simple PK?
Previous Message dananrg 2006-06-08 12:21:07 Fabian Pascal and RDBMS deficiencies in fully implementing the relational model