Json型の使い方

JSON型と正規形の両立について検討してみました。

1. JSON型の使い方 (SQLアンチパターンを超えて) @tsuda_ahr 2016/7/2
2. ジェイ・ウォーク (信号無視) • 略 • 「SQLアンチパターン (オライリー刊)」 を 読んでください。
3. そんなとき、PostgreSQLer (?) からは • ポスグレなら、配列型があるよ！ • ポスグレなら、JSON 型があるよ！ という話を聞くのですが。
4. それって、第一正規形違反じゃね？ • カラムに非スカラ値を持つので、第一正規形違反。 • 要するに正規化の第一歩目からつまづいてる。
5. ところで第一正規形とは？ • テーブルが繰り返しグループを持たない。 • 配列、リスト、テーブル内テーブル、レコード構造を持たない。 • すべての列がスカラ値である。 (スカラ値とは、それ以上分解不可能な原子的な値のこと)
6. つまり • 配列型やJSON型を使う時点で、非正規形に堕ちる。 • リレーショナルな設計はどこへ？ • 配列型やJSON型を使うことは、リレーショナルモデルが解決しようとしたものを壊すのでは？
7. 正規化の目的を確認 • 正規形とは、データベースにおいて正しいデータを破壊しないこと、または間違ったデー タを作らないことを保証するための試みである。 そうしたエラーを回避するための方法の 1つは、データベースにおいて1つの事実を1つ の場所にだけ格納することだ。 • というのも、もし1つの事実が2つの場所に現れるとしたら、更新の際に正しく同期を取 らないと不整合が起きることになる • 2つの腕時計をしている人間は、どちらが正しい時刻を指しているのかという不安に常 につきまとわれる。だから我々は、多くの時計を1つのマスタとなる時計に同期させ、1 つのデータソースから派生するビューとして扱うのだ。 「プログラマのためのSQL 第4版 (翔泳社刊)」 の 9章「正規化」より引用
8. なので今回は • 配列型やJSON型を、リレーショナルモデルに反しないように使うためにはどうすればよい か？を考えてみる。
9. ところで、落ち着いて考えてみると • 標準SQL (SQL92以前) でも認められている非スカラ値がある。
10. CHAR や VARCHAR, あるいは TEXT • 非スカラ値 (配列値相当) なのに、スカラ値 (単一値) 扱い。 • 文字列は最近の言語では string ですが、C では char[] ですし、 Haskell とかも文字の集合 (list) として扱ったりしますね？
11. あるいは LOB (Large Object) • ファイルや画像。これも Byte 配列。 • SQL 標準としては、配列型が認められた SQL99 からの型だが、 配列型や JSON 型のように、正規化の問題として語られることはない。
12. 配列型やJSON型と、文字列型,LOB型の違いはなにか • 一つの値として取得しているところ。
13. たとえば、 • 一般に、文字列の一部だけを (DBで) 検索することはない。
14. いやまあ、できますよ？確かに。 • select ENAME from EMP where substr(ENAME,2,1) = 'A'; • select ENAME from EMP where regexp_like(ENAME, 'L.*K', 'i') ; (↑oracle の場合) でも、こんなことすると遅いし！ ポスグレには 「関数インデックス」 という機能があるから速くできるよ！
15. でも普通やらないよね？ • つまり、配列だろうとなんだろうと、その集合を「ひとつの値」としてみなせる場合は、 第一正規形に違反していない、といえる。
16. 「ひとつの値」としてみなせる場合、とはどんな場合か？ • 例えば、会社から自宅までの経路測定のデータ列。 • 例えば、将棋盤のコマの配置状況。
17. ダメな場合 (「ひとつの値」としてみなせない場合) • 要素の一部と、他のテーブルを結合したい (外部キー制約) を張りたい場合 • 要素の一部分を検索,更新したい場合
18. 厳密な境界線はあるのか？ • ない • バランスを失うと、ジェイウォークにもあるように、「文字列型」に複数の要素を突っこむの と同じことが起こり得る。 • これはデータベースの型がスカラ値か非スカラかという問題ではなく、値の設計の問題。 整数型であっても、例えば桁に意味を持たせるようなことをすれば同じ問題が生ずる。
19. 配列型やJSON型を使うための基準 • データ型は非スカラ値でも、それをスカラ値のような扱える場合はアリ。 • 部分のデータを、他のテーブルのデータと関連を持たせるような設計は不可。 • 文字列型を参考すると解が見えやすい。(バランスがとりやすい)
20. 結論 • ご利用は計画的に。
21. もし「もっと良い基準があるよ！」って人がいたら。 • ぜひ教えてください！