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

Sorting Closure Table Hierarchical Data Structure

You can think this question as follow-up for that one: Sorting a subtree in a closure table hierarchical-data structure Let's consider the modified example (with a new row called rating in category table): -- -- Table `category` -- CREATE TABLE IF…
oezby
  • 15
  • 3
0
votes
0 answers

write a controller class from a model in a closure table

I'm trying to write a controller class from a model class for a closure table. Trying to get my head into it, but it seems difficult. I want to insert entries into a closure table. Here's the model: public $table; public $closure_table =…
N. francis
  • 75
  • 1
  • 12
0
votes
0 answers

Codeigniter: Controller issues with Closure table

I'm having issues implementing a controller for a closure table, I'm trying to INSERT a descendant. Here's what I've done Controller public function add() { $this->load->model('home_model', 'closures'); $node_id =…
N. francis
  • 75
  • 1
  • 12
0
votes
1 answer

codeigniter: closure table - add descendant

I'm trying to add a new descendant, but having difficulties achieving it, it displays some error, Would be grateful if you could take time to review what I've done thus far. Here's Controller public function index() { …
N. francis
  • 75
  • 1
  • 12
0
votes
1 answer

SQL Family Tree genealogy using Closure Table with support for spouse, sibling, in-laws, niblings, etc

I have read Bill Karwin's answer on this post and I was amazed on how it solved my basic problem on a Family Tree Genealogy i've been working on (at least on a parent-child relationship or ancestor-descendant level) using MySQL database. The…
johnsymelle
  • 1
  • 2
0
votes
0 answers

How to copy an array to another Document in MongoDB using Java?

In MongoDB and Java, I am building a binary tree using closure table concept. Say my initial data is as below: { "userid" : "1", "parent" : "root", "position" : "", "ancestry": ["1"] } # ... { "userid" : "7", "parent" : "5", "position" :…
alternat0
  • 43
  • 1
  • 6
0
votes
2 answers

MySQL transitive closure of distance ralation

There is a table distances(city1, city2, dist) that list couple of cities and distances between them. The deal is, that from this information we can get more distances, like there is in table distances dist A -> B and dist B -> C so we can get A ->…
user5457897
0
votes
1 answer

Moving a transitive closure subtree with MySQL

I'm trying to retrofit a transitive closure table into a system that currently uses adjacency lists using MySQL, based on the recipe given in SQL Antipatterns. However, I've run into a snag with the implementation of moving subtrees. I've…
GordonM
  • 31,179
  • 15
  • 87
  • 129
0
votes
0 answers

Recursive analysis of a table in oracle (not hierarchial i.e. not parent-child relationship)

I have this table RC_CHAT:- REFERRAL_ID BY_OFFICE TO_OFFICE STATUS_ID STATUS_DATE CHAT_ID PARENT_CHAT_ID R1 KL12 KL11 3 05-01-14 C1 R1 KL12 KL13 3 06-01-14 C2 R1 …
Ashish Kataria
  • 520
  • 3
  • 11
  • 31
0
votes
1 answer

Running a SELECT query on a closure table with a JOIN?

I have an app set up which has nested comments attached to posts. I decided to use the closure table method (slide 40) for the comments due to how cheap hard disk space is versus how easy it seems to be to query and manage the tree structure.…
Patrick
  • 489
  • 6
  • 15
0
votes
1 answer

SQL query to find final overridden id in adjacency list / closure table

I have an adjacency list table called attribute (which also has a closure table called attribute_closure mapped for ease of recursion). Each entry in the attribute table is one of 4 hierarchical types, with each type able to inherit and override…
Jon L.
  • 2,292
  • 2
  • 19
  • 31
0
votes
1 answer

codeIgniter closure table model

This is the class I am working with: https://gist.github.com/2174233 I have two tables: one table contains users and the other - closures. When I use get_children() method (let's say from the root or another parent), it show only the first level of…
Mister PHP
  • 307
  • 4
  • 17
-1
votes
1 answer

How get from the table all the nodes that have no parent nodes?

How do I get from a table all nodes that have no parent nodes? I am using a (Closure Table) template. I need to get all nodes that have no parent nodes. Consider the fact that each node refers to itself. That is, each node itself is a parent and…
Steve Jobs
  • 57
  • 9
-1
votes
1 answer

Data frame based on transitivity property of

I have a data frame as A: V1 V2 1 3 1 4 3 4 1 6 6 5 I want output which satisfies transitive property on V1 and V2 B: V1 V2 V3 1 3 4
anuja
  • 170
  • 1
  • 2
  • 11
-1
votes
1 answer

Getting lowest hierarchy child in join select using closure table

I have a closure table HIERARCHY ancestor | descendant | depth 100 | 100 | 0 2 | 100 | 1 3 | 100 | 2 4 | 100 | 3 and a joined table PROPERTIES id | key | value 4 | action …
fridaynosaur
  • 1
  • 3
1 2 3 4 5
6