6月下旬から連日40℃近い酷暑に見舞われ、最高気温が35℃を下回ると「今日はちょっと涼しいね」などという会話が交わされている甲府盆地よりお届け致します。 皆様におかれましては熱中症対策などを十分に講じ、体調に気を付けてお過ごし頂ください。

これまたいつものことですが、現在の仕事は既存システムの改修がメイン。 先月までは Internet Explorer のサポート終了に備え、モダンブラウザで動作しない JavaScript の書き直しや、VBScript による機能のサーバ側への移設といった作業に忙殺されておりました。 ブラウザの移行に伴って発生する動作不良への対応なので、問題が発生するのは基本的にクライアント側なのですが、その原因や対処法を探る際にはサーバ側の動作を含め、対象システムのすべての挙動を調査・把握する必要があります。

そうした分析業務を妨げる要因のひとつに「低品質なプログラム」の存在であることは以前の記事で述べた通り:

それでも、よく管理されているプロジェクト、よくメンテナンスされているシステムではプログラムの質も比較的優良であり、その改修・拡張といった作業は開発元企業に属する人員によって行われている場合が多いようです。 一方、私のような「外注」に改修依頼が出されるようなプログラムというのは、開発元の「正規部隊」が手を付けられない、あるいは手を付けたくないほどに低品質なものであるというのが相場。

低品質コードの成長過程

今回の記事では、これと双璧を成す「低品質なデータベース設計」について、事例ごとに問題点とその背景について解説してみたいと思います。

「主キーを除き、NOT NULL 制約 が付与されているカラムがひとつもないテーブル」に遭遇し頭を抱える、という事態は私の仕事では日常茶飯事。 「NOT NULL 制約」とは読んで字の如く、「そのカラムが NULL 値を取らない」ことの宣言あるいは保証です。 例えば、以下のような「会員」テーブル:

において、主キーである「ID」、すべての会員に一意に付与される「会員番号」、そして登録時に入力必須となる「氏名」「メールアドレス」は、入力任意項目である「住所」や「電話番号」のように NULL が入ってしまうと不都合が生じます。 そこで通常、これらのカラムには NOT NULL 制約をつけて、値として NULL が入らない (入れようとするとエラーが発生する) ようにします。

これによって、この「会員テーブル」を扱うプログラムを書く/読み解く人は

という考慮を省くことで、その仕事にかかる手間を減らすことができます。 逆に言えば、本来設定されているべき NOT NULL 制約がないことは、「氏名」が NULL であるという "ありえない状態" を想定することによる時間の浪費に繋がります。 プログラムのミスなどでその "ありえない状態" が発生してしまいシステム全体が機能不全に陥る、といった事態も考えられます。 (実際、そのようなトラブルは頻繁に起きている。)

NULLが入らない/入ってはいけないカラムには、必ず NOT NULL 制約を付けること。 個人的には、カラムはデフォルトで NOT NULL とし、NULL 値を保持することが許されるものに対して NULLABLE (NULL可) 設定を与えるようにすべきであったろう、と考えています。

テーブルを設計する際は、個々のカラムが表現する内容に応じて適切なデータ型を選択することが重要。 「注文の数量」なら整数型, 「氏名」ならテキスト (文字列) 型, 「開始日」なら日付型, ...といった具合です。 ところが、そうしたデータ型の存在を知らないのか、あるいは単なる横着なのか、これらすべてを「文字列型」のカラムとして定義しているテーブルに出くわすのは、決して珍しいことではありません。

特に厄介なのは、日付をテキストとして扱い、しかも書式が正規化されていないもの:

「年」「月」「日」の区切り文字として - (ハイフン) と / (スラッシュ) が混在し (区切り文字がないものもある)、「月」や「日」を表す値が1桁の場合に十の位をゼロ埋めしないものがある、といった混乱はよくお目にかかる光景。 ときには、

