4-3 データベース設計
Database Design
1. データベース設計の概要
1.1 設計の3段階
データベース設計は、概念設計、論理設計、物理設計の3段階で進められる。
| 段階 | 目的 | 成果物 |
|---|---|---|
| 概念設計 | 業務で扱うデータとその関係を定義 | 概念ER図 |
| 論理設計 | データモデルを正規化し構造化 | 論理ER図、テーブル定義 |
| 物理設計 | 性能・運用を考慮した実装設計 | DDL、インデックス定義 |
1.2 RDBMSとNoSQL
データベースの種類は大きく分けてRDBMS（リレーショナルデータベース）と NoSQLがある。要件に応じて適切なものを選択する。
| 観点 | RDBMS | NoSQL |
|---|---|---|
| データモデル | テーブル（行と列） | ドキュメント、キーバリュー、グラフなど |
| スキーマ | 固定スキーマ | スキーマレスまたは柔軟 |
| 整合性 | ACID準拠 | BASE（結果整合性） |
| スケーリング | 垂直（スケールアップ） | 水平（スケールアウト） |
| 適用 | トランザクション処理、複雑なクエリ | 大量データ、高スループット |
| 例 | PostgreSQL、MySQL、Oracle | MongoDB、Redis、Cassandra |
2. 概念設計
2.1 ER図の作成
ER図（Entity-Relationship Diagram）は、データのエンティティ（実体）と その関係を視覚的に表現する図である。
| 要素 | 説明 | 例 |
|---|---|---|
| エンティティ | 管理対象となるもの | 顧客、商品、注文 |
| 属性 | エンティティの特性 | 顧客名、商品価格 |
| 主キー | エンティティを一意に識別 | 顧客ID、商品コード |
| リレーションシップ | エンティティ間の関連 | 顧客が注文を行う |
| カーディナリティ | 関連の多重度 | 1:N、M:N |
2.2 カーディナリティの表記
ER図ではエンティティ間の関係性をカーディナリティで表す。
| カーディナリティ | 意味 | 例 |
|---|---|---|
| 1:1 | 一対一 | 社員と社員証 |
| 1:N | 一対多 | 部署と社員 |
| M:N | 多対多 | 学生と講座 |
RDBMSでは多対多（M:N）の関係は直接表現できないため、 中間テーブル（連関エンティティ）を導入して1:Nの関係に分解する。 例：学生-受講-講座
3. 論理設計と正規化
3.1 正規化とは
正規化は、データの冗長性を排除し、更新異常を防ぐための データモデルの構造化プロセスである。
3.2 正規形
正規化により、データの冗長性を排除し整合性を保つ。
| 正規形 | 条件 | 解消される問題 |
|---|---|---|
| 第1正規形（1NF） | すべての属性が原子値（繰り返し項目がない） | 繰り返しグループ |
| 第2正規形（2NF） | 1NF + 部分関数従属の排除 | 複合キーの一部への依存 |
| 第3正規形（3NF） | 2NF + 推移関数従属の排除 | 非キー属性間の依存 |
| ボイス・コッド正規形（BCNF） | すべての決定項が候補キー | 候補キー以外からの関数従属 |
3.3 正規化の例
非正規形の注文データを正規化する例：
| 段階 | テーブル構造 |
|---|---|
| 非正規形 | 注文（注文ID、顧客名、顧客住所、商品1、数量1、商品2、数量2...） |
| 1NF | 注文（注文ID、顧客名、顧客住所、商品名、数量）※繰り返し排除 |
| 2NF | 注文（注文ID、顧客ID）、注文明細（注文ID、商品ID、数量） |
| 3NF | 顧客（顧客ID、顧客名、住所）を分離 |
正規化を進めすぎると、テーブル数が増加しJOINが複雑になる。 性能要件によっては、意図的に非正規化（冗長性の許容）を行うこともある。 設計のトレードオフを理解して適切なレベルを選択すべきである。
4. 物理設計
4.1 テーブル定義
論理設計の結果を、具体的なテーブル定義（DDL）に落とし込む。
| 設計項目 | 考慮事項 |
|---|---|
| データ型 | 適切な型の選択（INTEGER、VARCHAR、DATEなど） |
| 桁数・長さ | 必要十分な桁数、将来拡張の余地 |
| NULL許容 | NOT NULL制約の設定 |
| デフォルト値 | DEFAULT句の設定 |
| 制約 | 主キー、外部キー、UNIQUE、CHECK |
4.2 インデックス設計
インデックスは検索性能を向上させるが、更新性能に影響する。 適切な設計が重要である。
| インデックス種類 | 特徴 | 適用 |
|---|---|---|
| B-Treeインデックス | 範囲検索、ソートに有効 | 一般的なカラム |
| ハッシュインデックス | 等価検索に高速 | 完全一致検索のみのカラム |
| 複合インデックス | 複数カラムを組み合わせ | 複合条件での検索 |
| カバリングインデックス | インデックスのみで結果を返す | 特定クエリの高速化 |
インデックス設計のポイント
WHERE句で頻繁に使用されるカラムにインデックスを作成する。 カーディナリティ（値の種類数）が高いカラムが効果的。 複合インデックスは左端のカラムから使用される。 更新が多いテーブルでは過剰なインデックスを避ける。
4.3 パーティショニング
大規模テーブルを複数の物理的な部分に分割する技法。
| パーティション種類 | 分割基準 | 適用例 |
|---|---|---|
| レンジパーティション | 値の範囲 | 日付による月次分割 |
| リストパーティション | 値のリスト | 地域コードによる分割 |
| ハッシュパーティション | ハッシュ値 | 均等な分散が必要な場合 |
5. データベース設計のベストプラクティス
5.1 命名規則
データベース設計では一貫した命名規則を定める。
| 対象 | 推奨規則 | 例 |
|---|---|---|
| テーブル名 | 単数形、スネークケース | customer、order_detail |
| カラム名 | 意味のある名前、略語を避ける | customer_name（× cust_nm） |
| 主キー | テーブル名_id | customer_id |
| 外部キー | 参照先テーブル名_id | customer_id（注文テーブル内） |
5.2 共通項目
多くのテーブルに共通して設ける項目。
| 項目 | 目的 |
|---|---|
| created_at | レコード作成日時 |
| updated_at | 最終更新日時 |
| created_by | 作成者 |
| updated_by | 更新者 |
| version | 楽観的ロック用バージョン |
| deleted_flag | 論理削除フラグ |
データベース設計を学んだら、次は「4-4 画面・API設計」で ユーザーインターフェースの設計について学習しよう。
[1] Date, C. J. (2003). An Introduction to Database Systems.
[2] Karwin, B. (2010). SQL Antipatterns.