« mission of the day: javascript windows

trees in sql – no more nested sets?

I looked for sql trees on the net, and it seems there is another method called ‘nested intervals with farey fractions’. This is really heavy math stuff, but looks interesting. Obviously it gets around the problem that on average half of the total nodes of an SQL nested set table must be updated if you do a structural change (insert, move, delete…) – which results in very bad performance for large trees.

Recommended reading:

Nested intervals with Farey fractions

Static hierarchies and binary fractions in PostgreSQL 

Modeling trees with nested sets and nested intervals 

Explore posts in the same categories: SQL

This entry was posted on November 17, 2006 at 3:13 am and is filed under SQL. You can subscribe via RSS 2.0 feed to this post's comments. You can comment below, or link to this permanent URL from your own site.

2 Comments on “trees in sql – no more nested sets?”

  1. Bossxero Says:

    December 29, 2006 at 1:03 pm

    Congratulations. Its a nice blog you are keeping here. Keep it up and all the best.

    If you have some time, do check my personal blog @ http://forexkid.blogspot.com and don’t forget to leave a little comment for me while you are there.

    Regards.

  2. Hendy Irawan Says:

    January 22, 2007 at 9:45 am

    Greg,

    I think it’s a good thing to implemented in the (distant) future…

    I’d say that this stuff requires performance of reads much more important than updates, which is why even plain nested sets is still a good thing compared to simple hierarchy.

    I think there are very few sites that require speedy updates to ACLs. Even so, a good DBMS should make it so that updates in progress doesn’t affect a running site (that performs only read operations to ACL tables).


Comment: