DatabaseDesignLecture.md

データベース設計徹底指南!!

Oracle 奥野 幹也さん

正しいDB設計

• 正しいデータ型を使う
  • カラム
    • 比較ができない。インデックスが効かない
  • 配列
    • 配列を他のデータ型でシミュレートしようとしたり…とかしないでしょ？
  • テーブル
    • 演算の単位。設計が重要
    • 正しく定義しないと、まともなクエリは書けない！
  • データ型が正しくないと
    • 遅い
    • クエリが長い
    • 複雑・難解
    • 間違う
• DB設計の欠陥は、技術的負債になる
  • データベースはリファクタリングが難しいから
  • 放置された負債が、更に足かせに!!
  • 原因
    • データモデルに対する無理解
    • リレーショナルモデルを知らなくてもRDBMSは便利
      • 知らないままでも便利
  • 蓄積すると
    • 雪だるま式に増える！
• RDBMSを使いこなすには、データモデルは超重要！
  • リレーショナルモデル

リレーショナルモデル

• テーブル間のリレーションシップ ≠ リレーション
• テーブル ≒ リレーション
• 集合
  • 順序がない
• リレーション
  • n次元の構造
  • データ型＝ドメイン
    • 属性が取りうる値の有限集合
  • 演算
    • 演算の入力も、結果もリレーションになる
      • 結果に対して演算を行い…という数珠つなぎを可能に
    • 制限(RESTRICT)
    • 射影(PROJECT)
    • 属性名変更(RENAME)
    • 拡張(EXTEND)
    • 和(UNION)
    • 積(INTERSECT)
    • 差(DIFFERENCE)
    • 直積(PRODUCT)
    • 結合(JOIN)
  • Outer joinはプリミティブな演算ではない。
    • UNIONに相当する概念
  • SQLとの関係？
    • SELECT 射影
    • FROM 直積
    • WHERE 制限
    • FROM → WHERE → SELECTの順になる
• リレーションの正規化
  • なぜ必要？
    • データの__論理的整合性__を保つため！
    • __矛盾__を防ぐ
• 正規化理論
  • リレーションから__重複__を排除するためのデータベース設計理論
    • 1NF〜6NF
  • 関数従属性(Functional Dependency)
  • 結合従属性(Join Dependency)
  • 第一正規形
    • 特徴
      • テーブルがリレーションであること
      • 行が順序づけられていない
      • 列が順序づけられていない
      • 重複する行がない
      • 属性値はドメインに属する要素の値をちょうど一つだけ含む
      • 全ての列の値は定義されたものだけで、かつそれぞれの行において常に存在する(=NULLを含まない)
    • 繰り返しグループがない
    • アトミックな属性（属性の内部に繰り返しグループがある）
      • データ型＝ドメインに含まれる集合の要素であればアトミック
      • 主キーの__一部__に意味を持たせるような設計もNG
  • 第二正規形
    • 候補キーの真部分集合から非キー属性への関数従属性を取り除いた状態。
  • 正規化するとテーブルが増えて問題？
    • 誤ったDB設計になるほうがはるかに問題！
  • 第三正規形
    • 非キー属性同士の関数従属性を取り除いた状態
  • ボイス・コッド正規形(BCNF)
    • 全ての自明でない関数従属性が取り除かれた状態。
  • 4NF/5NFのヒント
    • 非キー属性があるBCNFに自明でない結合従属性はない
    • __非キー属性があるBCNF__は自動的に5NFになる！
  • 自明でない結像従属性の本質
    • 見出し全体が候補キーであるようなリレーション
  • 第四正規形
    • 共通の属性を含む２つのリレーションに無損失分解可能な結合従属性を解消した状態
    • ☆{AB, AC}
  • 第五正規形
    • 全ての自明でない関数従属性が取り除かれた状態。
    • 3つ以上のリレーションに無損失分解可能。
    • コネクショントラップ
  • 第六正規形
    • 自明なものを含めて全ての関数従属性を取り除いた状態。
    • 非キー属性は最大でひとつ。
    • あまり実用的意味はない。
  • リレーションの直交性
    • 直交したリレーションとは、互いに重複したタプルを含まないものを指す。
    • 確認するには、6NFまで分解して重複の有無を調べる
    • 直交していないリレーションは統合する

リレーショナルモデルの限界

リレーショナルモデルでは扱いが難しい領域がある。

グラフの探索

• 構造を表現することは簡単だが、簡単にクエリできない！

ツリー

• 特殊なグラフ
• 代表的なモデル
  • 隣接リストモデル
  • パス列挙モデル
  • 入れ子集合モデル
  • クロージャテーブルモデル
• 決定版はない。要件次第で使い分ける

履歴データ

リレーショナルモデルで扱うのは難しい。

• 履歴データは時間軸と直交していない

• 行の意味が異なる。特定の行だけ特別な意味を持ってしまっている

• 対策

  • テーブルを分割する
    • 意味の異なるリレーションに分けてしまう。
    • 外部キー制約が使えない
    • 同じレコードが含まれてはいけないという制約をつけるのが難しい
  • 重複したデータを持つ
    • 現在のみのデータと、過去＋現在のデータをもつテーブルを作る
    • 外部キーが使える
    • 重複したデータがある
  • 擬似(pseudo)キーの利用
    • 擬似キーは本質的に不要

• 有効フラグを立てるような設計はNG!

まとめ

• リレーショナルモデルを理解する
• リレーションを正規化する
• リレーショナルモデルの限界を知る

Q&A

入れ子セット

ドメインについて

• そんなにこだわらなくていい

FROM＝直積？

• FROMに複数テーブルを書くような。

NULLを使わずにテーブルを分けるべき？

• 許容出来る時もある

あえて非正規化する？

• 結合したデータをキャッシュしておく、みたいなのはありかと。

外部キーを使うとパフォーマンスが…

• 外部キーなしではどうせアプリで面倒見なきゃいけないから、それくらいなら外部キー使った方が速いよ

DWH系のはリレーショナルモデルから外れる？

• うん

ブログにあった「開発者がモデリングすべきだ」の実例とかある？

• まわりのSIerの例とか見て、そういう考えを浸透する必要があると思ったから

RDBで表現しにくいデータ・モデル

• ランキングとか、全文検索とか。他の手段を併用するのもあり。

履歴のデータを開始日・終了日みたいに持つことについて

• 負荷もかかり、更新しにくいので、おすすめしない。

カラムの値によって条件判定したり、とか

• テーブルを分割するのがおすすめ

データが増えすぎるようなテーブルは正規化せずデータベースを分割していたけど、どうしたらいい？

• 頭の痛い問題。どこで分けるか、試行錯誤が必要。

公開フラグみたいなのは、公開・非公開でテーブルを分けるべき？

• リレーショナルモデル的にはそれが正解。

履歴管理のは、クエリ毎に結果が変わってしまうのが問題なの？

• うん。リレーショナルモデルにそぐわない。

刃牙の例で年齢があったけど、あれは日時によってクエリが変わるよね？

• まずいです。ただ、原作の設定上生年月日がなかったのでｗ