はじめに
ECサイトによくあるカテゴリの階層構造をどう表現すればいいのか、調べて面白いモデルを見つけたのでまとめてみます。
実際に使用されているモデルなんでしょうか？ぜひコメントで教えてください！
（追記）
下記の記事によるとアンチパターンの一つだったみたいです( ^ω^ )
SQLアンチパターン ナイーブツリー
これから紹介する手法では、「自己結合」と言う仕組みを用いています。テーブルが自分自身に対して外部キー制約を持つことで、テーブル内の行が他の行を参照することを可能にしています。
隣接リストモデル
隣接リストモデルでは、各レコードが親（または親が存在しない場合はNULL）を参照するように外部キー( parent_id )を持っています。つまり、各カテゴリは親カテゴリを参照する形となります。
以下に、ECサイトの商品カテゴリを管理するための「隣接リストモデル」のテーブル例を示します。
親カテゴリがない場合（最上位カテゴリの場合）、parent_idはNULLとなります。
このテーブルでは、"Electronics"と"Clothing"が最上位のカテゴリで、"Computers"と"Mobile Phones"は"Electronics"の下位カテゴリ、"Men's"と"Women's"は"Clothing"の下位カテゴリ、といった感じになります。
iOS, Android, Windowsは後からテーブルに追加した形になっています。
| id | category_name | parent_id |
|---|---|---|
| 1 | Electronics | NULL |
| 2 | Computers | 1 |
| 3 | Laptops | 2 |
| 4 | Desktops | 2 |
| 5 | Mobile Phones | 1 |
| 6 | Smartphones | 5 |
| 7 | Basic Phones | 5 |
| 8 | Clothing | NULL |
| 9 | Men's | 8 |
| 10 | Women's | 8 |
| 11 | iOS | 6 |
| 12 | Android | 6 |
| 13 | Windows | 6 |
階層構造をわかりやすく表現し直すと以下のようになっています。
- 1 Electronics
- 2 Computers
- 3 Laptops
- 4 Desktops
- 5 Mobile Phones
- 6 Smartphones
- 11 iOS
- 12 Android
- 13 Windows
- 7 Basic Phones
- 6 Smartphones
- 2 Computers
- 8 Clothing
- 9 Men's
- 10 Women's
パス列挙モデル
パス列挙モデルは階層データの一つの表現方法で、各ノードが自分自身までのパスを格納する方法です。パスは一般的に文字列として格納され、各ノードはその親ノードのパスに自身のIDを追加します。
以下に、ECサイトの商品カテゴリを管理するための「パス列挙モデル」のテーブル例を示します。
| id | category_name | path_ids |
|---|---|---|
| 1 | Electronics | /1 |
| 2 | Computers | /1/2 |
| 3 | Laptops | /1/2/3 |
| 4 | Desktops | /1/2/4 |
| 5 | Mobile Phones | /1/5 |
| 6 | Smartphones | /1/5/6 |
| 7 | Basic Phones | /1/5/7 |
| 8 | Clothing | /8 |
| 9 | Men's | /8/9 |
| 10 | Women's | /8/10 |
| 11 | iOS | /1/5/6/11 |
| 12 | Android | /1/5/6/12 |
| 13 | Windows | /1/5/6/13 |
閉包テーブルモデル
このモデルでは、すべての親と子の関係を別のテーブルに保存します。これにより、特定のカテゴリ以下のすべての子孫を取得するのが非常に効率的になります。また、ツリーの更新も比較的簡単です。
以下に、ECサイトの商品カテゴリを管理するための「閉包テーブルモデル」のテーブル例を示します。
まず、各カテゴリを表すテーブルがあります。
| id | category_name |
|---|---|
| 1 | Electronics |
| 2 | Computers |
| 3 | Laptops |
| 4 | Desktops |
| 5 | Mobile Phones |
| 6 | Smartphones |
| 7 | Basic Phones |
| 8 | Clothing |
| 9 | Men's |
| 10 | Women's |
| 11 | iOS |
| 12 | Android |
| 13 | Windows |
そして、その階層関係を表す「閉包テーブル」があります。
| ancestor | descendant | depth |
|---|---|---|
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 1 | 4 | 2 |
| 1 | 5 | 1 |
| 1 | 6 | 2 |
| 1 | 7 | 2 |
| 1 | 11 | 3 |
| 1 | 12 | 3 |
| 1 | 13 | 3 |
| 2 | 2 | 0 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
| 5 | 5 | 0 |
| 5 | 6 | 1 |
| 5 | 7 | 1 |
| 5 | 11 | 2 |
| 5 | 12 | 2 |
| 5 | 13 | 2 |
| 6 | 6 | 0 |
| 6 | 11 | 1 |
| 6 | 12 | 1 |
| 6 | 13 | 1 |
| 7 | 7 | 0 |
| 8 | 8 | 0 |
| 8 | 9 | 1 |
| 8 | 10 | 1 |
| 9 | 9 | 0 |
| 10 | 10 | 0 |
| 11 | 11 | 0 |
| 12 | 12 | 0 |
| 13 | 13 | 0 |
この閉包テーブルでは、「ancestor」列が祖先のノード、「descendant」列がその子孫のノード、「depth」列が祖先と子孫との間の距離（階層の深さ）を表しています。例えば、レコード (1, 12, 3) は、カテゴリ "Android"（ID = 12）がカテゴリ "Electronics"（ID = 1）の3階層下にあることを示しています。同様に、レコード (2, 3, 1) は、カテゴリ "Laptops"（ID = 3）がカテゴリ "Computers"（ID = 2）の1階層下にあることを示しています。
depth = o は自分自身を指しています。
う〜ん、とっつきにくいなぁ。。。
最後に
実務でカテゴリを設計する場合はどのような設計をするのでしょうか？
ぜひご存じの方がいましたらコメントお願いします！！
それではまた！ m(_ _)m