違法すれすれの「ライン上」に位置する設計

• グレーノウハウ

  • 著者の造語
  • バッドノウハウとはっきり断定することはできない
  • 無神経に使うと開発や運用に支障をきたす
  • 「副作用の強い薬」
• 正確な知識をもって、利点と欠点とを理解して正しい用法で

代理キー～主キーが役に立たないとき

• RDBにおいては原則主キーは必須

  • さもないと

    • SQLがDISTINCTまみれになる

      • 暗黙のソートつき
    • コストの高い結合演算が、対象行数が増えてさらに高コストに

主キーが決められない、または主キーとして不十分なケース

• パターン1: そもそも入力データに主キーにできるような一意キーが存在しない

  • 言語道断
  • そもそもそんなデータをテーブルに入れてはいけない
  • 前段のアプリケーションでデータクレンジングしろ

• パターン2: 一意キーはあるが、サイクリックに使い回される

  • コード体系の桁数が少なくて足りなくなるようなケース
  • 例: A001という市区町村コードが、ある年度まで「B市」を指していて、ある年度から「Q市」になってしまう

  • 時系列で過去のデータを取得したいとき困る

    • 履歴情報を残さないと対応できない

• パターン3: 一意キーはあるが、途中で指す対象が変わる

  • 市町村の例: B市がC市を合併
  • 名前はB市のまま
  • 中身のデータ(人口等)は大きく変わってしまう
  • やはり履歴情報を残さなければならない

代理キーによる解決

• surrogate key
• パターン2,3ともに解決可能

• しかし極力代理キーは避けるべき

  • 論理モデルをわかりにくくしてしまう
  • 本来「使わなくても何とかなる」道具
• まともな理論家ならば推奨しない

オートナンバーを主キーに使うことは、データモデルを欠いている証拠だ (ジョー・セルコ)

自然キーによる解決

• パターン1: むり
• パターン2,3: 自然キーによる解決が可能

タイムスタンプ

• 「B市」が廃止されたのが2007年度だとする
• 各年度のスナップショットを用意

• 利点

  • シンプル

• 欠点

  • スナップショットの頻度が短いとレコード数甚大

インターバル

• インターバル: 期間

  • cf.タイムスタンプ: 時点
• 終了年度はNULLでもよいが、SQLの使い勝手を考慮すると「入力上の最大値」のほうが便利

• 利点

  • レコード数少ない

• 欠点

  • タイムスタンプ方式に比べるとSQLが複雑

    • 言うほどでもない

オートナンバリングの是非

• 代理キー/自然キー論争
• 「可能な限り自然キーを使う」に尽きる
• が、現場では代理キーを使わざるを得ないこともある

• オートナンバリング

  • 代理キーの一実装
  • 数値を自動的に割り振る

  • 要件

    • 一意性(主キーとして必須)
    • 連続性(オプショナル)

• どうやって数値を払い出す

  • データベース機能

    • シーケンスオブジェクト

      • 標準SQL
      • CREATE SEQUENCEで作りSELECTでアクセス
      • 細やかな制御ができる

    • ID列

      • オプション少ない
      • 移植性低い

  • アプリケーション側で実装

    • 車輪の再発明。やめとけ

      • コストかかる
      • 排他制御大変

• 重大な欠点

  • 数値の払い出しがボトルネックになることが比較的よくある

    • 排他制御のため原理的に避けられない

列持ちテーブル

配列型は使えない、でも配列を表現したい

• 配列型は使うな(バッドノウハウ)
• 列持ちはある種「素直」

列持ちテーブルの利点と欠点

• 利点

  • シンプル

    • 正規化とか知らない人でも理解できる

  • 入出力のフォーマットと合わせやすい

    • 【補】インピーダンスミスマッチがない的な

• 欠点

  • 列の増減が難しい

    • テーブルは気軽に変更できる代物ではない

      • DOAなのでアプリケーションに撥ねる

  • NULL

    • 3値論理に足を踏み入れるはめになる
    • NULL撲滅委員会

行持ちテーブル

• 基本は行持ちせよ

• 列持ち/行持ちは相互変換できる

  • 列持ち->行持ち: 各列SELECTしてUNION
  • 行持ち->列持ち: CASE式とGROUP BYと集約関数

アドホックな集計キー

• 地方ごとに集計したい

  • 東北地方、とか
• 「地方」のキーがない
• アドホック(場当たり的)な集計キーを追加してしまう

• 問題点

  • 場当たり的に次から次へと追加される

    • 巨大なテーブルがさらに巨大化しパフォーマンスが劣化する

• 解決策1

  • キーを別テーブルに分ける

    • トランザクションテーブルに比べてレコード数少ない
    • メンテしやすい
  • JOINが必要なのでパフォーマンス問題の解決には寄与しない

• 解決策2

  • ビューを用意して地方コード追加する

• 解決策3

  • CASE式でad-hocにキーを読み替える

多段ビュー

• 「クエリの缶詰」by C.デイト

  • 保存が効く
  • 新鮮なデータを取り出せる
• 短所もある

ビューへのアクセスは「2段階」で行われる

• 2段階のSQLが発行される感じ

  1. ビューへのSELECT文
  2. 基底テーブルへのSELECT文
• 実際にはDBMSによってマージされて効率の良い形で実行されたりする
• が、1. だけよりは1. + 2.によって生まれるSELECT文のほうが高コストなはず

多段ビューの危険性

ビューの背後にあるテーブルの存在を、常に意識せよ

• エンジニアは決して物理層から自由になれない

  • 本当に基底テーブルを意識しなくていいのはエンドユーザーだけ
• 原則としてビューは1段にとどめておくように

• 多段ビューのデメリット

  • 芋づる式に多数の基底テーブルにアクセスしパフォーマンス低下

  • テーブルとビューの依存関係がわかりにくくなる

    • 仕様の複雑化
    • 設計者でさえ理解できなくなり、同じような機能のビューが乱立したり

データクレンジングの重要性

• 正規化の前処理
• 疎かにするとバッドノウハウ/グレーノウハウが生まれる原因となる

データクレンジングは設計に先立って行う

• DB設計はきれいなデータに対して行う

  • 論理設計には最低限「主キーがある」等の前提がある

• データが「汚い」

  • 新たに構築されるシステムにとって使えなかったり非効率なフォーマットであったりする

• データが汚くて掃除が必要な例

  • もともとシステム化されていなかった業務を初めてシステム化
  • システム化されてはいたがRDB以外のフォーマットのデータベースが利用されていた

代表的なデータクレンジングの内容

一意キーの存在しないデータは、バッドノウハウ「不適切なキー」をも生み出す

• 一意キーの特定

  • 宿泊者の名前しか記録していない、とか

    • 名前は一意キーにならない

名寄せをサボると、バッドノウハウ「ダブルマスタ」を生み出す

• 名寄せ

  • 表記ゆれを解消して名称を統一する

    • 個人名
    • 法人名
    • 住所
    • 電話番号
    • …
    • 「フリーハンド」を許していた時代の負の遺産

  • 「同一だ」とどうやって判断するの

    • 多要素を照合

      • 名前に加えて住所・年齢も照合するなど

    • 出現頻度を鑑みる

      • 会社名はふつう他社に似せない

まとめ

• グレーノウハウは用法次第では良薬になる

• 利点と欠点のトレードオフを検討せよ

  • 何も考えずに軽い気持ちで使うのは駄目