SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Closure Table

Closure Table

Let's explore another way to solve Naive Trees antipattern.

We'll cover the following...

The Closure Table solution is a simple and elegant way of storing hierarchies. It involves storing all paths through the tree, not just those with a direct parent-child relationship.

Creating TreePaths table with plain Comments table

To employ the Closure Table solution, we can create another table, TreePaths, in addition to a plain Comments table, having two columns, each of which is a foreign key to the Comments table.

MySQL
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  bug_id BIGINT UNSIGNED NOT NULL,
  author BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME NOT NULL,
  comment TEXT NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
CREATE TABLE TreePaths (
  ancestor BIGINT UNSIGNED NOT NULL,
  descendant BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(ancestor, descendant),
  FOREIGN KEY (ancestor) REFERENCES Comments(comment_id),
  FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);

Then, instead of using the Comments table to store information about the tree structure, we can use the TreePaths table.

Store one row in this table for each pair of nodes in the tree that shares an ancestor/descendant relationship, even if they are separated by multiple levels in the tree. In addition, add a row for each node to reference itself. Check the illustration below to see how the nodes are paired.

Querying the tables

The queries to retrieve ancestors and descendants from this table are even more straightforward than those in the Nested Sets solution.

Finding out descendants of a comment

To retrieve descendants of comment # ...