第1部 DB設計総論

第1章手順としての正規化

「One Fact in One Place（1つの事実は1つの場所にのみ存在する）」を徹底していく。
- データベース設計の正規化とは、「データ構造の部品化」を進めていくこと。
正規化の手法
- IDの導入
  - 繰り返し項目、値の重複を排除、IDによるレコードの関連付けによって、テーブル間の関係を疎結合にすることができる。
- 業務の視点からの正規化
  - 業務要件における2つの事実を1つの場所においてはいけない。
  - 例: 「数量 * 単価 ≠ 金額」であるときの「金額」を保持する器を作る。

第2章データベース設計の基礎知識

データベース設計のスキルは経験によって養われていくが、経験を積むことが難しい。
データベース設計における悩みは、正規化の悩みよりも、データ項目の洗い出しに対する自身の欠如の方が大きなウェイトを占めている。
データベースにおける「レコード」
- 消えると困るデータの「記録」。
「タプル」
- 一般にレコードと呼ばれる「データ項目の組み合わせの構造」のこと。
「インスタンス」
- タプルに実際の値が入って現実化したもの。
リレーショナルデータベースにおいての「ドメイン」
- カラム（列）のこと。
データベース設計の3つのポイント
- 箱（エンティティ）の見出し方
- 主キーの設定
- 重複の排除（いわゆる正規化）

箱（エンティティ）の見出し方

エンティティとは、何かの集合のこと。
- データモデリングとは、エンティティを模擬的に図式化すること。
エンティティ名は、「このデータは何についての記録なのか（何について記録したいのか）」が候補になる。
エンティティの分類
- 「リソース系エンティティ」
  - 「モノ」に関する記録のこと。名詞による分類を行う。
- 「イベント系エンティティ」
  - 「出来事」に関する記録のこと。動詞による分類を行う。
エンティティ名を並べていき、6W3Hを使って、網羅すべきデータ項目について見当をつけていく。
- データベース設計の場合、対象業務に関する知識をある程度以上持っている人間でないと、データモデル上不足していることを指摘することができず、プログラム設計ができず、プロジェクトの進捗が硬直化していきかねない。
- データベース設計の担当者が業務要件やビジネスモデルを理解するべき。
  - 積極的に業務要件を拾いに行き、分類に困るものは、まずは一番似合いそうなエンティティに放り込んでおく。

キーの設定

キーとは、データベース上においてデータを特定するための手がかり。識別子。
キーの種類
コードとキーの違い
- 『コード』は、ユーザがレコードに容易に到達するためのユーザインターフェイスであり、システムの都合でなくビジネスの都合によって決定されるもの。
  - インスタンスそのものではなく、ある特定のインスタンスにたどり着くための「アクセスパス」である候補キー。
  - 例) 従業員コード: A001、顧客コード: 37A0194
- 『アイデンティファイア・ID』とは、「別のレコードである」ことを見分けるための識別子。
  - インスタンスのライフサイクルを表現し、その集合の中に属してから消滅するまでの間、特定のインスタンスであることを示す座標として機能する。
  - データのライフサイクルを考えて、アイデンティファイアを重ねない。削除後の虫食いの再利用は避ける。
- アイデンティファイアという概念を導入することで、各種コード体系とは切り離された形で、純粋なインスタンス間の関係を『外部キー』によって表現できる。
  - 顧客のコード体系が変わろうと、レコード間の関係をそのまま維持するようになるため、参照制約というよりも存在制約となる。
アイデンティファイアと「あだ名（アクセスパス）」としてのコード体系と正規化された個々のドメインとをそれぞれ別々に用意してやることで、「コードの洗い替え」問題や「諸口」問題を回避することができる。
- 適切な正規化とは、そのデータ項目（ドメイン）の役割あるいは意味付けが単純である、すなわち役割あるいは意味しか有しない状態にまでアトミックに分解すること。
- リレーショナルデータモデルの正規化という考え方のポイントは、関数従属性にあり、（ひっくるめて見たい場合もある）「ビジネス上の都合によるコード体系」に従属するのではなく、（あるインスタンスを一意に特定できる）「キー」に従属する。
計画系のコードにおける「コードの履歴化」
- 例: 「商品コード付与業務」が存在するときに、商品エンティティ(商品ID)と商品コードエンティティ(商品コードID/商品コード/商品ID(FK)/適用開始日/適用終了日)を作る。
交差（インターセクション）エンティティ
- 例: 「製品コードの読み替え業務（発注先によって製品コードが異なったり、社内で同一に扱う製品を別の製品コードとすること）」が存在するときに、m:m関係(社外コードと社内コードのマッピング)を表現するエンティティを用意する。
- 例: Railsチュートリアル第4版第14章ユーザーをフォローするのrelationshipsテーブル。

