Re: Strategy for Primary Key Generation When Populating Table

On 2/9/2012 4:20 PM, Andy Colson wrote:
> On 2/9/2012 4:10 PM, David Salisbury wrote:
>>
>>
>> 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
>>
>
>
> Yeah, this is a Vim vs Emacs war. (Vim, :-) )
>
> I prefer surrogates like you. Its way to easy to pick something that one
> day has to change.
>
> Within the last year I remember a long thread about this same thing.
>
> -Andy
>

Ah, here it is:

http://archives.postgresql.org/pgsql-general/2011-04/msg00996.php

-Andy