【データモデリング】物理テーブル設計のチュートリアル

データモデリングにおいて、論理テーブル設計が完了したら、次のステップとして物理テーブル設計を行います。本記事では、ECサイトを題材にして、MySQL を使用した物理テーブル設計の手順を詳しく解説します。

1. シナリオの確認

ECサイトにおける主要な機能を整理します。

• 商品一覧の表示
• カートへの追加・削除
• 注文処理
• 決済処理

この機能をもとに、エンティティ抽出とER図を作成し、論理テーブル設計を行いました。これを基に物理テーブル設計を進めます。

2. 物理テーブル設計の考慮点

物理テーブル設計では、以下の観点を考慮して実装を進めます。

2.1 DB 製品特性・インフラ要件

• データベースの種類
  • MySQL, PostgreSQL, Oracle, SQL Server など、製品ごとに特性や制限が異なる。
  • 本記事では MySQL を前提とする。
• インフラ環境の違い
  • オンプレミスかクラウド（AWS RDS, GCP Cloud SQL など）かで運用が異なる。
  • クラウド環境では、自動バックアップやスケーリング機能を考慮。

2.2 具体的なデータ型マッピング（MySQL）

論理データ型	MySQL データ型	説明
VARCHAR	VARCHAR(n)	文字列（可変長）
INTEGER	INT	整数
DECIMAL	DECIMAL(p,s)	小数点を含む数値
DATE	DATE	日付
DATETIME	DATETIME	日時
BOOLEAN	TINYINT(1)	真偽値（MySQL では TINYINT を使用）

2.3 インデックス設計

• 主キー（Primary Key, PK）: AUTO_INCREMENT を設定して一意性を担保。
• 外部キー（Foreign Key, FK）: ON DELETE CASCADE などの制約を検討。
• 検索性能向上のためのインデックス
  • よく検索されるカラムに INDEX を設定。
  • ORDER BY でよく使用されるカラムには BTREE インデックスを検討。
  • 複数の検索条件を考慮して 複合インデックス を適用。

2.4 パーティショニング / シャーディング

• パーティショニング: 大量のデータを保持するテーブルは RANGE PARTITION や LIST PARTITION を適用。
• シャーディング: ユーザー数が多い場合、user_id を基準に水平分割を検討。

2.5 可用性・セキュリティ・運用設計

• レプリケーション / クラスタリング
  • MySQL の Master-Slave レプリケーションを設定し、読み込み負荷を分散。
• ユーザ権限
  • GRANT SELECT, INSERT, UPDATE ON database.* TO 'app_user'@'%' IDENTIFIED BY 'password';
• バックアップ / 監査ログ
  • mysqldump による定期バックアップ。
  • binlog を有効化し、データ変更履歴を保持。

3. 物理テーブル定義の例

3.1 User（ユーザー）

カラム名	データ型	制約	説明
user_id	INT	PK, AUTO_INCREMENT	ユーザーの一意識別子
username	VARCHAR(255)	NOT NULL	ユーザー名
email	VARCHAR(255)	UNIQUE, NOT NULL	メールアドレス（ナチュラルキー）
password	VARCHAR(255)	NOT NULL	パスワード
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	作成日時

3.2 Product（商品）

カラム名	データ型	制約	説明
product_id	INT	PK, AUTO_INCREMENT	商品の一意識別子
product_code	VARCHAR(100)	UNIQUE, NOT NULL	商品コード（ナチュラルキー）
name	VARCHAR(255)	INDEX, NOT NULL	商品名
price	DECIMAL(10,2)	NOT NULL	価格
stock	INT	NOT NULL	在庫数
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	作成日時

3.3 Cart（カート）

カラム名	データ型	制約	説明
cart_id	INT	PK, AUTO_INCREMENT	カートの一意識別子
user_id	INT	FK (User), NOT NULL	カート所有者
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	カート作成日時

3.4 CartItem（カート内商品）

カラム名	データ型	制約	説明
cart_id	INT	PK, FK (Cart)	カートの識別子
product_id	INT	PK, FK (Product)	カート内の商品
quantity	INT	NOT NULL	商品の個数

3.5 Order（注文）

カラム名	データ型	制約	説明
order_id	INT	PK, AUTO_INCREMENT	注文の一意識別子
user_id	INT	FK (User)	注文したユーザー
total_price	DECIMAL(10,2)	NOT NULL	合計金額
status	VARCHAR(50)	NOT NULL	注文ステータス（例: 確定, 発送済み）
order_date	DATETIME	DEFAULT CURRENT_TIMESTAMP	注文日時

3.6 OrderItem（注文内商品）

カラム名	データ型	制約	説明
order_id	INT	PK, FK (Order)	注文の識別子
product_id	INT	PK, FK (Product)	注文内の商品
quantity	INT	NOT NULL	商品の個数

3.7 Payment（決済）

カラム名	データ型	制約	説明
payment_id	INT	PK, AUTO_INCREMENT	決済の一意識別子
order_id	INT	FK (Order)	関連する注文
method	VARCHAR(50)	NOT NULL	決済方法（クレジットカード, 銀行振込など）
status	VARCHAR(50)	NOT NULL	決済ステータス（成功, 失敗など）
payment_date	DATETIME	DEFAULT CURRENT_TIMESTAMP	決済日時

※パーティショニング / シャーディング、可用性・セキュリティ・運用設計についてはこのチュートリアルでは設計しない。

4. まとめ

本記事では、ECサイトを題材に MySQL を使用した物理テーブル設計を行いました。

1. データベース製品特性とインフラ要件を考慮
2. 論理データ型を MySQL の物理データ型へマッピング
3. インデックス設計、パーティショニング、シャーディングの適用
4. 可用性・セキュリティ・運用設計を考慮
5. 実際の物理テーブル設計の例を表形式で記述

この設計をもとに、最適なクエリ設計やパフォーマンスチューニングを検討し、運用しやすいデータベースを構築しましょう。

---