ビジネス上の正規化

正規化
- Normalize。最小限のデータで必要にして十分なアウトプットを組み立てられるようにすること。
重複の排除
- 「事実の記録が重複している」ものを排除する。ビジネス上「別の事実」となるものは重複ではない。
  - それぞれのアイデンティファイアにぶら下がる事実であれば、各エンティティの属性として定義する。
  - そうでなければ別のエンティティを用意する。
    - 例: 「複数の売り上げに対して、ある伝票単位で一括に扱う」という事実を記録する際は、伝票番号という属性を持つ売上と、売上への参照を持つ売上明細。見出し・明細形式（ヘッダ・ディテール関係）を採用する。
履歴や時系列に状態遷移するデータ
- ある1つのものが時系列に状態が変化していく場合（例: 案件が引合→商談→見積→契約→納品→請求と状態遷移する）
  - フラグを使う場合
    - フラグの意味が複雑になり、さらには複数のフラグの組み合わせで表現するなど、単純性から乖離してしまいがち。
    - また、それぞれのデータ項目の中身（商談日時や商談相手と見積金額など？）は「非なるもの」であるという事実がある。
  - それぞれにエンティティを用意してやる場合
    - 結果としてプログラムも簡潔になり、データベースもすっきりする。
- 状態遷移自体が変わった場合のことを考える（例: 予約→チェックイン→チェックアウトから予約→確認→チェックイン→チェックアウト）
  - フラグ形式の場合
    - 区分コードを1つ追加するだけで対応できそうな感じがする。
    - 実際には業務上のワークフローが変わっており、ユーザーインターフェイスの変更や追加の業務が付随する可能性がある。
  - 別のテーブルを追加する場合
    - 安心して改修できる。

※ 履歴の扱いという観点で、論理削除については、#ronsakucasual DBの論理削除についてひたすら共有する論理削除 Casual Talks #1 にいってきたまとめが詳しい。
※ フラグを使うか、別テーブルを追加するかのトレードオフは、Railsでenumを使う場合のメリットとデメリットに近そう。

ビジネスルールの1つのしての導出（デリベーション）ルール
- 可逆性の導出項目だけを、テーブル上から排除する。
  - 例: 「数量 * 単価 = 金額」であるときの「金額」は可逆性を有する導出項目だが、「値引き」がある場合など「数量 * 単価 ≠ 金額」であるときの「金額」は不可逆性が生じるので、冗長性を排除できず、「金額という列をテーブルに持たせる」ことが「正規化した結果」となる。
- 可逆性の導出項目は「ビュー化（名前付きSELECT文を用意する）する」ことで、テーブルを補完する「仮想表」としてデータベース設計上に配置する。
導出ルールに似た妥当性検証（バリデーション）ルール
- エンティティ間の「関係」の管理。
  - 例: ある都道府県コード（大阪府）を選ぶと紐づいている営業所コード（堺営業所、梅田営業所、豊中営業所など）の中からしか選択できない。都道府県エンティティと営業所エンティティの「関係」を管理。
- 「リソース」同士の関係の事実と、その関係を管理する「イベント」についての事実
  - 例: 「組織ID」と「従業員ID」の関係を管理するための「所属」というエンティティを用意し、所属開始日と所属終了日の列を追加することで、組織変更の事実を記録する履歴データになる。
    - m:mの関係としてだけでなく、ユニーク制約を付与することで1:1や1:mにすることもできる。
    - 無条件にこの形式にすると思ってよい。
- 2つのリソース系エンティティがまったく同一の構造をしている場合
  - 例: 「組織ID」である「上位組織ID」と「下位組織ID」を持つ「組織構造」エンティティ。
    - 交差エンティティと同じだが、重複を排除した結果1つの箱になる。
    - m:mの関係としてだけでなく、ユニーク制約を付与することで1:1や1:mにすることもできる。
    - 無条件にこの形式にすると思ってよい。
