O'Reilly logo

SQL Antipatterns by Bill Karwin

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required