Re: Strategy for Primary Key Generation When Populating Table

On 2/9/12 10:08 AM, Rich Shepard wrote:
>    I have reports containing macroinvertebrate collection data for several
> hundred (or several thousand) of taxa. There is no natural key since there
> are multiple rows for each site/date pair. Years ago Joe Celko taught me to
> seek natural keys whenever they might exist. They don't here. That's why I
> specifically mentioned that in my message.


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

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.

-ds