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 :)
>> 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.
