Re: Strategy for Primary Key Generation When Populating Table

On Thu, Feb 9, 2012 at 2:10 PM, David Salisbury <salisbury(at)globe(dot)gov> 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.
>
> Well, here is the approach we have taken with LedgerSMB:

Every table has a defined primary key, and where possible this is the
natural key. There are cases where there is no natural key however and we
use a surrogate key. However every table also has at least one single
column key whether it is the natural primary key or a surrogate one.

All joins are done on surrogate keys.

This has a few very specific advantages as the db schema changes: if
criteria for the natural key must change because of evolving requirements,
the join conditions need not change. Moreover joins don't require intimate
knowledge of natural keys between tables, making joins simpler and more
predictable, and easier to read.

So I don't think this is an either/or proposition. I think there is a
great deal of benefit to the use of both.

Best Wishes,
Chris Travers