柔軟なデータモデル：EAVとJSONカラム

多様なデータ型や頻繁に変化する要件を扱うアプリケーションでは、従来のリレーショナルテーブル設計が煩雑になることがあります。柔軟なデータモデリングのための一般的なパターンとして、エンティティ・属性・値（EAV）パターンとJSONカラムの利用があります。これらのアプローチにより、エンティティごとに異なる属性や、まばらで予測できないデータを、データベーススキーマを頻繁に変更することなく格納できます。

EAVモデルは、主に3つの要素で構成されます：エンティティ（例：商品や顧客）、属性（例：色、重さ、保証）、およびその属性の値です。各属性と値のペアは個別の行として保存されるため、新しい属性を追加してもテーブル構造を変更する必要がありません。

一方、JSONカラムは、現代のデータベースがサポートする半構造化データの格納機能を活用します。JSONやJSONBカラムを使用することで、1行の中にキーと値のペアを直接格納でき、さまざまな方法でクエリやインデックスを作成できます。

-- EAV pattern: attributes table
CREATE TABLE attributes (
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    attribute_name VARCHAR(100) NOT NULL,
    attribute_value TEXT,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);

-- JSON column: products table already includes attributes as JSONB
-- See 'products' table in the schema init script

EAVやJSONカラムのような柔軟なデータモデルを利用することで、さまざまな利点とトレードオフが生じます。主な利点は以下の通りです：

• エンティティごとに異なる属性セットを持つような、非常に動的またはまばらなデータへの対応；
• 新しい属性の追加時にALTER TABLE文が不要となり、スキーマ変更の削減；
• 急速なプロトタイピングや要件の変化への迅速な適応。

一方で、重要な制限事項も存在します：

• 特定の属性をクエリする際に、特にEAVでは各値が個別の行となるため、クエリが複雑かつ非効率になりやすい；
• 属性がスキーマによって強く型付け・検証されないため、データ整合性や制約の強制が困難；
• 柔軟な形式で保存されたデータのピボットや集計が必要となり、レポートや分析が遅くなったり煩雑になったりする可能性。

EAVとJSONカラムのどちらを選択するかは、ユースケース、データベースの機能、パフォーマンスと柔軟性のどちらを重視するかによって決まります。

-- Insert using EAV pattern
INSERT INTO attributes (entity_type, entity_id, attribute_name, attribute_value)
VALUES
('product', 1, 'color', 'silver'),
('product', 1, 'memory_gb', '16'),
('product', 2, 'color', 'black');

-- Retrieve all attributes for a product using EAV
SELECT attribute_name, attribute_value
FROM attributes
WHERE entity_type = 'product' AND entity_id = 1;

-- Insert using JSON column (attributes already present in 'products' table)
UPDATE products
SET attributes = attributes || '{"weight_kg": 2.5}'
WHERE product_id = 1;

-- Retrieve a product with a specific attribute in JSON
SELECT name, attributes->>'brand' AS brand, attributes->>'color' AS color
FROM products
WHERE attributes ? 'color';

-- Query for products with a specific JSON attribute value
SELECT name
FROM products
WHERE attributes->>'brand' = 'BrandB';