Re: Recursive Arrays 101

On 10/26/2015 10:33 AM, Rob Sargent wrote:
> On 10/26/2015 11:14 AM, Adrian Klaver wrote:
>> 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
> Sorry, wasn't tracking carefully: 6 attributes
>>
>> 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.
>>
> But at the end of the day, is it not the intent to have those six filled
> per species. Is your point that maintenance would be problematic?
> Agreed.  Certainly not just a single pointer redirect in a recursive
> structure.  All depends on OPs usage patterns.  I personally love 'with
> recursion' but it's more complicated than for example
>      select count(*) from species where class = '<some class name>'
> if, and only if, all 6 attributes are always there.  Which highlights
> your caveat "In this classification system".

This is the current system. If you want to be historically complete then
you have to take into account the ways things where classified before.
Granted this is running in the crawl, walk , run sequence but it cannot
be entirely ignored. Then there  are the more detailed versions of the
above:

http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927

It comes done to what view of taxonomy you want to support.

>
>>>>> 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@aklaver.com