勉強日記

毎日投稿

達人に学ぶDB設計 徹底指南書 ch8 論理設計のグレーノウハウ

www.shoeisha.co.jp


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

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

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

  • RDBにおいては原則主キーは必須
    • さもないと
      • SQLがDISTINCTまみれになる
        • 暗黙のソートつき
      • コストの高い結合演算が、対象行数が増えてさらに高コストに

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

  • パターン1: そもそも入力データに主キーにできるような一意キーが存在しない
    • 言語道断
    • そもそもそんなデータをテーブルに入れてはいけない
    • 前段のアプリケーションでデータクレンジングしろ
  • パターン2: 一意キーはあるが、サイクリックに使い回される
    • コード体系の桁数が少なくて足りなくなるようなケース
    • 例: A001という市区町村コードが、ある年度まで「B市」を指していて、ある年度から「Q市」になってしまう
    • 時系列で過去のデータを取得したいとき困る
      • 履歴情報を残さないと対応できない
市町村コード(PK) 市町村名 人口
A000 A市 100,000
A001 B市 120,000
A002 C町 30,000
...
A999 Z市 15,000
市町村コード(PK) 市町村名 人口
A000 A市 100,000
A001 Q市 180,000
A002 C町 30,000
...
A999 Z市 15,000
  • パターン3: 一意キーはあるが、途中で指す対象が変わる
    • 市町村の例: B市がC市を合併
    • 名前はB市のまま
    • 中身のデータ(人口等)は大きく変わってしまう
    • やはり履歴情報を残さなければならない
市町村コード(PK) 市町村名 人口
A000 A市 100,000
A001 B市 120,000
A002 C町 30,000
...
A999 Z市 15,000
市町村コード(PK) 市町村名 人口
A000 A市 100,000
A001 B市 150,000
...
A999 Z市 15,000

代理キーによる解決

  • surrogate key
  • パターン2,3ともに解決可能
市町村管理コード(PK) 市町村コード 市町村名 人口
0 A000 A市 100,000
1 A001 B市 120,000
2 A002 C町 30,000
...
999 A999 Z市 15,000
市町村管理コード(PK) 市町村コード 市町村名 人口
0 A000 A市 100,000
1 A001 B市 120,000
2 A002 C町 30,000
...
999 A999 Z市 15,000
1000 A001 Q市 180,000
  • しかし極力代理キーは避けるべき
    • 論理モデルをわかりにくくしてしまう
    • 本来「使わなくても何とかなる」道具
  • まともな理論家ならば推奨しない

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

自然キーによる解決

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

タイムスタンプ

年度(PK) 市町村コード(PK) 市町村名 人口
2005 A000 A市 100,000
2005 A001 B市 120,000
2005 A002 C町 30,000
...
2006 A000 A市 100,000
2006 A001 B市 120,000
2006 A002 C町 30,000
...
2007 A000 A市 100,000
2007 A001 Q市 180,000
2007 A002 C町 30,000
...
  • 「B市」が廃止されたのが2007年度だとする
  • 各年度のスナップショットを用意
  • 利点
    • シンプル
  • 欠点
    • スナップショットの頻度が短いとレコード数甚大

インターバル

開始年度(PK) 終了年度 市町村コード(PK) 市町村名 人口
1945 9999 A000 A市 100,000
1998 2006 A001 B市 120,000
1955 9999 A002 C町 30,000
...
2007 9999 A001 Q市 15,000
  • インターバル: 期間
    • cf.タイムスタンプ: 時点
  • 終了年度はNULLでもよいが、SQLの使い勝手を考慮すると「入力上の最大値」のほうが便利
  • 利点
    • レコード数少ない
  • 欠点
    • タイムスタンプ方式に比べるとSQLが複雑
      • 言うほどでもない

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

  • 代理キー/自然キー論争
  • 「可能な限り自然キーを使う」に尽きる
  • が、現場では代理キーを使わざるを得ないこともある
  • オートナンバリング
    • 代理キーの一実装
    • 数値を自動的に割り振る
    • 要件
      • 一意性(主キーとして必須)
      • 連続性(オプショナル)
  • どうやって数値を払い出す
    • データベース機能
      • シーケンスオブジェクト
        • 標準SQL
        • CREATE SEQUENCEで作りSELECTでアクセス
        • 細やかな制御ができる
      • ID列
        • オプション少ない
        • 移植性低い
    • アプリケーション側で実装
  • 重大な欠点

