Re: pervasiveness of surrogate (also called synthetic) keys

John R Pierce wrote:
> otoh, there's plenty of places where natural keys are optimal. my
> company makes widgets, and we make damn sure our serial #s and part
> numbers are unique, and we use them as PK's for the various tables.
> further, the PN has a N digit prefix which is unique to a part family,
> then a M digit suffix which identifies a specific version of that
> PN. we use the N digit PN for the family tables, and the full N+M
> digit PN for the full PN tables. serial # is globally unique across
> all PNs so its the PK of any table related directly to a widget.
>
>
>
I'm surprised to see, as a defence of natural keys, an arbitrary
identifier composed of references to arbitrary identifiers elsewhere in
the system described. How many ways is this just wrong? That you're
assigning the serial numbers does not destinguish them from a UUID
generator, oh except you've put semantics into the value. oh and you're
at risk of having to reformat them when you buy out your competitor.
Starts to look like the database-in-a-name scheme of which I'm oh so
fond. I thought a natural key for a part would be more like
"bolt=geometry:head-mm:head-depth:thread-per-inch:etc". And for
something as describable as bolt indeed the complete record could well
qualify as a natural key especially if none of the attributes are
null-able (which is a believable concept for bolt). But bolt is not nut
and both are parts. Gets messy quickly without arbitary simple keys. I
think you're short on the "simple" part in your defn of serial number.

And interestingly you're dealing with types. What does one do when one
has to track instances of bolts. Given that all bolts of a certain
natural key are identical, save for the fact that one was made before
the other. The job is to register every bolt against its (current)
deployment. Naturally one assigns each bolt a non-upc barcode aka UUID.