複雑なビジネスルール
- 複雑な業務であればデータベース設計も複雑になる。
  - データベース設計を単純化してしまうとプログラムの設計が複雑になる。
  - 正規化した結果エンティティ数が膨大になったとしても、それが現在のビジネスルールの実態なのだということを認識する。

DB設計の手順

1. 大まかにブロック分けを行う（業務単位か部門単位）

例: 営業・購買・生産・経理、販売管理・購買管理・生産管理

2. それぞれのブロックごとにイベント系を洗い出す（この時点では、正規化を意識しない）

まずはイベント系を中心に考えていく。
- イベント系エンティティは、「動詞で言えるもの」「タイムスタンプを打てるもの」「入力系業務と出力系業務から当たりがつくもの」。
  - 例: 入金と出勤、入庫と出庫、受注と発注。予約、訂正、移動。

3. イベント系に対する正規化を行って、リソース系を洗い出す

イベント系エンティティの正規化によって、コード体系が見えてくるので、リソース系エンティティを見出すことができる。

4. リソース系に対する分類の洗い出しを行なって、リソース系の正規化を行う

リソース系は分類という概念を意識する。
- サブセット(副集合)はエンティティ化しておく方が良い。
  - 例: 顧客 = 法人顧客 + 個人顧客。
- 最初から性能を考えすぎないようにする。
  - JOINの多発を気にしない。SQLの性能問題は別の話として、論理的なデータ構造を押さえることに注力する。

5. ブロック間でのリソース系の統合を行い、さらに正規化を行う

あちこちで使われているエンティティを統合したり、複数のエンティティに分離したり、関連エンティティを見出してm:m構造でつないだりする。
- そのエンティティに対するメンテナンス（業務の洗い出しや業務プロセスの再確認）を怠らない。

6. 導出系の整理をして、最終的な正規化を行う

複数のエンティティを組み合わせて導出するものは、ビュー（仮想表）で住むものは冗長なものとして排除する。
- 「現在のxx」という名前のものは複数のイベント系エンティティの組み合わせで導出可能。
時系列の推移・予定系・計画系（リソース系の情報推移）について吟味する。

まとめ

コードというものをなぜ作るのかという疑問を持つ意識を忘れない。

※ ここでの『コード』は、プログラムのことではなく、データベース設計における『コード体系』のことを指す。参照: こんな商品コードはダメだ - Qiita

第2部 RDBMS総論

第1章 RDBMSの必要性を考える

RDMBMSを使ったシステム開発のメリット
- 標準化されたSQLの存在
  - プログラミング言語やアプリケーションに依存しない。
- 物理情報と論理情報の分離
  - byte単位での物理的なデータ構造を意識せずに、カラムに名前をつけてアクセスできる。
- データ整合性保持の容易性
  - データ型を扱える。
- 複数ユーザによる同時利用の実現
  - プログラミング上考慮しなくてはならないいくつかの課題を解決する。
RDBMSは構造化技法に対するアンチテーゼとして生まれた。
- 構造化プログラミングの基本3構造「連結(Concatenation)・分岐(Selection)・繰り返し(Repetition)」。
  - 「いかに効率良くプログラムを作るか」に着目。
- リレーショナルモデル
  - 「いかに効果的なプログラムを作るか」「いかに無駄なプログラムを作らずに済ませられるか」に着目。
RDBは「いかに取り出すか」が根幹。
使わないデータは蓄積する価値も意味もない。
- データを「活用する」という視点が必要。
- 「ビジネスプロセスの最短化を実現できるような」データの提供ができなければならない。
「必要なときに必要なところへ必要な分だけ必要なものを届けること」が重要。
- その実践のために「設計」が必要。

第2章経営資産の視点に基づくDB設計

DOA（データ中心アプローチ）

