I'm sorry, I don't know exactly what you mean by "definitions." The fields Taxon and Parent are both varchar, with a 50-character limit. ParentID is int(1).
Here's a discussion that describes the table in a little more detail -- http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii
And this is the discussion where someone suggested I check out PostgreSQL -- http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure
On Sun, Oct 25, 2015 at 10:59 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
I am not entirely following the above. Could you post the actual table definitions?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
--
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@xxxxxxxxxxx
