Re: pervasiveness of surrogate (also called synthetic) keys

On 05/02/2011 10:06 PM, Rob Sargent wrote:
> You would be surprise how many "bob smith"s where born on the same
> day. But then they weren't all born in a hospital etc etc etc.

I wouldn't be surprised. I once lived in a mile-square town (Hoboken,
that's it's nickname). In that town were 40K residents and three gyms.
I forgot my ID card one day when going to mine, and they took my name
and street name as alternate proof of identity. Some designer along the
line figured that was unique enough. Number of Greg Smiths living on
that street who were members of that one gym? Three.

I see this whole area as being similar to SQL injection. The same way
that you just can't trust data input by the user to ever be secure, you
can't trust inputs to your database will ever be unique in the way you
expect them to be. So if you build a so-called "natural key" based on
them, expect that to break one day. That doesn't mean you can't use
them as a sort of foreign key indexing the data; it just means you can't
make them the sole unique identifier for a particular entity, where that
entity is a person, company, or part.

--
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