DB 設計の実践手順：7 ステップで迷わず設計する

はじめに

DB 設計は、アプリケーション開発の基盤となる重要な工程です。しかし、実践で取り組んだことがなかったため、年始に動画を見て学習しました。

この記事では、そこで学んだDB 設計の手順を 7 つのステップに分けて解説します。EC サイトの例を使いながら、初心者でもわかるように具体的な手順を説明します。

この記事で学べること

• DB 設計を 7 つのステップで進める方法
• シナリオベースでテーブルを洗い出すコツ
• 正規化とインデックスの実践的な考え方
• 実際のプロジェクトで使える設計フロー

目次

• ステップ 1：要件を確認する
• ステップ 2：機能一覧を洗い出す
• ステップ 3：テーブルの一覧を洗い出す
• ステップ 4：日本語を英語に変換
• ステップ 5：カラムに型をつける
• ステップ 6：ER 図を書きながら正規化
• ステップ 7：制約条件やインデックスを作成
• 実践例：完成したテーブル定義
• まとめ

ステップ 1：要件を確認する

この記事では、要件は既に決まっている前提で進めます。まず、システムで何を実現したいのかを確認しましょう。

例：EC サイトの場合

• 商品を検索できる
• ユーザーが商品を購入できる
• 管理者が商品を登録できる
• 購入履歴を確認できる

要件が決まっていない場合は、まず要件定義から始めてください。

ステップ 2：機能一覧を洗い出す

要件を元に、ユーザーが実際に使える機能を洗い出します。フロントエンドの画面や操作をイメージしながら、機能をリストアップします。

例：EC サイトの機能一覧

一般ユーザー向け

• 商品検索
• 商品詳細表示
• 購入
• 購入履歴確認

管理者向け

• ログイン
• 商品登録
• 商品編集
• 商品削除

ステップ 3：テーブルの一覧を洗い出す

テーブルとは、データを保存する表のことです。商品情報を保存する「商品テーブル」、ユーザー情報を保存する「ユーザーテーブル」などがあります。

テーブルを洗い出すときは、闇雲に考えるのではなく、シナリオに沿って進めます。実際の操作フローを追いながら、必要なテーブルを特定します。

シナリオ 1：商品検索機能

1. ユーザーが商品を検索
2. 商品一覧が表示される

必要なテーブル: 商品テーブル

シナリオ 2：商品登録機能

1. 管理者としてログイン
2. 管理者が商品を登録

必要なテーブル: 店舗テーブル、商品テーブル、商品カテゴリテーブル

シナリオ 3：購入機能

1. 一般ユーザーが商品を購入
2. 購入履歴が記録される

必要なテーブル: ユーザーテーブル、購入履歴テーブル

洗い出したテーブル一覧

• 店舗テーブル
• 商品テーブル
• 商品カテゴリテーブル
• ユーザーテーブル
• 購入履歴テーブル

ポイント: シナリオに沿って考えることで、必要なテーブルを見落としにくくなります。機能から逆算するのではなく、実際の操作フローを追うことが重要です。

ステップ 4：日本語を英語に変換

テーブル名とカラム名を英語に変換します。カラムとは、テーブルの中の列（縦の列）のことです。例えば、商品テーブルには「商品名」「価格」などのカラムがあります。

以下のルールを守ります。

命名規則

• テーブル名は複数形：shops、products、users
• カラム名は単数形：name、email、password
• DB 予約語は避ける：order、group、userなどは使わない（SQL 予約語一覧を参照）

変換例

日本語	英語（テーブル名）	英語（カラム名例）
店舗	shops	name, login_id, password
商品	products	name, price, description
商品カテゴリ	product_categories	name
ユーザー	users	name, email
購入履歴	purchase_histories	purchase_date, total_amount

注意: orderは予約語のため、purchase_historiesを使います。userも予約語の可能性があるため、users（複数形）を使うのが安全です。

ステップ 5：カラムに型をつける

各テーブルのカラムにデータ型を設定します。データ型とは、そのカラムに保存できるデータの種類のことです。文字列なのか、数値なのか、日時なのかを決めます。

基本的なデータ型

• VARCHAR(255)：文字列（名前、メールアドレスなど）
• INT：整数（ID、数値など）
• DATETIME：日時（作成日時、更新日時など）
• DECIMAL(10,2)：小数（価格など）

必須カラム

ほぼすべてのテーブルに含めるべきカラム：

• id：INT、PRIMARY KEY、AUTO_INCREMENT（各レコードを識別するための番号）
• created_at：DATETIME（作成日時）
• updated_at：DATETIME（更新日時）

ポイント: PRIMARY KEYは主キーと呼ばれ、各レコードを一意に識別するためのカラムです。AUTO_INCREMENTは、自動的に連番を振る機能です。

