猫でもわかるWebプログラミングと副業

本業Webエンジニア。副業でもWebエンジニア。Web開発のヒントや、副業、日常生活のことを書きます。

データベースのテーブルの正規化について、とにかくわかりやすく説明する

達人に学ぶ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 と決まる
      • これが推移的関数従属性

さっきの「第二正規形」では、「主キーが関係してるところの関数従属をなくす」だったんですが、「第三正規形」こっちは簡単に言うと、「主キーと関係ないところの関数従属性がなくなる」ことになります。

実際のテーブルを見てみましょう。

「社員」テーブル

会社コード 社員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

参考ページ

情報無損失分解と関数従属性 | mLAB

達人に学ぶDB設計 | 正規化と非正規化、インデックスについて - Qiita

第四正規化・第五正規化・ボイスコッド正規化がややこしいので、備忘録として自分なりにまとめてみた(データベーススペシャリストの勉強) | メサイア・ワークス