Only One

イット業界

今から偉そうな事を書きます。

One Fact In One Place と云う言葉があります。DB設計の重要な指針としてよく挙げられます。これには二つの意味があるように思います。

一つ目は、One Fact In Only One Place みたいな意味です。これはDB設計の経験があまり無い人にも判り易いと思います。コーディングにおいての、3箇所同じ処理を書いたらルーチンにしろと云ったルールと同じ考えだからです。DB設計では正規化なんて言葉が使われまして、本でも読めばすぐにぶち当たる事が出来ます。

この一つ目の意味が守られてない物を直すのは、割と見通しが良い作業と思います。テーブル外出しすりゃいいし、既存処理とのインタフェースはビューでも作って保証すれば良い。

問題は二つ目の、Only One Fact In One Place みたいな意味の場合です。一つの場所にはたった一つの事実しか入れない。こう云う考えは正規化とはあまり関係ないからか、本を読んでもあまり見当たらないように思います。

俺がこの仕事を始めた時に、出向先のこはい上司に「データモデルにロジックを持ち込むな」と云う事と、「データは静的なものであって、この事を保証するためにアプリケーション側で最大限の努力をすべし」ってのをしつこくしくこく叩き込まれました。

最初は意味が判らなかったのですが、まずい設計をして怒られてる内に判って来ました。それが、Only One Fact In One Place って事です。

最近の我が社のトホホ事例で説明します。

  • 自社で発行した納品書を元にして、仕入データを日々入力します。
  • 月次締めで、部門別仕入先別商品別で集計して、仕入集計テーブルを作ります。
  • 指定した年月の仕入集計データを一覧表示する画面があります。
  • その画面では、会計上の調整の目的で、仕入集計テーブルに対して新規レコードを追加できます。

てなよその会社が作った既存画面がありまして。一覧はこんな感じです（各名称は省略）。

部門 仕入先 商品 金額
10 0001 01 120
10 0002 02 200
11 0001 02 320
11 0231 03 100
12 0002 02 230

さて、仕様変更が入りました。上の一覧画面に、部門単位でブレイクして集計行を出して欲しい、と。

部門 仕入先 商品 金額
10 0001 01 120
10 0002 02 200
10 320
11 0001 02 320
11 0231 03 100
11 420
12 0002 02 230
12 230

こうですね。こうすりゃ出るべな。

SELECT
    部門,
    仕入先,
    商品,
    金額,
    0  AS ソート順
FROM
    仕入集計テーブル
WHERE
    年月 = YYYY/MM
UNION
SELECT
    部門,
    ''  AS 仕入先,
    ''  AS 商品,
    SUM(金額),
    1  AS ソート順
FROM
    仕入集計テーブル
WHERE
    年月 = YYYY/MM
GROUP BY
    部門
ORDER BY
    部門,
    ソート順

それがあーた、俺が出向から帰ってきたらですよ、仕入集計テーブルの中がこうなってたんですわ！

部門 仕入先 商品 金額
10 0001 01 120
10 0002 02 200
10 9999 99 320
11 0001 02 320
11 0231 03 100
11 9999 99 420
12 0002 02 230
12 9999 99 230

これは画面イメージじゃないです。SELECT * で取ってきた結果みたいな感じです。

集計行が1レコードとして入ってる・・・・。ソート順も仕様通りになるようにご丁寧に9999だの99だの特殊コード突っ込んで。マスタにまで特殊コード登録して。月次処理で仕入集計テーブルを作る時に、アプリ側で一生懸命集計して部門につき1レコード作ってINSERTしてました。

作ったのはリーダーさんなので、そもそもがこう云う設計な訳です。

俺ならSQLを上記の様に直しておしまいです。この設計だと、特殊コードをマスタに登録、月次集計処理に部門別集計算定してINSERTする処理を追加、と影響範囲を無駄に広げてしまっています。問題はそれだけではありません。

さて、賢明な皆さんはお気づきでしょう。

既存機能である、仕入集計テーブルへの画面からの新規レコード追加が、不可能なのじゃああああ。

いや追加だけなら出来るけどさ、集計行の再計算せんとならん訳です。いちいち。SQLを書けない人がDBアプリの設計をやる事の悲劇がここにありますね。あまりの事に、リーダーさんにちょっと怒ってしまいました。つい。でも云わないと、これは。

あくまでも月次の部門別仕入先別商品別合計金額が入るべきフィールドに、仕入先と商品にある特殊なコードが入った場合のみ、部門別合計金額が入る、っていう形になってる訳です。

つまり、データ構造の中に、if(仕入先 == 特殊コード)みたいなIF文が組み込まれちゃってるのです。なんかの条件によって、一つの場所に複数の事実が入ってるのです。Two Fact なのです。

これは困ります。ほんと、今度やったらまじで怒りますよ。

と思ったら、もう一個やってました。これも俺が出向行ってる隙に実装されたものでして。

  • 注文画面から商品の受注データを入力します。
  • 当初は受注データは修正自由でした。会計にもつながってないし。
    • ここまでは俺も関わってた。
  • 利用者増加につき、モラルハザード発生
    • 受注日をいんちきして編集する人多発、ユーザが触れないデータ登録日を会計上の発生日とする。
    • 修正履歴を残したいってんで修正を赤黒処理に変更。

ここで大問題は、登録日です。レコードの登録日や登録ユーザーや更新日や更新ユーザーってなフィールド、各テーブルに作るじゃないですか。あれをそのまま流用したんですわ。Two Fact In One Place って訳です。

なんで新しくユーザーが編集出来ない日付フィールドを設けなかったのか。これは赤黒処理とは日付との格闘であると云う、経験者なら誰でも知ってる事実を知らなかったためですね。

このお客様は教育業界ですので、学校の学期ごとに受注金額を集計する帳票が欲しいって話になりまして。うちで唯一帳票向けSQLが書ける俺が作る事になりました。

ん？

さて、賢明な皆さんならばお気づきでしょう。

学期をまたがって赤黒切ると、受注金額が学期を移動するのじゃああああ。

例えば、備考欄に書いた字を間違えたので修正しよう、なんてのでも、1学期の実績が2学期に移ったりするのですわ。登録日基準で集計するから。そういう仕様なのですわ。ほんげー？

しかも、赤黒訂正なんて、学期またぎのタイミングで頻発して当たり前。学期末に帳票出して間違いに気付く事が多いから。

怒りましたよ。やんわりとですが。One Fact In One Place って、ホワイトボードに書いちゃいました。研修ですよ・・・。

てな訳で、二つ目の意味に対する違反をリカバリーするのは無茶苦茶骨が折れるかつ胃に悪いかつ人間関係も危うくなるので（俺は心が広いので大丈夫です、これくらいならまだ）なんとかして避けて戴きたい所存。

以上、ここ数ヶ月の疲れを癒すために偉そうな事を書きました。