SQL Antipatterns

Solution: Use Alternative Tree Models

There are several alternatives to the Adjacency List model of storing hierarchical data, including Path Enumeration, Nested Sets, and Closure Table. The following three sections show examples using these designs to solve the scenario in the “Antipattern” section, storing and querying a tree-like collection of comments.

These solutions take some getting used to. They may seem more complex than Adjacency List at first, but they make some tree operations easier that were very difficult or inefficient using the Adjacency List design. If your application needs to perform those operations, then these designs are a better choice than the simple Adjacency List.

Path Enumeration

One weakness of Adjacency List ...