Question:
Can you please tell if there are analogues of the nesteds sets pattern?
Answer:
There are at least three more patterns, in addition to nested sets, for organizing tree structures:
-
Neighborhood list (key
id
– parentparent_id
) -
Enumeration of paths (1/5/21/128 / – parent ID numbers)
-
Closure table, when an additional table is created to organize parent-child relationships, where each record corresponds to one relationship.
If you have selections that greatly exceed insert and edit operations, there is nothing better than nested sets in terms of performance. If the situation is the opposite, it is better to consider the closure table – it has the least pitfalls (especially when editing).