近々DB設計をやるため、以前読んだ良書を読み返してみました。
主に未来の自分が「ああ、そうそう、だいたいこんな感じだった」と振り返るため、要点をメモにまとめました。
勿論、書籍そのままではなく、自分なりに構成や表現を変えた部分もあります。
元の構成、表現で見たい方はご購入下さい(オススメです)。
https://www.amazon.co.jp/dp/4798157821
対象読者
- RDB、正規化、ER図がだいたいどういうものか(完璧ではないにせよ)知っている方
- DB設計について、さくっと概観をおさらいしたい方
要点まとめ: 前半
前半では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図
割愛
(多対多の関連が出てきたら、間に関連エンティティを置こう)
続き
後半は、パフォーマンス向上手段や、バッドノウハウ等に触れています。