テーブルにポリモルフィズムは必要か

(DBAzine (2005/09/08), "One True Lookup Table")

ナイスアイデア？

　皆さんも、日々の業務で色々なコードを使っていると思います。都道府県コード、顧客コード、性別コード、疾病コードなど、テーブルには色々な種類のコード列が含まれているでしょう。そしてコードを扱う際、重要な存在となるのが「マスタ」テーブルです。つまり、コードの全ての集合（県コードなら47種類）を保持しておくテーブルです。統計表を作成するときなど、表側・表頭が固定的な結果が欲しいときには、このマスタテーブルを主にしてデータテーブルと外部結合するという方法が一般的です。データテーブルのみだと、たまたま北海道のデータがテーブルに含まれていない場合には、表側が1行欠けてしまって嬉しくありません。
　そうすると、このマスタテーブルというのは、基本的にコード体系の数だけ必要ということになります。県コードマスタ、顧客マスタ、疾病マスタ･･････等々。マスタの数が増えること自体が即座に悪い、ということにはならないのですが、しかし大して行数のない小規模テーブルがどさどさ増えるのは、エンジニアとしての美意識に引っかかるところがあるのも事実。それならいっそのこと、こいつら全部まとめて一つのテーブルに放り込んでしまった方がいいんじゃない！？列も似たりよったりだし、用途も同じだし、テーブルの数も減って ER図もすっきりさわやか！うん、これで行こう！
　こうして作られることになるのが、セルコの言う「One True Lookup Table」 ―― 敢えて訳すなら「単一参照テーブル」とでもなるでしょうか ―― 略して OTLT。具体的には下図のような内容になります。

　セルコの手前、大きな声では言えませんが、私もこの OTLT を使ったことがあります。といっても、言い訳しておくと、私が設計したのではなく、プロジェクトに入ったときには既に OTLT が存在していて、「これ使って」と言われたのですけど。
　このテーブルを使って、都道府県別の人口を集計しようとする SQL は、例えば下のようになります。

　　SELECT MASTER.pref_cd,
　　　　　　　　DATA.population
　　　 FROM　 (SELECT pref_cd, SUM(population) AS population
　　　　　　　　　　FROM DataTable
　　　　　　　　　GROUP BY pref_cd) DATA RIGHT OUTER JOIN
　　　　　　　　 (SELECT code_value
　　　　　　　　　　FROM LookUp
　　　　　　　　　 WHERE code_type = 'pref_cd' ) MASTER　　--インライン・ビューで都道府県マスタを作る
　　　　　ON　DATA.pref_cd = MASTER.pref_cd ;

FROM句のインライン・ビュー（MASTER）で都道府県マスタを作り、それを主に外部結合しています。このビューが、場合によって性別マスタになったり疾病マスタになったり、七変化するわけです。

利点と欠点

　私が OTLT を使った最初の感想は、「結構便利なものだ」という肯定的なものでした。特にシステムの規模があまり大きくない場合は、テーブルのサイズもそれほど大きくならず、含まれるコード体系の管理も一括で行なえます。しかしセルコはこの記事で、OTLT の使用に強い警鐘を鳴らしています。本稿の主眼は、彼がこの手法を批判する理由を理解することにあるのですが、その前に、まずはこの手法の利点の方を整理しましょう。セルコは「初心者には人気があるんだけどね」とチクリと刺すだけで、利点は具体的には挙げていません。しかし、初心者だけにせよ、人気があるのなら、そこには人気があるだけの理由も同時にあるということですから、一応それも挙げておかないとフェアじゃありません。
　私が思うに、OTLT の持つ利点は以下の通りです。

マスタテーブルの数が減るので、ER図がシンプルになり、理解しやすい。
コード検索のSQLを共通化できる。
複数の業務で使用するコード集合を一箇所で管理できるので、保守・管理が容易になる。

　特に、2.番と3.番は、この設計の大きな利点だと実感しました。共通化によって設計を単純化できるのですから、これが多くのエンジニアに魅力的な手法に映っても不思議ではありません。
　しかし、セルコが指摘するように、この方法には実用面と理論面の両面で、無視できない欠点も抱えています。彼が指摘する欠点は以下の通りです。