テーブル定義の例

shops テーブル

CREATE TABLE shops (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  login_id VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

products テーブル

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  description TEXT,
  product_category_id INT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

ステップ 6：ER 図を書きながら正規化

ER 図を書きながら、テーブル間の関係を整理し、正規化を行います。正規化とは、データの重複を避けて、効率的にデータを管理するための手法です。

正規化の基本

• 1 対多の関係：外部キーで表現（例：商品カテゴリと商品）
• 多対多の関係：中間テーブルを作成（例：商品と購入履歴）

外部キーとは

外部キーとは、他のテーブルを参照するためのカラムです。例えば、商品テーブルに「商品カテゴリ ID」というカラムを追加することで、どのカテゴリに属するかを表せます。

外部キーの命名規則

外部キーは「参照先テーブルの単数形_id」とします。

• product_category_id（商品カテゴリテーブルを参照）
• shop_id（店舗テーブルを参照）
• user_id（ユーザーテーブルを参照）

多対多の関係：中間テーブル

商品と購入履歴は多対多の関係です。1 つの購入履歴に複数の商品が含まれるため、中間テーブルが必要です。

purchase_histories テーブル

CREATE TABLE purchase_histories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  purchase_date DATETIME NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

purchase_details テーブル（中間テーブル）

CREATE TABLE purchase_details (
  id INT PRIMARY KEY AUTO_INCREMENT,
  purchase_history_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (purchase_history_id) REFERENCES purchase_histories(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

ポイント: 購入明細テーブル（purchase_details）を作ることで、1 つの購入履歴に複数の商品を紐付けられます。数量や価格も記録しておくと、購入時の価格を正確に保持できます。

ステップ 7：制約条件やインデックスを作成

パフォーマンスとデータ整合性を高めるために、制約条件とインデックスを設定します。

インデックス：検索を高速化

インデックスとは、検索を高速化するための仕組みです。本の索引のようなもので、検索が頻繁に行われるカラムにインデックスを設定します。

CREATE INDEX idx_products_name ON products(name);

NOT NULL 制約：必要なデータを保証

NOT NULL制約とは、そのカラムに必ず値が入ることを保証する制約です。必須のカラムにはNOT NULL制約を付けます。

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  description TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

ユニークキー制約：重複を防ぐ

ユニークキー制約とは、同じ値が重複してはいけないことを保証する制約です。重複してはいけない値には、ユニークキー制約を設定します。

CREATE TABLE product_categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

外部キー制約：参照整合性を保証

外部キー制約とは、存在しない ID を参照することを防ぐ制約です。外部キー制約を設定することで、データの整合性を保てます。

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  product_category_id INT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (product_category_id) REFERENCES product_categories(id)
);

注意: 外部キー制約は、パフォーマンスに影響を与える可能性があります。開発初期は設定せず、必要に応じて追加するという選択肢もあります。

実践例：完成したテーブル定義

EC サイトの例で、完成したテーブル定義をまとめます。

-- 店舗テーブル
CREATE TABLE shops (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  login_id VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

-- 商品カテゴリテーブル
CREATE TABLE product_categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

-- 商品テーブル
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  description TEXT,
  product_category_id INT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (product_category_id) REFERENCES product_categories(id),
  INDEX idx_products_name (name)
);

-- ユーザーテーブル
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

-- 購入履歴テーブル
CREATE TABLE purchase_histories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  purchase_date DATETIME NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 購入明細テーブル（中間テーブル）
CREATE TABLE purchase_details (
  id INT PRIMARY KEY AUTO_INCREMENT,
  purchase_history_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (purchase_history_id) REFERENCES purchase_histories(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

まとめ

DB 設計は、以下の 7 つのステップで進められます。

1. 要件を確認する：要件は既に決まっている前提で確認
2. 機能一覧を洗い出す：フロントから何ができるかを洗い出す
3. テーブルの一覧を洗い出す：シナリオに沿って洗い出す
4. 日本語を英語に変換：命名規則を守る
5. カラムに型をつける：基本的な型と必須カラムを設定
6. ER 図を書きながら正規化：外部キーと中間テーブルで関係を整理
7. 制約条件やインデックスを作成：パフォーマンスと整合性を高める

特に重要なのは、ステップ 3 のシナリオに沿ったテーブル洗い出しです。機能から逆算するのではなく、実際の操作フローを追うことで、必要なテーブルを見落としにくくなります。

今回学んだことを次は実践で活かせるように、再学習していきます。

参考：

• データベース設計入門#1 リレーションとER図【11分でマスター！DB設計】 せお丸 様
  https://www.youtube.com/watch?v=T6g-DLWHscw