論理設計のバッドノウハウ
DBをマスターしたい
前回に引き続き、DBの基本知識を学習している
今回は論理設計のバッドノウハウについてまとめる
真似してはいけない設計のパターンをIT業界では、
「アンチパターン」や「バッドノウハウ」と呼ぶ
参考文献
目次
- 非スカラ値(第1正規形未満)
- ダブルミーニング
- 単一参照テーブル
- テーブル分割
- 不適切なキー
- ダブルマスタ
- バッドノウハウのどこが悪い?
はじめに
世の中にはダメな設計が溢れかえっている
そんな設計が生まれる理由は「何も考えていない」ことによるものである
これを学び、ポイントを押さえた論理設計ができるようにしたい
1.非スカラ値(第1正規形未満)
社員ID | 社員名 | 子 |
---|---|---|
000A | 加藤 | 達夫 信二 |
000B | 藤本 | |
001F | 三島 | 敦 飛鳥 洋子 |
これは正規化の際に複数のデータを入れないとしました。
しかし、RDBの標準機能に「配列型」というものがあります
理由はJavaなどのプログラミング言語では配列型を扱うため、
リレーショナルデータベースに配列型が実装されないと
データの受け渡しで齟齬が生まれるためです。
- 「配列型」は機能としてサポートされているが現在も普及には至っていません
- 結論、第一正規形を優先する
- 情報は可能な限り分割する→ただし、意味が壊れるほどの分割はしない
2.ダブルミーニング
1つの列に複数の意味が含まれている列が発生する状態
年度 | 学生名 | 列1 | 列2 |
---|---|---|---|
2001 | 石田純 | 170 | 62 |
2001 | 綾瀬はるみ | 155 | 58 |
2001 | 北島六郎 | 165 | 60 |
2001 | 山下智樹 | 175 | 64 |
2002 | 小泉淳二 | 170 | 18 |
2002 | 相川亮 | 180 | 17 |
2002 | 山田孝 | 185 | 18 |
2002 | 香川慎吾 | 172 | 19 |
5行目から「列2」の中身が変わっている
上記の例で言うと、体重から年齢?に変わっている
テーブルの列は変数ではないので一度決めたら変更不可
3.単一参照テーブル
単一参照テーブルの具体例
似た組み合わせのテーブルを1つのテーブルにまとめたもの
「ID」+『名称」のようなテーブルを1つにしてしまうイメージ
コードタイプ | コード値 | コード内容 |
---|---|---|
comp_cd | C0001 | A商事 |
comp_cd | C0002 | B化学 |
comp_cd | C0003 | C建設 |
・・・ | ||
comp_cd | C9999 | Z工業 |
pref_cd | 01 | 北海道 |
pref_cd | 02 | 青森 |
・・・ | ||
pref_cd | 47 | 沖縄 |
sex_cd | 0 | 不明 |
sex_cd | 1 | 男 |
sex_cd | 2 | 女 |
上記は、それぞれのマスタテーブルを1つにまとめたようなもの
「会社コード」、「県コード」、「性別コード」が1つのテーブルになっている
単一参照テーブルの功罪
ダブルミーニングを一般化したような手法
自然と多くの開発現場で使われてしまっている
オブジェクト指向言語における「ポリモルフィズム」に似ている
※ポリモルフィズム:多様性、オブジェクトが複数の型に属するという概念
<利点>
・マスタテーブルの数が減るため、ER図やスキーマがシンプルになる
・コード検索のSQLを共通化できる
<欠点>
・「コードタイプ」「コード値」「コード内容」の各列とも、必要な列長が異なる
→大きめの可変長文字列型で宣言が必要=データ量が無駄に増える
・1つのテーブルにレコードが集約する
→場合によっては、レコードが多くなり検索のパフォーマンスが悪化する
・コード検索のSQL内でコードタイプやコード値を間違えてもエラーにならない
→バグに気づきにくい
・ER図がスッキリするが、ERモデルとしては正確性を欠く
→ER図の可読性を下げる
4.テーブル分割
テーブル分割の種類
- 水平分割
レコード単位にテーブルを分割する方法 - 垂直分割
列単位にテーブルを分割する方法
年度 | 会社コード | 売上(億円) |
---|---|---|
2001 | C01 | 50 |
2001 | C02 | 52 |
2001 | C03 | 45 |
2001 | C04 | 55 |
2002 | C01 | 60 |
2002 | C02 | 40 |
2002 | C03 | 48 |
2002 | C04 | 54 |
2003 | C01 | 47 |
2003 | C02 | 41 |
2003 | C03 | 44 |
2003 | C04 | 58 |
上記のサンプルも実際には何百万〜何十億のレコードになることもある
その結果、テーブルにアクセルするSQL文のパフォーマンスが悪化することもある
→最大の原因はストレージに対するI/Oコストの増大
アクセスするデータ量を減らすことがパフォーマンス改善の手段となる
水平分割
先ほどのサンプルを年度ごとに水平分割することでテーブルサイズを小さくする
年度 | 会社コード | 売上(億円) |
---|---|---|
2001 | C01 | 50 |
2001 | C02 | 52 |
2001 | C03 | 45 |
2001 | C04 | 55 |
年度 | 会社コード | 売上(億円) |
---|---|---|
2002 | C01 | 60 |
2002 | C02 | 40 |
2002 | C03 | 48 |
2002 | C04 | 54 |
年度 | 会社コード | 売上(億円) |
---|---|---|
2003 | C01 | 47 |
2003 | C02 | 41 |
2003 | C03 | 44 |
2003 | C04 | 58 |
わかりやすい解決策だが、重大な欠点があるためRDBでは原則禁止されている
<欠点>
- 分割する意味的な理由がない
→パフォーマンスによる物理レベルの要請によるものなので、要請がなければ実施は不要 - 拡張性に乏しい
→この場合、全年度のデータを総なめで検索しない前提で成立する
また、テーブルが次々に増えてゆく
データの形がプログラムを決めるというDOAの原則に反する - 他の代替手段がある
→DBMSに「パーティション」という機能が存在する
パーティションキーを軸として格納領域を分離することができる
+α)パーティションとインデックスの使い分け
一般的には下記のような場合の列にパーティションを利用する
・カーディナリティが少ない(十〜数十)
・値の変更が起きない
垂直分割
会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
---|---|---|---|---|
C0001 | 000A | 武田 | 40 | D01 |
C0001 | 000B | 藤本 | 31 | D02 |
C0001 | 001F | 山下 | 25 | D03 |
C0002 | 000A | 斎藤 | 48 | D03 |
C0002 | 009F | 渋谷 | 50 | D01 |
C0002 | 010A | 太田 | 21 | D04 |
上記のテーブルに対する検索のSQL文に遅延が発生して改善が必要だとする
かつ、検索の利用例が常に「会社コード」「社員ID」および「年齢」だとする
これらの場合は下記のように分割することでSQLのアクセスするデータ量を減らせる
会社コード | 社員ID | 年齢 |
---|---|---|
C0001 | 000A | 40 |
C0001 | 000B | 31 |
C0001 | 001F | 25 |
C0002 | 000A | 48 |
C0002 | 009F | 50 |
C0002 | 010A | 21 |
会社コード | 社員ID | 社員名 | 部署コード |
---|---|---|---|
C0001 | 000A | 武田 | D01 |
C0001 | 000B | 藤本 | D02 |
C0001 | 001F | 山下 | D03 |
C0002 | 000A | 斎藤 | D03 |
C0002 | 009F | 渋谷 | D01 |
C0002 | 010A | 太田 | D04 |
しかし、これも分割する論理的な意味がないので原則利用するべきではない
また、垂直分割は「集約」で代替可能である
集約
集約はテーブル分割の代替案として位置付けられる方法である
種類は2種類挙げられる
どちらもオリジナルデータは残した対策となる
オリジナルデータ↓
会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
---|---|---|---|---|
C0001 | 000A | 武田 | 40 | D01 |
C0001 | 000B | 藤本 | 31 | D02 |
C0001 | 001F | 山下 | 25 | D03 |
C0002 | 000A | 斎藤 | 48 | D03 |
C0002 | 009F | 渋谷 | 50 | D01 |
C0002 | 010A | 太田 | 21 | D04 |
- 列の絞り込み
・元のテーブルから必要な列だけを持った新規テーブルを追加作成する
・上記のような小規模なテーブルをデータマートと呼ぶ(省略してマート)
・注意点はデータ同期が必要(適切な更新頻度が必要となる)
データマート↓
会社コード | 社員ID | 年齢 |
---|---|---|
C0001 | 000A | 40 |
C0001 | 000B | 31 |
C0001 | 001F | 25 |
C0002 | 000A | 48 |
C0002 | 009F | 50 |
C0002 | 010A | 21 |
- サマリテーブル
・集約関数によってレコードを集約した状態で保持したテーブル
・データマートと同じく、更新のタイムラグによるデータの不整合が問題となる
SELECT 会社コード, AVG(年齢) AS 平均年齢
FROM 社員
GROUP BY 会社コード
サマリテーブル↓
会社コード | 平均年齢 |
---|---|
C0001 | 41 |
C0002 | 35 |
5.不適切なキー
可変長文字列はキーに使用しない
キーとは下記の2つ
・主キー、外部キーなどデータベースの機能で設定されるもの
・テーブルの結合条件で使用される列(結合キー)
使用してはいけない理由
・不変性がないこと
・固定長文字列と混同すること
キーは永遠に不変
可変長文字列に使用するデータは名前などに採用されるのは自然
将来的に変化する可能性もある=コロコロ変わるのはキーに不向き
同じデータを意味するキーは同じデータ型にすべし
固定長文字列と可変長文字列は同じ文字列を保持しても、物理的には同じにならない
・固定長文字列:'テスト ' ←スペースが入っている
・可変長文字列:'テスト'
6.ダブルマスタ
顧客マスタA 顧客マスタB
顧客コード | 顧客名 |
---|---|
C001 | 山田 太郎 |
C002 | 中島 健二 |
C003 | 新谷 沙耶 |
C004 | 古谷 瑛二 |
顧客コード | 顧客名 |
---|---|
C001 | 山田 太郎 |
C002 | 中島 健二 |
C003 | 新谷 沙耶 |
K001 | 澤田 亜美 |
上記のようにマスタテーブルが複数あると、テーブルの結合が必要になる
→SQLで言うと完全外部結合or UNIONで実現できるがコストの高い処理
=パフォーマンスが悪くなる
結合の具体例
SELECT COALESCE(A.顧客コード, B.顧客コード),
COALESCE(A.顧客名, B.顧客名)
FROM 顧客マスタA A FULL OUTER JOIN 顧客マスタB B
ON A.顧客コード = B.顧客コード;
完全外部結合は両方のテーブルのレコードを保存する
COALESCE関数は引数から左をスキャンして、最初に見つけたNULLでない値を返す
SELECT A.顧客コード,
A.顧客名
FROM 顧客マスタ
UNION
SELECT B.顧客コード,
B.顧客名
FROM 顧客マスタB B;
どちらも結果は以下になります
顧客コード 顧客名
-------- ---------
C001 山田 太郎
C002 中島 健二
C003 新谷 沙耶
C004 古谷 瑛二
K001 澤田 亜美
ダブルマスタはシステムの統廃合によって生じる
7.バッドノウハウのどこが悪い?
①可読性
バッドノウハウを採用した場合、開発および運用コストは何十倍にも高くなる
その最大の理由は、設計に対する理解を妨げるから
例えば、ダブルミーニングや単一参照テーブルは最初に設計した人しかわからない
→人間にとって「わかりにくい」システムは理解やコミュニケーションを阻害する
=バグを生み出し開発効率を落とす温床となる
②設計変更の難しさ
RDBを開発の後工程の段階になってから修正することは難しい
テーブル構成にあとから手を加えると、アプリケーションにも修正が必ずいく
原則として「ERモデルの手戻り」はかなり代償が高くつく
もし、ERモデルを修正するとアプリも作り直してを繰り返す(ITでは「デスマーチ」)
③データ構造がコードを決めるのであって、その逆ではない
データ構造がダメな状態で、プログラミングによって挽回することはできない