Harmonic Society
オンライン予約 お問い合わせ
プログラミング

データベース設計入門｜正規化・ER図・テーブル設計の基本を実例で解説

kento_morota 24分で読めます

「テーブルの構成はどうすればいい？」「正規化って何のためにやるの？」「ER図の書き方がわからない」――データベース設計は、アプリケーション開発の土台となる重要なスキルですが、初心者にとっては取っつきにくい分野でもあります。

本記事では、データベース設計の基本概念を、ECサイト（オンラインショップ）という具体的な例を使って解説します。正規化の理論からER図の作成、実際のテーブル定義（DDL）まで、一連の設計プロセスを体験できます。

データベース設計はなぜ重要なのか

データベース設計は、建物で言えば「基礎工事」に相当します。基礎がしっかりしていなければ、どんなに美しい建物を建てても、いずれ問題が発生します。

設計が悪いとどうなるか

設計が不適切なデータベースでは、以下のような問題が発生します。

データの冗長性（無駄な重複）：同じ情報が複数の場所に保存され、ストレージを無駄に消費します。

更新異常：顧客の住所が変わった場合、注文テーブルの住所、配送テーブルの住所など、複数箇所を更新する必要があり、更新漏れが発生しやすくなります。

挿入異常：まだ注文がない商品の情報を登録できない、といった問題が起こります。

削除異常：注文データを削除すると、紐づいた顧客情報まで消えてしまう場合があります。

パフォーマンスの低下：不要なJOINが増え、クエリが遅くなります。

設計の全体フロー

データベース設計は、一般的に以下のステップで進めます。

  1. 要件定義：どのようなデータを扱うか洗い出す
  2. 概念設計：エンティティ（実体）と関連を整理する
  3. 論理設計：正規化を行い、テーブル構造を決定する
  4. 物理設計：データ型、インデックス、パーティションなどを決定する

エンティティと関連（リレーションシップ）の理解

データベース設計の第一歩は、システムに登場する「もの」（エンティティ）と、その「関係性」（リレーションシップ）を整理することです。

エンティティの洗い出し

ECサイトの場合、以下のようなエンティティが考えられます。

  • ユーザー（User）：サイトに登録した顧客
  • 商品（Product）：販売する商品
  • カテゴリ（Category）：商品の分類
  • 注文（Order）：顧客の注文
  • 注文明細（OrderItem）：注文に含まれる個々の商品
  • 配送先（ShippingAddress）：配送先住所
  • レビュー（Review）：商品に対するレビュー

リレーションシップの種類

エンティティ間の関係には、主に3つのパターンがあります。

1対1（One-to-One）：ユーザーとユーザープロフィールの関係。1人のユーザーには1つのプロフィールが紐づきます。

1対多（One-to-Many）：ユーザーと注文の関係。1人のユーザーは複数の注文を持ちますが、1つの注文は1人のユーザーに属します。

多対多（Many-to-Many）：商品とタグの関係。1つの商品に複数のタグが付き、1つのタグは複数の商品に紐づきます。多対多は中間テーブルで表現します。

ER図（Entity-Relationship Diagram）の書き方

ER図は、エンティティと関連を視覚的に表現する図です。設計の意図を他の開発者と共有するために欠かせません。

ER図の基本記法

ER図にはいくつかの記法がありますが、ここではIE記法（鶏の足記法、Crow's Foot記法）を使います。

ER図の記号：
  ||──||  1対1の関係
  ||──|<  1対多の関係
  >|──|<  多対多の関係

  ||  必須（exactly one）
  o|  任意（zero or one）
  |<  必須の多（one or more）
  o<  任意の多（zero or more）

ECサイトのER図（テキスト表現）

ECサイトのエンティティ関連図:

[users] ||──o< [orders]           : ユーザーは0以上の注文を持つ
[orders] ||──|< [order_items]     : 注文は1以上の注文明細を持つ
[products] ||──o< [order_items]   : 商品は0以上の注文明細に含まれる
[categories] ||──o< [products]    : カテゴリは0以上の商品を持つ
[users] ||──o< [reviews]          : ユーザーは0以上のレビューを持つ
[products] ||──o< [reviews]       : 商品は0以上のレビューを持つ
[users] ||──o< [shipping_addresses] : ユーザーは0以上の配送先を持つ
[products] >|──|< [tags]          : 商品とタグは多対多（中間テーブル product_tags）

ER図作成ツール

実際のプロジェクトでは、以下のツールを使ってER図を作成します。

  • dbdiagram.io：コードベースでER図を作成できるWebツール（無料）
  • DrawSQL：直感的なUIでER図を作成できるWebツール
  • Mermaid：マークダウン内でER図を記述できる（GitHub対応）
  • pgModeler：PostgreSQL専用のモデリングツール

