列持ちテーブル

次に紹介するのは、“⁠グレー⁠”な設計の中で最も頻繁に見かけるものです。そして最も“⁠黒⁠”に近い設計です。特に一般的な名前はついていないようなので、私は「列持ち」テーブルと呼んでいます。構造はごく単純で、銀行口座の残高の推移を管理するデータを例にして、対になる「行持ち」のテーブルと共にお見せしましょう（図6⁠）⁠。

第2回を読まれた方は、この両者を変換するSQLを紹介したことを覚えているでしょう。そのSQLを利用すれば、「⁠列持ち」⇔「行持ち」の変換を行うことが可能なので、最悪、設計時点でどちらかのモデルを選択したあとに、「⁠やはりうまくいかなかった」ということでもう一方のモデルへチェンジすることも、できないわけではありません（アプリケーション側の修正など、相応の工数は覚悟せねばなりませんが⁠）⁠。しかし、最初は可能な限り「行持ち」を選択するべきです。

その理由は、列持ちモデルの拡張性と保守性の低さです。実際、今は2008年なので年度ごとに作られた列も2008年まで用意していればよいとして、来年になったらどうすればよいのでしょう？ 列をもう1つ追加するほかありません。すると毎年テーブルの構造を変えなければなりませんし、このテーブルへアクセスするSELECT文から結果を受け取るホスト言語まで、ほとんどシステム全体のコーディングに変更が発生します。

同じ悩みは、ほかのケースでも同様に起きます。社員の子供の一覧を管理するテーブルを列持ちにした場合、5列でテーブルが宣言されていたとしたら、社員に6人目の子供が生まれた場合、どういう扱いにすればよいのでしょう？

列持ちのテーブルに対してSQLでアクセスする際にも、やはり拡張性の低さが悩みの種です。たとえば、「⁠すべての年度について、残高が100,000円以上の口座を選択する」というSELECT文を考えてみてください。優良顧客の動向を調べるためにも、コンスタントに残高の多い口座がどのぐらいあるかは頻繁に知りたいことでしょう。図6ならば、007634番だけが相当します（今回はNULLは0円として扱うことにします⁠）⁠。

列持ちの場合は、やはり1列ずつ指定せねばなりません。

すべての年度について、残高が100,000円以上の口座を選択する（列持ちの場合）

SELECT act_nbr
  FROM AccountsCols
WHERE amt_2006 >= 100000
  AND amt_2007 >= 100000
  AND amt_2008 >= 100000;

これでは、列が追加になるたびにクエリも変更する必要があります。そして、年度列が増えれば増えるほどSQLは無駄に長大になっていきます。

一方、行持ちテーブルの場合であれば、次のようなSQLが使えます。

すべての年度について、残高が100,000円以上の口座を選択する（行持ちの場合）

SELECT act_nbr
  FROM AccountsRows
 GROUP BY act_nbr
HAVING COUNT(*) = SUM(CASE WHEN amt >= 100000
                           THEN 1
                           ELSE 0 END);

このSQLの良いところは、年度がどれだけ増えようと変更しなくてよい一般性の高さです。このように、列持ちに比べて行持ちは非常に拡張性と保守性に優れるモデルです。

しかし、こうした明白で巨大な欠点があるにもかかわらず、列持ちのテーブルはいたるところで見かけます。DBがらみの開発に数年も携わった人なら、必ず一度は見たことがあるでしょう。このモデルがそれほどに強い誘惑を放つ理由は2つあります。

入力側の理由：ついつい列を配列に見立ててしまう

テーブルの入力となるデータが配列として存在していた場合、それを素直にaccount[0]、account[1]、account[2]……のようにテーブルの列へ展開したくなるのは、人情というものです。しかし、SQLに配列はありませんし[3]⁠、添え字でアクセスすることも不可能です。配列ならば、ループによって拡張性の高い方法ですべての要素にアクセスできます。

SQLで同じことをするためには、必ず列名を使ってアクセスせねばなりません。

全残高を0クリア（列持ちバージョン）

UPDATE AccountCols
   SET amt_2006 = 0,
       amt_2007 = 0,
       amt_2008 = 0;

これでは、1列増えるたびにクリアする列名を追加していく必要があり、およそ拡張性にかけます。一方、行持ちモデルを利用していれば、年度がどれだけ増えようと、次のクエリを修正なしで使いまわせます。

全残高を0クリア（行持ちバージョン）

UPDATE AccountRows
   SET amt = 0;

あるいは配列と似たようなケースで、COBOLなどが扱うフラットファイル（すべてのデータが平たく1行で表現された形式）を入力とする場合も、ファイルの形式にテーブルが引きずられて列持ちになってしまうケースを多く見かけます。

出力側の理由：帳票が列持ちの場合

列持ちモデルが有利な唯一のケースが、出力される帳票の形がテーブルと同じ列持ちの形式である場合です。そういうケースは結構多いのですが、この場合は、列持ちのテーブルから出力するほうが、SELECT文のパフォーマンスもよく、SQLも簡単で済みます。したがってこのケースでは、とりあえずデータを長期的に保持するために行持ちテーブルをバックに、出力のために利用する列持ちテーブルをフロントに用意する、という二段構えの設計を行うことが有効です（図7⁠）⁠。

このように出力の形に合わせたテーブルをフロントに持つ方法は、古くから利用されています。データを集計した結果を保持する集約テーブル（サマリーテーブル）も、このタイプのひとつに位置付けられるでしょう。

このように、列持ちモデルには、使い方次第では活躍の場があります。そのため、一般的に推奨する気はありませんが、捨て去るのももったいないので、“⁠グレー⁠”に分類して紹介した次第です。