A new version of this project is available.
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.
See the references below for some good links that describe the Nested Set model in more detail (or just try a search in Google).
This entry provides a library of ColdFusion code that you may like to use to help manage your hierarchical data using the nested set model.
About the Code
This code is based on a PHP/mysql implementation by Rolf Brugger (thanks Rolf!), so have a quick read through that page before getting started.
This code is also based around creating an additional “partner” table that is used with an existing data table.
The library should work in ColdFusion 6.1 and above and any common database but I have only tested in MS SQL Server.
Download the Code
You can download this code from RIAForge
This download file contains:
NestedSetTree.cfc – the function library
nstree – directory containing a sample application