違法すれすれの『ライン上』に位置する設計
上手に使えば論理設計に効果があるが、間違った使い方をすると運用に支障をきたすといった、『バッドノウハウ』ではないが『グレー』な設計方法について学ぶ。メリデメを理解した上で利用することが大切。
代理キー：主キーが役に立たない時
主キーを決められない、または主キーとして不十分なケースに遭遇したらどうするか？具体的なケースとその対応策を次にまとめる。
ケース１：入力データに主キーになるような一意キーが存在しない
入力データが、テーブルのデータとして成り立っていないので、データベースに登録できる形にデータを『データクレンジング（データの掃除）』をして対応する。データグレンジングについては、下記で説明。
ケース２：一意キーはあるが、サイクリックに使いまわされる
例えば、次のような市町村ごとの人口データがあるとする。
| 市町村コード | 市町村名 | 人口（人） |
|---|---|---|
| A000 | A市 | 100 |
| A001 | B市 | 300 |
| A002 | C市 | 500 |
今、B市が廃止され、新しくD市となったが、業務上のルールとして市町村コードを再利用することとなっている。
| 市町村コード | 市町村名 | 人口（人） |
|---|---|---|
| A000 | A市 | 100 |
| A001 | D市 | 900 |
| A002 | C市 | 500 |
上記のように、市町村コード『A001』は、あるときからB市からD市のデータになり、過去のB市のデータを辿ることができなくなってしまう。
ケース３：一意キーはあるが、途中で指す対象が変化する
例えば、次のような市町村ごとの人口データがあるとする。
| 市町村コード | 市町村名 | 人口（人） |
|---|---|---|
| A000 | A市 | 100 |
| A001 | B市 | 300 |
| A002 | C市 | 500 |
今、B市とC市が合併し、新たな『B市』となることになったため、市町村コードも引き継いだ。
| 市町村コード | 市町村名 | 人口（人） |
|---|---|---|
| A000 | A市 | 100 |
| A001 | B市 | 800 |
上記のように、合併前のデータを参照することができなくなる。
代理キーによる解決
ケース2、3のような時には『代理キー：サロゲートキー』と呼ばれる、自然キー（入力データに最初から存在するキー）の代わりに新たに追加するキーで解決する。
具体的にケース2に対して実施してみる。
| 市町村管理コード | 市町村コード | 市町村名 | 人口（人） |
|---|---|---|---|
| 0 | A000 | A市 | 100 |
| 1 | A001 | B市 | 300 |
| 2 | A002 | C市 | 500 |
| 3 | A001 | D市 | 900 |
ケース3についても同様の対処ができる。ただ、代理キーは論理的には不要なキーのため、論理モデルをわかりにくくしてしまう。
自然キーによる解決
ケース2、3の問題の根本は履歴管理ができないという点であった。よって履歴管理ができるように時間を表す列を追加してしまえばよい。その方法として、タイムスタンプとインターバルがある。
具体的にケース2に対して実施してみる。
タイムスタンプ
タイムスタンプはあるタイミングにおけるスナップショットを取るようなもの。下の例では『年度』列がタイムスタンプに相当する。
年度 市町村コード 市町村名 人口（人） 2007 A000 A市 100 2007 A001 B市 300 2007 A002 C市 500 2008 A001 D市 900 インターバル
インターバルは有効な期間を表す方法。下の例では『開始年度』『終了年度』列がインターバルに相当する。
開始年度 市町村コード 市町村名 人口（人） 2007 9999 A000 A市 100 2001 2007 A001 B市 300 2007 9999 A002 C市 500 2008 9999 A001 D市 900 SQLの使い勝手的には、上の例のように終了年度については
NULLよりも、入力上許される最大値を利用するのがベター。
列持ちテーブル
『配列型は使えないが、配列を表現したい』というようなときに、下のような列持ちテーブルを使用した。
| 社員ID | 社員名 | 子1 | 子2 | 子3 |
|---|---|---|---|---|
| 000A | 加藤 | 達夫 | 信二 | |
| 000B | 藤本 | |||
| 001F | 三島 | 淳 | 陽子 | 清美 |
列テーブルのメリデメは次の通り。
- メリット
- シンプルな設計：テーブルが直感的にわかる
- 入出力のフォーマットと合わせやすい：アプリケーションとのインタフェース設計が簡単
- デメリット
- 列の増減が難しい：列を増やす際にアプリケーション側の変更が必要になる場合もある
- 無用の
NULL：上のテーブル例のようにNULLが増える
メリデメの通り、『列持ち』テーブルはメリデメが拮抗している。じゃあ、どうするかというと、基本的には『行持ち』テーブルを採用する。
具体的には次のようにする社員テーブルを用意する。
| 社員ID | 社員名 |
|---|---|
| 000A | 加藤 |
| 000B | 藤本 |
| 001F | 三島 |
そして、行持ちテーブルで子の関係を表現する。
| 社員ID | 枝番 | 子 |
|---|---|---|
| 000A | 1 | 達夫 |
| 000A | 2 | 信二 |
| 001F | 2 | 淳 |
| 001F | 2 | 陽子 |
| 001F | 3 | 清美 |
『行持ち』⇄『列持ち』の変換はSQLによって変換可能なため、パフォーマンス上問題になってきたらテーブル変換するという方針がベター。
アドホックな集計キー
例えば、次ようなデータがある。
| 市コード | 市名 | 人口(万人) |
|---|---|---|
| 01 | 名古屋 | 200 |
| 02 | 知多 | 14 |
| 03 | 刈谷 | 20 |
| 04 | 豊田 | 18 |
上のデータに対して、地方ごと（尾張、西三河、東三河）の人口を求めたいというときに、アドホック（場当たり的な）な集計キーで対応すると次のような感じになる。
| 市コード | 市名 | 人口(万人) | 地方コード |
|---|---|---|---|
| 01 | 名古屋 | 200 | 01 |
| 02 | 知多 | 14 | 01 |
| 03 | 刈谷 | 20 | 02 |
| 04 | 豊田 | 18 | 03 |
簡単に地方ごとの論理構成を追加できたが、何度もこれを繰り返すと、テーブルが巨大になりパフォーマンスを劣化させてしまう。よりベターな方法として次のものがある。
変換テーブルを作る
下のように新しく変換テーブルを作る。この方法だと元のテーブルには影響しないが、検索では結局結合するのでパフォーマンス問題には寄与しない。
市コード 地方コード 01 01 02 01 03 02 04 03 ビューを使う
地方コードを追加したビューを用意すればOK。実質的にはオリジナルのテーブルにアクセスしているのと変わらないため、パフォーマンスを劣化しない。
GROUP BY句の中でアドホックキーをつくるGROUP BYでアドホックキーを表現する。
多段ビュー
ビューはテーブルを新規に作るわけではないので、パフォーマンスの劣化に対して有効な手段となる。ただ、利用する際には以下２点に注意する。
ビューへのアクセスは２段階で行われる
ビューでデータを見る時には、ビューを作るためのSQLが実行され、そのビューを用いてテーブルにアクセスするSQLが実行されるというような２段階の実行がされている。実際の処理としては、RDBMS内部で、ビューの処理が決定され、その２段階が1つの処理として実行される場合もあるが、純粋なSQLよりも複雑な処理になる。
多段ビュー
『ビューの背後にあるテーブルの存在を、常に意識する』ということが大事。ビューを、あまりネストしてしまうとテーブルとビューの依存関係が分かりにくくなり、管理が難しくなる。『KISSの原則：Keep It Simple, Stupid.(単純にしておけ、このばか)』を心がける
データクレンジングの重要性
データクレンジングは、データベースの構築にあたり、それまでの業務で利用されていたデータをデータベースに登録できる状態にする作業のこと。そうすることで正規化のような方法を導入できるようになる。
データクレンジングは設計に先立って行う。順番的には次のように進める。
- オリジナルデータ（汚れた状態）の収集
- データフォーマットの調査
- データクレンジング
- オリジナルデータ（綺麗なデータ）の取得
- データベース設計（論理/物理）
- データベースへ登録
では、クレンジングでは具体的に何をするかというと次のもの。
一意キーの特定
レコードを一意に特定出来るユニークキーを見つける。あるキー
-
標準化の一種に数えられる作業で、表記揺れを解消して名称を統一する。（例えば、goruchanとゴルちゃん、ごるちゃんは同一人物なので、名称をgoruchanに統一するみたいなことをする）
