News for hackers. Stuff that matters.
Re:Managing Hierarchical Data in MySQL (or really any database) by rjurney on Friday July 17, 2009 @10:05AM (#709875)
Do yourself a HUGE favor, and just use L-Tree for PostgreSQL instead: http://www.postgresql.org/docs/current/static/ltree.html
Re:Managing Hierarchical Data in MySQL (or really any database) by tom_b on Friday July 17, 2009 @10:44AM (#709963)
Knowing more than one approach for dealing with hierarchies in relational dbs is a good idea. I've used both nested sets and proprietary approaches (things like CONNECT BY in Oracle).

Thanks for the link to the PostgreSQL ltree, I wasn't aware of it.

Also the article mentions Joe Celko's books, which I recommend to everyone who wants to build up their SQL skills.

Re:Managing Hierarchical Data in MySQL (or really any database) by rjurney on Friday July 17, 2009 @11:26AM (#710064)
I agree. I implemented a pre-calculated nested tree, as in the article, based on an existing PHP implementation but in Perl. In benchmarking it, I found that it was faster than I thought to update the entire, large tree - but that the L-Tree was going to be a much better solution overall. L-Tree has that low-level C, Russian hacker edge going for it :) But your mileage may vary.

The nice thing about the pre-calc stuff is that it is very approachable, and you can build it yourself. I found doing so very educational and encourage anyone who is curious to play around.

As someone else mentioned - we also looked at rolling our own materialized path trees - and did an implementation of this as well, but in the end L-Tree 'just works' out of the box, and is wicked fast compared to a higher level implementation... so we went with it. Its great. I'm not a Postgres fanatic or anything - I like MySQL and Postgres, and lots of NoSQL databases too, but Postgres is great at this in particular.