dbdiagram.ioでの記述例を示します。

// dbdiagram.io の DBML記法
Table users {
  id serial [pk]
  username varchar(50) [unique, not null]
  email varchar(255) [unique, not null]
  created_at timestamptz [default: `now()`]
}

Table orders {
  id serial [pk]
  user_id integer [ref: > users.id]
  status varchar(20) [default: 'pending']
  total_amount numeric(10,2) [not null]
  created_at timestamptz [default: `now()`]
}

Table order_items {
  id serial [pk]
  order_id integer [ref: > orders.id]
  product_id integer [ref: > products.id]
  quantity integer [not null]
  unit_price numeric(10,2) [not null]
}

Table products {
  id serial [pk]
  name varchar(255) [not null]
  price numeric(10,2) [not null]
  category_id integer [ref: > categories.id]
}

Table categories {
  id serial [pk]
  name varchar(100) [unique, not null]
}

正規化の基本：第1〜第3正規形

正規化は、データの冗長性を排除し、更新異常を防ぐためのテーブル分割プロセスです。

非正規形（正規化前）

まず、正規化されていない「悪い例」を見てみましょう。

-- 非正規形：1つのテーブルに全部詰め込んだ例
CREATE TABLE orders_bad (
  order_id      INTEGER,
  order_date    DATE,
  customer_name VARCHAR(100),
  customer_email VARCHAR(255),
  customer_address TEXT,
  product1_name VARCHAR(255),
  product1_price NUMERIC(10,2),
  product1_qty  INTEGER,
  product2_name VARCHAR(255),
  product2_price NUMERIC(10,2),
  product2_qty  INTEGER,
  product3_name VARCHAR(255),
  product3_price NUMERIC(10,2),
  product3_qty  INTEGER
);

この設計の問題点は明らかです。商品が4つ以上ある注文は対応できず、顧客情報が注文ごとに重複し、商品が少ない注文ではNULL値だらけになります。

第1正規形（1NF）：繰り返しの排除

第1正規形のルールは以下の通りです。

  • すべてのカラムがアトミック（これ以上分割できない）な値を持つ
  • 繰り返しグループを排除する
  • 各行が一意に識別できる
-- 第1正規形：繰り返しを行に展開
CREATE TABLE orders_1nf (
  order_id       INTEGER,
  order_date     DATE,
  customer_name  VARCHAR(100),
  customer_email VARCHAR(255),
  customer_address TEXT,
  product_name   VARCHAR(255),
  product_price  NUMERIC(10,2),
  quantity       INTEGER,
  PRIMARY KEY (order_id, product_name)
);

-- データ例
-- | order_id | order_date | customer_name | product_name | product_price | quantity |
-- |----------|------------|---------------|--------------|---------------|----------|
-- | 1        | 2026-03-01 | 田中太郎      | ノートPC      | 89800         | 1        |
-- | 1        | 2026-03-01 | 田中太郎      | マウス        | 3980          | 2        |
-- | 2        | 2026-03-02 | 鈴木花子      | キーボード    | 12800         | 1        |

繰り返しは排除されましたが、まだ顧客情報が各行で重複しています。

第2正規形（2NF）：部分関数従属の排除

第2正規形では、複合主キーの一部にのみ依存するカラム（部分関数従属）を別テーブルに分離します。

-- 第2正規形：注文テーブルと注文明細テーブルに分離
CREATE TABLE orders_2nf (
  order_id       INTEGER PRIMARY KEY,
  order_date     DATE,
  customer_name  VARCHAR(100),
  customer_email VARCHAR(255),
  customer_address TEXT
);

CREATE TABLE order_items_2nf (
  order_id      INTEGER REFERENCES orders_2nf(order_id),
  product_name  VARCHAR(255),
  product_price NUMERIC(10,2),
  quantity      INTEGER,
  PRIMARY KEY (order_id, product_name)
);

注文情報と商品明細が分離され、注文ごとの顧客情報の重複がなくなりました。

第3正規形（3NF）：推移的関数従属の排除

第3正規形では、主キーに直接依存しない（他のカラムを介して間接的に依存する）カラムを分離します。

-- 第3正規形：顧客情報と商品情報を独立したテーブルに
CREATE TABLE customers (
  id       SERIAL PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  email    VARCHAR(255) UNIQUE NOT NULL,
  address  TEXT
);

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  product_id  INTEGER NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(10,2) NOT NULL  -- 注文時の価格を記録
);

order_itemsテーブルにunit_priceを持たせているのは意図的な非正規化です。商品の価格は変わる可能性があるため、注文時の価格を記録しておく必要があります。