という「日本語表現」で格納されているパターンにも遭遇します。 (極めつけは「月」「日」の値が1桁の場合は全角で表記するというルール!)

さらには、だいぶ昔、一度のみの経験ですが、

のように、末尾に曜日が括弧書きされている事例にも行き当たったことがあります。

こうした設計は以下のような問題を引き起こします:

(1) そのフィールドに対する比較やソートを正しく行うことができない

辞書順比較により、'2022/12/31' が '2022/3/1' より「前」と判定されてしまうなど

(2) 日付として無効な文字列が入っている可能性を排除できない

'0000-00-00', '9999/99/99', '2022-02-29', 'hogehoge' などが入っていたら?

これに次いでお目にかかりたくないのは、所謂「フラグ」として扱われる二値フィールド、すなわち ON/OFF (あるいは Yes/No) のいずれかが、テキスト型として定義されているパターンでしょうか。 通常、こうした値は整数型として定義し、以下のルールに従って取り扱うのが一般的な設計手法 (ブーリアン型をサポートしているデータベースエンジンは少ない; 古いものでは特に) です:

しかし、この種の「悪い設計」では、この「フラグ」を表すカラムが VARCHAR(4) のようにテキスト型のフィールドとして定義されており、実際に格納されているデータを覗いてみると、

のように、'Yes' や 'No' という文字列で識別されています。(しかも大文字小文字が入り乱れている。) ここに 'Both' なんて文字列でも入れてやったらどうなるのか......などと考えてしまうところですが、それでもこれはまだマシな方。 もっと厄介なのは、

• ひらがな: 'あり' / 'なし'
• 漢字: '有' / '無'
• 全角記号: '○' / '×'

といった非ASCII記号が用いられているパターンで、アプリケーション側のエンコーディングの違いによる動作不良を引き起こしたり、日本語入力ができないCUI端末などからメンテナンスをしなければならない場合などに大きな障害となったりします。

このような設計が何故広く行わているのかと考えたとき、その発生源として思い当たるのは Excel や Access 上で動く、しばしば当座の用のために即席で書かれるVBAプログラムです。 これらのシステムでは、データ構造とその表現がほぼ等しくなるため、例えば画面上に「検査済みかどうか」を '〇' か '－' かの文字列で表示したい場合、それがそのまま「生のデータ」としてセルに書き込まれるというケースが殆ど。 個人や小さな事業所で利用しているうちは問題が顕在化しにくいものの、運用をしていくうちに利用者が増え、適用される業務範囲が拡大していき、ある時点で「Excel (/Access) アプリとして使い続けるのは厳しくなってきたから、専用のWEBアプリとして移植しよう」という動きが出てきます。 出てくるんですったら。(経験談) その際にデータ構造と表現 (プレゼンテーション) の分離を図らず、VBAで記述されたロジックをそのまま移し替えることで、このような「悪しきWYSIWYG」が生まれるのではないか、と推測しています。

拙いテーブル設計には「テーブルやカラム名に英語が (適切に) 使われていない」という傾向が見られます。 私自身それほど英語が得意というわけでもなく、また「ソフトウェア開発では英語を使うのが正義」などと主張するつもりもありませんが、それでもこの分野の技術が英語圏を中心に発展していることもまた事実で、ある程度は英語を勉強しておく必要はあるのではないかと思うわけですが......。

もっともありふれたパターンはカラム名がローマ字で命名されているもの。 "siyou" が「使用」と「仕様」のどちらを指すのか、といった混乱は生じますが、それでも語句全体が表記されていれば、読む側としてはまだ助かります。 困るのは、そこから子音だけを取り出して、しかも3文字などごく短く切り詰めた表記が用いられているケース。 いくつか例をあげると、

• BMN → 部門
• CMN → 注文
• NHN → 納品
• SRY → 数量
• TNK → 単価
• TNT → 担当

......読者諸兄も「こんなもん分かるか!」と叫びたくなったのではないでしょうか。 テーブル構造を解析していると不意に KKK という単語が目に飛び込んできてギョッとしたものの、データとプログラムの前後関係から「価格 (KaKaKu)」と判明、ということもありました。

