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
3
votes
1 answer

How to get child nodes without grandchildren and great-grandchildren? Closure Table pattern

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? I have a sql query: SELECT p.id,…
Michael Joseph Jackson
  • 43
  • 5
3
votes
1 answer

Mondrian/Saiku - Closure Table - Null Pointer Exception

I am currently doing a PoC and facing a problem with closure table. I am using Saiku CE and database is postgres. Everything works until I add a closure table. If I remove closure table hierarchy, I don't get any error. If keep it I get the error. I…
Puneet Tayal
  • 31
  • 1
3
votes
0 answers

Need explanation on this SQL Closure Table Query

As a response to this question, Bill Karwin posted the following query to pull back an ordered sub-tree given any particular tree-node-id: SELECT d.`iD`, d.`subsectionOf`, CONCAT(REPEAT('-', p.`len`), d.`name`) as hier, p.`len`, p.`ancestor`,…
Shaheeb Roshan
  • 611
  • 1
  • 7
  • 17
3
votes
1 answer

How to determine Strahler number on a directed graph for a stream network

Question / example / expected values I need to determine a Strahler number or Strahler stream order for a directed graph representing a stream network. I can derive information forwards and backwards using WITH RECURSIVE queries, but it seems I need…
Mike T
  • 41,085
  • 18
  • 152
  • 203
3
votes
1 answer

Closure Table INSERT statement including the level/distance column

I'm referring Bill Karwin's presentation in order to implement a closure table which will help me manage hierarchies. Unfortunately, the presentation does not show how I could insert/update the Level column mentioned on slide 67; this would have…
Kassem
  • 8,116
  • 17
  • 75
  • 116
3
votes
2 answers

Finding Least Common Ancestor from a Transitive Closure Table

I have a table representing the transitive closure of an organizational hierarchy (i.e., its a tree with a single root): create table ancestry ( ancestor integer, descendant integer, distance integer ); I have another table that…
Faron
  • 1,354
  • 10
  • 23
3
votes
2 answers

Finding the "deepest" child in a hierarchical query

I need some help querying hierarchical data. Here is the single, simple, table, where parent_id references id and may be null for root entries. create table edition ( id NUMBER(20), parent_id NUMBER(20) ); For each…
andbi
  • 4,426
  • 5
  • 45
  • 70
3
votes
4 answers

How to get the parent given a child in SQL SERVER 2005

I have a table like this childid parentid ------------------------ 1 0 2 1 3 2 4 2 5 3 6 4 7 0 8 7 9 8 10 1 If I give a childid as 5, the parentid will be 1(output) If I give a childid…
priyanka.sarkar
  • 25,766
  • 43
  • 127
  • 173
3
votes
4 answers

PostgreSQL pass data from recursive CTE onto function

I have the following problem: I am trying to discover all possible paths from source node (node_s) to target node (node_t). The format of the original table with graph edges is simple: | node_x | node_y | strength | , where "node_x" -> "node_y" is …
aza07
  • 239
  • 1
  • 6
  • 14
2
votes
1 answer

Exceeding NoSQL Item Size limit while preserving read/write performance

I need a way to exceed the NoSQL document item size limitations while preserving read/write performance. All NoSQL implementations have document item size limits (e.g. AWS DynamoDB is 400KB, MongoDB is 16MB). Moreover, the larger the document item…
Fahad Alrashed
  • 1,272
  • 2
  • 11
  • 17
2
votes
0 answers

How to create a tree using Closure Table MySQL

I want to create a user hierarchical tree by using closure table. User table: CREATE TABLE `user` ( `id` INT(11) unsigned NOT NULL AUTO_INCREMENT, `parent_id` INT(11) unsigned NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO user (id,…
Alex Pavlov
  • 571
  • 1
  • 7
  • 24
2
votes
2 answers

mySQL transitive closure table

I have some code I've been using in SQL Server to generate a closure table from another table that has just the direct parent/child relationships, I can run very simple queries against this to determine lineage. Now I am needing to do all this in…
Matt C
  • 1,431
  • 2
  • 14
  • 18
2
votes
0 answers

How can I find a hierarchy from a closure table in MYSQL if I have a list of names within the hierarchy?

Fiddle: http://sqlfiddle.com/#!9/c1495/1 I have a table named "locations": id name parent 1 Sweden 0 2 England 0 3 Stockholm 1 4 Vasteras 1 5 Town 3 6 Town 4 7 London 2 8 Town 7 And…
user2145184
  • 440
  • 4
  • 15
2
votes
1 answer

MySql sorting hierarchical data in a closure table that has repeated nodes

I have a hierarchy that I have represented as a closure table, as described by Bill Karwin. I am trying to write a query that will return the nodes sorted as a depth-first traversal. This reply would solve my problem, except that in my structure…
whistling_marmot
  • 3,561
  • 3
  • 25
  • 39
2
votes
1 answer

Can't migrate in Laravel4 using ClosureTable

I'm using ClosureTable for Laravel. and i am having a problem with migrating. This is what my migration script looks like: //Page_Closure migration script public function up() { Schema::table('page_closure', function(Blueprint $table) { …
olleh
  • 1,248
  • 5
  • 16
  • 43
1 2
3
4 5 6