Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 1
Node Data Stored
We can represent this tree using just 3 pieces of information about each node:
- A unique identifier (ID) for each node
- Its title (e.g. General Resources)
- The ID of its parent node (e.g. for the Links node, this would be the ID of General Resources)
Why the parent model is good
If we represent the tree using only the three items of information above, we can easily determine the following about a node:
- We know it’s parent ID
- The node’s siblings are all the other nodes with the same parent ID
- The node’s children are all the nodes with a parent ID of the node’s ID.
Why the parent model is bad
Using only the ID, title and parent ID, it’s hard to determine anything other than siblings or children. For example, it would be hard to find every item within a single section, or to find the path from the root node to a node say 5 levels deep.
By hard, it can still be done, but will either require an arbitrary number of SQL statements, or require reading in the entire tree and processing it recursively.