データという非常に客観的なものを通じて、企業のあり方を見直す。
データの正規化
- 「One fact in one place」の徹底。
- 余計なものはつけないし、いつか使うかもしれないものもつけない。
RDBMSの価値は、ビジネスの流れの中で「本当に必要なものだけを保持し」、「必要なときに必要な形にすぐ構成して取り出す」ことができる仕組みを提供すること。
DLC（データライフサイクル）
- データの寿命。
- 大きくわけて「CRUD」と呼ばれる4つの状態に分かれる。
トランザクションの正規化
- データ構造とトランザクション（≒ビジネスプロセス）の間に、CRUDという概念を通じて書かれたマトリックスから、無駄なトランザクション（≒ビジネスプロセス）を排除すること。
UIとトランザクションの正規化
- トランザクションを実行するために必要になっているUIの無駄を省くこと。
ユーザの役割の正規化
- UIとそれを利用する立場（ロール、ユーザの役割）との間のマトリックスから、使う人間を最小限にすること。
組織の正規化
- ユーザの役割の正規化の結果、無駄になった組織を排除、あるいは組織構造を最適化すること。
- 無駄なビジネスプロセスを行っている組織を最適化することがリストラクチャリングの本質。
戦略の正規化
- 組織の正規化の結果、さほど収益を生まない事業であれば、その事業のあり方そのものや事業戦略から見直していくこと。
順序としてはトップダウンもボトムアップもその折衷型もあるが、こうした流れを推進していくことがデータ中心アプローチの本質。
データ中心アプローチのベースとして、データはプロセスよりも非常に永続性が高い、という大前提がある。
- データの構造は、基本的には変わらないはずであるため、データを基軸において考えていく。

データの正規化

数学的正規化と業務的正規化

ビジネスルールをデータ構造に見出す。
画面のユーザインターフェイスから分析を始めるのが良い。
- ユーザインターフェイスを先に設計して、そこからデータベースを考えていく。
データ構造が変われば、それに伴って業務も変わる。
「ERDを書く」というのは「ビジネスの写像を行う」ということ。
- 教科書に書いてあることを鵜呑みにして、いかに効率的に再現できるのかがデータベース設計のスキルではない。
- 「数学的な正規化」において、正しいとされるデータ設計であったとしても、ビジネス上のバリューを生み出すためには、必ずしも正解であるとは断言できない。→ 「業務的正規化」を行うことが重要である。

論理設計と物理設計

昨今のハードウェアの高性能化により、非正規化を行う必要はない。
物理設計
- ハードの性能・スペックをフルに引き出せるような状態にしてあげること。
  - 非正規化を行うことではない。
インデックスの設計
- 物理設計において一番重要。
- 正規化されたテーブルというのはそのままでは性能が出ない場合があるため、適切にインデックスを設定して性能を改善する。
  - 非正規化をすることで性能を上げようとしない。

インデックスの設計

RDBは全件総なめがデフォルト。
- 全件総なめを防ぐためにインデックスが必要不可欠。
インデックスの仕組み
- メリット
  - 全部のレコードを検索しなくても済む。
- デメリット
  - 常に指定された順序で並んでいなければならない。
  - 追加・更新時にも並べ替えを必要とするため、追加・更新に負荷がかかる。
- 例: 図書館で本を探すとき、「全部」を探したという確証を得るには、棚の最初から最後まで全てを確認するしか術はないが、図書カード（インデックス）が用意されていて、著者別にアイウエオ順で並べられた状態で収まっているのであれば、図書カードを探し、図書カードが示す棚を順番に前から見ていくことで、最小限の手間で済む。
検索を優先するのか、更新を優先するのかが、インデックス設計（物理設計）の設計上重要な視点であり核となる。

主キーとアイデンティファイアは別のもの

アイデンティファイアとコード体系に基づくキー設計とは、分けて考えなければならない。
そのレコードそのものの、システム内部における一意性を確保するものは、以下のいずれかにはならない。
- コード体系
  - 例: 社員番号
  - あろうとなかろうと、そのレコードが表現する事象（例: 社員）は存在しており、集合（テーブル）においてもユニークであるため。
- システムが勝手に振ってくれるもの
  - 例: OracleのROWID、PostgreSQLのOID
  - システムの都合によって勝手に振り直され、いつも同じ値でレコードを特定することができないため。
アイデンティファイア（識別子）
- ビジネス上のDLCを表現するもの。
  - データが生成された時から消滅するまで、一意でなければならない。
