Re: Recursive Arrays 101

On 10/25/2015 08:48 AM, David Blomstrom wrote:
> I'm creating a website focusing on living things (mostly animals). I
> have multiple huge MySQL database tables with animal taxons arranged in
> a parent-child relationship. I was trying to figure out how I could
> navigate to a URL like MySite/life/mammals and display the number of
> children (i.e. orders), grandchildren (families), great grandchildren
> (genera) and great great grand children (species).
>
> I was then steered towards some sort of MySQL substitute for a full
> outer join (which can apparently only be done in Postgre), followed by
> an introduction to stored procedures. Pretty complicated stuff.
>
> Then someone told me it's stupid to jump through all those hoops when
> you can easily do that sort of thing with Postgre.
>
> So that's my specific goal - to set up my animals website so it can
> quickly and efficiently calculate and display things like grandchildren,
> great grandparents, the number of children that are extinct, etc.
>
> My database tables look something like this, where Taxon, Parent and
> ParentID are the names of the key fields:
>
> Taxon | Parent | ParentID
> Animalia | Life | (NULL)
> Chordata | Animalia | (NULL)
> Animalia | Chordata | 0
> Mammalia | Animalia | 1
> Carnivora | Mammalia | 2
> Felidae | Carnivora | 3
> Panthera | Felidae | 2
> Panthera-leo | Panthera | 1
> Panthera-tirgis | Panthera | 1

I am not entirely following the above. Could you post the actual table
definitions?

>
> Is that table structure sufficient for PostgreSQL to calculate
> grand-children, etc., or will I have to modify it? I think the key words
> are "hierarchical query" and/or "nested set." There's a popular tutorial
> (though I can't find it at the moment) that illustrates the procedure,
> which involves creating TWO numerical fields - a process that I think
> would be overwhelming when working with over 50,000 taxonomic names.
>
> So that's my question; can I do all this recursive stuff in Postgre with
> the table structure posted above, or will I still have to add a second
> numerical column (or otherwise my table)?
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com