A common technique to manage hierarchical data in a relational database is to use an "Adjacency List" model, where you have both an ID column and a Parent ID column in a table. This is easy to understand and maintain but can be difficult or inefficient when you want to retrieve hierarchies of records.
The "Nested Set" model provides an alternative technique for managing this kind of data and is more efficient at reading a hierarchy but requires a little more work for inserts, updates, deletes and moves.
This project provides a library of ColdFusion code that you may like to use to help manage your hierarchical data using the nested set model.
Download from here:
Documentation is here:
Changes in this release
- Fix category gateway getCategorySubtree() and getCategoryChildren() bugs in the demo app.
- Add an MySQL script for the demo app (thanks to Will Tomlinson).
Please add any comments/problems regarding this release to this entry, thanks.
Thanks To …
Nested Set Trees, a PHP/mysql implementation
Managing Hierarchical Data in MySQL
Storing Hierarchical Data in a Database
Using the Nested Set Data Model for Breadcrumb Links
A Look at SQL Trees (Joe Celko)
Trees in SQL: Nested Sets and Materialized Path