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.

Sure; what I was commenting on is that you normally can't ever trust
external sources for identifiers. If you want to come up with your own,
internally unique keys for things, great. But one of the goals of using
a natural key is often to avoid the overhead of storing both that ID and
some made up internal number, too. And whether the number is made up by
the computer (the classic SERIAL or similar surrogate key), or you make
one up yourself, it's still another chunk of data that gets stored for
every item. It's just one that means something more useful in your
case. Probably going to take up more space in the process and possibly
be slower though--part number strings can easily end up longer than
SERIAL-like integers.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books