Zenn
🔥

正規化おさらいしてみた

2025/03/06に公開
RDB
db設計
正規化
データベース設計
tech

はじめに

普段の業務などでテーブル設計をするときに、なんとなくいい感じにカラムを分けていました。
あの作業には正規化という名前がついていたことを思い出したのでおさらいしてみました。

正規化とは

正規化とは

  • データの重複を排除し、整合性を保つ
  • データの整理・管理を効率化し、保守性や利便性を向上させる
  • データの追加・更新・削除時に生じる不整合を防ぐ

ということらしい。喜ばしいですね。

正規化は第1正規化から第5正規化まであり、今回は第1正規化から第3正規化を順番に見ていきます。

第1正規化

第1正規化のルール

  1. 各データがスカラ値(これ以上分割できない値)であること
  2. 繰り返しグループ(同じ種類のデータが複数の列に分散)の排除

下記の学生情報テーブルを例に考えてみます。

学生ID 学生名 電話番号 履修科目1 履修科目2
1 山田太郎 090-1234-5678, 090-9999-9999 プログラミング基礎 データベース論
2 佐藤花子 080-8765-4321, 080-0000-0000 統計学 プログラミング基礎

学生テーブルは正規化が行われておらず以下のような問題を抱えています。

  • 電話番号は1つの列に複数の値が入っており、検索や更新の複雑度が高い
    • ルール1に反しています。
  • メールアドレスは同じ種類のデータですが履修科目1, 履修科目2と繰り返しが行われており、1科目しか履修しない学生の場合は無駄な列になり、3科目以上履修する学生に対応できない
    • ルール2に反しています。

それでは第1正規化していきましょう。

  1. 1列に複数の値を持っていた電話番号のデータを行で管理する。
学生ID 学生名 電話番号
1 山田太郎 090-1234-5678
1 山田太郎 090-9999-9999
2 佐藤花子 080-8765-4321
2 佐藤花子 080-0000-0000
  1. 履修科目テーブルを作成して、履修科目1, 履修科目2と繰り返しが行われていた列を1つにする。
学生ID 学生名 履修科目
1 山田太郎 プログラミング基礎
1 山田太郎 データベース論
2 佐藤花子 プログラミング基礎
2 佐藤花子 統計学

第1正規化が行われたことによって
電話番号が「090-1234-5678」の生徒を検索するクエリがこのように変化しました。

正規化前

SELECT * FROM 学生情報テーブル WHERE 電話番号 LIKE '%090-1234-5678%';

正規化後

SELECT * FROM 学生情報テーブル WHERE 電話番号 = '090-1234-5678';

LIKE演算で%を使用するとINDEXが効かない、データ取得後に電話番号のデータを分割しないといけないなどの問題が解消されました。

履修科目で「プログラミング基礎」を履修する生徒を検索する場合も以下のように変化しました。

正規化前

SELECT * FROM 履修科目テーブル WHERE 履修科目1 = 'プログラミング基礎' OR 履修科目2 = 'プログラミング基礎';

正規化後

SELECT * FROM 履修科目テーブル WHERE 履修科目 = 'プログラミング基礎';

検索条件がシンプルになり、データ取得後に履修科目1と履修科目2のどちらにプログラミング基礎が含まれるのかを考慮する必要がなくなりました。

第2正規化

第2正規化のルール

  1. 第1正規形である（スカラ値である、繰り返し項目がない）
  2. 部分関数従属がない

下記の学生情報テーブルを例に考えてみます。

学生ID 学生名 電話番号
1 山田太郎 090-1234-5678
1 山田太郎 090-9999-9999
2 佐藤花子 080-8765-4321
2 佐藤花子 080-0000-0000

学生情報テーブルは第2正規化が行われておらず以下のような問題を抱えています。

  • 同じ情報（学生名）が複数回繰り返されており、データの冗長性がある
  • データの更新時に矛盾が発生する可能性がある
    • 山田太郎の名前が変わった時に電話番号が090-1234-5678のレコードしか更新されない
  • 電話番号を持たない生徒は存在できない

