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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
