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