Antipattern: Always Depend on One’s Parent

Let’s imagine a query in which there is a need to retrieve the parents or children of a node. What should be our solution to such a query?

The answer is Adjacency List.

Any solution that we would find for this problem would include modifying the code that manages the tree data structures. Let’s discuss it in detail.

Adjacency list

The solution we might first think of, and that is commonly shown in books and articles, is to add a column parent_id. This column references another comment in the same table, and the user can create a foreign key constraint to enforce this relationship.

This design is called Adjacency List. It’s probably the most common design software developers use to store hierarchical data.

The SQL to define this table is presented below.