What's the best way to store hierarchical information in a database, and what's the best way to retrieve it?
I'm programming a web application for planning events. The data will be stored in a SQL Server database and I'm using ColdFusion for the application.
Events can belong to one or more categories and subcategories. They don't have to have a subcategory. So, I'll have at least one events table, one categories table, and a table for linking the events and categories. What's the best way to record the relationships of subcategories to their parent categories? I found this SitePoint article
, but the Modified Preorder Tree Traversal it recommends is confusing.
Finally, I'll need to display the events in a category that aren't assigned to a category, then display the events that belong to the subcategory. What's the best way to do that?
In an earlier version of the application, the categories and subcategories were all in the same table. Each record had an ID, and the categories' IDs were the parent IDs of the subcategories. For each category, I did a query for events with the category's ID, then looped through the category's subcategories and did the same thing.