Posted inデータベース

データベースの正規化：「ゴッドテーブル」からクリーンアーキテクチャへ（第1正規形から第3正規形まで）

Database tutorial - IT technology blog
Database tutorial - IT technology blog

問題点：「ゴッドテーブル」という悪夢

開発者なら誰もが一度は「ゴッドテーブル」を作ってしまうものです。これは、あらゆる情報を一度に管理しようとする巨大な単一のエンティティのことです。最初は大学のデータを管理するために、学生名、住所、コース名、教授、研究室の場所などを保存する Student_Courses という名前の単純なスプレッドシートのようなテーブルから始めるかもしれません。最初は効率的に思えますが、データが1,000行を超えたあたりから、その設計は崩壊し始めます。

学生の住所を更新するだけで、15もの異なる行を検索して置換するスクリプトが必要になってはいけません。もし教授が研究室を移動した場合、たった1つのレコードでも更新し忘れると「真実のソース（Source of Truth）」に矛盾が生じます。さらに, このテーブルではすべてのエントリに学生IDが必要なため、学生が一人も履修登録していない新しいコースをシステムに追加することすらできません。これらの障害は、更新異常や挿入異常と呼ばれます。

PostgreSQL、MySQL、SQL Serverのいずれを使用している場合でも、正規化はホビープロジェクトとプロフェッショナルな本番システムを分かつ重要なスキルです。正規化とは、データの冗長性を排除し、すべての情報が正確に1か所だけに存在するようにデータ構造を洗練させるプロセスのことです。

出発点：非正規化テーブル

プロトタイプの初期段階でよく見られる、問題のある構造を考えてみましょう。

-- 脆弱な設計（非正規化）
| StudentID | Name   | Courses                      | Professor       | Prof_Office |
|-----------|--------|------------------------------|-----------------|-------------|
| 101       | アリス  | CS101 (Java), CS102 (SQL)    | スミス教授       | 401号室      |
| 102       | ボブ    | CS101 (Java)                 | スミス教授       | 401号室      |
| 103       | チャーリー| CS103 (Python)               | ブラウン教授     | 502号室      |

このテーブルは基本的なリレーショナル基準を満たしていません。Courses カラムに複数の値が含まれているため、複雑な文字列解析を行わずに履修者数を単純に COUNT() することすらほぼ不可能です。これを体系的に修正していきましょう。

第1正規形 (1NF)：原子価

1NFの目的は、リストを排除し、すべてのセルが分割不可能（原子値）であることを保証することです。1つのカラム内にセットやカンマ区切りの文字列を保存することはできません。また、すべてのレコードは主キー（Primary Key）によって一意に識別できる必要があります。

1NFのチェックリスト：

  • 各セルは正確に1つの値のみを含むこと（原子性）。
  • テーブルに主キーが存在すること。
  • 繰り返しのカラムグループ（例：Course1, Course2, Course3）がないこと。

1NFを達成するために、Courses カラムを分割し、各行が1つの学生とコースのペアを表すようにします。次に、StudentID と CourseID を使用して複合主キーを定義します。

-- 1NFのテーブル：原子値だが冗長
| StudentID | Name   | CourseID | CourseName | Professor | Prof_Office |
|-----------|--------|----------|------------|-----------|-------------|
| 101       | アリス  | CS101    | Java       | スミス教授 | 401号室      |
| 101       | アリス  | CS102    | SQL        | スミス教授 | 401号室      |
| 102       | ボブ    | CS101    | Java       | スミス教授 | 401号室      |
| 103       | チャーリー| CS103    | Python     | ブラウン教授 | 502号室      |

データは原子化されましたが、冗長性が目立ちます。アリスの名前が2回保存されています。もし彼女が名前を変更した場合、行を更新し忘れるリスクがあります。この非効率性が、次の段階へと導きます。

第2正規形 (2NF)：部分関数従属性の排除

1NFを満たし、かつ、すべての非キー属性（主キー以外のカラム）が主キーの全体に従属している場合、そのテーブルは2NFとなります。現在のテーブルの主キーは (StudentID, CourseID) です。

考えてみてください。Name はコースに依存していますか？いいえ、StudentID だけに依存しています。CourseName は学生に依存していますか？いいえ、CourseID だけに依存しています。これらは「部分関数従属性」と呼ばれます。これを解決するために、データを3つの独立したエンティティに分割します。