- 主キーとは別。
- コード体系は何となくなものが多いため、システムとしてきちんと作り上げるためにはIDを切り分けて扱う。
主キー
- そのレコードにたどり着くためのアクセスパスに過ぎない。
- コード体系を主キーにすると不自由になってしまう（レコードにたどり着けない）。
キー
- あるレコードをどういう形で呼びたいか、指し示したいかを決めるもの。
- 複数あっても良い。
  - 例: 主キー、オルタネートキー（代替キー）、またはそれら複数。
- あだ名があっても良い。
  - その時々で呼びやすい、名指ししやすいキーを設定してやれば良い。
    - そういった形でキーを見出して、それをインデックスとして実装することが、物理設計。
インデックスというのは、パフォーマンスチューニングの一環として出てくる。
- それと同時にビジネスの中では、データへのアクセスパスである以上、設計の一環である。

※ この節の意図する内容としてはSQLアンチパターン　ID Required - Qiitaが分かりやすい説明になっていると感じたためリンクして置かせていただきます。

第3章資産活用としてのSQL

SQLの本質・特性・動作について理解し、性能の出る使い方を確認する。

SQLはバッチ処理

SQLは（コンパイラではなく）インタプリタであり、バッチ処理である。
- 例えば、配列の要素1つずつについて、毎回SELECT文を投げてレコードを1件取得しては処理を行う、という繰り返し処理を行なってしまっては、性能が出ない。
RDBMSの内部SELECT文を理解する。
- SQL文を実行したとき、RDBMS自体が非常に多くの内部処理を実行している。
  - 例: SELECT * FROM t1
    1. RDMBSは、カタログテーブル（ディクショナリ）を検索し、t1というテーブルが存在するかを調べる。
    2. t1というテーブルが存在すれば、カタログテーブル（ディクショナリ）を検索し、そのテーブルが持っている列をEOFを検出するまでループ処理を実行し全件取得して、インライン展開する。
  - WHERE句による絞込条件が設定されていたとしても、条件に合致するかをレコード全件について検証してしまう。
  - UPDATE文 / DELETE文の場合も同様に、更新対象とするべきレコードをテーブルから検索してメモリ上に取り出して項目の書き換えを行い、書き換えたレコードをハードディスクに戻す必要がある。
- 余計なテーブルを作成しているとパフォーマンスが悪くなるのは、カタログテーブルの検索対象となるテーブルとレコードが多くなるため。
結合処理（ソートマージ型結合）において、RDBMSは内部SORT処理を行う。
- 例: 結合処理（JOINなど）と並び替え（ORDER BYなど）を行う場合
  1. 各テーブルに対してSORT処理を行う。
  2. 各テーブルを組み合わせる。（ここまでが結合処理。）
  3. 結果セットに対してSORT処理を行う。（これが並び替え。）
- SORT処理（≒ 結合処理）は負荷がかかるため、極力減らすようにしないと、まっとうな処理速度が出ない。

※ MySQLにおける結合処理はネステッドループ型結合であり、ソートマージ型結合とは異なる。

EXPLAIN(PLAN)コマンドを使い、RDBMSの内部動作（どのテーブルにどのようにアクセスするのか、どのインデックスを使っているのかなど）を確認するようにする。
SQLが内部で行なっていることは、構造化プログラミングによるレポート作成のためのアルゴリズムと同じ。
更新処理を行う際にFetchは使わず、副問合せを使う。
- Fetchする（1件ずつホスト言語のメモリ領域に取り出す）デメリット。
  - 不要なトラフィックが増え、パフォーマンスが劣化する。
  - アルゴリズムが荒っぽくなる。
- 副問合せを使い、（RDBMSに任せて）オプティマイザによる実行計画を作成した方が効率が良い。
  - RDMBSのメモリ上で直接データの書き換えを行える。
RDBMSの機能を使うか、自前で処理を書くかは「標準準拠 vs 実行効率」のトレードオフがある。
大規模なバッチ処理の場合に、負荷を軽減させる仕掛けを自前で作る方法。
- 作業用のテーブルを作成してそこに対象となるレコードだけをまず抜き出し、それを元に更新処理を行う。
  - 例: INSERT文またはCREATE TABLE文に副問合せを使う。
