SQLアンチパターン：EAV(エンティティ・アトリビュート・バリュー)について整理

どうも、シローです。

GWはどうお過ごしになりましたでしょうか。

僕は買ったきりで全然読んでいない、「SQLアンチパターン」(広告で宣伝してます⤵︎)を読み切りました。

なんで、備忘録を兼ねて学んだ事をこれから少しずつ発信していきたいと思います。

今回はEAV(エンティティ・アトリビュート・バリュー)というアンチパターンについて紹介します。

EAVとはなんぞ

アトリビュート(attribute)とは引数、バリュー(value)とは値ということから

一つのレコードで引数を表す列と、それに対応する値を格納する列を構成しているテーブルのことを指します。

サンプル

システム開発に関するバグ(Bug)や機能要望(FeatureRequest)などの大元はGitなどではIssueとして取り扱われます。

Issueに関するバグや機能要望などの属性をEAVで表現するとこのようになります。

IssueAttributesというテーブルがEAVとなっています。

このテーブルはIssueの属性名をattr_name、属性に対する値をattr_nameとしています。

実際に入れるデータはこのようになります。

issue_id	attr_name	attr_value
1	"author"	"shiro"
1	"issue_type"	"bug"
1	"bug_description"	"マイグレーションがこけた"
2	"author"	"taro"
2	"issue_type"	"feature_request"
2	"about_feature"	"認証機能をCognitoに移管したい"

このように、アトリビュート(attr_name)とバリュー(attr_value)をあたかもJSONのキー・バリューの組み合わせのようにしているのがEAVの特徴です。

うーん、ぱっと見自由で良さそう、、でも実は長期で運用するときにカオスを招くことを僕は理解しました。

どっかの本で読んだ「汎用的にすればするほど、個々に対する応用化ができなくなる」という言葉を思い出します。（意味あってるのか知らんけど

問題点１：データの型を共通にしないといけない

上の例では全て文字列型で済むのですが、次のようなアトリビュート・バリューの組み合わせを考えるとややこしくなります。

attr_name	attr_value
"issue_date"	"2021-05-11"
"favorite_count"	"10"

"issue_date"がIssueを発行した年月日、"favorite_count"がIssueに対するお気に入りの数とすれば

attr_valueの型はvarcharというよりかは、"issue_date"の時はDATE型、"favorite_count"の時はINT型の方が適切です。

ただ、列に対する型は一つにしか決められないので一番汎用的なvarchar以外を選択できません。

「アプリケーションで日付や数値に変換すればいいんじゃないの？」と思うかもしれませんが、

それではRDBの良さを活かせていないです。

型を正しく設定することにより、アプリケーションで間違った型のデータを入れた時にデータベース側でそれを阻止することができる(間違った値を何がなんでも入れない)というのが良さなので

それを無くしてしまうのはもったいないです。

問題点２：外部キー制約を付けられない

言わばattr_valueとattr_valueは何でもありなので、「この値しか入れてはダメ」みたいな外部キー制約をつけることができません。（付けたとしたらEAVでやりたいことができなくなるので)

例えば、attr_valueをバグのステータスのマスターテーブル(BugStatus)の状態(status)への外部キーにすることはできないのです。

ダメSQL

CREATE TABLE IssueAttributes (
  issue_id BIGINT UNSIGNED NOT NULL,
  attr_name varchar(100) NOT NULL,
  attr_value varchar(100),
  FOREIGN KEY (attr_value) REFERENCES BugStatus(status)
);

CREATE TABLE IssueAttributes (

issue_id BIGINT UNSIGNED NOT NULL,

attr_name varchar(100) NOT NULL,

attr_value varchar(100),

FOREIGN KEY (attr_value) REFERENCES BugStatus(status)

);

問題点３：アトリビュートとバリューの組み合わせの仕様を整理＆管理し続けないといけなくなる（運用コストが増える）

最後は何でも自由に入れられるという設定から、どこかで「どのような属性と値を持ちうるか」みたいな仕様を固めて、他の開発メンバーに共有したり

今後の機能開発で新たに属性が増える、または変更や削除などといったことが起きた時に仕様書への更新作業も発生する（そうでなければ、DBから仕様を推測するという沼に陥る）ので

なんだかんだ、一番これがリスクだと思ってます。

実装して運用を開始して大体2ヶ月くらいは「これでいいじゃん」と思うのですが、６ヶ月ぐらいたって障害が発生した時に「何だこの設計、めんどくせ！」という未来になります、絶対。

そのため、もしEAVを検討したい場合は仕様をちゃんと管理するというのを徹底された方がよろしいと思います。

僕はめんどくさがりなので、それができません。よってEAVの設計もできません。

解決策：横着せずに属性ごとのテーブルを作成しよう

例えば、Issueの属性がバグと機能要望でしたら、バグ(Bugs)と機能要望(FeatureRequests)を表すテーブルを用意すればいいだけです。

こうすれば、属性がバグの場合には深刻度(severity)、影響を受けているバージョン(version_affected)

機能要望では資金を出してくれる人(sponsor)などの拡張も用意ですし、

型を定義できるという強みもあります。

ステータスを外部キーにすることもできます。

やはり、RDB本来が持つ制約を活かした方が意図しないデータの挿入を防いで安全に運用できるので、それを捨ててまでEAVにするのはリスクが大きいかなと思います。

まとめ

EAVは複数の列同士で引数（アトリビュート）と値（バリュー）の組み合わせを表現したデータ構造である
汎用的で色んなデータを格納できる反面、型制約や外部キー制約が付けられず、用いる値の組み合わせに関する仕様の運用管理コストが増える
属性ごとにテーブルを作成すればRDBの良さを持ちつつ、EAVでできることも結局はより安全に実現できる
「汎用的にすればするほど、個々に対する応用化ができなくなる」←~~これ言いたかっただけやろ~~

SQLアンチパターン

本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗（アンチパターン）があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。

Amazon!