このようなカラム名の正体を突き止めるにあたっては、「それが日本語である」ということが保証されていればまだマシなのですが、多くのケースではこれに英語が混ざっています。 それも、"GPS (Globas Positioning System)" や "JIS (Japanese Industrial Standards)" のような、単語の頭文字を並べたアクロニムではなく、単語から (日本語の場合と同様にしばしば奇妙な規則で) 子音を抜き出したものだったりします:

• CNT → count
• CTG → category
• PRC → price
• SRI → serial
• SRN → serial Number
• TTL → title

とんちクイズやってる暇はないんだけどな。 そんなこんなで「珍妙な略語を使うくらいなら、もういっそ日本語で書いてくれ」とも思ったりするわけですが、日本語で付けられたカラム名もなかなかの強敵で、

• 備考 その他
• S/N比
• 単価（参考)

クォートすれば問題ないとは言え、半角スペースやスラッシュ, ピリオド, などプログラムやSQLにおいて特別な意味を持つASCII記号を入れたりされると、プログラマとしては泡を吹いて卒倒しそうになります。 3番目のものは一見問題なさそうに見えるかもしれませんが、実は「開き括弧は全角なのに、閉じ括弧は半角」という問題児。 こいつのせいで、デバッグ用に書いたSQLがエラーを出すものの、何度見返してもどこが悪いのか分からないという悲惨な目に遭いました。

これは余談になりますが、"number" という単語を "No." と短縮して書く慣例に倣って "serial_no", "order_no" といったカラム名を付けるのはおすすめできません。 というのは、誰もが知る通り、"no" という単語は既に存在するため。 省略を表すためのドット (.) 記号はカラム名に含めるのは好ましくないことから考えても、"serial_number" のようにフルスペルとするのが最も無難かつ素直な命名でしょう。 どうしても縮めたいというのであれば、"serial_num" の方がまだマシですが、そもそもこの例であれば "serial" だけで意味が通る場面が多いはず。 "order_number" を "order" にするのは適切とは言い難いので、場面に応じて臨機応変に、しかし奇抜に過ぎない命名を心掛けたいものです。

以前、カラム数が多すぎるテーブルの問題を取り上げたことがありました。

ちなみに、これまでに遭遇したことのあるテーブルのカラム数の最大値は376。 定義をコンソールで確認しようとしても、テキストが一瞬で画面上方にスクロールして飛び去ってしまいます。(泣)

でかいテーブルの恐怖

この記事を書いた以後も、「遭遇したテーブルのカラム数」記録は着々と更新されていき、現在は869と当時の倍以上の値に達しています。

カラム数肥大化の主な原因はこの記事で取り上げたように、レコードが含む配列データを別テーブルに切り出すという手法 (参考 関係データベースにおける配列の表現) を取らなかったために、"step1", "step2", ... "step30" のような「列の反復」が起きてしまっていることですが、それに加えてもっと「レベルの低い原因」でカラムが増えているケースもあります。 例えば、複式簿記システムにおいて、個々の取引を記載するテーブルのカラムが次のように定義されていると考えてみてください:

行データは次のようになっています:

この2件はどちらも2022年中の取引ですが、会計年度 (fiscal year) の始点である4/1をまたいでいるため、行#3324は「2021年度」、行#3325は「2022年度」に属するものとなり、そのことがフィールド "fiscal_year" に記録されています。 しかしこの "fiscal_year" は無駄なカラム。 何故なら、その値は "date" の値から求めることができるから。 SQL Server であれば、

のようにすれば計算することができます。 この事例では、作成者が YEAR や MONTH といった関数の存在を知らなかったのか、"year", "month", "day" といった「無駄な」カラムが、8個ほど定義されていました。

また別のテーブルでは、"xx_flag_a", "xx_flag_b" という2つの「フラグ」格納用のカラムが定義されていましたが、

