Re: Any *real* reason to choose a natural, composite PK

dananrg(at)yahoo(dot)com wrote:
> 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.

Surrogate keys have the advantage of performance. A composite key
composed of four fields of 50 characters each could create performance
problems. But a unique serial avoids this problem.

Of course, one of the drawbacks of surrogate keys is that, if you design
your table so that those four fields together are unique, once you index
the table on the serial key, there's nothing to guarantee your four
fields will *stay* unique.

Another reason for surrogate keys is that there may not be any
*meaningful* combination of fields to make up a unique key. That is,
although you may indeed have four unique fields for your table, together
they really have no meaning. For example, I have a log table in one of
my applications which adds several records per job. Yes, I could make
the key jobno + sequence_number. But why not just let the primary key be
a serial? Certainly simpler.

Here's another reason for using surrogate keys (real world). You have a
PHP application where you're passing GET parameters to the next PHP
script in the chain. If you're going to query a table with one of these
long composite keys, you've got to go through the operation of
concatenating all these values together in order to pass it via the GET
parameter. A real pain. It's far easier to retrieve the single serial
key field and pass that.

Overall, I'd say this is the problem with guys who write books full of
theory, expecting students to buy their pronouncements. Yes, some of
these guys have real world experience. But really, you should hang out
with some people who actually do this for a living after you read the
theory guys. The theory's keen and all that. But it's no substitute for
getting your hands dirty in the real world. Things are often very
different out there. Give me a mechanical engineer any day over an
architect who's never been near a building. (I don't know much about
Frank Lloyd Wright's education, but he built some beautiful buildings
which didn't stand up well to the elements.)

--
Paul M. Foster