『達人に学ぶDB設計徹底指南書』読書メモ: 前半

近々DB設計をやるため、以前読んだ良書を読み返してみました。
主に未来の自分が「ああ、そうそう、だいたいこんな感じだった」と振り返るため、要点をメモにまとめました。

勿論、書籍そのままではなく、自分なりに構成や表現を変えた部分もあります。
元の構成、表現で見たい方はご購入下さい(オススメです)。
https://www.amazon.co.jp/dp/4798157821


<br>
## 対象読者
* RDB、正規化、ER図がだいたいどういうものか(完璧ではないにせよ)知っている方
* DB設計について、さくっと概観をおさらいしたい方

<br>
## 要点まとめ: 前半

前半ではDB設計の基礎、主にDB設計の流れや正規化について触れています。
以下、テキストオンリーです。

```
## はじめに
DB設計は、大きく次の2つに分かれる
  論理設計: ER図など、データのモデル設計
  物理設計: サーバやストレージなど、ハードウェアレベルの設計

両者は強いトレードオフ関係にある
データベース設計とは、限られた予算範囲の中で整合性とパフォーマンスの平衡点を見つけ出そうとする努力

この本で学ぶこと
  望ましい論理設計
  望ましい論理設計を達成するときに犠牲になるものは
  望ましい物理設計
  望ましい物理設計を諦めなければならない理由



## 1章: DB全般
RDBとは
  割愛

3層スキーマ
  外部スキーマ
    ユーザーに見せるデータ構造
    ビューの世界
  概念スキーマ
    テーブル定義、リレーション
    テーブルの世界
    「論理設計」に対応
  内部スキーマ
    テーブルやインデックスを実ファイル上にどう格納するか
    ファイルの世界
    「物理設計」に対応



## 2章: 論理設計と物理設計
論理設計→物理設計の順に行う

論理設計のステップ
  エンティティ(≒テーブル)の抽出
    どんなデータが必要か抽出する
    要件定義を含む活動
  
  エンティティの定義
    列とキーを定義する
  
  正規化
    データの整合性が保たれるよう、エンティティを一定の規則に従って分割する
    最重要ステップ
  
  ER図の作成
    エンティティ同士の関係を図にする

物理設計のステップ
  テーブル定義
    CREATE TABLE文を実行して、テーブルを作る (ぐらいのニュアンスと解釈)
    /* 疑問
       正規化〜ER図作成とテーブル定義の境目がピンとこなかった
       型を決めたり、実際にCREATE TABLE文を作るのが物理設計? のように思われたが、
       どの辺がどう「物理」なのかピンとこなかった。
       あるいは、CREATE TABLE文を作るを作るまでが論理で、流し込んで実ファイルができたら物理??
    */

  インデックス定義
    インデックスを設定し、パフォーマンスを改善する

  ハードウェアのサイジング
    DBサーバのストレージ、CPU、メモリ選定を指す
    キャパシティ設計、パフォーマンス設計に分かれる
      キャパシティ設計
        システムで利用するデータ量、サービス終了時のデータ増加率を見積もって、十分な容量のストレージを選定する
        正確な見積もりは難しいので、安全率を大きくとって、余裕を持たせる
        後で容量を増やせる、スケーラビリティの高い構成を組む
      パフォーマンス設計
        システムが十分な性能を発揮できるだけのメモリ、CPUを選定

  ストレージの冗長構成決定
    適切なRAIDを選ぶ
      予算が許すならRAID10、無理ならRAID5を推奨

  ファイルの物理配置決定
    データベースに格納されるファイルは、大別して以下の5つ
      データファイル(影響大)
        テーブルの実データ
      インデックスファイル(影響大)
        インデックス情報
      システムファイル
        DBMSの内部管理用のデータ
      一時ファイル(影響大)
        サブクエリ、GROUP BY、DISTINCTなどの処理に必要なデータ
        SQL処理が終わったら削除される
      ログファイル
        トランザクションログ
        COMMITされていないデータの更新履歴
        COMMITしてデータファイルに反映したら、用済み
    全て同じディスクに置くと、ディスクI/Oが被り、パフォーマンスが出ない
    予算が許すなら、5種類を別々のディスクに置くのがベスト
      disk1: データファイル
      disk2: インデックスファイル
      disk3: システムファイル
      disk4: 一時ファイル
      disk5: ログファイル
    妥協案として、影響大のファイルをそれぞれ分ける構成が考えられる
      disk1: データファイル
      disk2: インデックスファイル
      disk3: 一時ファイル
      disk4: システムファイル、ログファイル
    低予算だと、さらにディスクを絞らざるを得ないが、性能が悪くなっていく

バックアップ設計
  手段は、完全/差分/増分バックアップの3種類
  以下のトレードオフ関係がある
    完全バックアップ: バックアップコスト大、リカバリコスト小
    差分バックアップ: バックアップコスト中、リカバリコスト中
    増分バックアップ: バックアップコスト小、リカバリコスト大
  完全バックアップ + (差分バックアップ or 増分バックアップ)が一般的
  バックアップ方法が決まれば、リストア方法は自ずと決まる



## 3章: 論理設計と正規化
二次元表と"テーブル"は違う
  テーブルは、見た目上は「二次元表」と似ている
  しかし、以下の点で違う
    テーブル: すべての行が、同じ構造を持つ                <=> 表: 行によって構造を変えてもOK
    テーブル: セル1つに複数の値を入れてはならない          <=> 表: 複数入れてOK
    テーブル: 重複レコードが存在してはならない(=主キーがある) <=> 表: 重複OK
    テーブル: レコードは上下の順序を持たない            　 <=> 表: 上下の順序あり
    テーブル: 列は左右の順序を持たない                  <=> 表: 左右の順序あり
  つまり、テーブルとは共通の構造を持つ情報の「集合」
  テーブル名はすべて複数形または複数名詞で書ける

テーブルの構成要素
  行と列
  キー
    主キー
    外部キー
  制約
    NOT NULL制約
    一意制約
    CHECK制約

正規化(normalization)とは
  定義
    テーブル構造を正規形(normal form)にすること
      正規形とは、データの冗長性を排し、一貫性と効率性を保持するためのデータ形式
      第1正規形〜第5正規形まである
      通常は、第3正規形まで正規化できればOK
      (第3正規形まで分割した時点で、自然と第4, 第5正規形になっているケースが大半)
  
  正規化を理解するには「関数従属性」の理解が大事
    y=f(x) のように、入力xを決めたら出力yが一意に定まるような関係性を、関数従属性という
    例えば、X列の値を決めたら、Y列の値が一意に定まるとき
    {X} -> {Y} あるいは YはXに従属する と表現する
    例: {主キー列} -> {その他の列1}
        {主キー列1, 主キー列2} -> {その他の列1}
  
  正規化とは、テーブルのすべての列が関数従属性を満たすように整理していく作業
  直感的にいうと「異なる実体(エンティティ)を1つのテーブルに押し込めず、テーブルレベルで分割する」ということ

第1正規形
  第1正規形を満たす例
    [社員テーブル#1]
    会社ID(pk) 会社名    社員ID(pk) 社員名 年齢    部署ID    部署名
    -----------------------------------------------------------------
    C0001      A商事     10        山田   31     D01      開発
    C0001      A商事     20        田中   42     D02      人事
    C0001      A商事     30        中村   53     D03      営業
    C0002      B商事     10        村上   24     D03      営業
    C0002      B商事     11        上島   35     D04      総務
    C0002      B商事     12        島田   46     D01      開発
  
  定義
    1つのセルに1つの値しか含まれない形
  どうやれば正規化できる?
    1つの列に複数の値を放り込まないようにする
      ↑の例で部署名を"総務、経理"にするような運用はやめよう
      配列型やJSON型を使うのもアウト
  正規化しないとなにがまずいの?
    主キーを指定しても、各列の値を一意に決定できなくなる
    (=関数従属性がなくなる)

第2正規形
  第2正規形を満たす例
    [社員テーブル#2]
    会社ID(pk) 社員ID(pk) 社員名 年齢    部署ID    部署名
    ---------------------------------------------------------
    C0001      10        山田   31     D01      開発
    C0001      20        田中   42     D02      人事
    C0001      30        中村   53     D03      営業
    C0002      10        村上   24     D03      営業
    C0002      11        上島   35     D04      総務
    C0002      12        島田   46     D01      開発
    
    [会社テーブル]
    会社ID(pk) 会社名
    -----------------
    C0001      A商事
    C0001      A商事
    C0001      A商事
    C0002      B商事
    C0002      B商事
    C0002      B商事
  
  定義
    部分関数従属がない形(=完全関数従属だけの形)
      主キー(複合キーを想定)の一部の列に対して従属する列がある場合、両者の関係を部分関数従属という
      逆に、主キーを構成する全ての列に対して従属する列がある場合、両者の関係を完全関数従属という
      社員テーブル#1を例にすると、
        {会社ID} -> {会社名} は部分関数従属
        {会社ID, 社員ID} -> {社員名} は完全関数従属
  どうやれば正規化できる?
    部分関数従属を見つけて、解消する
    (=異なる実体(エンティティ)を見つけて、別テーブルに切り出す)
  正規化しないとなにがまずいの?
    社員テーブル#1を例にすると、以下の問題が起きる
      社員情報が不明の会社をシステムに登録できない
      会社IDと会社名の対応が行によってまちまちになる
  その他補足
    第1正規形->第2正規形への正規化は、可逆的な操作(=無損失分解)
    テーブルを結合すれば元に戻せる

第3正規形
  第3正規形を満たす例
    [社員テーブル#3]
    会社ID(pk) 社員ID(pk) 社員名 年齢    部署ID
    -------------------------------------------
    C0001      10        山田   31     D01
    C0001      20        田中   42     D02
    C0001      30        中村   53     D03
    C0002      10        村上   24     D03
    C0002      11        上島   35     D04
    C0002      12        島田   46     D01
    
    [会社テーブル]
    会社ID(pk) 会社名
    -----------------
    C0001      A商事
    C0001      A商事
    C0001      A商事
    C0002      B商事
    C0002      B商事
    C0002      B商事
    
    [部署テーブル]
    部署ID(pk) 部署名
    --------------------
    D01        開発
    D02        人事
    D03        営業
    D04        総務
    
  定義
    推移的関数従属がない形
      主キーじゃない列同士に関数従属性がある場合、両者の関係を推移的関数従属と呼ぶ
      社員テーブル#2を例にすると、{部署ID} -> {部署名}が推移的関数従属
  どうやれば正規化できる?
    推移的関数従属を見つけて、解消する
    (=異なる実体(エンティティ)を見つけて、別テーブルに切り出す)
  正規化しないとなにがまずいの?
    社員テーブル#2を例にすると、以下の問題が起きる
      所属部署が決まっていない社員をシステムに登録できない
      部署IDと部署名の対応が行によってまちまちになる
  その他補足
    第2正規形と第3正規形は、主キーが絡むかどうかの違いしかない
    どちらも無損失分解

ボイス-コッド正規形
  非キーからキーへの関数従属をなくした形を指す
  第3正規形をより厳密に定義した正規形で、非公式には第3.5正規形とも呼ばれる
  第3正規形を満たせば、大抵はボイス-コッド正規形も満たしている
  詳細は割愛

第4正規形
  第3正規形を満たすが、第4正規形を満たさない例
    [社員-部署-製品テーブル]
    社員ID(pk) 部署ID(pk) 製品ID(pk)
    --------------------------------
    001        D01       P0001
    001        D01       P0002
    002        D01       P0001
    002        D02       P0001
    003        D01       P0002
    003        D02       P0002
  
  第4正規形を満たす例
    [社員-部署テーブル]
    社員ID(pk) 部署ID(pk)
    ---------------------
    001        D01
    002        D01
    002        D02
    003        D01
    003        D02
    
    [社員-製品テーブル]
    社員ID(pk) 製品ID(pk)
    --------------------------------
    001        P0001
    001        P0002
    002        P0001
    003        P0002
  
  定義
    関連エンティティに含まれる関連を1つだけにした形
    (本文中には多値従属性が〜と書いているが、↑の一文で十分理解できるため割愛)
  どうやれば正規化できる?
    関連エンティティに含まれる関連を1つだけにする
    (A-B-Cテーブル => A-Bテーブル、A-Cテーブル とする)
  正規化しないとなにがまずいの?
    社員-部署-製品テーブルを例にすると、以下の問題が起きる
      社員001が部署を移動した場合、複数行を更新する必要がある(=冗長)
      社員、部署、製品すべてが決まらないとシステムに登録できない

第5正規形
  第5正規形を満たす例
    上記、第4正規形を満たす例に次のテーブルを足す
    [部署-製品テーブル]
    部署ID(pk) 製品ID(pk)
    ---------------------
    D01        P0001
    D01        P0002
    D02        P0001
    D02        P0002
  
  定義
    関連すべてについて、関連エンティティを作った状態
  どうやれば正規化できる?
    存在する関連すべてについて、関連エンティティを作る
    (もちろん、業務上関連がないのに関連エンティティを作る必要はない)
  正規化しないとなにがまずいの?
    関連エンティティがない関連は、エンティティ同士の対応関係がわからなくなる

正規化のまとめ
  3つのポイント
    正規化は、更新時の不都合/不整合を排除するために行う
    正規化は、従属性を見抜くことで可能になる
    正規化は、いつでも非正規系に戻せる
  
  正規化のメリット、デメリット
    メリット
      データの冗長性が排除され、更新時の不整合を防止できる
      テーブルの持つ意味が明確になり、開発者が理解しやすくなる
    デメリット
      テーブルの数が増えるため、SQLで結合を多様することになり、パフォーマンスが悪化する
  
  正規化は常にするべきか
    第3正規形までは原則として行う
    関連エンティティが存在する場合は、関連とエンティティが1対1で対応するように注意する(=第4, 第5正規形にしよう!)
    
    パフォーマンス向上のためあえて「非正規化」することはあるが、筆者は原則許さないという立場
    (非正規化はあくまでも最後の手段(しかも劇薬))



## 4章: ER図
割愛
(多対多の関連が出てきたら、間に関連エンティティを置こう)
```

## 続き

[後半](https://qiita.com/arene-calix/items/02e8c380b6f9a829d724)は、パフォーマンス向上手段や、バッドノウハウ等に触れています。