Skip site navigation (1) Skip section navigation (2)

Re: pervasiveness of surrogate (also called synthetic) keys

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: John R Pierce <pierce(at)hogranch(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Date: 2011-05-03 04:53:30
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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.

In response to


pgsql-general by date

Next:From: Rob SargentDate: 2011-05-03 04:57:29
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Previous:From: Craig RingerDate: 2011-05-03 04:53:07
Subject: Re: pervasiveness of surrogate (also called synthetic) keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group