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
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
  • 17,054
  • 13
  • 68
  • 116
6
votes
2 answers

Rendering a tree from a closure table SELECT statement?

[previous question] I'm trying to add reddit-like comments to an app, and I decided to go with the closure table pattern for database organization. My app database looks somewhat like this: posts +----+-------+ | id | title | +----+-------+ | 1 |…
Patrick
  • 489
  • 6
  • 15
6
votes
1 answer

Django ORM and Closure Tables

I'm trying to model data that's organised as a hierarchical tree using a closure table. The entries that will represent nodes in the tree are nothing fancy and are defined as follows. class Region(models.Model): RegionGuid =…
CadentOrange
  • 3,263
  • 1
  • 34
  • 52
5
votes
1 answer

What's the best way to update a closure table?

I have a table relating records using the adjacency list method (table A), and another table relating the same records using a closure table (table B). They both capture the same graph, so they both need to be kept in sync. The question is, what's…
ladenedge
  • 13,197
  • 11
  • 60
  • 117
5
votes
1 answer

Directed Cyclic Graph with Closure Table in SQL

I'm trying to determine if it is possible to easily model a directed cyclic graph with a closure table (and/or possibly other helper tables) in SQL. For example, suppose I have this directed graph (all pointing down): I'm having trouble modeling…
matt.hallman
  • 141
  • 1
  • 6
5
votes
1 answer

Paginating hierarchical data using closure tables (MySQL)

I am trying to query paged hierarchical comments sorted by score. score is an integer, and the comments table has a self-referential parent_id column. Each page should have at least one root comment followed by its children. If only one root comment…
Bob
  • 81
  • 1
  • 2
5
votes
1 answer

How can I display tree structure in HTML from closure table

I'm storing some hierarchical data in MySQL. For various reasons, I've decided to use closure tables (instead of nested sets, adjacency lists, and the like). It's been working great for me so far, but now I'm trying to figure out how to actually…
Charles
  • 865
  • 1
  • 8
  • 9
4
votes
0 answers

How to replace an additional children table with a depth column in a closure table?

I'm trying to implement closure tables based on this blog post, which features the only complete and functional implementation of closure tables with sqlalchemy, and which I link here just to give credit, but I want to replace the children_table…
S818
  • 391
  • 3
  • 15
4
votes
3 answers

How can I speed up queries that are looking for the root node of a transitive closure?

I have a historical transitive closure table that represents a tree. create table TRANSITIVE_CLOSURE ( CHILD_NODE_ID number not null enable, ANCESTOR_NODE_ID number not null enable, DISTANCE number not null enable, FROM_DATE date…
Jon Bristow
  • 1,675
  • 3
  • 27
  • 42
4
votes
1 answer

Hierarchal data with Doctrine2 using closure table model

I have some existing data stored using the closure table model. I'm new to Doctrine, and trying to implement an Entity for this the "Doctrine way", and not really sure how to proceed. The philosophy I'm trying to follow is that the Entity should…
mfonda
  • 7,873
  • 1
  • 26
  • 30
4
votes
1 answer

Doctrine 2 Tree Extension: Closure Table

I'm using Tree - Nestedset behavior extension for Doctrine 2 and Closure Table Strategy. On my web, users can create folders and subfolders and view them. I implemented this by using Closure Table strategy and I render folders with…
Olga Budnik
  • 1,143
  • 2
  • 8
  • 10
4
votes
1 answer

Closure table with multiple data types?

I've been brushing up on my MySQL lately and I need to make a database with hierarchical data. I have several different types of data that need to be represented in a tree format, but don't know how to go about doing it. For example, Let's say I…
Charles W
  • 2,262
  • 3
  • 25
  • 38
4
votes
2 answers

Deadlocks when concurrent editing a closure tree hierarchy

How can I avoid database deadlocks when using closure_tree to concurrently manipulate a set of models with common attributes on a hierarchical structure? They present in the following flavors: When issuing an #append/prepend_sibling Mysql2::Error:…
elhoyos
  • 849
  • 9
  • 19
4
votes
1 answer

Implementing version history with a closure table schema

I have a custom CMS implementation that stores content nodes in two tables as described in the "closure table" section at http://www.slideshare.net/billkarwin/models-for-hierarchical-data and using this gem https://github.com/mceachen/closure_tree…
Rafael Vega
  • 4,575
  • 4
  • 32
  • 50
4
votes
0 answers

How to build a self referencing table with composite key in fluent api and EF

I'm building a hierarchical Database with "closure table" to build the tree It is a self referencing table, and the two keys should become the primary key. The Problem is, I end up with 5 columns, when I expect only 3. Here is what I tried: …
Wozilla
  • 61
  • 1
  • 8
1
2
3 4 5 6