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

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

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

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

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

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

例:
```mermaid
erDiagram
    ユーザー ||--o{ 注文 : "行う"
    注文 }o--o{ 商品 : "含む"
    ユーザー {
        int ユーザーID
        string 名前
        string メールアドレス
        string パスワード
    }
    商品 {
        int 商品ID
        string 商品名
        int 価格
        int 在庫数
    }
    注文 {
        int 注文ID
        int ユーザーID
        datetime 注文日時
        int 合計金額
    }
```

## 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. ビュー定義（必要に応じて）
- 複雑なクエリを簡略化するためのビューを定義する
- ビューの目的と定義を明記する

例:
- `注文詳細` ビュー:
  - 目的: 注文情報と商品情報を結合し、注文の詳細を表示する
  - 定義:
    ```sql
    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. 変更管理
- 設計変更の手順と承認プロセスを定義する
- バージョン管理とドキュメントの更新方法を明記する