本記事の対象
Qiita初投稿記事となります。
本記事はなるべく理論の細かい話は抜きにして正規化の要点を掴みたいという方を想定した記事です。
- 正規化の背景
- 正規化のやり方
という構成で理論の話は最小限に、正規化の背景を理解した上で正規化の手順を行えるように努めています。
正規化を理解する
正規化の目的
正規化の最大の目的はDB上で扱うデータの重複を排除し、「矛盾」の発生を「設計レベル」で防ぐことです。
矛盾とはなんなのかを改めておさらいします。
例えば組織における社員の所属を管理する（正規化が不十分な）テーブルがあるとします。
| 社員名 | 所属部署 | 部長名 |
|---|---|---|
| 田中 | 営業部 | 佐藤 |
| 鈴木 | 営業部 | 佐藤 |
| 佐藤 | 経理部 | 吉田 |
ここで人事異動があり営業部の部長が「中山」に代わるとします。
この際、営業部のレコードの部長名をすべて「中山」に更新しないと下記のような状態となりデータに矛盾が発生します。
| 社員名 | 所属部署 | 部長名 |
|---|---|---|
| 田中 | 営業部 | 中山 |
| 鈴木 | 営業部 | 佐藤（更新漏れ！） |
| 佐藤 | 経理部 | 吉田 |
この場合営業部の部長は社員によって異なる人物となっており矛盾が発生しています。
つまりこのテーブルは更新の仕方によっては矛盾が発生しうる設計となっています。
上記で矛盾が起きたのは、複数のレコードが同じ部長名という情報を持っていたことが原因です。
RDBにおいて矛盾が発生しうる原因はこのようなデータの重複です。
正規化を正しく行うことで、データの重複を排除し、矛盾が発生しないテーブル設計とすることが出来ます。
正規化によって得られるもの
正規化を正しく行うことで、DB上での矛盾の発生を設計レべルで防ぎ管理が容易なテーブル構造とすることが出来ます。
容易になることで色々なメリットを享受できますが、ここではコスト削減という点を見てみます。
DBは必ずしも正規化を行わないと矛盾を防げないわけではありません。
データに矛盾があるDBはそもそも使い物にならないため、業務においては正規化していようがしていまいが、DBに矛盾が発生しないようなんらかの対策が取られてます。
正規化も手段の一つではありますが、そのほかにも
- 実装でカバーする
- 運用でカバーする
- 仕様でカバーする
などの対策が考えられます。
1.実装で対処する場合はDBのデータを扱うアプリケーション等で矛盾が発生しないことを保証することになります。
例えば、部長が変わった場合は該当部署のレコードを「すべて」更新するよう実装するという具合です。
この場合は、現在、未来含め該当テーブルを扱うコードすべてにおいて対応漏れがないようにする必要があり、実装やテストといった部分にそのあたりを考慮するコストが積まれることが想像できます。
2.運用でカバーする場合は、例えばオペレーション自体を人間の手で対応したり、矛盾が発生した際にエラーログなどから対象データをパッチし回復する等の対応を行うことになります。
いずれにせよ、問題があるたびに手動での対応コストが発生することとなります。
3.仕様でカバーする場合は、そもそも矛盾が発生しうるようなものは許容しない、といった決断をすることとなります。
システム開発においてはこのような対応は避けたいですが、既存のテーブル設計が起因で対応を見送るということは実際の業務においてもしばしば発生します。
正規化以外のいくつかの対応方法を見ましたが、いずれにおいてもコストのかかる対応が発生しそうだというのは想像できるかと思います。
正規化を正しく行い矛盾を「設計レベル」で防ぐことで、これらのような問題と向き合わずに済むようになります。
正規化で行うこと
正規化は、テーブルを「無損失分解」となるようにテーブルを分割します。
無損失分解とは、分割後のテーブルを結合することで、分割前のもとの状態に戻せることを言います。
zipで圧縮したファイルを、圧縮前の元のファイルに戻せるというと同じです。
元に戻せない圧縮に意味が無いのと同じように、正規化もそのようなことが発生しないようテーブルを分割することを前提としています。
テーブルにはどれぐらい正規化されているかという度合いを表す正規系というものがあり、以下の段階があります。
- 非正規系（全く正規化されていない状態）
- 第1正規系
- 第2正規系
- 第3正規系
- BC（ボイスコッド）正規系
- 第4正規系
- 第5正規系
- 第6正規系
正規化にあたっては上から順番に正規化を進めていきます。
なお、業務上は第3正規系まで行えれば大半のケースは十分なため、本記事では第3正規系までに留めます。
この辺りはのちほど触れたいと思います。
正規化の手順
正規化手順の理解に必須の用語
正規化はmysqlなどのRDB製品ではなく、RDB理論上の考えです。
第3正規系までの正規化手順を理解するにあたっては下記RDB理論上の用語を理解しておく必要があります。
- 候補キー
- 非キー属性
- 関数従属性
- 部分関数従属性
- 推移関数従属性
候補キー
候補キーとはテーブル上で任意のレコードを特定するためのカラムの集合です。
つまり主キーやユニークキーのことです。
※ちなみに主キーというのはmysql等のRDB製品上の用語であり、RDB理論においては主キーという用語はありません。
非キー属性
候補キーに含まれないもの
主キーやユニークキーに含まれないカラムのことを指します。
関数従属性
ここは少し理論的な説明になりますが、第3正規系までの正規化を理解するに当たって特に重要な概念です。
関数従属性とは
あるレコードにおいて、特定のカラムAの値が決まれば、別のカラムBも特定できるような関係のことを指します。
この場合カラムBはカラムAに関数従属していると呼びます。
A → B
例えば社員カードにある社員番号を確認すれば、その持ち主の社員名を特定することが出来ます。
この場合は、社員名は社員番号に関数従属していると言えます。
部分関数従属性
部分関数従属性とは、ある非キー属性が、候補キーのー部に関数従属している場合のことを指します。
例えば顧客が店舗で購入した商品を管理する下テーブルがあるとします。
| 店舗名 | 購入者名 | 商品 | 価格 | 購入数 |
|---|---|---|---|---|
| 新宿店 | 田中 | うまい棒 | 10 | 2台 |
| 新宿店 | 田中 | ノート | 150 | 2台 |
| 池袋店 | 田中 | 加湿器 | 2200 | 2台 |
このテーブルの候補キーは下線が引いてある（店舗名,購入者名,商品）です。
上記テーブルには価格カラムがありますが、これは「うまい棒はいくらか？」と聞かれれば「10円」とように、商品の価格は商品ごとに決まっています。
上記の例では価格は商品に関数従属しています。
商品はこのテーブルの候補キー{店舗名,購入者名,商品}の一部です。
つまり、価格は候補キーの一部である商品に部分関数従属しています。
推移関数従属性
これは、あるテーブルの非キー属性Aが特定の非キー属性Bに関数従属しているような場合のことを指します。
なぜ推移関数従属性と呼ぶかというと、
上記においてはA自体も非キー属性であるため、第2正規系が適用されている前提上テーブルのなにかしらの候補キーXに関数従属しています。
※第3正規系の手順を適用するには、対象テーブルがすでに第2正規系となっている必要があります。
それを踏まえると、
X → A → B
のような関係となっており、Bは、Xが決まればAが決まりBが決まる、というようにBはXに対して間接的に従属していることとなります。
このような関係を推移という表現がされています。
正規化の手順
第1正規系
第1正規系の条件は、テーブルで表現できる形になっていることです。
つまり一つのカラムに1つの値のみ設定されている状態のことです。
テーブルという形で表現出来ていれば、概ね第1正規系の条件は満たしているという理解で大丈夫です。
※厳密にいうと第1正規系の条件は小難しいのですが、本記事では単純化し説明を省きます。
逆に第1正規系でない場合とは、例えばエクセルなどではセルの結合などによって一行の特定列（カラム）に複数の値を持たせているようなデータで、テーブルではこのようなデータ表現はできません。
※カラムにカンマ区切りなどでデータをいれるなどすればむりやり表現することはできますが、意味合いとしては一つのカラムに複数の値が設定されているのと同義なので、この場合も第1正規系の条件を満たしていません。
第2正規系
第2正規系の条件は、テーブルのすべての候補キーにおいて部分関数従属性が存在しないことです。
下記
| 店舗名 | 購入者名 | 商品 | 価格 | 購入数 |
|---|---|---|---|---|
| 新宿店 | 田中 | うまい棒 | 10 | 3 |
| 新宿店 | 田中 | ノート | 150 | 2 |
| 池袋店 | 田中 | 加湿器 | 2200 | 1 |
は価格が商品に対して部分関数従属しているので、これを
| 店舗名 | 購入者名 | 商品 | 購入数 |
|---|---|---|---|
| 新宿店 | 田中 | うまい棒 | 3 |
| 新宿店 | 田中 | ノート | 1 |
| 池袋店 | 田中 | 加湿器 | 2 |
| 商品 | 価格 |
|---|---|
| うまい棒 | 10 |
| ノート | 150 |
| 加湿器 | 2200 |
の２つのテーブルに分割して、部分関数従属性を排除します。
第2正規系は要は、候補キーの一部→非キー属性の関数従属を排除します。
第3正規系
第3正規系の条件は、テーブル内の非キー属性→非キー属性の関数従属、つまり推移関数従属性が存在しないことです。
例えばクラスの担任を管理する下記テーブルにおいて
| クラス | 担任 | 経験年数 |
|---|---|---|
| 2-A | 田中 | 3 |
| 1-C | 鈴木 | 11 |
このテーブルの候補キーは{クラス}ですが、担任の業務経験年数は担任の教師が決まればわかります。
つまり非キー属性の経験年数は、非キー属性の担任に依存しています。
これを
| クラス | 担任 |
|---|---|
| 2-A | 田中 |
| 1-C | 鈴木 |
| 担任 | 経験年数 |
|---|---|
| 田中 | 3 |
| 鈴木 | 11 |
という形でテーブルを分割し、非キー属性間の関数従属（推移関数従属性）を排除します。
第3正規系は要は、非キー属性→非キー属性の関数従属を排除します。
その他の正規系の手順について
前述の通り業務においては行う機会が少ないため、説明は省きますが、どういったものなのか、なぜあまりやらないのかを軽く触れておきます。
BC正規系
BC正規系ではざっくりいうと非キー→候補キーへの関数従属を排除します。
BC正規系は実は無損失分解ではない分解をしてしまう恐れがあるため、敢えてBC正規系を行わない場合もあります。
第4、第5正規系
こちらの正規系は今回扱わなかった結合従属性というもの取り除きます。
殆どの場合は、第3まで正規化すると自動的に第5正規系まで満たしているため、実務で行う機会はあまりないでしょう。
実は、第4、第5正規系への対応が必要となるのはテーブルのカラムに非キー属性が存在しない場合のみ、つまりテーブルのカラムがすべてキーとなっている場合のみです。
第6正規系
第6正規系は理論として存在しますが、実運用上ここまで正規化するメリットはないため、本記事では扱いません。
終わりに
本記事は正規化の要点をつかむということで、あえて内容を簡略化したり、馴染みのある単語を使うように意識してみました。
正規化はRDB理論上の概念なので、正規化を学ぼうとするとどうしてもRDB理論の馴染みのない単語が出てきます。
そこで、本記事は正規化そのものの要点に集中できるよう、どうしても必要なもの以外はなるべく馴染みのある単語を使うように心がけました。
参考書籍
理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)
正規化を含め、データべースを理論から学ぶには非常におすすめな本です。