INSERT文のバッチ処理。
1. 対象のテーブルや、列のデータ型などのチェックを行うために、カタログテーブルに対する検索を行う。
2. 主キーの重複がないことを確認するために、その主キーでレコードがあるかないかの内部SELECTを行う。
CREATE文のバッチ処理。
1. カタログテーブルを検索し、同じ名前のテーブルが存在するかを確認する。
2. テーブルが作成できたら、今後同じ名前のテーブルが作れないようにカタログテーブルにレコードを追加する（= 内部INSERT文）。
DELETE文のバッチ処理。
1. CREATE文のバッチ処理と同様、テーブルを作成したらカタログテーブルからレコードを削除する（= 内部DELETE文）。

インピーダンスミスマッチ

通常の開発では、ホスト言語の中にSQLを埋め込んで使う。
- その時に、プログラミング言語がレコード指向であることに対し、SQLはセット（集合）指向であることに対する使いづらさのこと。

ストアドプロシージャ（Stored Procedure）

RDBMSの中にあらかじめ手続き（Procedure。SQL文の集合。）を作成し、保存（Store）しておくもの。
- 複雑なSQL文を、パラメータを渡して戻り値を得る、1つの関数の状態にすることができる。
- 複雑な処理をひとまとまりにして呼び出せるため、処理の分離が容易になる。
- IFなどの論理構造を実現できる。
- 各RDBMSごとの独自の構文が多く、完全に互換性が取れているとは言えない。
- さまざまなホスト言語からの利用が可能。
メリット
- 処理をDBサーバ側で完結させることができる。
  - 処理の負荷分散にも有効。
ビューの代わりにストアドプロシージャを使うことが可能。
- SELECT文の記述を軽減するためにビューを作成する、ということが一般的だが、WHEREによる選択条件などに値が都度で指定される場合などは、SELECT文をホスト言語に埋め込んでやるか、あるいはストアドプロシージャを使うことで解決できる。
- メリット
  - 複雑な問い合わせが必要な場合も、すべてストアドプロシージャの中に隠蔽できることにより、SQLのチューニングが必要になってもホスト言語側に一切の影響を与えずにストアドプロシージャ内のコードを変更することが可能。
    - RDBMSに対するアクセスメソッドのカプセル化を実現できる。
  - ストアドプロシージャに対してユニットテストを行うことで、変更への柔軟性を確保することも可能。

※ DBへの変更は影響範囲が大きいため、ストアドプロシージャがロジックを持ちすぎると変更が大変な「強すぎる制約」になるとしている記事もある。

クラスの粒度とエンティティの粒度

クラスの粒度とエンティティ（テーブル）の粒度は同じだと思われがちだが、実際は異なる。
プログラミング言語とRDBMSは別物。
- プログラミング言語はデータ資産活用に、RDBMSはデータ資産管理に最適化されている。

機能レベルのインターフェイス

さまざまなレベルのインターフェイスを用意することで、アクセス権限を制約することができる。
- プログラムからのアクセスはストアドプロシージャを経由する。
  - RDBMSをSingletonオブジェクトに見立てて、格納されているデータをprivateなものとすることができる。
- ユーザーからのアクセスはビューを活用する。
  - システムの都合で持たざるを得ないデータ項目を隠すことができる。

第3部楽々ERDレッスン

身近にあるものを何でも手当たり次第にデータベース設計のネタにする。
- 「表組を見たらERDを描け！」

第4部付録

第1章 SQLのカラクリ

Explainコマンドを使い、実行計画を確認する。

第2章 RDBMSのボトルネックの原因と対策

何度もRDBMSに接続しにいくのを避ける
- コネクションプーリングの導入が必須。
同じデータを何度も検索するのを避ける
- オブジェクトキャッシュを利用して、RDBMSにSQLを投げずに済ませる。
同じディスクに集中するのを分散する
- ディスクパーティショニングを行う。
  - 例: 結合処理を行うテーブル同士をそれぞれ別のハードディスク上に分ける。テーブルとインデックスを別のハードディスク上に配置してやる。
1台のCPUに集中するのを分散する
- 1台のマシン上に複数のCPUを持つSMP構成にする。
- 複数台のサーバーに分散する。
  - RDBMSのレプリケーション機能を使う。
    - 物理サーバーを分散させるとRDBMS自体が複数動作し、データの整合性の維持が難しくなるため。
    - 冗長化は障害対策にも有効。
      - 1つのサーバーがダウンしても他のサーバーによって処理を継続できる。

個人的参照リンク