Re: Recursive Arrays 101 - Mailing list pgsql-general

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".

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