Re: Strategy for Primary Key Generation When Populating Table

On 2/9/12 5:25 PM, Rich Shepard wrote:
> 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.

Yea.  I was wondering if the surrogate key debate really boils down to the
composite primary key debate.  Seems so in my mind, though one could
maybe come up with a combination.  Basically aliases of values and
composite those.  Perhaps that's the ultimate methodology. :)

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

I didn't know that about worms.  I did know grasses only went to the genus.
You could make a tall skinny self referential table though, and nothing
would be null and everything would be unique ( I think, unless certain
taxon values can appear under different higher order taxon values ).

Thanks for the view points out there.

Cheers,

-ds