SaaSにおけるカスタムフィールド実装パターン

サーバーサイドエンジニアの田実です！

ヤプリのプロダクトである Yappli、Yappli CRM ではお客様のアプリごとにユーザ属性を自由にカスタマイズできます。 例えばクライアントAは「姓」「名」「性別」の属性を使い、クライアントBは「ログインID」「電話番号」の属性を使う、といったように自由に属性を追加・利用できます。 このような お客様自身が属性を自由にカスタマイズできる 「カスタムフィールド」の機能はSaaSの要件としては頻出ですが実装方法は複数あり、それぞれ一長一短があります。今回はカスタムフィールドの実装パターンとメリット・デメリットについてご紹介します。

※テーブル定義やSQL例はMySQLを想定して記述しています。

EAV

Entity Attribute Valueの略称で１レコードが１ユーザの１属性となるようなデータ設計をするパターンです。 一般的にはアンチパターンとされますが、ことSaaSのカスタムフィールドの実装で使うことに関しては検討の余地があります。

テーブル定義やデータの例は以下の通りです。

attributes テーブル

物理名	型	説明
client_id	BIGINT	クライアントのID
user_id	BIGINT	ユーザのID
key	VARCHAR(255)	属性のキー
value	TEXT	属性の値

データ例

client_id	user_id	key	value
1	1	last_name	山田
1	1	first_name	太郎
1	2	last_name	鈴木
2	3	nick_name	ボブ

メリットとしては利用するカラムが増えてもデータ定義を変更する必要が無く、カラム数の上限も無い事です。 また、データ作成・更新のパフォーマンス次第ではありますが (client_id, key, value) の複合インデックスを貼るだけで、全ての属性値のインデックスをカバーできます。

デメリットとしてはデータの取扱いの難しさです。

例えば、属性レコードを新規作成する場合は以下のように複数のattributesレコードをINSERTします。

INSERT attributes(client_id, user_id, key, value)
VALUES 
  (1, 1, 'last_name', '山田'),
  (1, 2, 'first_name', '太郎');

更新に関しても複数回UPDATE文を発行するか、以下のようなクエリで一括更新します。

UPDATE attributes 
SET value = 
  CASE key 
    WHEN 'last_name' THEN '山田' 
    WHEN 'first_name' THEN '太郎'
    ELSE value
  END
WHERE 
  client_id = 1 
  AND user_id = 1;

データ取得に関しても、任意のユーザの属性値を横持ちで取得するには以下のような結合を駆使したクエリを書く必要があります。

SELECT
  a.value AS last_name,
  b.value AS first_name
FROM attributes a 
LEFT JOIN attributes b ON b.user_id = a.user_id AND b.key = 'first_name'
WHERE
  a.client_id = 1 
  AND a.user_id = 2 
  AND a.key = 'last_name';

横持ちの結合クエリで取得しなくてもアプリケーション内で組み立てるすべはあるものの、そのあたりの取り回しのしづらさが出て来やすいのがEAVのデメリットです。

横持ちの汎用カラム

汎用カラムを予め用意して各クライアントごとに利用するカラムの意味を定義して利用するパターンです。 例えばattributesテーブルにcolumn1, column2, ... という感じで汎用の文字列カラムを用意して、クライアントAのcolumn1はログインID、クライアントBのcolumn1は性別、といった形で割り当てます。 ちなみにこのパターンは Salesforceが採用しているらしい です。

attributes テーブル

物理名	型	説明
client_id	BIGINT	クライアントのID
user_id	BIGINT	ユーザのID
column1	TEXT	属性1の値
column2	TEXT	属性2の値
column3	TEXT	属性3の値

※属性値をVARCHARではなくTEXTとしていますが、MySQLの１レコードあたりの行サイズ制限に抵触しないため、行サイズを節約する目的でTEXTにしています。

データ例

client_id	user_id	column1	column2	column3
1	1	山田	太郎	1999/10/10
1	2	鈴木

メリットとしてはデータの取扱いが楽なことです。

横持ちでデータ取得したい場合は以下のようなクエリで抽出可能です。

SELECT
  column01 AS last_name,
  column02 AS first_name
FROM attributes
WHERE client_id = 1 AND user_id = 2;