2NFへの分解：

テーブル：Students（個人の識別に焦点を当てる）

| StudentID | Name   |
|-----------|--------|
| 101       | アリス  |
| 102       | ボブ    |
| 103       | チャーリー|

テーブル：Courses（カリキュラムに焦点を当てる）

| CourseID | CourseName | Professor | Prof_Office |
|----------|------------|-----------|-------------|
| CS101    | Java       | スミス教授 | 401号室      |
| CS102    | SQL        | スミス教授 | 401号室      |
| CS103    | Python     | ブラウン教授 | 502号室      |

テーブル：Enrollments（関係性の構築）

| StudentID | CourseID |
|-----------|----------|
| 101       | CS101    |
| 101       | CS102    |
| 102       | CS101    |
| 103       | CS103    |

これで、アリスのプロフィールの更新は正確に1行だけで済むようになりました。しかし、Courses テーブルにはまだ欠陥があります。スミス教授の研究室の場所が、彼が担当するすべてのコースで繰り返されています。これが3NFが必要な理由です。

第3正規形 (3NF)：推移的関数従属性の排除

3NFでは、非キー属性は主キーのみに従属し、他の非キー属性には従属しないことが求められます。私たちの Courses テーブルでは、Prof_Office は Professor に依存していますが、Professor は主キーではありません。これは「推移的関数従属性」です。

もしスミス教授が10の異なるコースを担当している場合、彼の研究室の場所は10回保存されます。これを修正するために、教授の詳細を専用のテーブルに移動します。

最終的な3NFスキーマ：

テーブル：Professors

| ProfID | Professor | Prof_Office |
|--------|-----------|-------------|
| P1     | スミス教授 | 401号室      |
| P2     | ブラウン教授 | 502号室      |

テーブル：Courses

| CourseID | CourseName | ProfID |
|----------|------------|--------|
| CS101    | Java       | P1     |
| CS102    | SQL        | P1     |
| CS103    | Python     | P2     |

実践：SQLでの3NFの実装

論理モデルをデータ定義言語（DDL）に変換する際、多くの開発者がつまずきます。整合性を最大化するために外部キー（Foreign Key）を使用して、これらのルールを適用するSQL構造を以下に示します。

-- 1. 教授データを一度だけ保存する
CREATE TABLE Professors (
    ProfID VARCHAR(10) PRIMARY KEY,
    ProfessorName VARCHAR(100) NOT NULL,
    OfficeLocation VARCHAR(50)
);

-- 2. 学生プロフィールを整理する
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
);

-- 3. コースを教授に紐付ける
CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    ProfID VARCHAR(10),
    FOREIGN KEY(ProfID) REFERENCES Professors(ProfID)
);

-- 4. 多対多のリレーションシップのための中間テーブル
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

この構造により、存在しない教授にコースを割り当てることはできなくなります。また、ストレージのオーバーヘッドも削減されます。5万人の学生がいるシステムでは、こうした小さな変更によって、冗長な文字列データによる数百メガバイトの容量を節約できる可能性があります。

トレードオフ：いつ止めるべきか？

3NFはトランザクション処理（OLTP）システムの黄金律ですが、過度な正規化（Over-normalization）も起こり得ます。テーブルを分割するたびに、追加の JOIN 操作が必要になります。アプリケーションが15の異なるテーブルからデータを取得してダッシュボードを表示する必要がある場合、クエリのパフォーマンスが低下する可能性があります。

ほとんどのWebアプリケーションにおいて、3NFはデータの安全性と速度の最適なバランスを提供します。将来的にデータウェアハウス（OLAP）に移行する場合は、重い読み取り操作を高速化するために、データをフラットなテーブルに「非正規化（Denormalize）」することもあります。しかし、ほとんどの開発者にとって、クリーンな3NFスキーマから始めることが、データ整合性の危機を防ぐための最も安全な方法です。

まとめ

正規化は単なる情報科学の試験のための理論的な演習ではありません。メンテナンスや拡張が容易なソフトウェアを構築するための実践的な戦略です。1NF、2NF、3NFへと進むことで、ユーザー数が数十人から数百万人へと増加しても、予測可能性の高いデータベースを維持することができます。

Share:
Tags: