はじめに
「あとでテーブルを直すのにすごく時間がかかった…」
「多対多ってどう表すんだっけ？」
「正規化が大事と言われたけど、どこまでやればいいの？」
データベース設計は、一見シンプルに見えて、実務では驚くほど奥が深い分野です。
特にER図・主キー／外部キー・多対多・正規化といった基礎は、知っているだけではなく「どう使い分けるか」 が重要になります。
本記事では、運送業務を題材に、
- ER 図の読み方・描き方
- 主キー・外部キー・多対多の関係整理
を、リレーショナルデータベース（RDB）を扱って図解と実例を交えて解説します。
目的は、「実務で迷わないための設計の考え方」を身につけることです。
対象読者 :
本記事は次のような方を対象としています。
- SQL を少し書いたことがあり、既存のテーブル構成は読める
- しかし、ゼロからテーブルを設計するのは不安
- ER 図はなんとなく分かるけど、どう描けばいいか分からない
以上のような業務システムで「最初の設計」を任されそうな人に向けた内容です。
データベース設計とは
データベース設計とは、業務で扱うデータを適切に管理・運用するための構造を設計する作業です。適切な設計を行うことで、データの整合性を保ちながら、効率的にデータを活用できるシステムを構築できます。
なぜデータベース設計が重要なのか
データベースは多くのアプリケーションから参照・更新されるため、設計の良し悪しがシステム全体に影響します。特に以下の理由から、初期段階での適切な設計が不可欠なのです。
- 変更コストが高い: 後からテーブル構造を変更すると、すべての関連アプリケーションの修正が必要
- データ移行の負担: 既存データの移行には時間とコストがかかり、データロスの可能性
- 整合性の維持: 不適切な設計はデータの矛盾を生み、業務に影響
- パフォーマンス: 設計の良し悪しがシステムの応答速度に直結
また、業務データを使い続けるためには以下が重要になります。
| 項目 | 説明 |
|---|---|
| 整合性 | データに矛盾がないこと |
| 効率性 | 素早くアクセスすることができること |
| 安全性 | 不正なアクセスから守ること |
| 柔軟性 | 変化に対応できること |
| 継続性 | 長期間使い続けられること |
データベース設計について
システム全体の開発アプローチとしてDBをどのように設計を考えればよいか、今回は以下の２つを紹介します。
-
プロセス中心アプローチ
業務フロー（手続き・操作順序・イベント発生タイミング）を詳しく分解し、「誰が／いつ／何をするか」を軸にデータ構造を後から導く。BPMN（Business Process Model and Notation）、業務シナリオ、ユースケース記述などを使う。誰が操作するのか、権限境界の設計がわかりやすい。 -
データ中心アプローチ
業務上保持すべき「安定した概念（顧客、商品、契約…）」「履歴（注文、在庫変動…）」を先に特定し、その関係性・制約（主キー/外部キー/整合性）を基盤に後でプロセスを乗せる。ER図、概念モデルを先行。
冗長データを早期に統制し整合性基盤（主キー/外部キー/正規化）を固めやすい。
設計の3段階
データベース設計は、概念設計→論理設計→物理設計の３段階で進めます。各フェーズには明確な目的と成果物があります。
※この記事では物理設計は扱いません。
1. 概念設計
業務要件を分析し、どのようなデータを管理する必要があるのか、データ間にどのような関係があるのかを明確にします。この段階では技術的な詳細には踏み込まず、業務の本質的な構造を捉えることに集中します。
2. 論理設計
ER図をもとに、実際のテーブル構造を設計します。各テーブルの項目（カラム）、データ型、主キー、外部キーなどを定義します。また、正規化を行ってデータの冗長性を排除し、整合性を保ちやすい構造にします。
3. 物理設計
インデックスの設定、ストレージの配置、パーティショニング、バックアップ戦略など、パフォーマンスと運用を考慮した物理的な設定を行います。使用するDBMS（Database Management System）の特性を考慮した最適化も行います。
ER図とは
ER図とはデータとその関係を図で示したもののこと。
この図を用いることで関係者全員が同じ理解を持つことができます。
ER図の構成要素は以下の通りです。
また、ER図の記法についてはいくつか種類があります。
本記事ではER図を記載する際は IE（Information Engineering）を利用します。
代表的な記法
- IE（Information Engineering） - 実務で最も使われる
- UML - ソフトウェア設計で使われる
- その他：IDEF1X、Bachman
ER図構成要素
ER図の構成要素は先ほど示した図の通り「エンティティ」、「属性」、「リレーションシップ」の３つです。
エンティティとリレーションシップがER図の中核となります。
これらは表を書くことでイメージをつかみやすいです。
エンティティ
処理や管理の対象となるモノ、またはコトで、情報を持ちます。
〇エンティティの例
社員（エンティティ）には社員コードや社員名、生年月日が情報となる
以下の表のようにリソース系/イベント系のエンティティと独立/従属に分類することができます。
| 種類 | 説明・例 |
|---|---|
| リソース系 | リソース関係のもの。 社員、商品、部署など |
| イベント系 | 出来事、アクション等。予約、請求など |
| 種類 | 説明・例 |
|---|---|
| 独立 | 単独で存在することができる。部署など |
| 従属 | 注文明細（注文が必要） |
属性
エンティティが持つ情報のことです。
例えば、エンティティ"社員"の属性には氏名、社員番号、所属組織コード、生年月日、職種、などがあります。
また、属性には主キー（PK：PrimaryKey）、外部キー（FK：ForeignKey） が存在します。
-
主キー（PK）：データを一意に特定する項目（重複・NULL不可）
※複数項目の組み合わせを主キーとして扱うエンティティも存在し、複合主キーと呼ばれる - 外部キー（FK）：他のエンティティの主キーを参照
主キー（PK）/外部キー（FK）の例では以下のようになります。
〇"社員"でいうと社員コードが主キー（PK：PrimaryKey）
- データを一意に特定できる属性
- 複数の属性で組み合わせてもよい
- 空の値（NULL）は無し
〇"社員"でいうと部署コードが外部キー（FK：ForeignKey）
- ほかのエンティティの主キーを参照する属性
リレーションシップ
エンティティ間の関係を数で表現するもので片方の立場から見た時どう見えるのかで考えるとわかりやすいです。
エンティティ間の関係は多重度と要否の２つで表現されます。
- 多重度（カーディナリティ）：１または多
- 要否（オプショナリティ）：必須（｜）または任意（〇）
多重度（カーディナリティ）のパターン紹介
エンティティ同士の多重度（カーディナリティ）の関係は以下の３パターンで表現されます。
| パターン | 説明 | 例 |
|---|---|---|
| １対１ | 両方が１ずつ対応 | 見積書 ⇔ 注文書 |
| １対多 | 一方が１、他方が複数 | 部署 ⇔ 社員（複数） |
| 多対多 | 両方が複数で対応 | 商品（複数） ⇔ 倉庫（複数） |
要否（オプショナリティ）のパターン紹介
エンティティ同士の要否（オプショナリティ）の関係は以下の３パターンで表現されます。
| パターン | 表記 | 例 |
|---|---|---|
| 任意と任意 | 〇―〇 | 顧客 ⇔ 商品（どちらも単独で存在可） |
| 必須と任意 | ｜―〇 | 商品（必須） ⇔ 注文 |
| 必須と必須 | ｜―｜ | 注文 ⇔ 注文明細（同時登録） |
以上の多重度（カーディナリティ）と要否（オプショナリティ）の内容を踏まえるとリレーションシップには４つの組み合わせが存在します。
- 任意の１：人は運転免許証を０または１持つ
- 必須の１：人は必ず１つの生年月日を持つ
- 任意の多：人は０以上の不動産を持つ
- 必須の多：人は１以上の名前を持つ
ER図を使った設計の流れ
ここからは実際にER図を使った設計としてデータ中心での設計アプローチの流れを説明します。
冒頭でも案内しましたが、今回は以下のような運送業務を管理するシステムを想定してER図を書いていきます。
- 乗務員が車両を使って荷物を運行します
- 乗務員はシステム上で運行に関する情報（走行距離、運行時間）を日報として記載します
- 適切な休憩を取得するため、運行ごとに休憩時間も管理します
また、設計は次の５つのステップで進めます。
| 内容 | 目的 | |
|---|---|---|
| ① | エンティティの洗い出し | 管理すべきデータを明確にする |
| ② | リレーションシップの決定 | エンティティ同士の関係を整理する |
| ③ | 属性の洗い出し | 各エンティティの項目を決める |
| ④ | 主キー・外部キーの決定 | データの識別と結びつけを設計する |
| ⑤ | 全体の確認・見直し | 抜け漏れや関係の整合性を確認する |
① エンティティの洗い出し
まず、業務内容・資料・ヒアリングなどから、管理対象となる「名詞」を洗い出します。
見つけ出す方法の一例として業務文章を読み、登場する名詞を箇条書きに書き出して整理します。
例：運送業務の場合
「車両に荷物を載せて乗務員が運行し、日報を記録する」
出てきた名詞：
- 車両
- 荷物
- 乗務員
- 運行
- 日報
これらをエンティティ候補として整理します。
| 候補 | 種類 | メモ |
|---|---|---|
| 車両 | リソース系 | 物理的な対象（管理する資産） |
| 荷物 | リソース系 | 運ぶ対象 |
| 乗務員 | リソース系 | 人に関する情報 |
| 運行 | イベント系 | 業務の出来事（いつ・どこで） |
| 日報 | イベント系 | 記録する出来事 |
ポイント
- 「名詞を拾う」→「グループ分けする」→「グループをもとにエンティティを決める」
② リレーションシップの決定
次に、エンティティ間の関係性（誰が何をする） を整理します。
文の中の「動詞（使う・運ぶ・担当するなど）」に注目します。
〇手順
- 業務の中心となるエンティティを選ぶ
- 関連するエンティティと関係を設定
- 数の関係（1対多、多対多）を決める
- 多対多があれば中間エンティティ(※)に分解する
- 他のエンティティも同様に関連付ける
※"車両"⇔"荷物"は多対多のリレーションであり、この関係を適切に表現するために
すでに洗い出し済みのエンティティである"運用"を「中間エンティティ」として用いることで、下図のように乗務員や日報などを含めたエンティティ間の関係を明確に表現することができます
例：運送業務の場合
| 関係 | 意味 | 関係の型 |
|---|---|---|
| 車両 → 運行 | 車両を使って運行する | 1対多 |
| 乗務員 → 運行 | 乗務員が運行を担当する | 1対多 |
| 運行 → 荷物 | 運行ごとに複数の荷物を運ぶ | 1対多 |
| 運行 → 日報 | 運行ごとに日報を記録する | 1対1または1対多 |
ポイント
- 「誰が」「何を」「どうする」を文にして関係を見つける
- 多対多の関係は、中間（交差）エンティティ(※1)を作って分解する
- 関係が業務上“必ず存在する”かどうか（要否関係／オプショナリティ）も意識する
③ 属性の洗い出し
エンティティが決まったら、どんな情報（項目）を持たせるか を決めます。
既存帳票やシステムのデータ項目を参考にしながら整理します。
属性の選び方
- 既存データ・書類から項目を抜き出す
- どのエンティティに属する情報かを決める
- 粒度をそろえて、項目名を統一する
例１：乗務員データ
| 乗務員番号 | 乗務員名 | 生年月日 | 資格・免許 | 最終更新日 |
|---|---|---|---|---|
| E200 | 田中太郎 | 1988年1月23日 | 大型自動車免許 | 2023年3月1日 |
上記データの分け方：
乗務員：乗務員番号、乗務員名、生年月日
資格・免許：資格名、最終更新日（乗務員1人に複数免許 → 1対多）
例２：日報データ
| 車両番号 | 運行番号 | 乗務員番号 | 出発日時 | 到着日時 | 出発地 | 到着地 | 休憩開始 | 休憩終了 |
|---|---|---|---|---|---|---|---|---|
| 00001 | 554 | E200 | 7/3 9:30 | 7/3 19:00 | 東京 | 大阪 | 13:00, 17:15 | 14:00, 17:45 |
上記データの分け方：
日報：車両番号、運行番号、乗務員番号、出発日時、到着日時、出発地、到着地
休憩：休憩開始、休憩終了（1対多）
命名と粒度
- 粒度は「将来の業務」を考慮して細かく（例：日付＋時刻 → 日付と時刻を分ける）
- 名称は「同じ意味なら同じ名前」に統一する
④ 主キー・外部キーの決定
属性の特徴をもとに、主キー（PK） と 外部キー（FK） を設定します。
| 種類 | 内容 | 例 |
|---|---|---|
| 主キー（PK） | その表のデータを一意に特定する | 乗務員番号、運行番号など |
| 外部キー（FK） | 他の表とつなぐためのキー | 車両番号（→ 車両テーブル） |
決め方の基本ルール
- 1対多 → 「1側」の主キーを「多側」の外部キーにする
- 1対1 → 主となる側の主キーを他方に設定（先に存在・独立している側）
- 多対多 → 中間エンティティの外部キーとして両方を持たせる
⑤ 全体の確認・見直し
全体の整合性を確認し、抜け漏れをチェックします。
| 対象 | 確認観点 |
|---|---|
| エンティティ | 不要・重複・粒度のばらつきがないか |
| 多重度（カーディナリティ） | 業務内容に合っているか、多対多が残っていないか |
| 要否関係（オプショナリティ） | 関連が必須か任意かを業務ルールで確認 |
| 主キー | 一意性が保てるか、複合主キーで問題ないか |
| 外部キー | 関連が正しい方向で設定されているか |
| 属性 | 名前・粒度・単位が統一されているか |
全体イメージ図（運送業例）
ER図の関係まとめ
| エンティティ | 主な役割 | 親関係 | 子関係 |
|---|---|---|---|
| 車両 | 使用される物理資産 | ― | 運行 |
| 乗務員 | 運転する人 | ― | 運行 |
| 運行 | 業務の中心（イベント） | 車両・乗務員 | 荷物・日報 |
| 荷物 | 運行で扱う対象 | 運行 | ― |
| 日報 | 運行の記録 | 運行 | 休憩 |
| 休憩 | 運行中の行動記録 | 日報 | ― |
このように、概念設計は「名詞→関係→属性→キー→全体確認」という一連のプロセスを何度も繰り返すことで、設計品質を高めることが肝要です。
1回で正しいER図を描くよりも、「仮で作って、見直して直す」ことが重要です。
また、実務においては以下も意識してもよいかもしれません。
- 通常フローだけでなく「例外発生時」も想定する
- 現場の帳票・入力画面と突き合わせて抜けを確認する
- 「誰が更新・登録するデータか」も意識しておく
まとめ
システム全体のためのデータベースを作り上げることは「最初にきっちり決めるもの」ではなく、業務理解を深めながら何度も見直すプロセスです。最初は仮のER図でも大丈夫です。
- 業務担当者と会話しながら
- 資料や画面と照らし合わせながら
- 小さく修正を重ねて
「正しい構造」を作り上げることが、結果的に長く使えるシステムにつながります。
参考資料
- Udemy「データ設計初心者のための初めてのER図入門」
- Udemy「初めてのテーブル設計・データベース設計」
We Are Hiring!