EAVとポリモーフィック関連をあえて組み合わせて体感してみた

はじめに

データベース設計において「アンチパターン」は避けるべきものとされていますが、実際にそれを体験してみることで学べることも多いのではないでしょうか。今回は『アンチSQLパターン』の第5章「EAV（エンティティ・アトリビュート・バリュー）」と第6章「ポリモーフィック関連」のアンチパターンを組み合わせたテーブル構成を考え、それに伴う課題や苦労を実体験としてまとめました。

背景：EAVとポリモーフィック関連の概要

EAV（エンティティ・アトリビュート・バリュー）

これは属性を柔軟に管理するために、1つのテーブルで「エンティティ」「属性名」「値」を表現する構造です。しかし、柔軟性の代償として、データの扱いが複雑化し、クエリが煩雑になりがちです。

ポリモーフィック関連

異なる種類のエンティティ間の関連を1つのテーブルで表現する手法です。たとえば、コメントが記事にも写真にもつけられるような場合に、1つの「コメント」テーブルで両方の関係を管理することがあります。この手法も柔軟性が高い反面、整合性やクエリパフォーマンスの問題が生じやすいです。

実験：EAVとポリモーフィック関連を組み合わせたテーブル設計

まずは以下のようなシナリオを設定しました。

• 商品（Product）とサービス（Service）という2つの異なるエンティティが存在する
• これらに共通して属性（Attribute）を自由に定義できる
• さらに、それぞれのエンティティにはコメントをつけられる

テーブル設計

以下のようなシナリオを想定して設計しました：

• 商品（Product）とサービス（Service）という2つの異なるエンティティが存在する
• これらに共通の属性（Attribute）をEAV形式で管理する

DDL（テーブル定義）

商品テーブル

CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

サービステーブル

CREATE TABLE Services (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    hourly_rate DECIMAL(10, 2) NOT NULL
);

属性テーブル（EAV形式 + ポリモーフィック関連）

CREATE TABLE Attributes (
    attribute_id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('Product', 'Service') NOT NULL,
    entity_id INT NOT NULL,
    attribute_name VARCHAR(255) NOT NULL,
    attribute_value TEXT NOT NULL
);

ER図

実装での工夫と苦労

EAV形式でdescriptionを管理

Attributes テーブルの attribute_name 列に「description」という属性名を追加し、その値を attribute_value 列に格納することで、商品やサービスに紐付くdescriptionを管理してみました

attribute_id	entity_type	entity_id	attribute_name	attribute_value
1	Product	1	description	"汚れが落ちる"
2	Service	1	description	"しっかり綺麗に"

クエリの煩雑さ

特定の商品やサービスの属性（例：description）を取得するクエリが複雑になりました。例えば、以下のようなクエリが必要です：

-- 商品に関連するdescriptionを取得するクエリ
SELECT 
    p.name AS product_name,
    a.attribute_value AS description
FROM Products p
JOIN Attributes a 
    ON a.entity_type = 'Product' 
    AND a.entity_id = p.product_id
WHERE a.attribute_name = 'description';

-- サービスに関連するdescriptionを取得するクエリ
SELECT 
    s.name AS service_name,
    a.attribute_value AS description
FROM Services s
JOIN Attributes a 
    ON a.entity_type = 'Service' 
    AND a.entity_id = s.service_id
WHERE a.attribute_name = 'description';

descriptionを取得するだけなのに、なんだか少し冗長な感じがしますよね。
さらに集計を組み合わせるSQLだととても複雑になってしまいます。

SELECT 
    COUNT(DISTINCT p.product_id) AS product_count
FROM Products p
JOIN Attributes a 
    ON a.entity_type = 'Product' 
    AND a.entity_id = p.product_id
WHERE p.price >= 100
  AND a.attribute_name = 'description';

パッと見でわかるでしょうか？
上記のSQLは、100円以上の価格で description が存在する商品の数を調べるSQLのサンプルです。

データ整合性の課題

ポリモーフィック関連の仕組みをAttributes テーブルで実現したことで、entity_type と entity_id のペアが正しいかどうかを保証するのが難しく、distinctからもわかるようにアプリケーションレベルでの検証が必要でした。

学びと反省

• 属性を柔軟に管理できるEAV形式は、データスキーマが頻繁に変化するシステムでは有用です。しかし、属性ごとに型が異なる場合や複雑なクエリが必要になる場合には、慎重な設計が求められます
• ポリモーフィック関連をEAV形式に組み込むことで、柔軟性が向上する一方で、データ整合性の管理がアプリケーション依存になる課題を強く感じました

結論

今回の実験を通じて、アンチパターンを実際に体験することの重要性を改めて実感しました。設計の柔軟性と運用の効率性のバランスをどう取るかが、データベース設計の鍵となります。