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