はじめに
DB設計をしていると、「正規化」という言葉が出てきます。
私自身も過去に正規化を意識してDB設計をしたことがありますが、しばらく経つと
- 第1正規形って何だっけ？
- 第2正規形と第3正規形の違いは？
- そもそも、なぜ正規化するんだっけ？
のようになっていたので、久しぶりに復習してみました。
正規化とは？
ざっくり言うと、
データの重複や不整合を減らすために、テーブルを適切に分割・整理すること
です。
例えば、注文情報を次のように管理していたとします。
| 注文ID | 顧客名 | 顧客住所 | 商品名 |
|---|---|---|---|
| 1 | 田中太郎 | 福岡市中央区 | ノートPC |
| 2 | 田中太郎 | 福岡市中央区 | マウス |
この状態で田中さんが引っ越すと、住所を複数のレコードで更新する必要があります。
もし更新漏れがあると、
| 注文ID | 顧客名 | 顧客住所 | 商品名 |
|---|---|---|---|
| 1 | 田中太郎 | 福岡市早良区 | ノートPC |
| 2 | 田中太郎 | 福岡市中央区 | マウス |
のように、同じ顧客なのに住所が違うという状態になってしまいます。
そこで、顧客情報と注文情報などを別々に管理します。
-
customers：顧客情報 -
orders：注文情報 -
products：商品情報
これが正規化の基本的なイメージです。
なお、理論上は第3正規形より先の正規形も定義されていますが、実際のDB設計では第3正規形までの考え方がよく使われます。
第1正規形
例えば、顧客の電話番号を次のように管理していたとします。
| 顧客ID | 顧客名 | 電話番号 |
|---|---|---|
| 1 | 田中太郎 | 090-XXXX-XXXX, 080-XXXX-XXXX |
| 2 | 佐藤花子 | 070-XXXX-XXXX |
この場合、田中さんの「電話番号」という1つのセルに、2つの電話番号が入っています。
このような構造だと、特定の電話番号を検索したり、電話番号を1件ずつ扱ったりするのが難しくなります。
そこで、電話番号を1つずつ別のレコードとして管理します。
| 顧客ID | 顧客名 | 電話番号 |
|---|---|---|
| 1 | 田中太郎 | 090-XXXX-XXXX |
| 1 | 田中太郎 | 080-XXXX-XXXX |
| 2 | 佐藤花子 | 070-XXXX-XXXX |
このように、1つのセルには1つの値だけを入れるようにします。
第1正規形は、
1つのセルに複数の値を入れない
と覚えておけばOKです。
第2正規形
例えば、学生の履修情報を次のように管理しているとします。
このテーブルでは、学生IDと授業IDを複合主キーとして組み合わせることで、「誰がどの授業を履修したのか」を特定できます。
| 学生ID | 授業ID | 学生名 | 授業名 | 成績 |
|---|---|---|---|---|
| 1 | A | 田中太郎 | 数学 | 優 |
| 1 | B | 田中太郎 | 英語 | 良 |
| 2 | B | 佐藤花子 | 英語 | 可 |
ここで、学生名に注目してみます。
| 学生ID | 授業ID | 学生名 |
|---|---|---|
| 1 | A | 田中太郎 |
| 1 | B | 田中太郎 |
| 2 | B | 佐藤花子 |
同じ学生IDの行では、学生名も同じです。
つまり、学生名を管理するために授業IDは必要ありません。
授業名も同じです。
| 学生ID | 授業ID | 授業名 |
|---|---|---|
| 1 | A | 数学 |
| 1 | B | 英語 |
| 2 | B | 英語 |
同じ授業IDの行では、授業名も同じです。
つまり、授業名を管理するために学生IDは必要ありません。
このように、複合主キーの一部だけで決まるデータがある状態を「部分関数従属」といいます。
そこで、学生・授業・履修をそれぞれ分けます。
-
studentsidname
-
coursesidname
-
enrollmentsstudent_idcourse_idgrade
こうすることで、学生名や授業名を履修情報のたびに持たせる必要がなくなります。
第2正規形は、
複合主キーの一部だけで決まるデータがあれば分離する
と覚えておけばOKです。
第3正規形
例えば、社員情報を次のように管理しているとします。
このテーブルでは、社員IDを主キーとします。
| 社員ID | 社員名 | 部署ID | 部署名 |
|---|---|---|---|
| 1 | 田中太郎 | D01 | 営業部 |
| 2 | 佐藤花子 | D02 | 開発部 |
| 3 | 山田一郎 | D01 | 営業部 |
部署名に注目してみます。
部署ID = D01なら「営業部」、部署ID = D02なら「開発部」です。
つまり、部署名は社員IDから直接決まるのではなく、部署IDによって決まる情報です。
そのため、社員情報の中に部署IDと部署名の両方を持たせる必要はありません。
このように、部署名が主キーである社員IDから直接決まるのではなく、部署IDを介して決まる関係を「推移的関数従属」といいます。
そこで、社員と部署をそれぞれ分けます。
-
employeesidnamedepartment_id
-
departmentsidname
こうすることで、部署名を社員情報の中に繰り返し持たせる必要がなくなります。
第3正規形は、
主キー以外のカラムを介して決まるデータを分離する
と覚えておけばOKです。
正規化しすぎればいいわけではない
正規化の目的は、とにかくテーブルを細かく分割することではありません。
データの重複や不整合を減らしつつ、扱いやすい構造にすることが目的です。
一方で、読み取り性能やクエリの複雑さなどを考慮して、あえてデータを重複して持つ非正規化を行うこともあります。
そのため実際のDB設計では、「どこまで正規化するか」 を考えることになります。
まとめ
- 第1正規形 → 1セル1データ
- 第2正規形 → 部分関数従属をなくす
- 第3正規形 → 推移的関数従属をなくす