iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article
🥺

Representing Hierarchical Structures in RDBs Using Closure Tables

に公開

What is a Closure Table?

It is a data model used to represent hierarchical structures in relational databases (RDBs).
It is useful when hierarchies are required for requirements such as folders or groups.

Table Definition

We will create table definitions assuming the implementation of a folder structure.
The RDB being used is MySQL 5.7.18.

CREATE TABLE folder (
  id INT(11) NOT NULL auto_increment,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE folder_path (
  id INT(11) NOT NULL auto_increment,
  ancestor_id INT(11) NOT NULL,
  descendant_id INT(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY u_idx_ancestor_id_descendant_id(ancestor_id, descendant_id)
);

Adding a Root Folder

INSERT INTO folder (name)
VALUES ('親フォルダ');

INSERT INTO folder_path (ancestor_id, descendant_id)
VALUES (LAST_INSERT_ID(), LAST_INSERT_ID());

Adding a Child Folder

SET @parent_id=1;

INSERT INTO folder (name)
VALUES ('子フォルダ');

INSERT INTO folder_path (ancestor_id, descendant_id)
SELECT ancestor_id, LAST_INSERT_ID()
FROM folder_path
WHERE descendant_id = @parent_id
UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID();

Getting Folder Depth

SET @folder_id=1;

SELECT
    DISTINCT folder.id
  , folder.name
  , COUNT(folder.id) as depth
FROM folder
INNER JOIN folder_path
ON folder.id = folder_path.ancestor_id
LEFT OUTER JOIN folder_path AS fp
ON fp.descendant_id = folder_path.ancestor_id
WHERE folder.id = @folder_id
GROUP BY folder_path.id;

Getting Folder Paths

SET @folder_id=2;

SELECT
    folder_path.descendant_id AS id
  , CONCAT("/", GROUP_CONCAT(folder_path.name ORDER BY folder_path.depth ASC SEPARATOR "/")) AS path
FROM (
    SELECT
        folder_path.ancestor_id
      , folder_path.descendant_id
      , folder_path.depth
      , folder.name
    FROM (
        SELECT
            folder_path.id
          , folder_path.ancestor_id
          , folder_path.descendant_id
          , COUNT(folder_path.id) as depth
        FROM folder_path
        LEFT OUTER JOIN folder_path AS fp
        ON fp.descendant_id = folder_path.ancestor_id
        WHERE folder_path.descendant_id = @folder_id
        GROUP BY folder_path.id
    ) folder_path
    INNER JOIN folder
    ON folder.id = folder_path.ancestor_id
) AS folder_path
GROUP BY folder_path.descendant_id;

Discussion