SQL#12 データモデルの設計と正規化の実践

はじめに
学習の概要
実践
ER 図の設計
非正規化の考え方
1. ❌ 例: JOINが増えてパフォーマンス低下
2. ✅ 例: 一部非正規化
課題
まとめ

はじめに

こんにちは、シルです。
データベース設計は、システムのパフォーマンスや保守性に大きな影響を与えます。本記事では、 正規化の基礎から高度なデータモデリングまでを実践し、効率的なデータベースを構築する方法 を学びます。

正規化（Normalization）とは？
- データの冗長性を排除し、整合性を維持するための設計手法
- 第1正規形（1NF）、第2正規形（2NF）、第3正規形（3NF）を中心に解説
非正規化のメリットとデメリット
- データの取得速度向上のための手法
- パフォーマンスとデータ整合性のトレードオフ

前回までの内容です
SQL#1 環境準備と基本操作
 SQL#2 テーブルとデータベースの管理
 SQL#3 ユーザー管理と権限の設定
 SQL#4 サーバーステータスとインデックス管理
 SQL#5 データベースのサイズ管理と構造確認
 SQL#6 基本的なデータ操作
 SQL#7 データ検索と削除の拡張
 SQL#8 ウィンドウ関数を使ったデータ分析
 SQL#9 集計と結合
 SQL#10 インデックス管理と最適化
 SQL#11 バックアップとリカバリ

学習の概要

第1～第3正規形への正規化
複数のER図（エンティティ関係図）を用いた設計
非正規化とクエリパフォーマンスのバランスを考慮した設計

実践

正規化の基本

正規化を適用することで、データの冗長性を減らし、整合性を向上させることができます。

第1正規形（1NF）

ルール：各列の値が 単一の値 である（リストや配列を含まない）

❌ 悪い例（非正規化）

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    Items VARCHAR(255) -- 複数の商品がカンマ区切りで保存されている
);

✅ 改善後（1NF）

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(255)
);

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ItemName VARCHAR(255),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

💡 ポイント
➡ 1つのテーブルに「リスト」や「配列」を持たないように分割する。

第2正規形（2NF）

ルール：部分関数従属性を排除（主キーの一部のみに依存する列を分離）

❌ 悪い例（1NF のみ）

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ItemName VARCHAR(255),
    SupplierName VARCHAR(255), -- これは ItemName のみに依存している
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

✅ 改善後（2NF）

CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(255),
    SupplierName VARCHAR(255)
);

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ItemID INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ItemID) REFERENCES Items(ItemID)
);

💡 ポイント
➡ 注文と商品を分離し、商品ごとの情報を Items テーブルに移動。

第3正規形（3NF）

ルール：推移的従属性を排除（主キーに直接関連しない列を分離）

❌ 悪い例（2NF のみ）

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    City VARCHAR(255),
    ZipCode VARCHAR(10) -- ZipCode から City を特定できる
);

✅ 改善後（3NF）

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    ZipCode VARCHAR(10)
);

CREATE TABLE ZipCodes (
    ZipCode VARCHAR(10) PRIMARY KEY,
    City VARCHAR(255)
);

💡 ポイント
➡ 顧客テーブルから City を削除し、郵便番号と都市の関係を ZipCodes テーブルに分離。

ER 図の設計

正規化されたデータを ER 図（エンティティ関係図） で表現し、視覚的に理解できるようにします。

例：顧客、注文、商品の関係

Customers (CustomerID) 1 - N (Orders) N - 1 (OrderItems) N - 1 (Items)

➡ 顧客が複数の注文を持ち、各注文には複数の商品が含まれる。

Customers とOrdersは、1 対多（1以上）
OrdersとOrderItemsは、多（1以上）対 1
OrderItemsとItemsは、多（1以上）対 1

非正規化の考え方

正規化を進めるとデータの整合性は向上しますが、パフォーマンスに悪影響を与えることがあります。

❌ 例: JOINが増えてパフォーマンス低下

SELECT Orders.OrderID, Customers.CustomerName, Items.ItemName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
JOIN Items ON OrderItems.ItemID = Items.ItemID;

➡ テーブル分割が多すぎると、JOIN回数が増えて遅くなる。

✅ 例: 一部非正規化

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CustomerName VARCHAR(255), -- 非正規化（Customersテーブルからデータをコピー）
    ItemName VARCHAR(255), -- 非正規化（Itemsテーブルからデータをコピー）
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

➡ 一部の情報を冗長に持たせて、パフォーマンス向上

課題

📌 実践課題

顧客、注文、在庫管理システムの正規化を行い、適切なテーブル設計をする
正規化後のテーブルを作成し、実際にデータを挿入
非正規化した場合のパフォーマンス比較
- JOIN を多用するクエリと、非正規化した場合の SELECT の実行速度を比較

まとめ

✅ 正規化（1NF、2NF、3NF）を適用し、データの整合性を確保
✅ ER 図を活用し、適切なリレーションを設計
✅ 非正規化のメリット・デメリットを理解し、パフォーマンス向上を考慮

データベース設計は パフォーマンスと保守性のバランスが重要 です。
実際の業務で適用できるよう、設計 → 実装 → テストを繰り返しながら学習しましょう！