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

90 questions
2
votes
0 answers

Maintain node order column in SQL/MySql closure tables

I have read and used http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/ and http://www.slideshare.net/billkarwin/models-for-hierarchical-data, but does not solve my problem. My question is: How is it possible to…
user3747300
  • 21
  • 2
2
votes
2 answers

Multiple vertical menu from closure table

I'm looking for some multiple vertical menu such here. I don't want any drop menu. I'm using in my mysql database typical closure table hierarchy (ancestor/descendant/depth) for categories and I want to render they. To get all parents and childrens…
Jaroslav Klimčík
  • 4,548
  • 12
  • 39
  • 58
2
votes
1 answer

Finding the transitive closure of a graph

I am trying to calculate a transitive closure of a graph. Let`s consider this graph as an example (the picture depicts the graph, its adjacency and connectivity matrix): Using Warshall's algorithm, which i found on this page, I generate this…
TheAptKid
  • 1,559
  • 3
  • 25
  • 47
1
vote
3 answers

Closure Table the best SELECT QUERY

I am developing threaded comments system for MySQL and PHP. I chose Closure Table pattern, but I have problem. I need query (queries) to get whole tree. How to do that? I have searched a lot about this, but I can´t find nothing optimal. If you have…
Filip Glazar
  • 43
  • 1
  • 8
1
vote
0 answers

MySQL Query for Closure Table

Current USER table: Field Type id int(11) firstname varchar(64) lastname varchar(64) status varchar(5) permission smallint(5) Current USER_RELATIONS table: (it has 2 FOREIGN KEYs - ancestor->USER.id -…
Louis Van Zyl
  • 33
  • 5
1
vote
1 answer

SQLite Closure Tabe: How to improve my SQL to split a tree

here is a classic tree paths table: CREATE TABLE treepaths( tpa_ance INTEGER NOT NULL, -- ancestor tpa_desc INTEGER NOT NULL, -- descendant tpa_leng INTEGER NOT NULL, -- lenght UNIQUE(tpa_ance, tpa_desc) ); Initial…
Danilo
  • 193
  • 10
1
vote
1 answer

How to search tree structure with multiple types with TypeORM

thanks for reading and provide any suggestions or any keyword I can do more research. Quite new to TypeORM and have an issue on searching the tree structure with TypeORM. The tree structure is a typical tree structure but it may have different node…
Chun Young
  • 83
  • 6
1
vote
2 answers

sql - Deletion in closure table with multiple same paths

I have the following hierarchical structure: A -> E -> C -> D | | |-> B -> D Here is the closure table I've come up with: | Ancestor | Descendant | Depth | | A | A | 0 | | B | B | 0 | | C | C …
Zenor27
  • 171
  • 1
  • 11
1
vote
1 answer

How to rebuild a tree hierarchy from a closure table (Entity Framework Core 5)

I'm having a tough time building my tree hierarchy for a dto. The tree hierarchy is captured in a closure table with depths. The closure table is built with triggers in the database, and is not relationally mapped to the Teams table in EF. This is…
Brice
  • 21
  • 4
1
vote
1 answer

TypeORM migration file for @Tree('closure-table') entity

@Entity() @Tree('closure-table') class Comment { @PrimaryGeneratedColumn('uuid') id: string; @TreeParent() parent: Comment; @TreeChildren({ cascade: true }) children: Comment[]; } Is there any way to generate a TypeORM…
critrange
  • 5,652
  • 2
  • 16
  • 47
1
vote
1 answer

How to get only the first level of depth child nodes?

I am using tree storage. Closure Table pattern. https://towardsdatascience.com/closure-table-pattern-to-model-hierarchies-in-nosql-c1be6a87e05b How to get child nodes without grandchildren and great-grandchildren? For example, from ancestor C, get…
Michael Joseph Jackson
  • 43
  • 5
1
vote
1 answer

Print a Complete Transitive Closure Tree

On our online shop (PHP/MySQL), for our category structure we are using a transitive closure table (ancestor, descendant, length) as described by Bill Karwin. I am finding it very flexible and useful, but can not work out how to print the complete…
Paul
  • 73
  • 6
1
vote
2 answers

Recursive query with optional depth limit with MySQL 5.6

I have two table schemas (MySQL 5.6 so no CTE), roughly looking like this: CREATE TABLE nodes ( node_id INT PRIMARY KEY, name VARCHAR(10) ); CREATE TABLE edges ( edge_id INT PRIMARY KEY, source INT, target INT, FOREIGN KEY (source)…
Zizheng Tai
  • 6,170
  • 28
  • 79
1
vote
1 answer

Transitive-Closure Table Restructure

How would I be able to retrieve a full-tree from the current structure, or refactor the current table structure to allow for an optimized recursive query? Issue Unable to retrieve full-tree of components from base component without iteration. A…
Will B.
  • 17,883
  • 4
  • 67
  • 69
1
vote
0 answers

Mysql Closure table address descendant in PHP

I have Post Table +----+-------+--------------+ | Id | Name | Message | +====+=======+==============+ | 1 | John | John's msg | | 2 | Marco | Marco's msg | | 3 | Ivan | Ivan's msg | +----+-------+--------------+ Comment table,…
Energizem
  • 211
  • 1
  • 5
  • 11
1 2 3
4
5 6