Re: Strategy for Primary Key Generation When Populating Table

On Thu, 9 Feb 2012, David Salisbury wrote:

> Interesting. I used to think natural keys were okay, but have since decided
> that surrogates are the way to go. That second layer of abstraction allows
> for much easier data modifications when needed. What would be an example
> of a natural key that would be good to use, and why would it be preferable??

For water quality data the primary key is (site, date, param) since
there's only one value for a given parameter collected at a specific site on
a single day. No surrogate key needed.

> I'd think the key value must never change, and even say kingdom values in
> a taxa table could possibly change.. might discover something new and do a
> little reordering. :) Also natural keys might be strings, which I'm
> thinking would not be as efficient as integers for an index.

The problem with real world data is that different taxonomic levels are
used. Not all organisms can be identified to species; some (such as the
round worms, or nematodes) are at the level of order. That means there is no
combination of columns that are consistently not NULL. Sigh.

Rich