達人に学ぶDB設計 徹底指南書 ch8 論理設計のグレーノウハウ
違法すれすれの「ライン上」に位置する設計
- グレーノウハウ
- 著者の造語
- バッドノウハウとはっきり断定することはできない
- 無神経に使うと開発や運用に支障をきたす
- 「副作用の強い薬」
- 正確な知識をもって、利点と欠点とを理解して正しい用法で
代理キー~主キーが役に立たないとき
主キーが決められない、または主キーとして不十分なケース
- パターン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が複雑
オートナンバリングの是非
- 代理キー/自然キー論争
- 「可能な限り自然キーを使う」に尽きる
- が、現場では代理キーを使わざるを得ないこともある
- オートナンバリング
- 代理キーの一実装
- 数値を自動的に割り振る
- 要件
- 一意性(主キーとして必須)
- 連続性(オプショナル)
- どうやって数値を払い出す
- 重大な欠点
列持ちテーブル
配列型は使えない、でも配列を表現したい
- 配列型は使うな(バッドノウハウ)
- 列持ちはある種「素直」
列持ちテーブルの利点と欠点
- 利点
- シンプル
- 正規化とか知らない人でも理解できる
- 入出力のフォーマットと合わせやすい
- 【補】インピーダンスミスマッチがない的な
- シンプル
- 欠点
- 列の増減が難しい
- テーブルは気軽に変更できる代物ではない
- 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が発行される感じ
- ビューへのSELECT文
- 基底テーブルへのSELECT文
- 実際にはDBMSによってマージされて効率の良い形で実行されたりする
- が、1. だけよりは1. + 2.によって生まれるSELECT文のほうが高コストなはず
多段ビューの危険性
ビューの背後にあるテーブルの存在を、常に意識せよ
- エンジニアは決して物理層から自由になれない
- 本当に基底テーブルを意識しなくていいのはエンドユーザーだけ
- 原則としてビューは1段にとどめておくように
- 多段ビューのデメリット
- 芋づる式に多数の基底テーブルにアクセスしパフォーマンス低下
- テーブルとビューの依存関係がわかりにくくなる
- 仕様の複雑化
- 設計者でさえ理解できなくなり、同じような機能のビューが乱立したり
データクレンジングの重要性
- 正規化の前処理
- 疎かにするとバッドノウハウ/グレーノウハウが生まれる原因となる
データクレンジングは設計に先立って行う
- DB設計はきれいなデータに対して行う
- 論理設計には最低限「主キーがある」等の前提がある
- データが「汚い」
- 新たに構築されるシステムにとって使えなかったり非効率なフォーマットであったりする
- データが汚くて掃除が必要な例
- もともとシステム化されていなかった業務を初めてシステム化
- システム化されてはいたがRDB以外のフォーマットのデータベースが利用されていた
代表的なデータクレンジングの内容
一意キーの存在しないデータは、バッドノウハウ「不適切なキー」をも生み出す
- 一意キーの特定
- 宿泊者の名前しか記録していない、とか
- 名前は一意キーにならない
- 宿泊者の名前しか記録していない、とか
- 名寄せ
- 表記ゆれを解消して名称を統一する
- 個人名
- 法人名
- 住所
- 電話番号
- ...
- 「フリーハンド」を許していた時代の負の遺産
- 「同一だ」とどうやって判断するの
- 多要素を照合
- 名前に加えて住所・年齢も照合するなど
- 出現頻度を鑑みる
- 会社名はふつう他社に似せない
- 多要素を照合
- 表記ゆれを解消して名称を統一する
まとめ
- グレーノウハウは用法次第では良薬になる
- 利点と欠点のトレードオフを検討せよ
- 何も考えずに軽い気持ちで使うのは駄目