• 両方とも 0 または 1 の値を取る
• xx_flag_a = 0 ならば、 xx_flag_b = 1
• xx_flag_a = 1 ならば、 xx_flag_b = 0

という具合に、常に互いと逆の値を保持する運用となっていました。 当然のことながら、どちらから片方だけがあれば十分であり、もう片方は「無駄な」カラムということになります。

この無駄が、ディスクスペースの空費をもたらすだけであれば、放置しておいても構わないかもしれません。 しかし、何らかの理由でこれらの値に食い違いが生じた場合、どちらが正しいデータなのか判断が付かなくなるという事態が発生します。 例えば、前述の複式簿記の取引テーブル内に下記の行が存在するのを発見したとしましょう:

このとき、"date" と "fiscal_year" の値の少なくともどちらかが間違っていることは明らかですが、どちらがそうなのかを知る術はありません。 つまり、この行のデータを修正するのに必要な操作が

のどちらであるかを決定することはできないということ。 これはシステムにとって致命的な事態だと言えます。

冗長なデータ構成にすることでエラー検出に利用できると考える人がいるかもしれませんが、それはこれらのデータが個別に入力されることが前提となっています。 登録時にプログラムが "date" から "fiscal_year" を計算して入れているのであれば、単にバグを作る機会を増やしているだけに過ぎません。

Don't repeat yourself (DRY) あるいは Single Source of truth (英) は、特にコンピューティングの領域で、重複を防ぐ考え方である。 この哲学は、情報の重複は変更の困難さを増大し透明性を減少させ、不一致を生じる可能性につながるため、重複するべきでないことを強調する。

Don't repeat yourself - Wikipedia

そもそもの問題は、「食い違っては困る」大切なデータを「食い違った状態にできる」業務フロー/システムにあります。 そうしたデータは本来、ただ一度だけ「マスタ」として入力させた上で、出力や集計の用途に合わせて「加工」して使うべきもの。 そうすれば、プログラムにミスがない限り、複数の帳票上で数値が食い違うような事態は起こりません。 プログラムのミスもしばしば発生はしますが、一度修正してしまえば、次からは気を付ける必要そのものがなくなるというのが素晴らしいところです。

Less Chance to Make Mistakes

今回書いた内容は、私が仕事や仕事を通じて習得したデータベース設計関連の知識や技術の中では基本中の基本、初歩中の初歩と言えるもので、だいぶ昔に完了し、必要に応じてごくたまに振り返る程度のもの。 より進んだトピックとして、例えば

• 外部キー設定の活用
• 集約関数の扱い方
• インデクス設定によるパフォーマンス改善
• 実行計画の読み方

などの解説をしたいと思っていたのですが、そうした記事はより良質なものが他所に溢れています。 となると、自分としては、そうしたノウハウよりもずっと手前にある「駄目な設計の避け方」を共有する方が開発現場にとって資するところが大きいのではないか、と思い直してキーを叩き始めました。

案件の特定を避けるため、細部を変更したり複数の案件の情報をミックスしたりしてはいますが、現実の「レベルの低い開発現場」では概ねこの水準のテーブル設計が横行しているのが実情です。 そうした環境では、(現在の水準と比較して) 遥か高見にある「良い設計」を示すだけでなく、現物を前にしてどこがどうマズいのかを個別に具体的に批判・検討するということをしない限り状況の改善は望めない、と感じるところ。 しかし裏を返せば、そこまで基本が疎かになっている現場では、特段高度な知識・技術の習得がなくとも「やってはいけない」地雷パターンを避けるだけで、劇的に成果物のクオリティが上がるということでもあります。

本エントリは、そうした「底上げ」のための努力は業界にとって決して無駄ではないだろうとの考えから執筆したもの。 これからデータベース設計を始める、あるいは、これまで「動けばいいや」という方針でやってきたけど限界を感じている、という方の一助となれば幸いです。