新規作成、更新も以下のような感じで直感的です。

INSERT attributes(client_id, user_id, column01, column02) VALUES (1, 1, '山田', '太郎');

UPDATE attributes
SET column1 = '山田', column2 = '太郎'
WHERE client_id = 1 AND user_id = 1

デメリットとしては予めカラムを用意する必要があるため、カラムの上限が存在することです。 また、EAVだと key を見ることで何のデータかどうかをある程度推測できるのですが、横持ちな汎用カラムのパターンではcolumn1やcolumn2が何のデータかはデータだけではわかりません。

さらに、インデックスを効かせる場合はカラム数分のインデックスが必要になります。50カラムある場合、50カラム分のインデックスを貼るのは更新パフォーマンスが良くないため、attributesテーブルだけの仕組みだとインデックスを貼るのが難しいです。

ちなみにSalesforceにはName、外部IDなどのインデックスの仕組みがありますが、内部実装としてはEAVなインデックス用のテーブルを作っているようです。 （↓の インデックスとリレーションの定義 の Indexes テーブルを参照 www.publickey1.jp

JSONカラム

属性値をすべてJSONで管理する方式です。

attributes テーブル

物理名	型	説明
client_id	BIGINT	クライアントのID
user_id	BIGINT	ユーザのID
value	TEXT	属性の値

データ例

client_id	user_id	value
1	1	{"last_name": "山田", "first_name": "太郎", "birth_day": "1999/10/10"}
1	2	{"last_name": "鈴木"}

メリットとしてはEAVよりもデータが扱いやすく、JSONのkey/valueで属性が格納されるため汎用カラムよりデータの可読性が良いことです。

例えば、横持ちで取得する場合はJSON関数・演算子で簡単に取得できます。

SELECT
  value->>'$.first_name' AS first_name,
  value->>'$.last_name' AS last_name
FROM attributes
WHERE client_id = 1 AND user_id = 2;

新規作成、更新も以下のようにJSON形式でデータを入れたりJSON関数を活用して簡単に操作することできます。

INSERT attributes(client_id, user_id, value) VALUES (1, 1, '{"last_name":"山田","first_name":"太郎"}');

UPDATE attributes
SET value = JSON_MERGE_PATCH (value, '{"last_name":"山田","first_name":"太郎"}')
WHERE client_id = 1 AND user_id = 1

また、key/valueの文字列長にも依りますが、JSONカラムの最大サイズ範囲内でカラム数を動的に増やせることもメリットです。

デメリットとしてはJSON操作によるアプリケーションやDBのCPU的な負荷、運用面での負荷（JSON関数自体の認知負荷）です。また、インデックスに関しても汎用カラムと同じ課題があります。JSON内に各keyを必ず含む必要があるため、汎用カラムと比較してデータサイズが大きくなります。

クライアントごとのカスタムテーブル

最後にクライアントごとにテーブル定義する方法です。 この方法は お客様自身 が 自由 に属性を追加するという要件は満たさないのですが、要件分析の結果、これで十分なこともあるからこちらも併せて紹介します。

メリットとしては他のパターンと比較して型が厳密になり、インデックスも柔軟に貼れることやデータの可読性、操作性が高いことです。

デメリットとしてはカラムを動的に増やすことが出来ないことです。ALTER文をアプリケーションから打つことで回避できそうですが、お客様側で任意のタイミングでDDLを実行させるのはリスクが高いです。

Yappli CRMではお客様の外部システムとCRM間のETLに関してはカスタムテーブルを切って運用することがあります。ETLの特性に応じてテーブルを定義できることで運用性が上がりますし、ETLの仕様が変わることはあまり無いので動的にカラムを増やせないデメリットが無視できます。

まとめ

SaaSにおけるカスタムフィールド実装パターンを紹介しました。 ちなみに、個人的には各種DBにおいてJSON関数が充実してきたことやSQLでの取り扱いのしやすさから、JSONカラムが推しです。

実はヤプリでは今回紹介した全ての実装パターンがあり、カスタムフィールドの実装面・運用面の現実的な課題に対して向き合ってきました。 SaaSにおけるカスタムフィールドの設計で今回の記事がお役に立てれば幸いです！