Storing hierarchical (tree) data in SQL using Ruby on Rails

I have a Ruby On Rails project with a fair amount of hierarchical data.  I have already tried a few different techniques of managing this data.  Nested sets and materialized paths sound good in theory but in practice they didn’t work out so well.  Here are some thoughts thus far.

What I’ve Tried

Other Options

  • Closure Table – more efficient than other methods but I’m not sold on having an additional table with large numbers of rows.  After my experience with nested sets I’m leery of overkill solutions and I think this fits.  But there is a gem called closure_tree and I would probably choose this over nested set or materialized path solutions.
  • SQL99 WITH RECURSIVE – If using a database that implements SQL-99 recursion (postgres does, mysql does not) you can use an adjacency list and have SQL do the heavy lifting of finding descendants.  The gem acts_as_sane_tree implements this for postgres databases.
  • Adjacency List plus root_id – It occurs to me that there is a distinct difference between needing to get a subset of a tree and just wanting to query an entire tree with 1 query.  For this project I have only need of the latter which makes most of these options overkill because I simply do not need their features.  I think I can get the performance I want by simply adding a root_id field and using that to query entire trees or count descendants from the root.  It’s a lot less data than other solutions and relatively trivial to implement.  As far as I know there are no gems with this pattern, though it would be a nice addition to something like acts_as_tree.



Leave a Reply

Your email address will not be published. Required fields are marked *