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)?