正規化
正規化はなぜ必要なのか
以下のような生徒とその生徒が受ける授業を管理するためのテーブルがあったとする。
| id | student | homeroom | class1 | class2 | class3 |
|---|---|---|---|---|---|
| 0 | 田中太郎 | 2-4 | 国語 | 英語 | 数学 |
| 1 | 鈴木次郎 | 3-5 | 英語 | 物理 | 数学 |
テーブルの問題点
このテーブルには問題点が2つある。
- 重複する情報が存在する
- エンティティの属性の中に他のエンティティの情報が含まれる
重複する情報が存在する
生徒名、科目、生徒のクラスの情報が重複している。1人の生徒に対して、複数の科目が繰り返されている。生徒が試験を受けるほど、生徒の成績だけでなく生徒の情報も増えていくので非効率なデータベースになってしまう。
エンティティの属性の中に他のエンティティの情報が含まれる
生徒の情報と成績の情報が含まれている。この状態ではある生徒の成績やある科目の試験を受けた生徒のような情報を抽出したい時に苦労する。
これらの問題を解決することが出来るのが正規化である。
正規化の順番
正規化には順番があり、その順番に応じて形が定義されている。ここでは以下の非正規形のテーブルを対象に正規化を進めながら代表的な2つの正規形の特徴を見ていく。
第一正規形
第一正規形の原則は、テーブル内に繰り返されるレコード（例ではclassレコード）をなくすことである。
先ほどの例では、classというレコードが繰り返されていた。第一正規化に従って、繰り返されるレコードをなくすと以下のようなテーブルになる。
| id | student | homeroom | class |
|---|---|---|---|
| 0 | 田中太郎 | 3-2 | english |
| 0 | 田中太郎 | 3-2 | science |
| 0 | 田中太郎 | 3-2 | math |
| 1 | 鈴木次郎 | 3-3 | japanese |
| 1 | 鈴木次郎 | 3-3 | japanese history |
| 1 | 鈴木次郎 | 3-3 | world history |
第三正規形
第二正規形と第三正規形は非常に似ているが、その違いには複雑な概念が入ってくるので第二正規形を割愛する。
第三正規形の原則は、複数のレコードで重複している内容を別のテーブルとして管理することである。
先ほど、第一正規形を行ったテーブルには生徒の属性と授業の属性が含まれているので、属性ごとにテーブルを分けると以下のようなテーブルになる。
◼︎生徒テーブル
| id | name | homeroom |
|---|---|---|
| 0 | 田中太郎 | 3-2 |
| 1 | 鈴木次郎 | 3-3 |
◼︎授業テーブル
| name | student_id |
|---|---|
| english | 0 |
| science | 0 |
| math | 0 |
| japanese | 1 |
| japanese history | 1 |
| world history | 1 |
正規化のデメリットと第四正規形以降について
正規化の手順としては第三正規形以降に、ボイスコッド正規形、第四正規形、第五正規形が続く。しかし、実際の現場では第三正規形までで正規化を終えることがほとんど。
これは、正規化のデメリットと関係している。
実は正規化は手順を進めるほどに、パフォーマンスが低下する。なぜなら小さなテーブルが増えていくため。テーブルが増えれば関連するテーブルを検索するたびにSQLを実行する回数が増え、速度が低下する。そこで正規形は必要最低限のレベルである第三正規形で終えることが多い。
データベース設計実践
以下の非正規化テーブルを正規化する。
| id | student | class |
class _teacher |
lesson1 | lesson2 |
lesson1 _score |
lesson2 _score |
|---|---|---|---|---|---|---|---|
| 0 | 加藤貴大 | 2-4 | 石川正義 | english | math | 74 | 59 |
| 1 | 木下拓也 | 3-1 | 大関功 | japanese | science | 79 | 83 |
| 2 | 吉田弘人 | 3-3 | 上野真斗 | japanese | math | 93 | 53 |
| 3 | 水野智 | 2-4 | 石川正義 | math | english | 75 | 86 |
回答
正規化を行うこと、以下のような5つのテーブルに分かれた。重複している内容を別のテーブルに分けることで、生徒のレコードで担任や試験を受けた科目とその点数などを管理せずに済む。
◼︎studentsテーブル
生徒を管理するテーブル。クラスとリレーションしている。生徒のレコードを追加するたびにクラス名を文字列で入れる必要がなくなる。
| id | name | homeroom_id |
|---|---|---|
| 0 | 加藤貴大 | 0 |
| 1 | 木下拓也 | 1 |
| 2 | 吉田弘人 | 2 |
| 3 | 水野智 | 0 |
◼︎teacherテーブル
教師を管理するテーブル。クラスを外部キーとして持つことで、生徒のレコードが追加されるたびに担任名を文字列で入れる必要がなくなる。
| id | name | homeroom_id |
|---|---|---|
| 0 | 石川正義 | 0 |
| 1 | 大関功 | 1 |
| 2 | 上野真斗 | 2 |
◼︎homeroomsテーブル
クラスを管理するテーブル。studentsテーブル、teachersテーブルとリレーションを組むことで誰がどのクラスなのかが把握できるようになる。
| id | name |
|---|---|
| 0 | 2-4 |
| 1 | 3-1 |
| 2 | 3-3 |
◼︎subjectsテーブル
科目のテーブル。生徒がテストを受けるたびに生徒のレコードにカラムを追加する必要がなくなる。
| id | name |
|---|---|
| 0 | english |
| 1 | japanese |
| 2 | math |
| 3 | science |
◼︎scoresテーブル
試験の点数のテーブル。生徒と科目を外部キーに持つことでどの生徒がどの科目の試験を受けて何点だったかが確認できるようになる。
| id | student_id | subject_id | score |
|---|---|---|---|
| 0 | 0 | 0 | 74 |
| 1 | 0 | 2 | 59 |
| 2 | 1 | 1 | 79 |
| 3 | 1 | 3 | 83 |
| 4 | 2 | 1 | 93 |
| 5 | 2 | 2 | 53 |
| 6 | 3 | 2 | 75 |
| 7 | 3 | 0 | 86 |