列持ちテーブル

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

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

  • 利点
    • シンプル
      • 正規化とか知らない人でも理解できる
    • 入出力のフォーマットと合わせやすい
  • 欠点
    • 列の増減が難しい
      • テーブルは気軽に変更できる代物ではない
        • DOAなのでアプリケーションに撥ねる
    • NULL
      • 3値論理に足を踏み入れるはめになる
      • NULL撲滅委員会

行持ちテーブル

  • 基本は行持ちせよ
  • 列持ち/行持ちは相互変換できる
    • 列持ち->行持ち: 各列SELECTしてUNION
    • 行持ち->列持ち: CASE式とGROUP BYと集約関数

アドホックな集計キー

県コード(PK) 県名 人口
01 北海道 550
02 青森 130
03 岩手 133
04 静岡 370
05 愛知 740
  • 地方ごとに集計したい
    • 東北地方、とか
  • 「地方」のキーがない
  • アドホック(場当たり的)な集計キーを追加してしまう
県コード(PK) 県名 人口 地方コード
01 北海道 550 01
02 青森 130 01
03 岩手 133 01
04 静岡 370 02
05 愛知 740 02
  • 問題点
    • 場当たり的に次から次へと追加される
      • 巨大なテーブルがさらに巨大化しパフォーマンスが劣化する
  • 解決策1
    • キーを別テーブルに分ける
    • JOINが必要なのでパフォーマンス問題の解決には寄与しない
県コード(PK) 地方コード
01 01
02 01
03 01
04 02
05 02
  • 解決策2
    • ビューを用意して地方コード追加する
  • 解決策3
    • CASE式でad-hocにキーを読み替える

多段ビュー

  • 「クエリの缶詰」by C.デイト
    • 保存が効く
    • 新鮮なデータを取り出せる
  • 短所もある

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

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

多段ビューの危険性

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

  • エンジニアは決して物理層から自由になれない
    • 本当に基底テーブルを意識しなくていいのはエンドユーザーだけ
  • 原則としてビューは1段にとどめておくように
  • 多段ビューのデメリット
    • 芋づる式に多数の基底テーブルにアクセスしパフォーマンス低下
    • テーブルとビューの依存関係がわかりにくくなる
      • 仕様の複雑化
      • 設計者でさえ理解できなくなり、同じような機能のビューが乱立したり

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

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

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

  • DB設計はきれいなデータに対して行う
    • 論理設計には最低限「主キーがある」等の前提がある
  • データが「汚い」
    • 新たに構築されるシステムにとって使えなかったり非効率なフォーマットであったりする
  • データが汚くて掃除が必要な例
    • もともとシステム化されていなかった業務を初めてシステム化
    • システム化されてはいたがRDB以外のフォーマットのデータベースが利用されていた

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

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

  • 一意キーの特定
    • 宿泊者の名前しか記録していない、とか
      • 名前は一意キーにならない

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

  • 名寄せ
    • 表記ゆれを解消して名称を統一する
      • 個人名
      • 法人名
      • 住所
      • 電話番号
      • ...
      • 「フリーハンド」を許していた時代の負の遺産
    • 「同一だ」とどうやって判断するの
      • 多要素を照合
        • 名前に加えて住所・年齢も照合するなど
      • 出現頻度を鑑みる
        • 会社名はふつう他社に似せない

まとめ

  • グレーノウハウは用法次第では良薬になる
  • 利点と欠点のトレードオフを検討せよ
    • 何も考えずに軽い気持ちで使うのは駄目