Re: Basic Q on superfluous primary keys

On 4/16/07, Craig A. James <cjames(at)modgraph-usa(dot)com> wrote:
> Merlin Moncure wrote:
> > Using surrogate keys is dangerous and can lead to very bad design
> > habits that are unfortunately so prevalent in the software industry
> > they are virtually taught in schools. ... While there is
> > nothing wrong with them in principle (you are exchanging one key for
> > another as a performance optimization), they make it all too easy to
> > create denormalized designs and tables with no real identifying
> > criteria, etc,...

> I can't recall ever seeing a "natural" key that was immutable. In my business (chemistry), we've seen several disasterous situations were companies picked keys they thought were natural and immutable, and years down the road they discovered (for example) that chemical compounds they thought were pure were in fact isotopic mixtures, or simply the wrong molecule (as analytical techniques improved). Or during a corporate takeover, they discovered that two companies using the same "natural" keys had as much as 10% differences in their multi-million-compound databases. These errors led to six-month to year-long delays, as each of the conflicting chemical record had to be examined by hand by a PhD chemist to reclassify it.

while your example might be a good case study in proper
classification, it has nothing to do with key selection. it is
especially unclear how adding an integer to a table will somehow
magically solve these problems. are you claiming that a primary key
can't be changed?

mutability is strictly a performance argument. since RI handles
cascading primary key changes, it's simply a matter of if you are
willing to wait for RI to do its work or not (if not, swap in the id
key as in my example). the performance argument really only applies
to specific cases, and can be considered a attribute of certain
tables. extraordinary cases do happen, like a company overhauling its
numbering systems, but such cases can be dealt with by a number of
methods including letting RI do its thing.

merlin