DataTerminology.comNEW
A relational modeling pattern that stores all ancestor–descendant pairs in a separate table to query hierarchies efficiently.
DESCRIPTION
The closure table pattern represents hierarchical relationships by listing every ancestor–descendant path in a dedicated table. It enables fast queries for subtree, ancestry, and distance without deep recursion. Trade-offs include extra storage and write-time maintenance to update paths on insert, move, or delete. Compared to adjacency lists, closure tables improve read performance at the cost of more complex writes. They compete with nested sets, materialized paths, and graph databases depending on use case.
ALTERNATIVE DEFINITIONS
Wikipedia
A closure table is a technique for representing hierarchical data in a relational database by storing all paths between nodes.
Stresses storing all ancestor–descendant paths to represent hierarchies.
Bill Karwin
Closure table stores every path in the hierarchy explicitly, allowing efficient queries of ancestors and descendants.
Highlights query efficiency for ancestry and descendants due to explicit paths.
RELATED TERMS
Path Enumeration Recursive CTE Hierarchical Data Graph Database Tree Traversal Transitive Closure Denormalization Materialized Path
COMPETING TERMS
Nested Set Model Adjacency List
RELATIONS
- Broader: Table
KNOWLEDGE AREAS
Data Modeling Data Storage & Operations Data Engineering
TARGET AUDIENCE
Data Modeler Data Engineer Software Developer Data Architect Analytics Engineer
EXTERNAL SOURCES
- Wikipedia - Closure table
- Wikipedia - Core concept overview
- Bill Karwin - Hierarchical models talk
- MySQL - Hierarchical data patterns
TOP QUESTIONS
What is a Closure Table?
It’s a relational pattern that stores all ancestor–descendant pairs for a hierarchy, enabling efficient queries like subtrees and paths without deep recursion.
Category: Definition
When should I use a Closure Table?
Use it when read performance for hierarchical queries is critical and you can afford extra storage and write complexity.
Category: Recommendation
How does Closure Table compare to Adjacency List?
Closure tables speed up reads by precomputing paths, while adjacency lists are simpler but require recursive queries at read time.
Category: Comparison
What are the write costs of Closure Tables?
Inserts, moves, and deletes require updating many path rows. Triggers or application logic often maintain the transitive closure.
Category: Costs
Is Closure Table good for deep hierarchies?
Yes for reads; the number of path rows grows with depth and fan-out, so storage and maintenance must be managed carefully.
Category: Scalability
Can Closure Tables track distances or levels?
Yes, many implementations store path length, enabling depth queries and shortest-path filtering.
Category: Capabilities
How does Closure Table compare to Nested Set Model?
Closure tables simplify arbitrary subtree and ancestor queries; nested sets excel at contiguous subtree reads but are complex to update.
Category: Comparison
What database features help Closure Tables?
Composite indexes, foreign keys, and constraints ensure integrity and performance. Batch operations help maintain closures.
Category: BestPractices
Are graph databases a better alternative?
For highly connected data and path queries, native graph stores can be superior. In RDBMS-centric stacks, closure tables are pragmatic.
Category: Alternatives