- 達人に学ぶDB設計 徹底指南書
- はじめに: DB設計の重要性
- テーブルの正規化
- 第一正規形
- 正規形と「更新時異常」
- 関数従属性
- 第二正規形
- 情報無損失分解
- テーブルを分割したことによるパフォーマンスへの影響
- 第三正規形
- 正規化を行う場合と行わない場合
- オススメ書籍
- 参考ページ
達人に学ぶDB設計 徹底指南書
このページは、書籍『達人に学ぶDB設計 徹底指南書』をベースにしています。
この本は、「とりあえずデータベースを勉強したい」という人におすすめできます。実践に必要な情報だけがきれいにまとまっていて、おすすめです。
はじめに: DB設計の重要性
DB設計を後から変更する場合、DBを止める必要がある事が多いです。
DBを止めるということは、当然、アプリケーションも止める必要があるため、DB設計を後から変更することは、非常に高コストであることが分かります。
DBについては、「一回テーブルを作ったら、もう変えられない」くらいの気持ちのほうが良いです。
だからこそ、DB設計については理論がしっかりしています。
ここでは、DB設計に必要不可欠な「正規化」の話をします。
テーブルの正規化
データの更新で不整合にならないようにするため、正規化は非常に重要です。
正規化にはいくつかのレベルがあります。
- 第1正規形
- 第2正規形
- 第3正規形
- ボイスコッド正規形
- 第4正規形
- 第5正規形
数字が大きくなるほど、データベースが「きれいに」なります。
「きれいに」なるとは、「更新時の不整合が起こりづらくなる」ということです。
実務で使うのは第3正規形までなので、今回は第3正規形まで説明します。
それより上の正規形について意識しなければならないようなデータ構造になることは、実務では非常に少ないので、説明しません。気になる人は調べてください。
第一正規形
テーブルの1つのマスに、1つの値のみを含む場合は、第一正規形になります。
第一正規形になっていない場合は、そもそも RDB に保存することができないか、保存はできるが、非常に扱いづらい形式となります。RDBにデータを保存しようと思ったら、まず第一正規形にすることになります。
この「社員」テーブルは、第一正規形を満たしません
| ID | 名前 | 子供 |
|---|---|---|
| 000A | 加藤 | 達夫 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 陽子 清美 |
このように変形すれば、第一正規形はみたします。これであれば、RDB に保存することができます。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
しかし、このテーブルではまだ使いづらいため、第二正規形や、第三正規形などの、より高次な正規形を目指す必要があります。
例えば、藤本さんのように、子供がいない人のデータが扱いづらいのが現状です。
ちなみに、一応、こういう形式にする方法もありますが、子供が3人以上の場合には対応できないという問題があります。
| ID | 名前 | 子供1 | 子供2 | 子供3 |
|---|---|---|---|---|
| 000A | 加藤 | 達夫 | 信二 | |
| 000B | 藤本 | |||
| 000F | 三島 | 敦 陽子 清美 |
正規形と「更新時異常」
先程のテーブルは、第1正規形の条件を満たしているが、第2正規形の条件は満たしていません。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
つまり、この表は「きれいでない」ということです。
確かに、「藤本」だけ、「子」が空欄だったりして、なんとなくきれいじゃない感じはしますが、具体的にどこがダメなのでしょうか。
それは「更新時異常」が起こるからです。
更新時異常とは
「更新時異常」には、3種類あります
- 挿入時異常: データを挿入（追加）する時に異常が発生すること
- 削除時異常: データを削除する時に異常が発生すること
- 修正時異常: データを修正（更新）する時に異常が発生すること
それぞれ具体例を見ていきます。
挿入時異常
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
ここに、以下のデータを追加します
- (000A, 山本, 一郎)
結果、こうなります
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
| 000A | 山本 | 一郎 |
山本と加藤の社員IDがかぶってしまいました。
これが「更新時異常」です。このテーブルの構造をしていると、このような異常なデータを弾くことができません。
削除時異常
この表があったとして、
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
加藤さんの子供「達夫」が、結構するなどして、戸籍が別になったとします。
達夫のデータを削除します。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
さらに、「信二」の戸籍が別になったとします。
信二のデータを削除するとこうなります。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
すると、社員ID 00A の加藤のデータがなくなってしまいました。
このように、法則に則って、削除していくと、加藤のデータが失われてしまいます。これが、削除時異常です。
修正時異常
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
「加藤」の名前が、何らかの理由で「山本」になったとします。
ここで、更新を失敗してこうなってしまいました。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 山本 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
加藤のデータは2つあったのですが、片方だけ名前変更されてしまい、もう片方は加藤のままになってしまいました。
これが修正時異常になります。
このように、データを変更するときに、様々な異常が起こる可能性があるのが「第1正規形」なのです。
関数従属性
この「異常」と関わってくるのが「関数従属性」という考え方です。
第2正規形、第3正規形を理解するのに大事なのが「関数従属性」で、「更新時異常」も、この関数従属性が関わってきます。
難しい言葉ですが、言葉ではなくて、イメージで覚えましょう。
テーブルにおいて、「Aが決まるとBも決まる」というのが「関数従属性」です。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 000F | 三島 | 敦 |
| 000F | 三島 | 陽子 |
| 000F | 三島 | 清美 |
この表は、「社員IDが決まると、社員名が決まる」
これを「関数従属性がある」と良い、社員ID→社員名 と書きます。
第二正規形
第1正規形のテーブルから、部分関数従属な属性を排除したら第2正規形になります。
部分関数従属とは...？さっきまでの表だと説明できないので、新しい表を用意します。
「社員」テーブル
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C0001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | B化学 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | B化学 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | B化学 | 010A | 渋谷 | 33 | D04 | 総務 |
主キー: {会社コード, 社員ID}
このテーブルにはいくつかの「関数従属」があります
- 会社コード→会社名 （会社コードが決まると、会社名が決まる、ということです）
- {会社コード, 社員ID}→社員名
- 部所コード→部署名
- ... （他にもいっぱいあります）
「部分関数従属」とは、矢印の左側が「主キーの一部（全部ではない）」になる場合のことを言います。
会社コード→会社名は、左が主キーの一部なので、「部分関数従属」になります{会社コード, 社員ID}→社員名は、左側が主キーの「全部」なので、これは、部分関数従属にはなりません
この、部分関数従属があることで、更新不整合が起こります。例えば、「加藤」「藤本」「三島」の社員情報を消すと、C0001 = A商事 の情報が消えてしまいます。
そこで、部分関数従属になっている部分を分解します。
「社員」テーブル
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
主キー: {会社コード, 社員ID}
「会社」テーブル
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
主キー: {会社コード}
この分解により、会社コード→会社名の部分については、「更新時異常」が起こらなくなります。
挿入時異常について
会社テーブルに対して、C0001, C自動車を追加すると、相変わらず挿入時異常が起こりそうな気がしますが、ここで「主キー」が効いてきます。
主キーは重複が認められません。
テーブルを分けたことにより、新しくできた会社テーブルの主キーは 会社コード。つまり、会社コードの重複は認められなくなり、エラーで弾くことができるのです。
削除時異常について
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
試しに「加藤」「藤本」「三島」を消してみますが、「A商事」は「会社」テーブルに残っているので、削除時異常は起こらなくなっています。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
修正時異常
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
B科学→B工業 に名前が変わった時、変わる部分は「会社」テーブルの1行しかないため、修正時の異常は起こらない。
このように、会社コードと会社名の部分では、各種異常が起こらなくなっていることが分かります。
情報無損失分解
正規形のレベルを上げる、ということは、テーブルを分割（分解）することになりますが、分解するときに、情報が消えないことが重要です。
このような分解を 「情報無損失分解」といいます。
テーブルを分割したことによるパフォーマンスへの影響
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
テーブルを分割したことにより、いくつかのクエリが少し複雑になります。
例: 社員名と、所属している会社の名前を取りたい場合
- 分割した後の場合
- 社員テーブルから社員を取得 → 会社テーブルから会社名を取得しなければいけない
- 分割する前
- 一つのテーブルだけ見たらよい
しかし、パフォーマンスの低下はわずかなので、「異常がおこらない」というメリットのほうが大きいと判断できます。
第三正規形
さらに、推移的関数従属な属性を排除したら第3正規形になります。
またよくわからん言葉が出てきましたが、覚えなくてもいいです。
一応説明すると、
- Aが決まるとBが決まる（A→B） = 関数従属性
- さらに、Bが決まるとCが決まる場合（B→C）
- A→B→C と決まる
- これが推移的関数従属性
- A→B→C と決まる
さっきの「第二正規形」では、「主キーが関係してるところの関数従属をなくす」だったんですが、「第三正規形」こっちは簡単に言うと、「主キーと関係ないところの関数従属性がなくなる」ことになります。
実際のテーブルを見てみましょう。
「社員」テーブル
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
ここで、「社員」テーブルは、
- {会社コード, 社員ID}→部署コード→部署名
- {会社コード, 社員ID} は主キーなので、これが決まると、当然他の値も決まります。
- さらに、部署コードから部署名が決まるので（実は説明してなかったのですが、そうなんです...）
という推移的従属性があることになります。これをなくします。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
そこで、これを分解する。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 藤本 | 32 | D02 |
| C0001 | 001F | 三島 | 50 | D03 |
| C0002 | 000A | 斎藤 | 47 | D03 |
| C0002 | 009F | 田島 | 25 | D01 |
| C0002 | 010A | 渋谷 | 33 | D04 |
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
「部署」テーブル
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
第二正規形と、第三正規形の違いが分かりづらいですが、そんなことはどうでも良くて、このように関数従属性（Aが決まると、Bが決まる）の部分を分解していくと、正規化される、という感じです。
関数従属性（Aが決まるとBが決まる）があって、かつ、その部分で異常（不整合）が起こるかどうかが重要なのです。
第三正規形になったことにより、起こらなくなった「挿入時異常」「削除時異常」「修正時異常」は、第二正規系とほぼ同じです。どのような異常が起こりうるか考えてみてください。
正規化を行う場合と行わない場合
基本的に、第3正規形には思考停止でやってよいです。これで困ることはほぼありません。
正規系を行わないほうがいい場合はどういう場合？というと、パフォーマンスの問題で「どうしても」、という場合です。
ただ、相当なデータ量（数千万行とか、数億行の規模）になってこないと、ほぼ無いんじゃないかなと思います。
オススメ書籍
『達人に学ぶDB設計 徹底指南書』 https://www.amazon.co.jp/達人に学ぶDB設計-徹底指南書-初級者で終わりたくないあなたへ-ミック/dp/4798124702
参考ページ
達人に学ぶDB設計 | 正規化と非正規化、インデックスについて - Qiita
第四正規化・第五正規化・ボイスコッド正規化がややこしいので、備忘録として自分なりにまとめてみた(データベーススペシャリストの勉強) | メサイア・ワークス
