達人に学ぶDB設計 徹底指南書 ch7 論理設計のバッドノウハウ

RDB勉強メモ閃乱カグラ

出典: 


論理設計の「やってはいけない」

  • 戦略の失敗を戦術で取り返すことはできない
  • バッドノウハウ/アンチパターン根絶委員会

非スカラ値(第1正規形未満)

  • 最低限のルールすら守られていない

配列型による非スカラ値

配列型は利用しない。第1正規形を守ろう

  • RDBの掟破りともいうべき機能
  • アプリケーションとのインピーダンスミスマッチを解消できるのが嬉しい
  • が、あまり普及しなかった

    • 実用化のハードル高い

      • DBMSだけ対応しても駄目
      • ミドルウェアやアプリケーションも対応しなければ意味がない
    • 採用には細心の注意が必要

スカラ値の基準は何か?

情報は可能な限り分割して保存するのが良い。ただし意味を壊してはいけない

  • 著者見解

    • 意味的に分割できる限り、なるべく分割して保存する

        • 名字と名前
        • メールアドレスの@の前後
        • 【補】数値と単位

          • 【補】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図やスキーマがシンプルになる
    • コード検索のSQLを共通化できる
  • 欠点

    • 列長は余裕をみてかなり大きめの可変長文字列にせざるをえない

      • すべてが依存するテーブルとなるため、後から拡張が許されない
    • レコード数が多くなり検索のパフォーマンスが悪化
    • バグに気づきにくい

      • コードタイプやコード値を間違えて指定してもエラーにならない
      • 【補】静的に型がないプログラミング言語みたいな気持ち
    • 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とかが必要になる

ダブルマスタはなぜ生じるのか

  • システム統廃合でデータクレンジングをサボると生じる