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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org <http://www.geobop.org>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