コードの整合性を保つために、テーブル定義の DDL に CASE式を用いた長大な CHECK 制約を書かねばならない。
それぞれの列を、DBMS が許容する最大の VARCHAR（場合によっては NVARCHAR ）で宣言する必要がある。その結果、間違ったデータを格納する危険が増える。
複数のマスタを使う場合に比べて一テーブルの行数が多くなり、検索のパフォーマンスが悪化する。「コード値」列の結合時に型変換が発生するとなおさら。
SQL 内でコードタイプやコード値を間違えて指定してもエラーにならず、間違った結果が返される。そのため間違いに気付きにくい
テーブルは無関係な要素の寄せ集めではなく、同一種類の物の集合であるというモデリングの原理に反する

　私自身、2.番の欠点のために嫌な経験をしたことがあります。「コード値」列のサイズがたったの5バイト（！）で宣言されていたため、7バイトのコードを登録できなかったのです。おまけに、様々な業務で共通に使うテーブルということが災いして、列のサイズを拡張することも許されず、結局、別にそれ専用のマスタテーブルを作るという本末転倒な結果に終わりました。せめて最初に10バイトぐらいで宣言しておいてくれれば･･････。今でも納得のいかない設計として頭から離れない事例です。（かといって、サイズを増やせば増やすほど、セルコの言うように間違ったデータが登録される危険も増えるのですが。）
　その他の欠点については、1、3番は、「その通りだけど、致命的なものとは思えない」というレベルです。CHECK 制約は確かに複雑になりますが、投げ出したくなるほど複雑怪奇なものにはなりませんし、修正が必要なタイミングは、新しいコード体系を登録するときだけです^[1]。パフォーマンスの問題も、{ コードタイプ, コード値 } の主キーのインデックスが使用できれば、極端に悪化することはないでしょう。ただ、ちょっと厄介なのは 4.番です。これは、開発者の不注意が見つけにくいバグを生む要因となる性質のため、本当に好ましくありません。
　そして、もう一つ、セルコが挙げていない欠点を追加しておきたいと思います。それは

　　　　　世代管理の必要なコード体系を OTLT で管理するのは骨が折れる。

　ということです。県コードや性別のように半永久的に不変の体系なら OTLT で一括管理するメリットは大きいのですが、顧客コードや疾病コードのように時間とともにコード集合が変化するような体系の場合、管理が難しくなります。「年月」列を追加することになるでしょうが、時間変化しない体系とのごった煮テーブルにするのは少し明瞭さを欠きますし、検索 SQL を共通化する方法も工夫が要ります。それぐらいなら、時間変化する体系については独立のマスタテーブルを作った方が良いように思えますが、それではコードの一括管理という、OTLT のそもそもの目標が崩れることになります。

　また、5.番に関連して、セルコは OTLT の発想の源について面白い勘繰りをしています。これが編み出された背景には、オブジェクト指向的なポリモルフィズムの発想があるのではないか、というのです。確かに、OTLT とは要するに、一つのテーブルに、場合場合に応じて異なるテーブルであるかのように振舞わせる方法ですから、その意味でテーブルを使ったポリモルフィズムと言えなくもありません。なかなか卓抜な視点ですが、「テーブルはクラスではないのに･･････」と、本人はぼやき節です。セルコに限らず、リレーショナル・データベース界の論者の多くは、テーブル設計にオブジェクト指向的な発想を持ち込まれることを嫌いますし、実際に多くの場合、非効率的です。
　でもきっと OTLT を使っている人の大半は、そこまで深く考えているわけではなく、「共通化できる部分は共通化したい」という素朴な発想でやっているだけでしょう。しかし、こういう風に現象を見る視点を変えて、その奥に潜む原理まで取り出して見せてくれるのが、セルコを読む醍醐味の一つです。

まとめ

　私は、OTLT を絶対に使うべきではない、とは思いません。明確な意図のある設計方針ですし、実現方法もいたってシンプル、しかも享受できるメリットは、時と場合を選べばかなり大きいからです。いつでも馬鹿の一つ覚えのようにこれを使うのは問題としても、トレードオフの計算をして使う分には許されると思います。ただし、仮に OTLT を採用する場合でも、セルコが苦言を呈した諸々の理由と、これがリレーショナル・データベースの世界の正統的な教義からは外れる方法だということは、理解しておきましょう。

註
[1] CHECK 制約など付けなければよいのではないか、という悲しいことは言わないでください。マスタテーブルのデータが誤っているというのは、データテーブルのデータが誤っていることよりもずっと重大な事態を招きます。マスタテーブルのデータには厳密にチェックを行なうべきです。しかも、大量データの INSERT や DELETE が発生するテーブルではないので、CHECK 制約がパフォーマンス悪化を招く危険も低いのですから。

戻る