Hi Bill, I read the “Naive tree” chapter of your book and found very interesting the closure table approach, however I haven’t found a real implementation of this pattern on the web. I’m planning to use this pattern for MLM (multilevel marketing) application but I’m afraid that the size of the closure table will grow exponentially as the depth of my tree increases. Considering PHP and mySQL for building this app, do you think is a good idea to use closure table? Given an node in my tree, I plan to display depth 10th as the maximum depth of a subtree. Would it be better to use adjancency model considering the above hypothesis? Have you experienced closure table in real world application? Thanks in advance
I implemented closure table for a Perl application in 2002, and it worked, but the depth never went very deep.
I tested closure table for a PHP demo with a tree of about 500K nodes. Here’s a webinar in which I demo it: http://www.percona.tv/percona-webinars/models-for-hierarchical-data-in-sql-and-php
It’s very important to have the right indexes for the types of queries you run. I ended up with several multi-column indexes, with columns in different orders. So remember to MENTOR your indexes!
Sir, I have read the chapter about closure tables and I am planning to implement it in my project, for a threaded comment module. I have found a slightly different implementation of closure table which uses a separate column to store depth and also it stores relation of all depth(not only depth 1) here: Closure Table – Store Hierarchical Data Seamlessly. I need your your suggestion on which method I should implement.And also the merit or demerit of both methods.