Data Modeling medium
Which pattern is simplest for modeling a hierarchical tree (e.g., Comment Threads) in SQL, despite its recursive query limitations?
- A Nested Sets, which provide superior write performance by utilizing a continuous mathematical range to identify all child nodes in a single operation.
- B Adjacency List, where each row simply contains a reference to its immediate parent node (e.g., a `parent_id` foreign key column).
- C Materialized Path, which encodes the entire ancestral lineage of a node into a single, searchable string field that is indexed for rapid prefix-based lookups.
- D Closure Table, which maintains a separate join table mapping every relationship between any two nodes in the tree, regardless of their distance.
Answer: B. Adjacency List, where each row simply contains a reference to its immediate parent node (e.g., a `parent_id` foreign key column).
The Adjacency List is the most straightforward way to model a hierarchy, but it makes fetching a whole subtree difficult without using recursive Common Table Expressions (CTEs), which can be slow and hard to optimize. Other patterns like 'Closure Tables' optimize for complex tree traversals at the cost of additional storage and write-time complexity whenever a node moves or is deleted.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?