SQLアタマアカデミー

第3回 テーブル設計のグレーゾーン~毒と薬は紙一重 (1)単一参照テーブル~テーブルにポリモフィズムは必要か

この記事を読むのに必要な時間:およそ 3 分

はじめに

リレーショナルデータベースが関わる案件において,その開発効率と品質を最も大きく決定する要因は,テーブル設計です。テーブル設計は,工程のかなり初期の段階でなされますが,ここがまずいと,その後の開発全体を無駄に不効率で混乱したものにしてしまい,かつ容易に後戻りがきかないという重要なステップです。したがって,「はじめにテーブルありき」は何にもまして重要な合言葉です。

しかし,この工程の難しいところは,往々にして一義的な正解を定められないことです。常に「これが正解」と呼べるような決まったアルゴリズムが存在しないのです。もちろん,数十年にわたる多くの人々の努力によって,いくつかの効果的な設計技法や,原則として踏み外してはいけない最低限のルール(可能な限り正規化すること,主キーを必ず設定すること,多対多の関連は作らないこと,等々)は確立されています。それでもなお,DB界の権威であるクリス・デイト氏自ら『データベース設計はきわめて主観的である』と認めなければならないのが,この分野の悩ましいところです。

今回は,上で挙げたような最低限の基礎ルールを踏まえたうえで,使い方次第によっては有用であり得る..しかし万能ではない..いわば「グレーゾーン」に位置する設計方法やモデルを紹介し,そのメリット/デメリットについて考えてみたいと思います。毒にも薬にもなるため,現場での微妙な判断が要求されるものばかりですが,たとえ自ら使うことがなくとも,既存の設計にこうした方法が使われていた場合に対処するためにも,知って損をすることはないでしょう。

稼働環境
  • Oracle
  • SQL Server
  • DB2
  • Postgre SQL
  • My SQL

今回の内容は実装のバージョンには依存しません。実際に動作確認を行った環境は「Oracle 10.2.0.1」「Postgre SQL 8.3」「My SQL 5.0」です。

単一参照テーブル~テーブルにポリモフィズムは必要か

みなさんも,日々の業務の中では,いろいろなコード体系を使っていると思います。「都道府県コード」「顧客コード」「性別コード」「疾病コード」など,きっとテーブルにはいろいろな種類のコード列が含まれていることでしょう。中には「年齢階級」「人口規模の階級」など「階級」という名前で呼ばれているものもありますが,これらもある特定の集団や範囲,概念を指示するという点で同じ働きをするので,ここでは一括して「コード」と呼ぶことにします。

こういうコードを扱う際,重要な存在となるのが,俗に「マスタ(台帳)」と呼ばれるテーブルです。つまり,コードのすべての集合(県コードなら47種類)を保持しておくテーブルです。SQLを使って帳票を作成するときは,このマスタテーブルを主としてデータテーブルと外部結合する,という方法が一般的です。

そうすると,このマスタテーブルというのは,基本的にコード体系の数だけ必要ということになります図1)。

図1 増えていくマスタテーブル

都道府県マスタ:PrefMaster

code
(コード値)
code_desc
(コード内容)
01北海道
02青森県
03秋田県
 


47沖縄県

顧客マスタ:ClientMaster

code
(コード値)
code_desc
(コード内容)
A001A商社
B002B建設
C003C保険
 


B027Z化学

性別マスタ:SexMaster

code
(コード値)
code_desc
(コード内容)
0不明
1
2
3適用不能

マスタの数が増えること自体が即座に悪い,というわけではありません。しかし行数の少ない小規模テーブルが増えると,いささかER図も暑苦しくなり,管理する人間の側も一目でシステムの構造を把握できなくなってきます。しかもマスタテーブルの構造は,どれも似たり寄ったり。とすれば,「いっそこれらのテーブルをすべてひとつにまとめてしまったほうが,すっきりして何かと便利なのではないか?」という発想が出てくるのも,不思議なことではありません。

こうして生まれるのが,いわゆる「One True Lookup Table」略してOTLTです注1)。あえて訳すなら単一参照テーブルとでもなるでしょうか。具体的には図2のような構造をとります。

図2 OTLT:雑多なコード体系の寄せ集め

そう,一見しておわかりでしょう。複数のコード体系を1つのテーブルにまとめてしまったのです。あとは,「コードタイプ」をキーに必要なコードだけ切り出して使用するわけです。たとえば,47都道府県を表側として各県の人口を求めるSQLは,リスト1のようになるでしょう。データテーブルは図3を使います。実行した結果は図4のようになります。

リスト1 OTLTを利用して人口一覧を出力する帳票を作成

SELECT MASTER.code_value AS pref_cd,
         MASTER.code_description AS pref_name,
       DATA.population AS pop
  FROM OTLT MASTER LEFT OUTER JOIN DataPop DATA
    ON MASTER.code_value = DATA.pref_cd
 WHERE MASTER.code_type = 'pref_cd';

図3 使用するデータテーブル
人口テーブル:DataPop

pref_cd
(県コード)
population
(人口)
011000
032000
041200
055000
078000

図4リスト1の結果

pref_cd pref_name pop
------- --------- ------
01      北海道    1000
02      青森
03      秋田      2000
04      岩手      1200
05      宮城      5000
06      山形
07      福島      8000
         :
47      沖縄

外部結合することによって,データテーブルには含まれていない県(青森や沖縄など)まですべて含む47都道府県の完全なリストが得られる点は,通常のマスタテーブルと変わりません。1つのテーブルが,あるときは「都道府県」の集合になり,またあるときは「顧客」の集合になる,というように七変化するのが,このOTLTの特徴です。呼び出されるたびにテーブルの役割が変わることから,これをオブジェクト指向におけるポリモフィズムになぞらえる論者もいます注2)。このOTLTを使うことのメリットは,大きく次の3つにまとめられます。

  • マスタテーブルの数が減るので,ER図やスキーマがシンプルになる
  • コード検索のSQLを共通化できるため,コーディングを簡略化できる
  • 複数の業務で使用するコード群を一ヵ所で管理できるので,保守/管理が容易になる

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入