On 10/26/2015 08:32 AM, Rob Sargent wrote:
On 10/26/2015 09:22 AM, Adrian Klaver wrote:
On 10/26/2015 08:12 AM, Rob Sargent wrote:
On 10/26/2015 08:43 AM, Jim Nasby wrote:
On 10/25/15 8:10 PM, David Blomstrom wrote:
@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign
keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so
many
species - especially fish - the spreadsheets I use to organize them
are
just about maxed out as it is.
The suggestion is simply to have 7 tables:
CREATE TABLE kingdom(
kingdom_id serial PRIMARY KEY
, kingdom_name text NOT NULL
, ...
);
CREATE TABLE phylum(
phylum_id serial PRIMARY KEY
, kingdom_id int NOT NULL REFERENCES kingdom
, ...
);
CREATE TABLE class(
...
);
and so-on.
Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.
Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)
kingdom phylum class genus as attributes in species table. Talk about
your "natural key". The hibernate boys would love it :)
Well in this classification system it would need to be:
kingdom phylum class order family genus
What makes it complicated is that these are just the slots. How
organisms are slotted depends on attributes and there are a lot of them.
This means there is a constant rearrangement in the slotting.
Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.
Personally I think the recursive structure is the way to go.
Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
