N階層のデータ構造をRDSを用いて閉包テーブル(Closure Table)で表現したい

## 概要
フォルダ／ディレクトリ構造や組織図など、システム設計をする際には、単純な親子関係で表しきれない構造が出てくることが多々あります。

この記事では図書館の蔵書検索に使うカテゴリーを例にして、N階層の親子関係を扱うテーブル設計をしていきます。

なお、下記の記事を参考にしました。
https://teitei-tk.hatenablog.com/entry/2020/11/30/130000

## 表現したい構造
下記の構造を表現する方法を考えます。

- 文系
  - 哲学
    - 東洋思想
      - 日本思想
      - インド哲学. バラモン教
    - 西洋哲学
      - 古代哲学
      - 中世哲学
      - 近代哲学
  - 歴史
    - 日本史
      - 北海道地方
      - 東北地方
      - 関東地方
    - アジア史. 東洋史
      - 朝鮮
      - 中国
      - 東南アジア
    - ヨーロッパ史. 西洋史
      - 古代ギリシア
      - 古代ローマ
      - イギリス. 英国
- 理系
  - 自然科学
    - 数学
      - 代数学
      - 数論［整数学］
    - 物理学
      - 理論物理学
      - 力学


[日本十進分類表(NDC)区分表](https://www.libnet.pref.okayama.jp/shiryou/ndc/ndc40.htm)

岡山県立図書館ウェブサイトの日本十進分類表(NDC)区分表より一部抜粋＆文系および理系を足しました。
4階層になっています。
階層ごとにテーブルを用意しても表現はできますが、5階層目以降や文系／理系の上位概念が出てきたときに対応しにくくなります。

## 用意するテーブル

### categoriesテーブル
カテゴリーを表すテーブルです。

ノードの情報のみを持たせます。（カテゴリー自身は親子関係を知りません）
```
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);
```

下記SQLを流します。

```
INSERT INTO categories VALUES
    (1, '文系'),
    (2, '哲学'),
    (3, '東洋思想'),
    (4, '日本思想'),
    (5, 'インド哲学. バラモン教'),
    (6, '西洋哲学'),
    (7, '古代哲学'),
    (8, '中世哲学'),
    (9, '近代哲学'),
    (10, '歴史'),
    (11, '日本史'),
    (12, '北海道地方'),
    (13, '東北地方'),
    (14, '関東地方'),
    (15, 'アジア史. 東洋史'),
    (16, '朝鮮'),
    (17, '中国'),
    (18, '東南アジア'),
    (19, 'ヨーロッパ史. 西洋史'),
    (20, '古代ギリシア'),
    (21, '古代ローマ'),
    (22, 'イギリス. 英国'),
    (23, '理系'),
    (24, '自然科学'),
    (25, '数学'),
    (26, '代数学'),
    (27, '数論[整数学]'),
    (28, '物理学'),
    (29, '理論物理学'),
    (30, '力学');
```

### category_closureテーブル
親子関係を表すテーブルです。
自分自身も子とみなし、自身より下のカテゴリーを全て保存します。

```
CREATE TABLE category_paths (
    ancestor_id INT NOT NULL,
    descendant_id INT NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id),
    FOREIGN KEY (ancestor_id) REFERENCES categories(id),
    FOREIGN KEY (descendant_id) REFERENCES categories(id)
);
```
下記のSQLを流すと例で挙げた構造が表現されます。

```
INSERT INTO category_paths (ancestor_id, descendant_id) VALUES
-- 文系（自身も含む）の下のカテゴリーをすべて列挙
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),
(1, 9),
(1, 10),
(1, 11),
(1, 12),
(1, 13),
(1, 14),
(1, 15),
(1, 16),
(1, 17),
(1, 18),
(1, 19),
(1, 20),
(1, 21),
(1, 22),
-- 哲学（自身も含む）の下のカテゴリーをすべて列挙
(2, 2),
(2, 3),
(2, 4),
(2, 5),
(2, 6),
(2, 7),
(2, 8),
(2, 9),
-- 東洋思想（自身も含む）の下のカテゴリーをすべて列挙
(3, 3),
(3, 4),
(3, 5),
-- 日本思想は下のカテゴリーがないので自身のみ
(4, 4),
-- インド哲学. バラモン教は下のカテゴリーがないので自身のみ
(5, 5),
-- 西洋哲学（自身も含む）の下のカテゴリーをすべて列挙
(6, 6),
(6, 7),
(6, 8),
(6, 9),
-- 古代哲学は下のカテゴリーがないので自身のみ
(7, 7),
-- 中世哲学は下のカテゴリーがないので自身のみ
(8, 8),
-- 近代哲学は下のカテゴリーがないので自身のみ
(9, 9),
-- 歴史（自身も含む）の下のカテゴリーをすべて列挙
(10, 10),
(10, 11),
(10, 12),
(10, 13),
(10, 14),
(10, 15),
(10, 16),
(10, 17),
(10, 18),
(10, 19),
(10, 20),
(10, 21),
(10, 22),
-- 日本史（自身も含む）の下のカテゴリーをすべて列挙
(11, 11),
(11, 12),
(11, 13),
(11, 14),
-- 北海道地方は下のカテゴリーがないので自身のみ
(12, 12),
-- 東北地方は下のカテゴリーがないので自身のみ
(13, 13),
-- 関東地方は下のカテゴリーがないので自身のみ
(14, 14),
-- アジア史. 東洋史（自身も含む）の下のカテゴリーをすべて列挙
(15, 15),
(15, 16),
(15, 17),
(15, 18),
-- 朝鮮は下のカテゴリーがないので自身のみ
(16, 16),
-- 中国は下のカテゴリーがないので自身のみ
(17, 17),
-- 東南アジアは下のカテゴリーがないので自身のみ
(18, 18),
-- ヨーロッパ史. 西洋史（自身も含む）の下のカテゴリーをすべて列挙
(19, 19),
(19, 20),
(19, 21),
(19, 22),
-- 古代ギリシアは下のカテゴリーがないので自身のみ
(20, 20),
-- 古代ローマは下のカテゴリーがないので自身のみ
(21, 21),
-- イギリス. 英国は下のカテゴリーがないので自身のみ
(22, 22),
-- 理系（自身も含む）の下のカテゴリーをすべて列挙
(23, 23),
(23, 24),
(23, 25),
(23, 26),
(23, 27),
(23, 28),
(23, 29),
(23, 30),
-- 自然科学（自身も含む）の下のカテゴリーをすべて列挙
(24, 24),
(24, 25),
(24, 26),
(24, 27),
(24, 28),
(24, 29),
(24, 30),
-- 数学（自身も含む）の下のカテゴリーをすべて列挙
(25, 25),
(25, 26),
(25, 27),
-- 代数学は下のカテゴリーがないので自身のみ
(26, 26),
-- 数論[整数学]は下のカテゴリーがないので自身のみ
(27, 27),
-- 物理学（自身も含む）の下のカテゴリーをすべて列挙
(28, 28),
(28, 29),
(28, 30),
-- 理論物理学は下のカテゴリーがないので自身のみ
(29, 29),
-- 力学は下のカテゴリーがないので自身のみ
(30, 30);
```

## データの操作
### 取得
#### 自身以下のカテゴリーを取得する場合
例）数学以下のカテゴリーを取得したい場合
ancestor_idが25（数学）のものを取得するだけです。

##### SQL
```
SELECT categories.id, categories.name
  FROM categories
  INNER JOIN category_paths ON categories.id = category_paths.descendant_id
  WHERE category_paths.ancestor_id = 25;
```

##### 結果
|id|name|
|---:|:---|
|25|数学|
|26|代数学|
|27|数論[整数学]|

数学自身を含めた結果が返ってきました。


#### 自身以上のカテゴリーを取得する場合
例）近代哲学以上のカテゴリーを取得したい場合
descendant_idが9（近代哲学）のものを取得するだけです。
```
SELECT categories.id, categories.name
  FROM categories
  INNER JOIN category_paths ON categories.id = category_paths.ancestor_id
  WHERE category_paths.descendant_id = 9;
```

### カテゴリを追加する
例）数学カテゴリの下に「幾何学」を加えます。
まずはcategoriesに「幾何学」を登録します。

```
INSERT INTO categories VALUES
    (31, '幾何学'),
```

category_pathsテーブルには、「幾何学」以上のカテゴリーを「幾何学」の親として登録します。

```
INSERT INTO category_paths (ancestor_id, descendant_id)
  SELECT category_paths.ancestor_id, 31 -- 「幾何学」のidが31
    FROM category_paths
    WHERE category_paths.descendant_id = 25 -- 「数学」のidが25
  UNION All
    select 31, 31 -- 自分自身
```

数学以下を確認

##### SQL
```
SELECT categories.id, categories.name
  FROM categories
  INNER JOIN category_paths ON categories.id = category_paths.descendant_id
  WHERE category_paths.ancestor_id = 25;
```

##### SQL実行結果
|id|name|
|---:|:---|
|25|数学|
|26|代数学|
|27|数論[整数学]|
|31|幾何学|

きちんと幾何学が入っているのがわかります。

### カテゴリーを削除する
例）先ほど加えた「幾何学」を削除します。
descendant_idが31（幾何学）のものを削除するだけです。

```
DELETE FROM category_paths
WHERE category_paths.descendant_id = 31;

DELETE FROM categories
WHERE categories.id = 31;
```

数学以下を確認

##### SQL
```
SELECT categories.id, categories.name
  FROM categories
  INNER JOIN category_paths ON categories.id = category_paths.descendant_id
  WHERE category_paths.ancestor_id = 25;
```

##### SQL実行結果
|id|name|
|---:|:---|
|25|数学|
|26|代数学|
|27|数論[整数学]|

幾何学が削除されました。

## 課題
- 「このカテゴリーは上から数えて何階層目なのか？」を知るためには、深さも保存しておかないと大変そう
- 階層が深くなればなるほどデータが増えていく