前回polymorphic associationsについて話しました。今回もう一つのanti-pattern Jaywalkingについて話します。
Jaywalkingはあまり聞き慣れない言葉ですが、辞書を調べたら次の意味らしいです。
When you cross the street through the middle and not at the intersections were you are supposed to. They call it Jay walking, because back in the 1900s in New York people new yorkers would call call tourist jays, and since jays were really impressed by all of the tall buildings they would walk up to the middle of the street to see how tall they were, therefore the word Jaywalking emerged
多分無断横断を意味しています。
複数の情報を一レコードで表現したい
学生の成績管理機能を作るとしましょう。
要件としては、学生の数学、英語、歴史という成績を記録したい。
エンジニアさんAが次のテーブル設計を提案した。
student_results
| student_id | math_result | english_result | history_result |
|---|
言うまでもなく、簡単な設計です。その設計をレビュー者に出します。
エンジニアA: いかがでしょうか？
レビュー者: もし今後物理の成績を記録したくなったらどうする？
エンジニアA: physicsという列を足せばいいです。
レビュー者: スキーマ変更はコストだよ。項目が増えるたびに列を足す訳にはいかないでしょう？
エンジニアA: そ、そうですが…
レビュー者: どうすればいいかわかる？
エンジニアA: 申し訳ありません。わかりません。
レビュー者: ◯◯(非常に高雅な言葉)！！！成績データを全部jsonにぶち込めばいいでしょう？
エンジニアA: な、なるほど（それ？
）
するとテーブル設計は以下のようになります
student_results
| student_id | result_json |
|---|
＊ 原書のなかで、jaywalkingはカンマで複数のデータを繋いで、文字列にして保存することを指しています。今風ではないので、jsonを例にしました。
問題
検索
成績に対するqueryが書きにくいです。
数学が60点の人を探したいんですが、どうすればいいですか。
select * from student_results where result_json like "%math : 60%";
非常にわかりやすいquery(クエリ？ クエリー？)ですね。多分rdbさんに怒られることはないが、rdbさんにとって大変ですね。しかも文字列に仮にインデックスがはってあるとしても、前方一致の場合しか使えないので、結構非効率なqueryになりますね。
学生さんの成績の集計はやりにくいです。
sum(), avg()とか使えないし（要求しすぎるかもしれませんが…）
成績を更新します
例えば、数学の点数を更新したい場合、1とつのupdateで、更新はできません。
まず一回検索して、成績の情報を全部取得してから、数学の点数だけ変更して、更新するという風にしないといけません。
データ整合性
上の節と関わるんですが、原書のなかで、rdb更新処理という観点からすると、一回データを取得してから、更新するという操作はコストと見ています。ただ個人的にアプリケーションのデータ整合性担保という観点からすると、そういう操作は許容範囲だと思います。（むしろそうせざるを得ません。）
データベースの制約で実現できる制限はもちろん理想ですが、例えばデータ型制限、文字数制限、not null制限。
それだけでは実現できない制限が多いでしょう。例えば、数学と英語の総点数が150を超えてはいけない（変な要求ということはわかっています。意味が伝わればいいです。）
そういう要求を満たすために、仮にテーブルを設計するときに、jsonカラムにしなくても、更新するために一回検索をしないといけないということは避けられません。
当然jsonカラムにしたら、仮にデータ整合性の要求が複雑じゃなくても、カラムに対する個別の更新という選択肢はありません！
カラムのサイズはどうします？
今は3項目に対応すればいいのですが、今後の拡張性を考慮すると…しかし何項目まで対応すればいいか誰もわかまえんよね？極論1000項目があれば十分ですが、dbの容量を無駄遣いにしてしまいます。
もしくはtext型にします？
どうやってこのanti-patternに気づけますか
以下の話を聞いたら、必ず警戒してください。
- 1カラムで複数の項目をいれたい！
- 最大何項目があれば足りるの？
使ってもいい場合
anti-patternというのは定常なものではなく、要件・要求を満たすために、適切ではない方法を用いたらanti-patternになります。
このjaywalkingというanti-patternにおいては、もしjsonのfieldに対して検索をかける必要がなければ、jsonをそのままテーブルに保存するという選択肢はありだと思います。
どう直せばいいのですか
答えは原書にintersection tableと書いてあるんですが、（日本語はわかりません。インターセクション・テーブル？
）ちょっと微妙に意味合いが違って、私の書いた例が適切ではないからかもしれません。
テーブルの設計を見直し、以下のようにすれば、解決できます。
student_results
| student_id | subject | result |
|---|
学生のid、項目と成績という構成で、以下のデータ例を見たらイメージつくと思います。
| student_id | subject | result |
|---|---|---|
| 1 | 数学 | 60 |
| 1 | 英語 | 65 |
| 1 | 歴史 | 90 |
| 2 | 数学 | 100 |
| 2 | 英語 | 100 |
| 2 | 歴史 | 59 |
残念ながら1番の学生さんはもしかしたらエンジニアに向いていないかもしれません。2番の人はエンジニアに適する人材ですね。
成績に対する検索は楽に書けます
select * from student_results where result = 60 and subject = "数学";
集計関数も普通に使えます
拡張性もいいです
もしある日学校の先生たちが突然頭がおかしくなって、物理という項目を増やしたがったら、楽に対応できます。スキーマの変更もなく、レコードを増やすだけのことです。
| student_id | subject | result |
|---|---|---|
| 1 | 数学 | 60 |
| 1 | 英語 | 65 |
| 1 | 歴史 | 90 |
| 2 | 数学 | 100 |
| 2 | 英語 | 100 |
| 2 | 歴史 | 59 |
| 1 | 物理 | 66 |
| 2 | 物理 | 85 |
最後に
原書の言葉をお借りします。
Store each value in its own column and row.
excelのように、1つのセルに、1つの値を入れるのは通常です。よっぽどな理由がなければその規範を守りましょう。
問題を説明するために、あえてゴミみたいな設計を例としてでっち上げました。この記事を読んだら、「これ大げさだなぁ、本当にこういう間違いを犯す人がいる？」と思う人がいるかもしれません。もしそんな惨事を目撃していなければ、正直私も同感だったはずです。
またmysql 5.7から、json型が登場しましたが、json列に対して直接インデックスが貼れないので、まだおそらく非推奨の段階ですね。