Michael Wang (10/25/2010)
...But for countless DBAs who are unfortunately tasked with designing a data model for business and cannot properly decipher the ever evolving business world, the word is a good word. I'd rather see them *always* use a surrogate key than see them being *smart* in choosing a natural key (except for timestamps that are naturally sequential and never change) in the name of *optimizing* the data model. Such optimization rarely yields good results in the long run.
a) We're paid professionals, not trade-school pipe-welders. Justifying a rule on the basis of "I/he/she/they can't understand to how do any better" is rather specious. I recognize the attractiveness of cookie-cutter set-in-stone rules, but for anyone trying to be at the top of their game, I strongly urge a deeper understanding.
b) Optimizing out spurious surrogates can sometimes yield performance benefits exceeding 100%. Gains of such magnitude are not common, but prevalent enough that it behooves us to understand the situations under which they can occur.
c) The timestamp example I gave was just one situation. There are many others. In databases which must hold older or original values of rows, for instance, a natural key is often preferred, as even if the key later changes, you don't *want* that change cascading downward.
d) Even in situations where the natural key may be updated, claiming this somehow precludes its use is a facile argument. It all depends on the situation. Balance the cost of a cascading update vs. the additional index maintenance of a surrogate. Often, the cascade is essentially "free", since any change to the natural key data must also trigger some other business logic anyway. OTOH, a changed key may cause complications beyond a simple cascade, in which case the balance tilts towards the surrogate.
Having said all this, I still prefer surrogates as a *general* rule. And there are also cases where surrogate key use can *boost* performance. That's why the point in my original post stands: there is no hard and fast rule as to which key is better in all cases. Well-educated professionals understand why to use one versus the other, and make the choice appropriately.