実践データモデリングパターン：階層構造・タグ・多言語対応の正しい設計法

データモデリングパターンを学ぶ意義

データベースを設計していると繰り返し登場する構造があります。階層構造（メニュー、カテゴリ、組織図）、タグ/ラベル、多言語対応、履歴管理などです。これらのパターンに対して検証済みの設計方法を知っておくと、試行錯誤を減らし、より効率的なスキーマを作れます。

パターン1：階層構造（ツリー構造）

カテゴリ、メニュー、組織図、コメントスレッドなど親子関係があるツリー構造です。

方法1：隣接リスト（Adjacency List）— 最もシンプル

CREATE TABLE categories (
  id        INT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)  -- 自己参照
);

• 利点：シンプル、直接の親/子取得が速い
• 欠点：全ツリー取得には再帰クエリが必要

-- PostgreSQL再帰CTEで全ツリー取得
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

方法2：クロージャーテーブル（Closure Table）— 最も柔軟

CREATE TABLE category_closure (
  ancestor_id   INT REFERENCES categories(id),
  descendant_id INT REFERENCES categories(id),
  depth         INT,
  PRIMARY KEY (ancestor_id, descendant_id)
);

全祖先・子孫関係を別テーブルに保存。読み書き両方に効率的です。

パターン2：タグシステム

記事や商品に複数タグを付ける典型的なN:M関係です。

基本構造

CREATE TABLE tags (
  id   INT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

タグ検索

-- 複数タグを全て含む記事（AND条件）
SELECT p.title
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ('database', 'sql')
GROUP BY p.id, p.title
HAVING COUNT(DISTINCT t.name) = 2;

PostgreSQL配列で簡略化

CREATE TABLE posts (
  id    INT PRIMARY KEY,
  title VARCHAR(255),
  tags  TEXT[]
);

CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
SELECT title FROM posts WHERE tags @> ARRAY['database', 'sql'];

パターン3：多言語対応（i18n）

方法1：翻訳テーブル

CREATE TABLE products (
  id    UUID PRIMARY KEY,
  price DECIMAL(10,2)
);

CREATE TABLE product_translations (
  product_id  UUID REFERENCES products(id) ON DELETE CASCADE,
  language    CHAR(2) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY (product_id, language)
);

-- 日本語取得（なければ英語にフォールバック）
SELECT p.id, COALESCE(t_ja.name, t_en.name) AS name
FROM products p
LEFT JOIN product_translations t_ja ON p.id = t_ja.product_id AND t_ja.language = 'ja'
LEFT JOIN product_translations t_en ON p.id = t_en.product_id AND t_en.language = 'en';