I would suggest that you use int or bigint for primary keys, and have
mapping tables to convert the scientific term to the surrogate primary key.
If the mapping table has additional attributes, like date of change &
reason, then you can also print a history of changes.
Then the relationships between tables will be more isolated from changes
in scientific nomenclature! Plus if the same animals known by different
scientific names at different times, you can have several mappings to
the same animal. Also if an organism is moved from one phylum to
another, you can find the organism via either new or old references.
I've heard of cases were one species, is suddenly found to be 2 or
distinct species!
Cheers,
Gavin
On 26/10/15 18:19, David Blomstrom wrote:
> LOL - I don't think there are any natural keys here. Traditional
> scientific names are amazingly flaky. I guess I shouldn't call them
> flaky; it's just that no one has ever figured out a way do deal with
> all the complexities of classification. The new LSID's might be more
> stable - but which LSID does one choose? But it's amazing how many
> "aliases" are attached to many taxonomic names; utterly bewildering.
>
> On Sun, Oct 25, 2015 at 10:09 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 10/25/2015 09:10 PM, David Blomstrom wrote:
>
> It's also interesting that some entities (e.g. EOL) are now using
> something called Life Science ID's (or something like that) in
> lieu of
> traditional scientific names. It sounds like a cool idea, but
> some of
> the LSID's seem awfully big and complex to me. I haven't
> figured out
> exactly what the codes mean.
>
>
> Aah, the natural key vs surrogate key conversation rears its head.
>
>
>
> Then again, when I navigate to the Encyclopedia of Life's
> aardvark page
> @ http://www.eol.org/pages/327830/overview the code is actually
> amazingly short.
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org <http://www.geobop.org>