DB設計について考えてみた

データベース設計書作成ガイドライン

1. 概要

• システムの目的と要件を明確に記述する
• データベースの役割と範囲を説明する

2. エンティティの特定

• システムで扱うエンティティ（実体）を洗い出す
• エンティティの定義と属性を明確にする
• エンティティ間の関係を特定する

例:

• ユーザー: システムを利用する人
  • 属性: ユーザーID、名前、メールアドレス、パスワード
• 商品: システムで販売する商品
  • 属性: 商品ID、商品名、価格、在庫数
• 注文: ユーザーが商品を購入する行為
  • 属性: 注文ID、ユーザーID、注文日時、合計金額

3. ER図の作成

• エンティティ、属性、関係を表現するER図を作成する
• ER図の表記法（Chen表記、IDEF1X、Crow's Foot表記など）を選択する
• エンティティ、属性、関係に適切な名前を付ける
• カーディナリティ（1対1、1対多、多対多）を明示する

例:

4. リレーショナルスキーマへの変換

• ER図をリレーショナルスキーマに変換する
• 正規化（第1正規形、第2正規形、第3正規形など）を適用する
• 主キー、外部キー、制約条件を定義する

例:

• ユーザー テーブル:
  • ユーザーID (主キー)
  • 名前
  • メールアドレス
  • パスワード
• 商品 テーブル:
  • 商品ID (主キー)
  • 商品名
  • 価格
  • 在庫数
• 注文 テーブル:
  • 注文ID (主キー)
  • ユーザーID (外部キー, ユーザー テーブルを参照)
  • 注文日時
  • 合計金額
• 注文明細 テーブル:
  • 注文明細ID (主キー)
  • 注文ID (外部キー, 注文 テーブルを参照)
  • 商品ID (外部キー, 商品 テーブルを参照)
  • 数量

5. テーブル定義

• 各テーブルの詳細な定義を記述する
• テーブル名、列名、データ型、制約条件を明記する
• 主キー、外部キー、ユニークキー、インデックスを指定する
• デフォルト値、NULL許容性を明示する

例:

• ユーザー テーブル:

  列名	データ型	制約条件
  ユーザーID	INT	主キー, AUTO_INCREMENT
  名前	VARCHAR(50)	NOT NULL
  メールアドレス	VARCHAR(100)	NOT NULL, UNIQUE
  パスワード	VARCHAR(100)	NOT NULL

• 商品 テーブル:

  列名	データ型	制約条件
  商品ID	INT	主キー, AUTO_INCREMENT
  商品名	VARCHAR(100)	NOT NULL
  価格	INT	NOT NULL
  在庫数	INT	NOT NULL, DEFAULT 0

• 注文 テーブル:

  列名	データ型	制約条件
  注文ID	INT	主キー, AUTO_INCREMENT
  ユーザーID	INT	外部キー (ユーザー テーブル)
  注文日時	DATETIME	NOT NULL
  合計金額	INT	NOT NULL

• 注文明細 テーブル:

  列名	データ型	制約条件
  注文明細ID	INT	主キー, AUTO_INCREMENT
  注文ID	INT	外部キー (注文 テーブル)
  商品ID	INT	外部キー (商品 テーブル)
  数量	INT	NOT NULL

6. ビュー定義（必要に応じて）

• 複雑なクエリを簡略化するためのビューを定義する
• ビューの目的と定義を明記する

例:

• 注文詳細 ビュー:
  • 目的: 注文情報と商品情報を結合し、注文の詳細を表示する
  • 定義:

    CREATE VIEW 注文詳細 AS
    SELECT
        注文.注文ID,
        注文.ユーザーID,
        注文.注文日時,
        注文明細.商品ID,
        商品.商品名,
        注文明細.数量,
        商品.価格,
        (注文明細.数量 * 商品.価格) AS 小計
    FROM
        注文
        INNER JOIN 注文明細 ON 注文.注文ID = 注文明細.注文ID
        INNER JOIN 商品 ON 注文明細.商品ID = 商品.商品ID;
    

7. インデックス戦略

• パフォーマンス向上のためのインデックス戦略を策定する
• インデックスを作成する列とインデックスの種類を指定する

例:

• ユーザー テーブル:
  • メールアドレス 列にユニークインデックスを作成（ログイン処理の高速化）
• 注文 テーブル:
  • ユーザーID 列に非ユニークインデックスを作成（ユーザーごとの注文検索の高速化）
• 注文明細 テーブル:
  • 注文ID 列に非ユニークインデックスを作成（注文ごとの明細検索の高速化）
  • 商品ID 列に非ユニークインデックスを作成（商品ごとの販売実績検索の高速化）

8. セキュリティと権限

• データベースのセキュリティ要件を明確にする
• ユーザーとロールを定義し、適切な権限を割り当てる

例:

• admin ユーザー:
  • すべてのテーブルに対する SELECT, INSERT, UPDATE, DELETE 権限を付与
• customer ユーザー:
  • ユーザー テーブルに対する SELECT, UPDATE 権限を付与（自分の情報のみ）
  • 商品 テーブルに対する SELECT 権限を付与
  • 注文 テーブルに対する SELECT, INSERT 権限を付与（自分の注文のみ）
  • 注文明細 テーブルに対する SELECT 権限を付与（自分の注文の明細のみ）

9. 物理設計の考慮事項

• データ容量の見積もりと成長予測を行う
• ハードウェア構成とストレージ要件を検討する
• バックアップとリカバリの戦略を策定する

10. 変更管理

• 設計変更の手順と承認プロセスを定義する
• バージョン管理とドキュメントの更新方法を明記する