「メタデータテーブル」という設計 / 考え方について、ご意見をお聞きしたいです

Question

メタデータテーブルの実用性について最近考えており、皆様のご意見をお聞きしたいです。お気軽にご意見下さい！！

「メタデータテーブル」が正しい呼称かもわからないのですが、下記のようなテーブルの設計のことを指しています。

+++++ 質問したいことについて +++++
■ メタデータテーブル
![イメージ説明]WIDTH:450
（wordpressのテーブルです）

usersテーブルではログインや、最低限必要な情報のみを保持しています。
usermetaでは、userの拡張情報を格納します。例えば、IDが1のユーザーの住所は、usermeta内で、
umeta_id:（AUTO_INCREMEMT）,
user_id: 1,
meta_key: address,
meta_value: "東京都品川区住所1-2-3"
のように1レコードとして保持されます。

■ 対して、通常の？テーブル
![イメージ説明]WIDTH:220
userに持たせたい情報単位で、カラムを作成しています。

+++++ お聞きしたいこと +++++
上記のような設計を採用した際のメリット・デメリットはどのようなことが挙げられますでしょうか？

私個人、CakePHP等を利用してWebアプリケーションを幾つか立ち上げてきたのですが、毎回同じようなモデル（User, Admin, Post 等）を作っていました。上記設計手法であれば、Model・Controllerの再利用がかなりやりやすくなるのではないかと考えており、次のプロジェクトでは一度採用してみようと思っています。

また、上記設計によってnull許可のカラムが多く存在してしまうことを防げることもメリットなのかなぁ...と。。（住所が入力されていない場合、meta_key: address のレコードを作成しないような制御をとる）

問題点としては、meta_value列への検索クエリが遅くなることが予想されますが、実際どのくらい差分があるものなのだろうかと、疑問に思っています。
meta_keyは、usermetaテーブルのmeta_key と user_id にしっかりとindexを貼っておけば、それほどの差は出ないのではないかと考えております。
あとは、Validateion系は完全にアプリケーション側の責任になってしまいますね。。。

最近Wordpressをゴリゴリいじる機会があって、こういう設計もアリだなぁと。。。
Wordpressは、プラグインフレンドリーを優先して考えて、そうなっているのでしょうね。
だとすると、Web開発フレームワークとの相性も良いはず？！なんて考えています。

ご意見いただければ幸いです！

Answer 1

いわゆるEntity-Attribute-Valueと呼ばれるSQLアンチパターンですね。

デメリットを認識してそれでも採用するのなら構わないと思いますが…（自分も使うことはあります）。

ただ今回の場合は、メタデータテーブルを作らなくても、例えばusersテーブルのaddressだけをオプショナルにしたいのなら、users_adressテーブル的なものを作ってuser_idでusersテーブルと1:1で紐付ければ良いのではないかなと思います。

Comments

[dk1987]

有難うございます！アンチパターンの一つとして挙げられていたんですね。

確かに、ある１つの大きなシステムの設計（例えば、金融系の基幹など）だと、ありえないと思います。クエリの投げ方が複雑になるのと、Validationの部分が、問題視されているようですね。

参考のためにお聞かせ願いたいのですが、EAVパターンを採用される場合はどのような状況でしょうか？

[naga3]

社内で開発したCMSがあって、そのカスタム値を格納するためにEAVパターンを使いました。そのCMSは多数のサイトで使っているので元のテーブルの構造を変えるわけにはいかず、しかし各サイトによって独自に拡張する可能性がある場合ですね。

Answer 2

私的には、あまりお勧めができない設計かと思います。

デメリットとして、以下のようなものが考えられます。
(SQLアンチパターンの5章 EAVからの受け売りですが…)

1. データの整合性を担保できない
質問内容に記載されていますが、validationがアプリケーション任せになります。
いくらアプリケーションで注意深くプログラムを作っても、不整合を起こしたデータが紛れ込まないとは言い切れません。(アプリケーション側でもvalidationチェックをすること前提としても)

not null制約をつけれない
→必須項目がないデータが入る可能性

データ型を指定できない(meta_value列はおそらく、文字列で格納すると思うので)
→日付項目に日付変換不能データ、数値項目に数値変換不能データが入る可能性

外部キーを使えない
→他のテーブルとのデータ整合性を強制できない

属性名を管理しなければいけない
→meta_key列に入力できる属性値は、どこで管理する？従来ならカラム名として名前は強制されていたもの。

2. SQLを構築するの面倒
ユーザの情報をすべて取得するときは、大変だと思います。
userテーブルをselectした後、そのidをもとに、usermetaテーブルに再度selectすると
1回でよかったsql発行が2回になってしまいますし。また取得したデータをループで回してプログラムで1ユーザを1行のデータとして再構築しなければいけません。

