On Thu, Feb 9, 2012 at 2:10 PM, David Salisbury
<salisbury@globe.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.
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