はじめに
こんにちは、私は底辺SEです。この記事では柔軟なデータに対するRDBのテーブル設計について調べたことや考えたことを書きます。
ここで柔軟なデータというのは以下のような特徴を持つデータのことを想定しています。
- サブタイプ: レコードのカテゴリ（種類）によって項目が異なる
- 動的なスキーマの変更: 項目が仕様変更等で動的に増えていく
こういったものを管理するにはどういう設計すれば良いんだろうね？というのが本記事のテーマです。結論から申し上げますとなんもわかりません。誰か助けて。
例
例として、ゲームのキャラクターのパラメータをDBで管理したいとします。
キャラクターには色々な職業があります。職業によらず全員が持つ共通の属性と、職種ごとの固有の属性があります。
例:
- 共通: 名前、体力、年齢
- 剣士固有: 流派、腕力、スピード
- 魔法使い固有: 属性、魔力、詠唱速度
- 技師固有: 専門分野、知識量
データモデリングではこういうパターンを「排他的サブタイプ」と呼びます。
さらに、このゲームは頻繁に内容をアップデートしており、職業の種類や固有の項目が増えていく、つまり動的なスキーマ変更があるとしましょう。
この例のような特徴を持つデータはこの例に限らず以下のように様々な状況で出現します。:
-
商品管理: 商品のカテゴリごとに固有の情報があり、新しくカテゴリが増えることもある
-
ユーザ管理: ユーザの種別や権限によって設定項目が変わる
-
顧客管理: 顧客の業界によって保存すべき情報が異なる
-
科学実験: 研究テーマごとに固有の実験条件があり、研究テーマが変われば実験条件も変わる
-
品質測定: 測定機器によって測定項目が異なり、製品が変われば新しい測定条件が必要になることもある
サブタイプの数、項目の数、それらがどれくらいの頻度で増えるのか、増える要因は何か、といった点が対象ドメインによって異なってくるでしょう。
EAV (Entity-attribute-value) モデル
このようなデータをRDBで管理する方法としてEAV (Entity-attribute-value) という設計モデルが存在します。EAVでは、エンティティ（ID）、属性、値の3列だけであらゆる種類のデータを管理します。
共通情報テーブル
| キャラID | 名前 | 職業 | 体力 | 年齢 |
|---|---|---|---|---|
| 1 | "Goro" | "剣士" | 30 | 18 |
| 2 | "Sachiko" | "魔法使い" | 20 | 45 |
| 3 | "John" | "剣士" | 25 | 30 |
| 4 | "Mike" | "技師" | 15 | 25 |
固有情報テーブル（↓↓↓これがEAV↓↓↓）
| キャラID | 属性 | 値 |
|---|---|---|
| 1 | "流派" | "爆裂居合流" |
| 1 | "腕力" | "25" |
| 1 | "スピード" | "30" |
| 2 | "属性" | "火" |
| 2 | "魔力" | "20" |
| 2 | "詠唱速度" | "15" |
| 3 | "流派" | "超絶五刀流" |
| 3 | "腕力" | "20" |
| 3 | "スピード" | "10" |
| ... | ... | ... |
EAVモデルを用いると、テーブルがNULL値で埋まる（スパースになる）ことを避けることができます。また、新たな職業や固有パラメータが追加されたときに行を INSERT するだけで対応でき、 ALTER TABLE でテーブル構造を変更する必要がないというメリットもあります。
EAV の問題点
しかし、このEAVはアンチパターンとしても知られています。『SQLアンチパターン』という本の5章で登場するのが有名です。具体的な問題点として以下が指摘されています。
-
全項目のデータの値が1列に収められているため、必須属性、データ型、参照整合性といった、データの整合性を保つためのRDBの機能が利用できず、アプリケーション側で再実装しなければならない
-
表記ゆれでバグが混入する可能性がある。例えば、属性名の"スピード"が一部の処理でうっかり"速度"になる（属性名自体を管理するテーブルを別に用意することで回避は可能）
-
各属性を個別に列として持つ形（横持ち）のデータを得るためには長いSQLを書く必要がある（そもそもEAVのまま取得してアプリケーション側で再構築するのが一般的？）
また、それ以外にも以下のような問題点がよく挙げられています。
- どのようなサブタイプや固有項目が存在するのかが、テーブル定義から分からない
- DBテーブルとオブジェクトを対応付ける仕組み（ORM）と相性が悪い
- クエリのパフォーマンスが悪い（具体的にどれくらいのデータ量のオーダーで使い物にならなくなるのかはよくわかっていません）
EAV の代替策
EAVは上述のような多くの問題を抱えているため、『SQLアンチパターン』でも「EAVアンチパターンの使用を正当化する理由は、簡単には見つかりません」と丁寧な言葉でバチクソ批判的な視点が示されています。
同書では、代替策として以下に示す4つの方式が紹介されています。ただ、私の考えでは、これらの代替策もそれぞれ問題点があり、やはりEAVを使わざるを得ないケースがあるのではないか、どうなんだ、誰か教えてくれ、という話になります。
1. シングルテーブル継承
共通か固有かにかかわらず、全項目を列に持った1つのテーブルで管理する方式です。
キャラクターテーブル
| キャラID | 名前 | 職業 | 体力 | 年齢 | 流派 | 腕力 | スピード | 属性 | 魔力 | 詠唱速度 | 専門分野 | 知識量 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | "Goro" | "剣士" | 30 | 18 | "爆裂居合流" | 25 | 30 | NULL | NULL | NULL | NULL | NULL |
| 2 | "Sachiko" | "魔法使い" | 20 | 45 | NULL | NULL | NULL | "火" | 20 | 15 | NULL | NULL |
| 3 | "John" | "剣士" | 25 | 30 | "超絶五刀流" | 20 | 10 | NULL | NULL | NULL | NULL | NULL |
| 4 | "Mike" | "技師" | 15 | 25 | NULL | NULL | NULL | NULL | NULL | NULL | "ロボット工学" | 20 |
自身の職業以外の固有項目は全てNULLとなってしまいますが、固有項目が少ないのであればこれを許容し、データ管理をシンプルに保つアプローチです。
問題点としては以下があります:
-
固有の部分が多いとNULLだらけになり、ディスクスペースの無駄遣いに繋がる。また、NULLがテーブルに混入する設計は様々な問題が生じるバッドプラクティスとされている
-
新しい項目を追加する時は
ALTER TABLEでテーブル構造の変更が必要になる。また、列を追加すると見た目上は末尾に追加されていくため、どの列がどの職業に属する項目なのか一見して分からなくなる
2. 具象テーブル継承
サブタイプごとにテーブルを作成します。共通項目はすべてのテーブルに重複して持たせます。
剣士テーブル
| キャラID | 名前 | 体力 | 年齢 | 流派 | 腕力 | スピード |
|---|---|---|---|---|---|---|
| 1 | "Goro" | 30 | 18 | "爆裂居合流" | 25 | 30 |
| 3 | "John" | 25 | 30 | "超絶五刀流" | 20 | 10 |
魔法使いテーブル
| キャラID | 名前 | 体力 | 年齢 | 属性 | 魔力 | 詠唱速度 |
|---|---|---|---|---|---|---|
| 2 | "Sachiko" | 20 | 45 | "火" | 20 | 15 |
技師テーブル
| キャラID | 名前 | 体力 | 年齢 | 専門分野 | 知識量 |
|---|---|---|---|---|---|
| 4 | "Mike" | 15 | 25 | "ロボット工学" | 20 |
シングルテーブル継承と違い、NULLを避けることができます。また、どの固有項目がどのサブタイプに属するのかも一目でわかります。
問題点としては以下があります:
-
新たな共通項目を追加する際には、全てのサブタイプのテーブルに対して忘れずに列追加を行う必要がある
-
各テーブルを見比べないと共通項目と固有項目の区別が付かない。特に共通項目が後から追加された場合わかりづらくなる
-
シングルテーブル継承と同じく、項目が増える際はテーブル構造の変更（列追加）が必要。ただし、サブタイプが新たに追加される場合にはテーブル作成でよいので既存のテーブルには影響を与えなくて済む
3. クラステーブル継承
共通情報テーブルと、サブタイプごとの1つの固有情報テーブルを作ります。
キャラクター共通情報テーブル
| キャラID | 名前 | 体力 | 年齢 |
|---|---|---|---|
| 1 | "Goro" | 30 | 18 |
| 2 | "Sachiko" | 20 | 45 |
| 3 | "John" | 25 | 30 |
| 4 | "Mike" | 15 | 25 |
剣士テーブル
| キャラID | 流派 | 腕力 | スピード |
|---|---|---|---|
| 1 | "爆裂居合流" | 25 | 30 |
| 3 | "超絶五刀流" | 20 | 10 |
魔法使いテーブル
| キャラID | 属性 | 魔力 | 詠唱速度 |
|---|---|---|---|
| 2 | "火" | 20 | 15 |
技師テーブル
| キャラID | 専門分野 | 知識量 |
|---|---|---|
| 4 | "ロボット工学" | 20 |
共通情報テーブルと各固有情報のテーブルが 1 対 0..1 の関係になっています。共通項目の扱いが固有項目と分離されたため、具象テーブル継承よりも明確です。
ただし、このクラステーブル継承においても、項目が増えたらもちろんテーブル構造の変更（列追加）が必要です。上記の3つの方式は、サブタイプを持つという特徴には対応できるものの、管理項目が仕様変更等で動的に増えていく（動的スキーマ変更）という特徴に対する解決策にはなっていません。
4. 半構造化データ（とNoSQL）
最後に、固有情報をXMLやJSONの形式で一つの列に保存してしまう方法です。
| キャラID | 名前 | 職業 | 体力 | 年齢 | 固有情報 |
|---|---|---|---|---|---|
| 1 | "Goro" | "剣士" | 30 | 18 | {"流派":"爆裂居合流","腕力":25,"スピード":30} |
| 2 | "Sachiko" | "魔法使い" | 20 | 45 | {"属性":"火","魔力":20,"詠唱速度":15} |
| 3 | "John" | "剣士" | 25 | 30 | {"流派":"超絶五刀流","腕力":20,"スピード":10} |
| 4 | "Mike" | "技師" | 15 | 25 | {"専門分野":"ロボット工学","知識量":20} |
列はTEXT型などにするか、XML型やJSON型が使えるRDBMSならばそれが使えます。また、いっそのことRDBを使わず、ドキュメント型DBやキーバリュー型DBのようなNoSQLを使うという選択肢もあります。
半構造化データやNoSQLであれば、スキーマに縛られないため、動的なパラメータの追加にも容易に対応することができます。
ただし、これらの方法はEAVと同じ問題点が概ね当てはまってしまいます。「動的なスキーマはリレーショナルモデルには向いていないので、EAVにするくらいなら、JSON型やNoSQLデータベースを用いるべき」という主張がよくありますが、データモデルが非リレーショナルであるという点だけでは、RDBの恩恵を受けられないEAVを使うことと、そもそもRDBと異なる技術を使うことは、本質的に違いがありません。項目を増やすのに ALTER TABLE を避けたいのであればいずれにせよリレーショナルモデルのレールから逸脱するしかなく、その方法としてのNoSQL等に、EAVと比較してどのようなメリット・デメリットがあるのかを議論する必要があります。
NoSQL VS EAV
ここではNoSQLとしてキーバリュー型とドキュメント型を想定しています。キーバリュー型ではAmazon DynamoDB、ドキュメント型ではMongoDBが有名でしょうか。
EAVではなくNoSQLを使用するメリットは以下が挙げられます:
-
NoSQLの特徴として処理速度の速さや高いスケーラビリティが強みとされている
-
キーバリュー形式やJSONの形式はアプリケーションのプログラムでよく用いられるデータ構造なので、そのままアプリケーションで扱いやすい場合が多そう
一方で、NoSQLを選択せず、EAVを使用する理由もいくつか考えられます:
-
動的スキーマ変更が必要な最小限の部分のみEAVテーブルを利用し、その他は従来どおりのテーブル設計を用いることでRDBの恩恵を受けられる
-
RDB/SQLは長い間第一線で使用され続けている実績があり、今後も同じ技術を使い続けられるという安心感がある。NoSQLはまだ若い技術も多く、数年先には廃れてしまっているかもしれないという不安感がつきまとう。また、伝統的な企業においてはNoSQLのような新しい技術を導入したら、誰も引き継ぐことができずに保守運用が困難になるという問題がリアルに発生し得るため、EAVという実現方法が存在する上でNoSQLをあえて選択するには説得力のある説明が必要（令和の記事です）
上記はNoSQLとEAVの比較ですが、JSON型などの半構造化データカラムはどうでしょうか。JSON型であれば、その他の部分は従来のテーブル設計でRDBの恩恵を受けることが可能ですし、DBMSをRDBから乗り換える必要がないという点でもハードルが低く感じます。
ただ、わざわざ非標準的な機能を採用するほどEAVに勝っている面があるのかは疑問です。個人的には、JSON型はわざわざテーブルを分けるほどではないような小規模なエンティティ（軽い設定項目など）に利用するくらいが適していると思っています。（実際のところ、JSON型でEAVを代替するのって推奨される方法なんでしょうか…？）
そもそも動的なスキーマ変更が必要な状況について
データモデルの変更が発生した際はテーブル構造に手を入れるのは普通のことです。それでもALTER TABLEを避けたい事情としては以下のような状況が挙げられます:
-
仕様策定の主導権を利用者（利用部署の担当者）が握っており、頻繁に発生する項目追加のたびに開発担当者への「依頼」を挟むのが非常に非効率的な場合。こうしたケースでは、特定の権限をもつ一部の利用者が管理画面で項目を編集できるような機能があると便利で、これにはEAVやNoSQLなどによる動的なスキーマ変更に対応した設計が必要になります
-
多数の会社／部署／ユースケースにまたがるような汎用的システムを構築するようなケースにおいては、その対象ごとに個別に専用のテーブル設計を行うのはあまりに大変であり、それぞれの固有情報を受け止められる汎用的な入れ物が必要
-
テーブルの規模が大きいと
ALTER TABLEは非常に重い処理になる。（と、よくいわれていますが、調べてみたところ、現代ではよっぽどの大規模でもない限りスキーマ変更が性能問題を引き起こすことはそうそうないみたいです。ただし、MySQLだとやっぱり遅いみたいな話もあり・・・いずれにしてもダウンタイムが発生するレベルの場合、頻繁なスキーマ変更はとても許容できないでしょう）
もちろん、動的なスキーマ変更が本当に必要なのかを慎重に検討することも重要です。例えば、利用部署から頻繁に項目追加があると聞いていたのに、実際には年に数回しか発生しなかったということもありえます。この頻度であれば、都度依頼を経由して ALTER TABLE で対応する方式も十分考えられます。
EAVの改良
実際にEAVを採用する場合、最初に紹介した形よりも凝った設計にすることが多いと思います。ここでは再びキャラクターと職業の例を使って、この点について少し掘り下げます。
1. 属性名をメタ情報テーブルで管理する
元のEAVでは属性名の表記ゆれによる問題が起こり得ました。この対策として、職業や職業ごとの項目をマスタテーブルで管理します。
職業マスタ
| 職業ID | 職業名 |
|---|---|
| 1 | "剣士" |
| 2 | "魔法使い" |
| 3 | "技師" |
項目マスタ
| 項目ID | 職業ID | 属性名 |
|---|---|---|
| 1 | 1 | "流派" |
| 2 | 1 | "腕力" |
| 3 | 1 | "スピード" |
| 5 | 2 | "属性" |
| 6 | 2 | "魔力" |
| 7 | 2 | "詠唱速度" |
| 8 | 3 | "専門分野" |
| 9 | 3 | "知識量" |
共通項目テーブル
| キャラID | 名前 | 職業ID | 体力 | 年齢 |
|---|---|---|---|---|
| 1 | "Goro" | 1 | 30 | 18 |
| 2 | "Sachiko" | 2 | 20 | 45 |
| 3 | "John" | 1 | 25 | 30 |
| 4 | "Mike" | 3 | 15 | 25 |
職業固有項目テーブル(EAV)
| キャラID | 項目ID | 値 |
|---|---|---|
| 1 | 1 | "爆裂居合流" |
| 1 | 2 | "25" |
| 1 | 3 | "30" |
| 2 | 4 | "火" |
| 2 | 5 | "20" |
| 2 | 6 | "15" |
| 3 | 1 | "超絶五刀流" |
| 3 | 2 | "20" |
| 3 | 3 | "10" |
| ... | ... | ... |
EAVテーブルでは属性名を直接指定せず、代わりに項目IDを指定します。このマスタは、利用者が項目をカスタマイズできる設定画面のようなものに対応するテーブルとして利用できます。また、ここでは属性名だけを管理していますが、パラメータテーブルにデータ型や値の範囲などの情報を入れる列を用意することで、動的なデータチェックに使うといったこともできます。
2. 値のデータ型の制御
EAVではすべての値が文字列として扱われるため、アプリケーションとデータをやり取りする際には適切なデータ型への変換が必要になります。この問題への対策としては、データ型ごとに列を分ける方法と、テーブルを分ける方法が考えられます。
列を分ける方法
職業固有項目情報テーブル
| キャラID | 項目ID | 文字属性 | 数値属性 |
|---|---|---|---|
| 1 | 1 | "爆裂居合流" | NULL |
| 1 | 2 | NULL | 25 |
| 1 | 3 | NULL | 30 |
| 2 | 4 | "火" | NULL |
| 2 | 5 | NULL | 20 |
| 2 | 6 | NULL | 15 |
| 3 | 1 | "超絶五刀流" | NULL |
| 3 | 2 | NULL | 20 |
| 3 | 3 | NULL | 10 |
| ... | ... | ... | ... |
テーブルを分ける方法
職業固有項目_文字列テーブル
| キャラID | 項目ID | 文字属性 |
|---|---|---|
| 1 | 1 | "爆裂居合流" |
| 2 | 4 | "火" |
| 3 | 1 | "超絶五刀流" |
| ... | ... | ... |
職業固有項目_数値テーブル
| キャラID | 項目ID | 数値属性 |
|---|---|---|
| 1 | 2 | 25 |
| 1 | 3 | 30 |
| 2 | 5 | 20 |
| 2 | 6 | 15 |
| 3 | 2 | 20 |
| 3 | 3 | 10 |
| ... | ... | ... |
(※EAVテーブル以外は省略)
ここでは文字列と数値だけを対象にしましたが、必要に応じて型ごとに列／テーブルを分けていくことになります。
これにより、データ型変換は必要なくなりますが、かわりに設計が複雑になります。アプリケーションがデータの入力／表示のみを行うような場合、文字列のまま扱っても問題ないケースが多いため、この対策を取らないという選択肢も十分あり得ると思います。
Salesforceの事例
ここまでの説明に挙げてない、サブタイプと動的なスキーマ変更に対応できる設計方法として、以下の記事でSalesforceの斬新なテーブル設計が紹介されています。
知られざる「マルチテナントアーキテクチャ」（3）～スキーマとメタデータの謎 － Publickey
Value0,Value1,Value2,...,Value500 と可変長文字列の列が大量に用意されており、それらの値がどの項目に該当するかは別のテーブルで管理していて個別にカスタマイズできるようになっているようです。掟破りではありますがSalesforceのような大規模なシステムで問題なく動作していることがその有効性を証明しています。
余談: 業界によるEAVに対する認識の違い
個人的な感覚ですが、事業寄りのシステム（営業システムや基幹系システムなど）やコンシューマ向けサービスを担当していた人よりも、現場寄りのシステム（生産システムや品質管理システムなど）を担当していた人のほうが、EAVを肯定的に捉えている人が多いように感じます。ともすれば、EAVはアンチパターンではなく一般的な設計手法だという認識の人もいるように思います。
これは以下のような理由が考えられるかと思います。
- 現場寄りのシステムのほうがケースバイケースの対応が必要で、固定的なデータモデルで表現できず、スキーマの変更も頻繁に起こるから
- 事業寄りのシステムは個々のシステムの規模が大きく、パフォーマンスが問題になりやすいから
- 事業寄りのシステムは契約や法令が関わってくる場合が多く、データ整合性が重視されるから
- コンシューマ向けは大規模でスケーラビリティが重要なのでNoSQLを選択するから
まとめ
サブタイプがあり、かつ動的なスキーマ変更が必要になるようなデータ構造に対するデータベースの設計方法について考えました。
シングルテーブル継承、具象テーブル継承、クラステーブル継承はサブタイプに対応することができますが、動的なスキーマ変更への対応は難しいです。それに対して、EAVやNoSQL/半構造化データは動的なスキーマ変更にも柔軟に対応できますが、RDBの恩恵が損なわれます。
また、EAVとNoSQLを比較すると、NoSQLには処理速度やスケーラビリティ、扱いやすさのメリットがあるのに対し、EAVは周辺のテーブルはRDBの恩恵を享受できることや、枯れた技術であるRDB/SQLの範囲内で利用できる事などのメリットがあります。
EAVはアンチパターンとされており、基本的には使用を避けるべきですが、それでもEAVを使ってまで、動的なスキーマ変更への対応が必要になるケースについても考察しました。パフォーマンスの要件や、利用者との関係、システムの役割、利用者の業務内容等をよく調査し、慎重に判断することが大切ですね。
結局なんもわからん・・・。