ECサイトのテーブル設計：完全版

ここまでの知識を踏まえて、実用的なECサイトのテーブル設計を作成します。

完全なDDL

-- ========================================
-- ECサイト テーブル定義
-- ========================================

-- ユーザー
CREATE TABLE users (
  id             SERIAL PRIMARY KEY,
  email          VARCHAR(255) UNIQUE NOT NULL,
  password_hash  VARCHAR(255) NOT NULL,
  display_name   VARCHAR(100) NOT NULL,
  phone          VARCHAR(20),
  is_active      BOOLEAN DEFAULT true,
  created_at     TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 配送先住所（1ユーザーが複数の配送先を登録可能）
CREATE TABLE shipping_addresses (
  id           SERIAL PRIMARY KEY,
  user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  label        VARCHAR(50) DEFAULT '自宅',        -- 「自宅」「会社」など
  postal_code  VARCHAR(10) NOT NULL,
  prefecture   VARCHAR(10) NOT NULL,
  city         VARCHAR(100) NOT NULL,
  address_line VARCHAR(255) NOT NULL,
  building     VARCHAR(255),
  is_default   BOOLEAN DEFAULT false,
  created_at   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- カテゴリ（階層構造に対応）
CREATE TABLE categories (
  id          SERIAL PRIMARY KEY,
  parent_id   INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(100) UNIQUE NOT NULL,
  sort_order  INTEGER DEFAULT 0,
  created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 商品
CREATE TABLE products (
  id           SERIAL PRIMARY KEY,
  category_id  INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  name         VARCHAR(255) NOT NULL,
  slug         VARCHAR(255) UNIQUE NOT NULL,
  description  TEXT,
  price        NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock        INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  is_published BOOLEAN DEFAULT false,
  created_at   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 商品画像
CREATE TABLE product_images (
  id          SERIAL PRIMARY KEY,
  product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  image_url   VARCHAR(500) NOT NULL,
  alt_text    VARCHAR(255),
  sort_order  INTEGER DEFAULT 0,
  created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 注文
CREATE TABLE orders (
  id                  SERIAL PRIMARY KEY,
  user_id             INTEGER NOT NULL REFERENCES users(id),
  status              VARCHAR(20) NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
  subtotal            NUMERIC(10, 2) NOT NULL,
  shipping_fee        NUMERIC(10, 2) NOT NULL DEFAULT 0,
  tax                 NUMERIC(10, 2) NOT NULL DEFAULT 0,
  total_amount        NUMERIC(10, 2) NOT NULL,
  shipping_postal_code VARCHAR(10),
  shipping_prefecture VARCHAR(10),
  shipping_city       VARCHAR(100),
  shipping_address    VARCHAR(255),
  shipping_building   VARCHAR(255),
  note                TEXT,
  ordered_at          TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  shipped_at          TIMESTAMPTZ,
  delivered_at        TIMESTAMPTZ
);

-- 注文明細
CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id  INTEGER NOT NULL REFERENCES products(id),
  product_name VARCHAR(255) NOT NULL,   -- 注文時の商品名を記録
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(10, 2) NOT NULL,  -- 注文時の単価を記録
  subtotal    NUMERIC(10, 2) NOT NULL   -- quantity * unit_price
);

-- レビュー
CREATE TABLE reviews (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  title       VARCHAR(255),
  body        TEXT,
  created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, product_id)  -- 1ユーザー1商品につき1レビュー
);

-- ========================================
-- インデックス
-- ========================================
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_published ON products(is_published, created_at DESC)
  WHERE is_published = true;
CREATE INDEX idx_orders_user ON orders(user_id, ordered_at DESC);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_shipping_addresses_user ON shipping_addresses(user_id);

設計のポイント解説

配送先住所をordersテーブルにコピーしている理由：ユーザーが配送先住所を後から変更しても、過去の注文の配送先は変わってはいけません。そのため、注文時点の住所をordersテーブルに直接保存しています。

order_itemsにproduct_nameとunit_priceを持つ理由：商品名や価格は変更される可能性があるため、注文時のスナップショットを保存します。

categoriesの自己参照：parent_idで階層構造を表現しています。「メンズ > トップス > Tシャツ」のような多段カテゴリに対応できます。

設計のアンチパターンと対策

初心者が陥りがちなデータベース設計のアンチパターンを紹介します。

アンチパターン1：EAV（Entity-Attribute-Value）

-- アンチパターン：何でも入るテーブル
CREATE TABLE attributes (
  entity_id   INTEGER,
  entity_type VARCHAR(50),
  key         VARCHAR(100),
  value       TEXT
);

-- こうではなく、適切なテーブルを設計しましょう
-- 商品のスペックが可変なら、JSONBカラムが有効
CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  specs JSONB DEFAULT '{}'
);

-- 使用例
INSERT INTO products (name, specs) VALUES
('ノートPC', '{"cpu": "M3", "memory": "16GB", "storage": "512GB SSD"}'),
('Tシャツ', '{"size": "L", "color": "ネイビー", "material": "コットン100%"}');

アンチパターン2：カンマ区切りのリスト

-- アンチパターン：タグをカンマ区切りで保存
CREATE TABLE products_bad (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(255),
  tags VARCHAR(500)  -- "新作,セール,人気" のような文字列
);

-- 正しい設計：中間テーブルを使う
CREATE TABLE tags (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE product_tags (
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  tag_id     INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, tag_id)
);

アンチパターン3：論理削除の濫用

-- 論理削除：deleted_atカラムを追加
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;

-- 論理削除されたデータを除外する条件が全クエリに必要になる
SELECT * FROM products WHERE deleted_at IS NULL;

-- 代替案：ステータスカラムを使う
ALTER TABLE products ADD COLUMN status VARCHAR(20) DEFAULT 'active'
CHECK (status IN ('active', 'archived', 'deleted'));

論理削除は便利ですが、すべてのSELECTにWHERE deleted_at IS NULLが必要になり、インデックスにも影響します。必要に応じて履歴テーブルを使う方法も検討しましょう。

まとめ：良いデータベース設計のためのチェックリスト

本記事で解説した内容を、チェックリストとしてまとめます。

  • エンティティの洗い出し：システムに登場する「もの」を漏れなく抽出できたか
  • リレーションシップ：1対1、1対多、多対多の関係を正しく把握しているか
  • 正規化：最低限、第3正規形まで正規化したか
  • 意図的な非正規化：パフォーマンスや履歴保存のための非正規化は明示的に判断したか
  • 主キー：すべてのテーブルに適切な主キーがあるか
  • 外部キー：参照整合性を保つ制約が設定されているか
  • NOT NULL：必須カラムにNOT NULL制約があるか
  • CHECK制約：値の範囲や許可値が制限されているか
  • インデックス：検索条件に使われるカラムにインデックスがあるか
  • 命名規則：テーブル名・カラム名が一貫した命名規則に従っているか

データベース設計は最初から完璧を目指す必要はありません。まずは基本的な正規化を行い、アプリケーションの要件に合わせて段階的に改善していくアプローチが現実的です。本記事で紹介したECサイトの設計例を参考に、ぜひ自分のプロジェクトのテーブル設計に取り組んでみてください。

#データベース#設計#正規化
共有:
無料メルマガ

週1回、最新の技術記事をお届け

AI・クラウド・開発の最新記事を毎週月曜にメールでお届けします。登録は無料、いつでも解除できます。

※ プライバシーポリシーに基づき管理します

関連記事

AIエージェント開発入門｜自律型AIの仕組みと構築方法を解説【2026年版】

AIエージェント開発入門｜自律型AIの仕組みと構築方法を解説【2026年版】
AI駆動コーディングワークフロー｜Claude Code・Cursor・Copilotの実践的使い分け

AI駆動コーディングワークフロー｜Claude Code・Cursor・Copilotの実践的使い分け
プロンプトエンジニアリング上級編｜Chain-of-Thought・Few-Shot・ReActの実践

プロンプトエンジニアリング上級編｜Chain-of-Thought・Few-Shot・ReActの実践
APIレート制限の設計と実装｜トークンバケット・スライディングウィンドウ解説

APIレート制限の設計と実装｜トークンバケット・スライディングウィンドウ解説
APIバージョニング戦略｜URL・ヘッダー・クエリパラメータの使い分け

APIバージョニング戦略｜URL・ヘッダー・クエリパラメータの使い分け
BIツール入門｜Metabase・Redash・Looker Studioでデータ可視化する方法

BIツール入門｜Metabase・Redash・Looker Studioでデータ可視化する方法
チャットボット開発入門｜LINE Bot・Slack Botの構築方法と活用事例

チャットボット開発入門｜LINE Bot・Slack Botの構築方法と活用事例
CI/CDパイプラインの基礎｜継続的インテグレーション・デリバリーの全体像

CI/CDパイプラインの基礎｜継続的インテグレーション・デリバリーの全体像
クリーンコードの原則｜可読性・保守性を高める7つの実践ルール

クリーンコードの原則｜可読性・保守性を高める7つの実践ルール

AI活用のヒントをお探しですか？お気軽にご相談ください。

まずは話だけ聞いてもらう