selectを一回で済ませたいなら

select i.*
      ,i1.meta_value as address
      ,i2.meta_value as mail
・・・・
  from user i
left outer join usermeta i1
  on i.id = i1.id and i1.meta_key = 'address'
left outer join usermeta i2
  on i.id = i2.id and i2.meta_key = 'mail'
・・・・・

とかいうsqlになります。

メリットは、すいません思いつかないです。
あえて言うなら、柔軟に属性値をふやせるということでしょうか。
でも上記のデメリットを許容してまでのメリットかどうかは…

毎回同じようなモデル（User, Admin, Post 等）を作っていました。上記設計手法であれば、Model・Controllerの再利用がかなりやりやすくなるのではないかと考えており

おそらく、User,Admin,Postのユーザ情報を格納して、同じような項目が複数のテーブルに出てくるのが嫌ということでしょうか？

それでしたら、ユーザ情報で共通しているものをuserテーブルに切り出して、そのユーザ区分で異なる情報だけを切り出したテーブルmember,administrater,contributorとかのテーブルを作ればいいのでないでしょうか？

Comments

[dk1987]

やはり、普通に考えて不自然な設計で、本来享受できる機能・メリットが使えなくなってしまうということですよね。その点、激しく同意しております。。（もともと、そのような設計方法を取っておりました）

恐らくfuzzyをどこまで許容できるシステムかどうか、に依存するかと思うのですが、Wordpressを見て、論理的にどうあるべきかということと、実装として（外部から利用される者として）どうか、ということと少し乖離があるのかなと思いその点で悩んでおりました。

書いて頂いてやっと気づいたのですが、データ型を指定できないのは致命的ですね。。。order by 句系、検索系でしんどい思いをするのがなんとなく見えてきました...
ご回答ありがとうございます。

>属性名を管理しなければいけない
>→meta_key列に入力できる属性値は、どこで管理する？従来ならカラム名として名前は
>強制されていたもの。
これが、かなり厳しい問題なのかもしれませんね。。。打ち間違いでもすんなり保存されてしまいますもんね。

Answer 3

パッケージ製品で、入力項目を動的に増やしたり減らしたりしたい場合、メタデータテーブル？のような設計になっている事はありました。

メタデータテーブルのような設計にすれば、動的に入力項目を増やす必要がない場合でも、設計変更などで後々、入力項目を追加するケースが発生したとしても、項目定義のマスタデータ投入だけで対応できます。

上記のような利点がありますが、個人的にはメタデータテーブルのような設計は入力項目を動的に増やしたい場合など、必要不可欠な場合以外はするべきでないと思います。

理由としてはシステム開発を行っていると、汎用性などを追い求める事があると思いますが、過剰な汎用性は内部を複雑にし、往々にして開発の失敗要因になる事があるからです。

私個人としては、「必要な物を必要なだけ、できるだけシンプルに」というような方針で設計を行った方がいいのではないかと思います。

Comments

[dk1987]

コメントありがとうございます！
>設計変更などで後々、入力項目を追加するケースが発生したとしても、項目定義のマスタデータ投入だけで対応できます。
考えるきっかけになったのは、この一文の表すところです。
naga3 さんと re-24 さんに書いていただいたとおり、型が常に文字列型にならざるを得ないところが明確なデメリットですが、、、わりとどうでもいい属性：プログラム動作上必須でなく、まさにEntity attribute value なものに関しては、こんな設計もアリなのかなぁという想いがまだぬぐいきれず、、

シンプルにしたいのは山々なのですが、転用したいのも山々でして、その時に、コアとなるuserテーブルのカラムが決まっていると楽、ということもあるかなぁと考えております。

そして皆様のご意見をお聞きして、更に悶々となっている次第です。

そのシステムにとっては、逐一カラムをちゃんと定義してあげるのがベスト、ということは私も激しく同意なのですが・・・、わがままで恐縮なのですが、できれば明らかなデメリットや、苦労談が知りたいです！

[chiku_]

デメリットとしては、初めて採用しようとした時、明らかに工数は増えますよね。それがデメリットかと。

一つのプロジェクトで採用して、それをテンプレート的に使ってやれば、仕様が明確化されていないようなアジャイル開発とかでは有用性はあると思います。ただ一般的な開発手法ではないので、都度開発メンバーに周知は必要かと思います。

苦労談について、動的に入力項目を増やすとかという事に関連してはないです。
本件と同じケースだとは思いませんが、必須でないものをこだわりをもって追及していくというのは、地獄をみるような失敗プロジェクトの要因の一つであるという個人的な経験による所感があります。おれおれフレームワーク的な設計はできれば避けた方がいいかと。。