Stack Overflow
Stack Overflow

Questions tagged [transitive-closure-table]

A transitive closure table is a method for storing hierarchical information in a flat database. It supports referential integrity like adjacency list, but also supports querying hierarchies of arbitrary depth like nested sets.

A transitive closure table is a method for storing a hierarchical graph in a flat database. It supports referential integrity like adjacency lists, but also supports querying hierarchies of arbitrary depth like nested sets (also known as MPTT), smoothing out some of the difficulties inherent in each in exchange for storage space.

Example hierarchy

Let's start out with a tree like this (IDs are in bold):

  • 1 Pet shop
    • 2 Fish
      • 3 Zebrafish
      • 4 Tilapia
    • 5 Rodents
      • 6 Gerbil
      • 7 Squirrels
        • 8 Flying squirrel
        • 9 Red squirrel
    • 10 Birds
      • 11 Cardinal

Closure table

You need a separate table to store your hierarchical information, unlike either adjacency lists or nested sets. For each node, you store the entire path as a set of individual records. For example, 'Squirrels' (node 7) has the following records:

| ancestor | descendant |
-------------------------
| 1        | 7          |
| 5        | 7          |
| 7        | 7          |
| 7        | 8          |
| 7        | 9          |

You'll note a few things:

  • All the ancestors of node 7 have their own record, so you can find out the path of a node just by going SELECT * FROM closure WHERE descendant = 7 (if you're storing your records in SQL).
  • All the direct and indirect descendants can be retrieved as well, by going SELECT * FROM closure WHERE ancestor = 7.
  • Each node is an ancestor and descendant of itself.

Pros

  • Just as with nested sets, an entire path or tree can be retrieved quite simply with one query.
  • Just as with adjacency lists, referential integrity is preserved.
  • Write queries are quite fast and straightforward -- much simpler than nested sets.

Cons

  • Still more complex to understand than adjacency lists.
  • Sacrifices storage space for efficiency.
  • Requires a separate table (although a database that supports triggers can minimize the maintenance cost of this issue).

References

84 questions
18
votes
1 answer

Hierarchical SQL data (Recursive CTE vs HierarchyID vs closure table)

I have a set of hierarchical data being used in a SQL Server database. The data is stored with a guid as the primary key, and a parentGuid as a foreign key pointing to the objects immediate parent. I access the data most often through Entity…
jp36
  • 1,733
  • 16
  • 27
16
votes
1 answer

How to traverse a hierarchical tree-structure structure backwards using recursive queries

I'm using PostgreSQL 9.1 to query hierarchical tree-structured data, consisting of edges (or elements) with connections to nodes. The data are actually for stream networks, but I've abstracted the problem to simple data types. Consider the example…
Mike T
  • 34,456
  • 15
  • 128
  • 169
15
votes
1 answer

MySQL Closure Table hierarchical database - How to pull information out in the correct order

I have a MySQL database holding hierarchical data using the Closure Table method. A simple sample database create script follows the question. My issue at the moment is how do I pull data out of the database in the correct order? I'm currently using…
Justin808
  • 19,126
  • 41
  • 143
  • 241
15
votes
1 answer

Sorting a subtree in a closure table hierarchical-data structure

I would like to ask you to help me with the problem with sorting of the hierarchical data structure stored as a closure table. I wanted to use this structure to store my website menu. Everything works fine, but the problem is that I do not know how…
JCZ
  • 370
  • 5
  • 16
14
votes
2 answers

Recursive query used for transitive closure

I've created a simple example to illustrate transitive closure using recursive queries in PostgreSQL. However, something is off with my recursive query. I'm not familiar with the syntax yet so this request may be entirely noobish of me, and for that…
Dowwie
  • 1,453
  • 1
  • 15
  • 22
12
votes
2 answers

hierarchical data in a database: recursive query vs. closure tables vs. graph database

I'm starting on a new project that has some hierarchical data and I'm looking at all the options for storing that in a database at the moment. I am using PostgreSQL, which does allow recursive querying. I also looked into design patterns for…
tospo
  • 561
  • 6
  • 12
12
votes
3 answers

How to maintain a transitive closure table efficiently?

I have a DAG in my relational database (Firebird) with two tables edge and node (adjacency list model). I want to query them recursively, but found recursive queries very inefficient. So I tried to implement triggers to maintain the transitive…
Christoph Walesch
  • 2,129
  • 2
  • 33
  • 44
11
votes
3 answers

Recursive query challenge - simple parent/child example

Note: with help from RhodiumToad on #postgresql, I've arrived at a solution, which I posted as answer. If anyone can improve on this, please chime in! I have not been able to adapt a previous recursive query solution to the following directed…
Dowwie
  • 1,453
  • 1
  • 15
  • 22
11
votes
3 answers

How can I create a closure table using data from an adjacency list?

I have a database containing a hierarchy of categories stored using the adjacency list model. The hierarchy is 3 levels deep (not including an imaginary root node) and contains approx 1700 nodes. Nodes in the 2nd and 3rd levels can have multiple…
Adnan
  • 371
  • 4
  • 11
9
votes
1 answer

What are the differences in these SQL closure table examples?

I am having some difficulty wrapping my mind around SQL closure tables, and would like some assistance in understanding some of the examples I have found. Lets say I have a table called sample_items with the following hierarchical data: id name …
Seth McCauley
  • 864
  • 11
  • 24
7
votes
1 answer

Depth in MYSQL and Closure Table Trees

How would I go about populating a closure table's depth/length column when inserting a new node to the tree? The values in ancestor and descendant are IDs from another table that represent pages to be arranged in a tree structure. Closure…
Guy
  • 856
  • 1
  • 9
  • 26
7
votes
1 answer

closure tables with entity framework 6

I want to implement hierarchical data structure (e.g Product --> Product 2 ----> Product3, Product 2----> Product4) using entity framework 6 code first approach. There are several approaches available but i think closure table approach is one that…
Nabeel
  • 684
  • 2
  • 8
  • 23
6
votes
1 answer

MySQL hierarchical data help - Closure Table Method

I'm trying to implement a system in MySQL to store hierarchical data. I've decided to go with the system implemented here as described by Bill Karwin starting on slide number 40. I'm trying to setup the database so the EntryPaths table is maintained…
Justin808
  • 19,126
  • 41
  • 143
  • 241
6
votes
1 answer

Does SQLAlchemy support "closure tables?"

I've been reading about closure tables as a way of modeling hierarchies over SQL. Does [SQLAlchemy] have any built-in support for creating and traversing hierarchical collections of object instances (tree structured collections) using closure…
Jim Dennis
  • 15,808
  • 11
  • 58
  • 108
6
votes
1 answer

Closure table equivalent for graph structures in SQL

This question How to store tree structure in sql? lead to the idea of a Closure table for storing trees that is optimal in many ways. The question is is there something along these lines for graph structures in SQL. I saw this paper which seems to…
Lance Pollard
  • 66,757
  • 77
  • 237
  • 416
1
2 3 4 5 6