それでは第2正規化していきましょう。

ルール1の第1正規形は満たしているため、ルール2の部分関数従属に着目して正規化を行なっていきます。

学生名は 主キー「学生ID + 電話番号」に部分関数従属しているためテーブルを分割します。

  1. 学生情報テーブルには学生IDと学生名のみ存在させる
学生ID 学生名
1 山田太郎
2 佐藤花子
  1. 電話番号テーブルを作成し、学生名を別のテーブルに移動させ部分関数従属を排除する
学生ID 電話番号
1 090-1234-5678
1 090-9999-9999
2 080-8765-4321
2 080-0000-0000

第2正規化が行われたことによって
繰り返されていた学生名が1つのレコードで管理されるようになり、データの冗長性が減少し、学生名の更新時の整合性も保たれるようになりました。
また、テーブルを分割したことで電話番号を持たない学生も学生情報テーブルに登録できるようになりました。

第3正規化

第3正規化のルール

  1. 第1正規形である（スカラ値である、繰り返し項目がない）
  2. 第2正規形である（部分関数従属がない）
  3. 推移的関数従属がない

下記の学生情報テーブルを例に考えてみます。

学生ID 学生名 学部ID 学部名 学部長名
1 山田太郎 101 経済学部 山田一郎
2 佐藤花子 102 法学部 佐々木二郎
3 鈴木正一 102 法学部 佐々木二郎

学生情報テーブルは第3正規化が行われておらず以下のような問題を抱えています。

  • 同じ情報（学部名、学部長名）が複数回繰り返されており、データの冗長性がある
  • データの更新時に矛盾が発生する可能性がある
    • 学部長の名前が変わった時に特定のレコードしか更新されない
  • 新しい学部「医学部」を追加したいが、まだ学生がいない場合に「医学部」を登録できない
  • 学生ID:1のレコードを削除すると経済学部の情報が消えてしまう
    • 学部の情報が学生の情報に依存しているため

それでは第3正規化していきましょう。

ルール1の第1正規形は満たしており、ルール2の第2正規形については学生IDが単独の主キーのため部分関数従属は発生していません。
ルール3の推移的関数従属に着目して正規化を行なっていきます。

学部名、学部長名は学生IDに推移的関数従属しているためテーブルを分割します。

  1. 学生情報テーブルには学部名、学部長名を含めず学部IDを学部キーとして保持するようにする
学生ID 学生名 学部ID
1 山田太郎 101
2 佐藤花子 102
3 鈴木正一 102
  1. 学部テーブルを作成し、学部IDを主キーとして学部名、学部長名を管理することで推移的関数従属を排除する
学部ID 学部名 学部長名
101 経済学部 山田一郎
102 法学部 佐々木二郎

第3正規化が行われたことによって
学生情報テーブルに重複して出てきていた学部名や学部長名が学部テーブルで管理され
データの冗長性が減少し、学部名や学部長名の更新時の整合性も保たれるようになりました。
また、学部の情報が学生情報に依存していないため、新しい学部の追加が行えるようになり
学生情報テーブルから学部に属している学生の情報が消えても学部そのものが消えることが無くなりました。

おわりに

第1正規化から第3正規化までおさらいしました。
テーブル設計をするときに経験則やアプリケーションでのデータの扱い方を考えてカラムの定義をしていると、結果的に第n正規形の形をとっていることがありました。（1カラムに複数の電話番号を突っ込もうと考えることはそうそうないはず）
今回改めて正規化のルールを確認することで、データの整合性を守ることや冗長性を減らすことの重要性も再確認できました。

残りの第4正規化、第5正規化、ボイス・コッド正規化（聞いたことなかった）についても理解を深めて
雰囲気で設計しちゃうのやめよ！！と思いました。

Discussion