How do you model hierarchies in SQL? Adjacency list, nested sets, closure table.

Answer preview

Four standard models for trees: adjacency list (each row stores its parent_id), path enumeration (each row stores its full ancestor path such as /1/7/23/), nested set (each row stores left/right traversal numbers), and closure table (a separate table with one row per ancestor-des…