達人に学ぶDB設計 徹底指南書 ch7 論理設計のバッドノウハウ
論理設計の「やってはいけない」
非スカラ値(第1正規形未満)
- 最低限のルールすら守られていない
配列型による非スカラ値
配列型は利用しない。第1正規形を守ろう
スカラ値の基準は何か?
情報は可能な限り分割して保存するのが良い。ただし意味を壊してはいけない
- 著者見解
- 意味的に分割できる限り、なるべく分割して保存する
- 例
- 名字と名前
- メールアドレスの
@
の前後 - 【補】数値と単位
- 【補】Embedded Value Pattern
- 分割された状態のものを結合するのは楽
- 結合された状態のものを分割するのは大変
- 例
- 意味を壊してはいけない
A商事
をA
と商事
にするとか
- 意味的に分割できる限り、なるべく分割して保存する
ダブルミーニング
この列の意味はなんでしょう?
年度(PK) | 学生名(PK) | 列1 |
---|---|---|
2001 | 両備 | O |
2001 | 両奈 | O |
2002 | 雪泉 | G |
2002 | 叢 | H |
2002 | 夜桜 | H |
列1
の意味が途中から変わっている- ~2001: 血液型
- 2002~: カップ
- 列の意味が不明確になることでシステムのバグの原因になる
テーブルの列は「変数」ではない
列は変数ではない。一度意味を決めたら変更不可
年度(PK) | 学生名(PK) | 血液型 | カップ |
---|---|---|---|
2001 | 両備 | O | |
2001 | 両奈 | O | |
2002 | 雪泉 | G | |
2002 | 叢 | H | |
2002 | 夜桜 | H |
- 「2002年からカップを格納したい」と思ったエンジニアは
カップ
列を追加すべきだった - 列名を見ればどんなデータが入っているのか一目瞭然
単一参照テーブル
多すぎるテーブルをまとめたい?
- 学校
学校コード(PK) | 学校名 |
---|---|
S001 | 半蔵 |
S002 | 蛇女 |
S003 | 月閃 |
... |
- 忍ランク
ランクコード(PK) | ランク名 |
---|---|
R001 | 下忍 |
R002 | 中忍 |
R003 | 上忍 |
... |
- 「『識別ID』+『名称』で同じ形やんけ!まとめたろ!」
- 単一参照テーブル
コードタイプ(PK) | コード値(PK) | コード内容 |
---|---|---|
sch_cd | S001 | 半蔵 |
sch_cd | S002 | 蛇女 |
sch_cd | S003 | 月閃 |
... | ||
rnk_cd | R001 | 下忍 |
rnk_cd | R002 | 中忍 |
rnk_cd | R003 | 上忍 |
... |
単一参照テーブルの功罪
テーブルにポリモーフィズムは要らない
- ダブルミーニング: 列の意味が七変化
- 単一参照テーブル: テーブルの意味が七変化
- 利点
- 欠点
- 列長は余裕をみてかなり大きめの可変長文字列にせざるをえない
- すべてが依存するテーブルとなるため、後から拡張が許されない
- レコード数が多くなり検索のパフォーマンスが悪化
- バグに気づきにくい
- コードタイプやコード値を間違えて指定してもエラーにならない
- 【補】静的に型がないプログラミング言語みたいな気持ち
- ER図の正確さを欠く
- 列長は余裕をみてかなり大きめの可変長文字列にせざるをえない
- 欠点が多いからやめよう
テーブル分割
水平分割
売上
年度(PK) | 会社コード(PK) | 売上 |
---|---|---|
2001 | C0001 | 50 |
2001 | C0002 | 52 |
2001 | C0003 | 55 |
2001 | C0004 | 46 |
2002 | C0001 | 52 |
2002 | C0002 | 55 |
2002 | C0003 | 60 |
2002 | C0004 | 47 |
2003 | C0001 | 46 |
2003 | C0002 | 52 |
2003 | C0003 | 44 |
2003 | C0004 | 60 |
- 行を小分けにする
売上(2001)
年度(PK) | 会社コード(PK) | 売上 |
---|---|---|
2001 | C0001 | 50 |
2001 | C0002 | 52 |
2001 | C0003 | 55 |
2001 | C0004 | 46 |
売上(2002)
年度(PK) | 会社コード(PK) | 売上 |
---|---|---|
2002 | C0001 | 52 |
2002 | C0002 | 55 |
2002 | C0003 | 60 |
2002 | C0004 | 47 |
売上(2003)
年度(PK) | 会社コード(PK) | 売上 |
---|---|---|
2003 | C0001 | 46 |
2003 | C0002 | 52 |
2003 | C0003 | 44 |
2003 | C0004 | 60 |
- 利点
- I/Oコストの削減
- 欠点
- 分割する意味的な理由がない
- 純粋にパフォーマンス目的
- 拡張性に乏しい
- テーブル増えてく
- 経年変化を分析したいとき、パフォーマンス目的で分割した意味がなくなる
- 他の代替手段がある
- パーティション機能
- cf.カーディナリティが高い場合はインデックス
- パーティション機能
- 分割する意味的な理由がない
- 上記の重大な欠点があるため原則禁止
垂直分割
- 列を小分けにする
- 分割することが論理的な意味をもたないため原則禁止
- 「集約」を使え
集約
- 分割の代替案
学生テーブル
学校コード(PK) | 学生コード(PK) | name | grade | birthday | age | blood_type | height | boob | waist | hip | cup |
---|---|---|---|---|---|---|---|---|---|---|---|
S003 | 000A | 雪泉 | 3 | 2000-12-31 | 17 | A | 167 | 92 | 56 | 84 | G |
S003 | 000B | 叢 | 3 | 2000-10-08 | 17 | B | 172 | 96 | 58 | 85 | H |
S003 | 000C | 夜桜 | 2 | 2000-04-05 | 16 | O | 159 | 90 | 53 | 82 | H |
S003 | 000D | 四季 | 1 | 2000-03-25 | 15 | AB | 161 | 95 | 54 | 83 | I |
S003 | 000E | 美野里 | 1 | 2000-02-14 | 15 | AB | 144 | 86 | 50 | 75 | G |
... |
列の絞り込み
- データマート
- どうせおっぱいしか検索しないので小規模テーブルを新たに作成する
- I/Oコスト下げる
- オリジナルの
学生
テーブルは残すため、分割ではない
- どうせおっぱいしか検索しないので小規模テーブルを新たに作成する
学生(バストのみ)
学校コード(PK) | 学生コード(PK) | name | boob | cup |
---|---|---|---|---|
S003 | 000A | 雪泉 | 92 | G |
S003 | 000B | 叢 | 96 | H |
S003 | 000C | 夜桜 | 90 | H |
S003 | 000D | 四季 | 95 | I |
S003 | 000E | 美野里 | 86 | G |
... |
- デメリット
- データ同期問題
- 要件と照らし合わせて頻度を慎重に検討せよ
- データ精度的には高頻度が望ましいが、性能問題的には本末転倒になりうる
- データ同期問題
サマリテーブル
社員平均バスト
学校コード(PK) | 平均boob |
---|---|
S003 | 91.8 |
... |
- マートと同じデメリット
shardingとカラムベース
- 新しい物理的なアーキテクチャ
- sharding
- 水平分割的なやつ
- 論理的にも物理的にも分割
- シェアードナッシング前提
- cf.パーティション
- 論理的には単一のテーブル扱い
- カラムベースデータベース
- 「1つのSQL文で利用する列数は限られている」という洞察に基づく
- ローベース: 1列しか使わなくてもレコード丸ごと読まなくてはいけない
- カラムベース: 必要な列だけ読めるためI/Oコスト削減できる
不適切なキー
- キーに(可変長配列)VARCHAR使うな
- 不変性(Stability)を備えていない
- (固定長文字列)CHARと混同
キーは永遠に不変です!
可変長文字列は不変性がないためキーには不向き
- 可変長文字列は何に使われる?
- 何かの名前
- 「名前」は変動する可能性が高い
- 人名: 結婚で変わる
- 部署名: 組織変更で変わる
同じデータを意味するキーは同じデータ型にすべし
キーは固定長文字列の「コード」が望ましい
- さもないとパディング違いで一致しなくなっちゃったりする
ダブルマスタ
ダブルマスタはSQLを複雑にし、パフォーマンスを悪化させる
- UNIONとかFULL OUTER JOINとかが必要になる
ダブルマスタはなぜ生じるのか
- システム統廃合でデータクレンジングをサボると生じる