JSON配列をSQL中間テーブル（多対多）に変換する

JSONからの多対多の関係

JSONオブジェクトが参照の配列（タグ、カテゴリ、ロールなど）を含む場合、これは通常SQLで中間テーブルが必要な多対多の関係を表します。

JSON例

{
  "id": 1,
  "title": "Getting Started with PostgreSQL",
  "tags": ["database", "postgresql", "tutorial", "beginner"]
}

生成されるSQL

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE article_tags (
  article_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  CONSTRAINT fk_article_tags_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  CONSTRAINT fk_article_tags_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE INDEX idx_article_tags_tag_id ON article_tags (tag_id);

なぜ中間テーブルが必要か

リレーショナルデータベースでは、カラムに直接配列を格納することはできません（PostgreSQLには配列型がありますが、参照整合性が失われます）。中間テーブルは：

1. 参照整合性を強制 — すべてのタグ参照が有効なタグを指している必要があります。
2. 双方向のクエリを可能に — 「記事Xのすべてのタグ」と「タグYを持つすべての記事」。
3. 追加のメタデータをサポート — 中間テーブルにadded_atやsort_orderなどのカラムを追加できます。

検出ロジック

コンバーターは以下の場合に中間テーブルを作成します：

1. スカラー値（文字列、数値）の配列 — ルックアップテーブルと中間テーブルの両方を作成。
2. idを持つオブジェクトの配列 — 既存のエンティティを参照する中間テーブルを作成。

複合主キー

中間テーブルはサロゲートIDの代わりに複合主キー(article_id, tag_id)を使用します。これにより：

• 定義により重複した関係を防止。
• article_idによるルックアップ用の自然なカバリングインデックスを提供。
• 不要なオートインクリメントカラムを追加しないことでストレージを節約。

メタデータの追加

より複雑な関係の場合、中間テーブルにカラムを追加します：

CREATE TABLE course_enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
  grade CHAR(2) NULL,
  PRIMARY KEY (student_id, course_id)
);

スカラー配列の代替

参照整合性が不要でデータベースがサポートする場合、PostgreSQLの配列型がより簡単な代替です：

tags TEXT[] NOT NULL DEFAULT '{}'

ただし、クエリ効率が犠牲になり（GINなしでは個別要素のインデックスなし）、タグがマスターリストに存在することを強制できません。