Posted inDatabase

SQLにおけるポリモーフィック（Polymorphic）関連の設計：実運用から学ぶ3つのアーキテクチャ

Database tutorial - IT technology blog
Database tutorial - IT technology blog

「1つのエンティティ、複数の主体」という課題の解決

SNSのコメント（Comment）機能を構築しているとしましょう。ユーザーは投稿（Post）、動画（Video）、または商品（Product）に対してコメントを付けることができます。最も単純な方法は、post_commentsやvideo_commentsといった3つのテーブルを作成するか、1つのテーブルに3つの外部キー（Foreign Key）カラムを詰め込むことです。しかし、どちらの方法もデータベースを肥大化させ、システムの拡張時にメンテナンスを極めて困難にします。

ポリモーフィック（Polymorphic）関連は、こうした状況の救世主です。これにより、1つのテーブルが単一のリレーションを通じて複数のテーブルと柔軟にリンクできるようになります。LaravelやRailsなどの有名なフレームワークでは、通常IDとTypeのペアを使用してこれを処理します。

-- クイックな実装方法（スタートアップ向け）
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    commentable_id INT NOT NULL, -- Post, Video, または Product の ID
    commentable_type VARCHAR(50) NOT NULL -- 'Post', 'Video' などの値を保存
);

この時のデータ取得クエリは非常にシンプルです：

SELECT * FROM comments WHERE commentable_type = 'Post' AND commentable_id = 10;

設計自体は5分で終わります。しかし、システムが100万レコードに達すると、この構造はパフォーマンスとデータの整合性の面で致命的な弱点を露呈します。

ポリモーフィック設計の一般的な3つの手法

多くの実務プロジェクトを経て、私は「唯一の正解」となるアーキテクチャは存在しないという結論に至りました。選択は、開発スピードを優先するか、データの安全性を優先するかによって決まります。

1. Polymorphic Association (IDとType의 ペア)

これは最も柔軟なアプローチです。commentsテーブルの構造を変更することなく、新しいエンティティ（PhotoやAlbumなど）を自由に追加できます。

  • メリット: 実装が非常に速く、アプリケーション層のコードがすっきりとします。
  • デメリット: 外部キー制約を作成できません。データベース側でcommentable_idが実際に存在することを保証できません。Postを削除する場合、ゴミデータが残らないように手動でコメントを削除するコードを書く必要があります。

2. Exclusive Belongs To (複数のNULL許容外部キー)

汎用的なIDカラムを使う代わりに、エンティティの種類ごとに個別の外部キーカラムを作成します。

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    content TEXT,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    video_id INT REFERENCES videos(id) ON DELETE CASCADE,
    CHECK (
        (post_id IS NOT NULL)::int + (video_id IS NOT NULL)::int = 1
    )
);
  • メリット: 外部キーの強力な機能やON DELETE CASCADEを活用できます。SQL標準のインデックスにより、クエリのパフォーマンスが非常に高くなります。
  • デメリット: エンティティの種類が多いと、テーブルが「煩雑」になります。新しいコンテンツタイプを追加するたびに、ALTER TABLEでカラムを追加する必要があります。

3. Class Table Inheritance (中間親テーブル)

これは最も正統的で正規化（Normalized）された手法です。IDを管理するための共通の「インターフェース」テーブルを作成します。

-- 共通の識別子テーブル
CREATE TABLE commentable_entities (id SERIAL PRIMARY KEY);

-- PostはこのテーブルからIDを継承する
CREATE TABLE posts (
    id INT PRIMARY KEY REFERENCES commentable_entities(id),
    title VARCHAR(255)
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    entity_id INT REFERENCES commentable_entities(id),
    content TEXT
);

この方法は、ポリモーフィック関連を伝統的な1対多（1-N）のリレーションに変換します。データは非常にクリーンで透明性が高くなります。

インデックスとクエリの最適化テクニック

手法1（IDとType）を使用する際の最も一般的な間違いは、idカラムだけにインデックスを貼ることです。データが大量になると、データベースは正しいtypeを絞り込むためにフルテーブルスキャン（Full Table Scan）を行う必要があります。

解決策: 常に複合インデックス（Composite Index）を使用してください。

CREATE INDEX idx_comments_type_id ON comments (commentable_type, commentable_id);

実際には、typeカラムは値の種類が少ない（選択性が低い）ため、インデックスの先頭に配置します。これにより、データベースは検索範囲を大幅に早く絞り込むことができます。

新しいポリモーフィック構造にインポートするために、CSVファイルからデータを処理する必要がある場合があります。複雑なPythonスクリプトを書く代わりに、私はよくtoolcraft.app/ja/tools/data/csv-to-jsonを使ってブラウザ上ですばやく JSONに変換します。このツールはローカルで処理されるため、プロジェクトデータの安全性も高いです。

N+1クエリ問題の解消

N+1問題は、20件のコメントを取得した後に、それぞれの親記事のタイトルを取得するためにさらに20件の個別のクエリが発生する場合に起こります。ポリモーフィック関連では、データが複数のテーブルに分散しているため、この問題はより深刻です。

ORMのEager Loadingを使用するか、純粋なSQLを書く場合はUNION ALLを使用しましょう：

(SELECT c.*, p.title as parent_name FROM comments c 
 JOIN posts p ON c.commentable_id = p.id 
 WHERE c.commentable_type = 'Post' LIMIT 10)
UNION ALL
(SELECT c.*, v.name as parent_name FROM comments c 
 JOIN videos v ON c.commentable_id = v.id 
 WHERE c.commentable_type = 'Video' LIMIT 10);

実務経験からのアドバイス

ゴミデータのクリーンアップという「代償」を何度も払ってきた経験から、いくつかのアドバイスがあります：

  • スピード重視のスタートアッププロジェクト：IDとTypeを優先。初期段階では外部キーにこだわりすぎず、ただし必ず最初から複合インデックスを貼ること。
  • 金融システムやERP：ベーステーブル（手法3）の使用が必須です。データの不整合は許されません。
  • メモリの節約：typeカラムにはTEXTではなくVARCHAR(30)やENUMを使用しましょう。
  • PostgreSQLを使用する場合：各ポリモーフィックエンティティ固有の属性を保存するために、JSONBとの組み合わせを検討してください。

データベース設計はトレードオフです。CREATE TABLEコマンドを実行する前に、柔軟性と安全性のバランスを慎重に検討してください。

Share:
Tags: