PostgreSQLテーブル設計入門 - 正規化とリレーションシップ

はじめに

データベース設計において、テーブル構造の設計は最も重要な工程の一つです。適切に設計されたテーブル構造は、データの整合性を保証し、クエリのパフォーマンスを向上させ、将来の機能拡張にも柔軟に対応できます。一方で、設計の誤りは後から修正することが困難であり、システム全体に深刻な影響を及ぼします。

本記事では、PostgreSQLにおけるテーブル設計の基本から実践的なベストプラクティスまでを解説します。CREATE TABLE文の構文、各種制約（PRIMARY KEY/FOREIGN KEY/UNIQUE/NOT NULL/CHECK/DEFAULT）の設定方法、正規化（第1〜第3正規形）の理論と実践、ER図の読み書き、そして現場で役立つ設計のベストプラクティスを、豊富なサンプルコードとともに学んでいきます。

この記事を読むことで、以下のことができるようになります。

CREATE TABLE文でテーブルを定義し、適切なカラム構成を設計できる
制約を活用してデータの整合性をデータベースレベルで保証できる
正規化の概念を理解し、データの重複と更新異常を排除できる
ER図を読み書きし、テーブル間のリレーションシップを設計できる
実務で使えるテーブル設計のベストプラクティスを適用できる

前提条件

PostgreSQL 14以降がインストールされていること
psqlまたはGUIツール（pgAdmin、DBeaver等）でデータベースに接続できること
基本的なSQL構文（SELECT、INSERT等）を理解していること

CREATE TABLE文によるテーブル定義

基本構文

CREATE TABLE文は、データベースに新しいテーブルを作成するための基本的なDDL（Data Definition Language）文です。

1
2
3
4
5
6


CREATE TABLE テーブル名 (
    カラム名1 データ型 [制約],
    カラム名2 データ型 [制約],
    ...
    [テーブル制約]
);

シンプルなテーブル作成例

実際に商品テーブルを作成してみましょう。

1
2
3
4
5
6
7


-- 商品テーブルの作成
CREATE TABLE products (
    product_id   INTEGER,
    name         VARCHAR(100),
    price        INTEGER,
    stock        INTEGER
);

このテーブルに対してデータを挿入できます。

1
2


INSERT INTO products (product_id, name, price, stock)
VALUES (1, 'ノートPC', 120000, 25);

ただし、このテーブルには制約が設定されていないため、以下のような問題が発生する可能性があります。

同じproduct_idを持つ行を複数挿入できてしまう
nameやpriceにNULL値を入れられてしまう
負の価格や在庫数を入力できてしまう

これらの問題を防ぐために、制約を活用します。

テーブル制約の種類と設定方法

制約（Constraint）は、テーブルに格納されるデータのルールを定義する機能です。PostgreSQLでは以下の制約が利用可能です。

制約	説明
PRIMARY KEY	主キー制約。一意性とNOT NULLを保証
FOREIGN KEY	外部キー制約。参照整合性を保証
UNIQUE	一意制約。重複を禁止
NOT NULL	NULL値を禁止
CHECK	指定した条件を満たすことを保証
DEFAULT	値が指定されない場合のデフォルト値

PRIMARY KEY（主キー制約）

主キー制約は、テーブル内の各行を一意に識別するためのカラム（または複数カラムの組み合わせ）を指定します。主キーに設定されたカラムには、自動的にUNIQUE制約とNOT NULL制約が適用されます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


-- カラム定義時に指定する方法
CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        INTEGER NOT NULL
);

-- テーブル制約として指定する方法
CREATE TABLE products (
    product_id   INTEGER,
    name         VARCHAR(100) NOT NULL,
    price        INTEGER NOT NULL,
    PRIMARY KEY (product_id)
);

複合主キー

複数のカラムを組み合わせて主キーとすることもできます。これは、単一カラムでは一意性を保証できない場合に使用します。

1
2
3
4
5
6
7
8


-- 注文明細テーブル（複合主キー）
CREATE TABLE order_items (
    order_id     INTEGER,
    product_id   INTEGER,
    quantity     INTEGER NOT NULL,
    unit_price   INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

この例では、order_idとproduct_idの組み合わせが一意であることを保証します。同じ注文内で同じ商品が重複して登録されることを防ぎます。

SERIAL型による自動採番

主キーには連番を自動的に割り当てるSERIAL型がよく使用されます。

1
2
3
4
5
6
7
8
9


CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        INTEGER NOT NULL
);

-- product_idを指定せずに挿入すると自動採番される
INSERT INTO products (name, price) VALUES ('ノートPC', 120000);
INSERT INTO products (name, price) VALUES ('マウス', 3500);

1
2
3
4


 product_id |  name   | price  
------------+---------+--------
          1 | ノートPC | 120000
          2 | マウス   |   3500

FOREIGN KEY（外部キー制約）

外部キー制約は、あるテーブルのカラムが別のテーブルの主キー（またはUNIQUE制約のあるカラム）を参照することを保証します。これにより、参照整合性（Referential Integrity）が維持されます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 顧客テーブル
CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) UNIQUE
);

-- 注文テーブル（customersテーブルを参照）
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    order_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount  INTEGER NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

外部キー制約により、以下の動作が保証されます。

ordersテーブルに存在しないcustomer_idを持つ行を挿入しようとするとエラーになる
customersテーブルから参照されている行を削除しようとするとエラーになる

1
2
3
4


-- 存在しない顧客IDで注文を作成しようとするとエラー
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (999, '2026-01-03', 50000);
-- ERROR: insert or update on table "orders" violates foreign key constraint

参照アクション

外部キー制約では、参照先の行が削除または更新された際の動作を指定できます。

アクション	説明
NO ACTION	エラーを発生させる（デフォルト）
RESTRICT	即座にエラーを発生させる
CASCADE	参照している行も一緒に削除/更新する
SET NULL	参照元のカラムをNULLに設定する
SET DEFAULT	参照元のカラムをデフォルト値に設定する

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 注文テーブル（ON DELETE CASCADEを設定）
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    order_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount  INTEGER NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

この設定により、顧客が削除されると、その顧客の注文も自動的に削除されます。

カラム定義での外部キー指定

より簡潔にカラム定義内で外部キーを指定することもできます。

1
2
3
4
5
6


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

UNIQUE（一意制約）

一意制約は、カラムの値がテーブル内で重複しないことを保証します。主キーとは異なり、NULL値を許容します（ただし、PostgreSQLではNULL値も重複チェックの対象にするオプションがあります）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE TABLE users (
    user_id     SERIAL PRIMARY KEY,
    username    VARCHAR(50) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    nickname    VARCHAR(50) UNIQUE  -- NULLを許容するユニーク制約
);

-- ユニーク制約違反の例
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
INSERT INTO users (username, email) VALUES ('user1', 'user2@example.com');
-- ERROR: duplicate key value violates unique constraint "users_username_key"

複合ユニーク制約

複数カラムの組み合わせに対して一意制約を設定することもできます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- テナント別ユーザーテーブル（テナント内でユーザー名が一意）
CREATE TABLE tenant_users (
    user_id     SERIAL PRIMARY KEY,
    tenant_id   INTEGER NOT NULL,
    username    VARCHAR(50) NOT NULL,
    UNIQUE (tenant_id, username)
);

-- 同じテナント内での重複はエラー
INSERT INTO tenant_users (tenant_id, username) VALUES (1, 'admin');
INSERT INTO tenant_users (tenant_id, username) VALUES (1, 'admin');
-- ERROR: duplicate key value violates unique constraint

-- 異なるテナントであれば同じユーザー名が可能
INSERT INTO tenant_users (tenant_id, username) VALUES (1, 'admin');
INSERT INTO tenant_users (tenant_id, username) VALUES (2, 'admin');
-- 成功

NOT NULL（非NULL制約）

NOT NULL制約は、カラムにNULL値を格納することを禁止します。必須項目には必ずNOT NULL制約を設定しましょう。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255),           -- NULLを許容
    department    VARCHAR(50) NOT NULL,
    salary        INTEGER NOT NULL
);

-- NOT NULL違反の例
INSERT INTO employees (name, department, salary) VALUES (NULL, '開発部', 500000);
-- ERROR: null value in column "name" violates not-null constraint

PostgreSQLの公式ドキュメントでは、「ほとんどのデータベース設計において、大部分のカラムはNOT NULLとしてマークされるべき」と推奨されています。

CHECK（検査制約）

CHECK制約は、カラムの値が指定した条件を満たすことを保証します。ビジネスルールをデータベースレベルで強制できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        INTEGER NOT NULL CHECK (price > 0),
    stock        INTEGER NOT NULL CHECK (stock >= 0),
    discount     INTEGER CHECK (discount >= 0 AND discount <= 100)
);

-- CHECK制約違反の例
INSERT INTO products (name, price, stock) VALUES ('テスト商品', -100, 10);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"

名前付きCHECK制約

制約に名前を付けることで、エラーメッセージが分かりやすくなり、後から制約を削除する際にも便利です。

1
2
3
4
5
6
7
8


CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        INTEGER NOT NULL,
    discount_price INTEGER,
    CONSTRAINT price_must_be_positive CHECK (price > 0),
    CONSTRAINT discount_must_be_lower CHECK (discount_price IS NULL OR discount_price < price)
);

複数カラムにまたがるCHECK制約

CHECK制約は複数カラムの関係を検証することもできます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE TABLE events (
    event_id     SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    start_date   DATE NOT NULL,
    end_date     DATE NOT NULL,
    CONSTRAINT valid_date_range CHECK (end_date >= start_date)
);

-- 終了日が開始日より前の場合はエラー
INSERT INTO events (name, start_date, end_date) 
VALUES ('会議', '2026-01-10', '2026-01-05');
-- ERROR: new row for relation "events" violates check constraint "valid_date_range"

DEFAULT（デフォルト値）

DEFAULT制約は、INSERT時に値が指定されなかった場合に自動的に設定される値を定義します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    order_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    status        VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_paid       BOOLEAN NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- order_date、status、is_paid、created_atは省略可能
INSERT INTO orders (customer_id) VALUES (1);

1
2
3


 order_id | customer_id | order_date |  status  | is_paid |         created_at         
----------+-------------+------------+----------+---------+----------------------------
        1 |           1 | 2026-01-03 | pending  | f       | 2026-01-03 12:00:00.000000

正規化の理論と実践

正規化（Normalization）は、データの冗長性を排除し、更新時の異常を防ぐためのテーブル設計手法です。エドガー・F・コッドが提唱した関係モデルに基づいています。

なぜ正規化が必要なのか

正規化されていないテーブルは、以下の「更新異常」が発生する可能性があります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 正規化されていない注文テーブル（悪い例）
CREATE TABLE orders_denormalized (
    order_id        INTEGER PRIMARY KEY,
    customer_name   VARCHAR(100),
    customer_email  VARCHAR(255),
    customer_phone  VARCHAR(20),
    product_name    VARCHAR(100),
    product_price   INTEGER,
    quantity        INTEGER,
    order_date      DATE
);

このテーブルには以下の問題があります。

異常の種類	説明
挿入異常	注文がない顧客情報を登録できない
更新異常	顧客の電話番号を変更する際、すべての注文レコードを更新する必要がある
削除異常	顧客の最後の注文を削除すると、顧客情報も失われる

flowchart LR
    subgraph 問題のある設計
        A[注文テーブル<br/>顧客情報を含む]
    end
    
    subgraph 更新異常
        B[挿入異常<br/>注文なしで顧客登録不可]
        C[更新異常<br/>複数レコードの更新が必要]
        D[削除異常<br/>最後の注文削除で顧客情報消失]
    end
    
    A --> B
    A --> C
    A --> D

第1正規形（1NF）

第1正規形は、正規化の最初のステップです。以下の条件を満たす必要があります。

各カラムが原子値（分割不可能な値）のみを持つ
繰り返しグループ（配列や複数値）を持たない

違反例と修正

1
2
3
4
5
6


-- 第1正規形に違反（カンマ区切りで複数の値を格納）
CREATE TABLE orders_1nf_violation (
    order_id      INTEGER PRIMARY KEY,
    customer_name VARCHAR(100),
    products      VARCHAR(500)  -- 'ノートPC,マウス,キーボード' のように格納
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- 第1正規形に準拠（繰り返しグループを別テーブルに分離）
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_date    DATE NOT NULL
);

CREATE TABLE order_items (
    order_item_id  SERIAL PRIMARY KEY,
    order_id       INTEGER NOT NULL REFERENCES orders(order_id),
    product_name   VARCHAR(100) NOT NULL,
    quantity       INTEGER NOT NULL
);

第2正規形（2NF）

第2正規形は、第1正規形を満たした上で、以下の条件を追加します。

すべての非キーカラムが、主キー全体に完全関数従属する
主キーの一部にのみ依存するカラムを排除する

第2正規形は主に複合主キーを持つテーブルで問題になります。

違反例と修正

1
2
3
4
5
6
7
8
9


-- 第2正規形に違反
-- product_nameはproduct_idにのみ依存し、order_idには依存しない
CREATE TABLE order_items_2nf_violation (
    order_id      INTEGER,
    product_id    INTEGER,
    product_name  VARCHAR(100),  -- product_idにのみ依存
    quantity      INTEGER,
    PRIMARY KEY (order_id, product_id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


-- 第2正規形に準拠（部分関数従属を排除）
CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL
);

CREATE TABLE order_items (
    order_id      INTEGER,
    product_id    INTEGER REFERENCES products(product_id),
    quantity      INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

flowchart TD
    subgraph "第2正規形違反"
        A["order_items<br/>(order_id, product_id) → product_name, quantity"]
        B["product_name は product_id のみに依存"]
    end
    
    subgraph "第2正規形準拠"
        C["products<br/>product_id → product_name"]
        D["order_items<br/>(order_id, product_id) → quantity"]
    end
    
    A -- "分離" --> C
    A -- "分離" --> D

第3正規形（3NF）

第3正規形は、第2正規形を満たした上で、以下の条件を追加します。

すべての非キーカラムが、主キーに直接依存する
非キーカラムが他の非キーカラムに依存しない（推移的関数従属を排除）

違反例と修正

1
2
3
4
5
6
7
8
9


-- 第3正規形に違反
-- department_locationはdepartment_idに依存し、employee_idには直接依存しない
CREATE TABLE employees_3nf_violation (
    employee_id         SERIAL PRIMARY KEY,
    name                VARCHAR(100) NOT NULL,
    department_id       INTEGER NOT NULL,
    department_name     VARCHAR(50),   -- department_idに依存
    department_location VARCHAR(100)   -- department_idに依存
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


-- 第3正規形に準拠（推移的関数従属を排除）
CREATE TABLE departments (
    department_id       SERIAL PRIMARY KEY,
    department_name     VARCHAR(50) NOT NULL,
    department_location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER NOT NULL REFERENCES departments(department_id)
);

正規化の実践例

実際のECサイトを想定した正規化の流れを見てみましょう。

正規化前（非正規形）

1
2
3
4


注文データ:
order_id | customer_name | customer_email | products                          | order_date
---------|---------------|----------------|-----------------------------------|------------
1        | 田中太郎      | tanaka@ex.com  | ノートPC:120000:2, マウス:3500:1  | 2026-01-01

第1正規形

繰り返しグループを分離します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


CREATE TABLE orders_1nf (
    order_id        INTEGER,
    customer_name   VARCHAR(100),
    customer_email  VARCHAR(255),
    product_name    VARCHAR(100),
    product_price   INTEGER,
    quantity        INTEGER,
    order_date      DATE,
    PRIMARY KEY (order_id, product_name)
);

第2正規形

部分関数従属を排除します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


CREATE TABLE customers (
    customer_id     SERIAL PRIMARY KEY,
    customer_name   VARCHAR(100) NOT NULL,
    customer_email  VARCHAR(255) UNIQUE
);

CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    product_price INTEGER NOT NULL
);

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

CREATE TABLE order_items (
    order_id      INTEGER REFERENCES orders(order_id),
    product_id    INTEGER REFERENCES products(product_id),
    quantity      INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

第3正規形

推移的関数従属がないことを確認します。上記の設計は既に第3正規形を満たしています。

erDiagram
    customers ||--o{ orders : "places"
    customers {
        int customer_id PK
        string customer_name
        string customer_email UK
    }
    orders ||--|{ order_items : "contains"
    orders {
        int order_id PK
        int customer_id FK
        date order_date
    }
    products ||--o{ order_items : "included_in"
    products {
        int product_id PK
        string product_name
        int product_price
    }
    order_items {
        int order_id PK,FK
        int product_id PK,FK
        int quantity
    }

非正規化の判断基準

正規化は万能ではありません。過度な正規化はJOINの増加によるパフォーマンス低下を招きます。以下のケースでは意図的に非正規化を検討します。

ケース	非正規化の例
読み取りが圧倒的に多い	集計結果をカラムとして保持
JOINコストが許容できない	参照頻度の高いデータを複製
履歴データの保持	注文時点の商品名・価格を保持

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 非正規化の例：注文時点の商品情報を保持
CREATE TABLE order_items (
    order_id        INTEGER REFERENCES orders(order_id),
    product_id      INTEGER REFERENCES products(product_id),
    quantity        INTEGER NOT NULL,
    -- 注文時点の価格を保持（商品マスタの価格変更に影響されない）
    unit_price      INTEGER NOT NULL,
    product_name    VARCHAR(100) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

ER図の読み書き

ER図（Entity-Relationship Diagram）は、データベースの論理設計を視覚的に表現する図です。テーブル（エンティティ）とその関係（リレーションシップ）を示します。

エンティティの表現

エンティティ（テーブル）は矩形で表現し、属性（カラム）を内部に記述します。

erDiagram
    CUSTOMERS {
        int customer_id PK
        string name
        string email UK
        string phone
        date registered_at
    }

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

テーブル間の関係は、カーディナリティ（多重度）で表現します。

記法	意味
\|\|–\|\|	1対1（One-to-One）
\|\|–o{	1対多（One-to-Many）
o{–o{	多対多（Many-to-Many）

1対1の関係

erDiagram
    USERS ||--|| USER_PROFILES : has
    USERS {
        int user_id PK
        string username UK
        string email UK
    }
    USER_PROFILES {
        int user_id PK,FK
        string bio
        string avatar_url
        date birth_date
    }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- 1対1の関係：ユーザーとプロフィール
CREATE TABLE users (
    user_id   SERIAL PRIMARY KEY,
    username  VARCHAR(50) UNIQUE NOT NULL,
    email     VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INTEGER PRIMARY KEY REFERENCES users(user_id),
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birth_date  DATE
);

1対多の関係

erDiagram
    DEPARTMENTS ||--o{ EMPLOYEES : employs
    DEPARTMENTS {
        int department_id PK
        string name
        string location
    }
    EMPLOYEES {
        int employee_id PK
        int department_id FK
        string name
        int salary
    }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- 1対多の関係：部署と社員
CREATE TABLE departments (
    department_id  SERIAL PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    location       VARCHAR(100)
);

CREATE TABLE employees (
    employee_id    SERIAL PRIMARY KEY,
    department_id  INTEGER NOT NULL REFERENCES departments(department_id),
    name           VARCHAR(100) NOT NULL,
    salary         INTEGER NOT NULL
);

多対多の関係

多対多の関係は、中間テーブル（連関エンティティ）を介して表現します。

erDiagram
    STUDENTS }o--o{ COURSES : enrolls
    STUDENTS {
        int student_id PK
        string name
        string email UK
    }
    ENROLLMENTS {
        int student_id PK,FK
        int course_id PK,FK
        date enrolled_at
        string grade
    }
    COURSES {
        int course_id PK
        string name
        int credits
    }
    STUDENTS ||--o{ ENROLLMENTS : has
    COURSES ||--o{ ENROLLMENTS : has

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


-- 多対多の関係：学生と講義
CREATE TABLE students (
    student_id  SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE courses (
    course_id   SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    credits     INTEGER NOT NULL
);

-- 中間テーブル
CREATE TABLE enrollments (
    student_id   INTEGER REFERENCES students(student_id),
    course_id    INTEGER REFERENCES courses(course_id),
    enrolled_at  DATE NOT NULL DEFAULT CURRENT_DATE,
    grade        VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

ER図からDDLへの変換手順

ER図からCREATE TABLE文を作成する際は、以下の手順に従います。

各エンティティをテーブルとして定義
属性をカラムとして定義し、適切なデータ型を選択
主キーを設定
1対多のリレーションシップに外部キーを追加
多対多のリレーションシップに中間テーブルを作成
制約（NOT NULL、UNIQUE、CHECK、DEFAULT）を追加

テーブル設計のベストプラクティス

実務で使えるテーブル設計のベストプラクティスを紹介します。

命名規則

一貫した命名規則は、コードの可読性と保守性を向上させます。

項目	推奨規則	良い例	悪い例
テーブル名	複数形、スネークケース	users, order_items	User, OrderItem
カラム名	スネークケース	created_at, user_id	createdAt, userId
主キー	テーブル名の単数形_id	user_id, product_id	id, ID
外部キー	参照先テーブルの主キー名と同じ	user_id, department_id	user, dept
真偽値	is_ または has_ プレフィックス	is_active, has_permission	active, permission
日時	_at サフィックス	created_at, updated_at	created, modified

共通カラムの設計

多くのテーブルで共通して使用するカラムを標準化しましょう。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


-- 共通カラムを含むテーブルのテンプレート
CREATE TABLE example_table (
    -- 主キー
    example_id   SERIAL PRIMARY KEY,
    
    -- ビジネスカラム
    name         VARCHAR(100) NOT NULL,
    
    -- 監査カラム（共通）
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by   INTEGER,
    updated_by   INTEGER,
    
    -- 論理削除カラム（共通）
    is_deleted   BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at   TIMESTAMP
);

論理削除と物理削除

データの削除方法には論理削除と物理削除があります。

方式	メリット	デメリット
論理削除	データ復旧が可能、履歴を保持	クエリにWHERE条件が必要、データ量が増加
物理削除	データ量を削減、シンプルなクエリ	データ復旧不可、監査証跡が残らない

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 論理削除の実装例
CREATE TABLE users (
    user_id     SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    is_deleted  BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at  TIMESTAMP
);

-- 論理削除されていないデータのみ取得するビュー
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_deleted = FALSE;

-- 論理削除の実行
UPDATE users SET is_deleted = TRUE, deleted_at = CURRENT_TIMESTAMP
WHERE user_id = 1;

インデックスを考慮した設計

外部キーと頻繁に検索されるカラムにはインデックスを作成します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- 外部キーにインデックスを作成
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date    DATE NOT NULL,
    status        VARCHAR(20) NOT NULL DEFAULT 'pending'
);

-- 外部キーへのインデックス（JOINの高速化）
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 頻繁に検索されるカラムへのインデックス
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_order_date ON orders(order_date);

-- 複合インデックス（よく一緒に検索されるカラム）
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

テーブル設計チェックリスト

新しいテーブルを設計する際は、以下のチェックリストを確認しましょう。

主キーは設定されているか
必須項目にNOT NULL制約が設定されているか
一意であるべきカラムにUNIQUE制約が設定されているか
外部キー制約で参照整合性が保証されているか
ビジネスルールがCHECK制約で表現されているか
デフォルト値が適切に設定されているか
第3正規形まで正規化されているか（または意図的な非正規化か）
監査カラム（created_at, updated_at）が含まれているか
命名規則に従っているか
適切なインデックスが計画されているか

実践演習：ECサイトのテーブル設計

学んだ内容を活かして、シンプルなECサイトのテーブル設計を行います。

要件

ユーザーは会員登録してログインできる
商品にはカテゴリがあり、価格と在庫を持つ
ユーザーは商品を注文でき、1回の注文で複数の商品を購入できる
注文には配送先住所と支払い状況を記録する

ER図

erDiagram
    users ||--o{ orders : places
    users {
        int user_id PK
        string username UK
        string email UK
        string password_hash
        timestamp created_at
        timestamp updated_at
        boolean is_active
    }
    
    categories ||--o{ products : contains
    categories {
        int category_id PK
        string name UK
        string description
        int parent_category_id FK
    }
    
    products ||--o{ order_items : included_in
    products {
        int product_id PK
        int category_id FK
        string name
        text description
        int price
        int stock
        boolean is_available
        timestamp created_at
        timestamp updated_at
    }
    
    orders ||--|{ order_items : contains
    orders {
        int order_id PK
        int user_id FK
        string shipping_address
        string payment_status
        int total_amount
        timestamp ordered_at
        timestamp shipped_at
    }
    
    order_items {
        int order_id PK,FK
        int product_id PK,FK
        int quantity
        int unit_price
    }

DDL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66


-- ユーザーテーブル
CREATE TABLE users (
    user_id        SERIAL PRIMARY KEY,
    username       VARCHAR(50) UNIQUE NOT NULL,
    email          VARCHAR(255) UNIQUE NOT NULL,
    password_hash  VARCHAR(255) NOT NULL,
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- カテゴリテーブル（自己参照で階層構造を表現）
CREATE TABLE categories (
    category_id        SERIAL PRIMARY KEY,
    name               VARCHAR(100) UNIQUE NOT NULL,
    description        TEXT,
    parent_category_id INTEGER REFERENCES categories(category_id)
);

-- 商品テーブル
CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    category_id   INTEGER NOT NULL REFERENCES categories(category_id),
    name          VARCHAR(200) NOT NULL,
    description   TEXT,
    price         INTEGER NOT NULL,
    stock         INTEGER NOT NULL DEFAULT 0,
    is_available  BOOLEAN NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT price_must_be_positive CHECK (price > 0),
    CONSTRAINT stock_must_be_non_negative CHECK (stock >= 0)
);

-- 注文テーブル
CREATE TABLE orders (
    order_id          SERIAL PRIMARY KEY,
    user_id           INTEGER NOT NULL REFERENCES users(user_id),
    shipping_address  TEXT NOT NULL,
    payment_status    VARCHAR(20) NOT NULL DEFAULT 'pending',
    total_amount      INTEGER NOT NULL,
    ordered_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shipped_at        TIMESTAMP,
    CONSTRAINT valid_payment_status CHECK (
        payment_status IN ('pending', 'paid', 'failed', 'refunded')
    ),
    CONSTRAINT total_must_be_positive CHECK (total_amount > 0)
);

-- 注文明細テーブル
CREATE TABLE order_items (
    order_id     INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id   INTEGER REFERENCES products(product_id),
    quantity     INTEGER NOT NULL,
    unit_price   INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT quantity_must_be_positive CHECK (quantity > 0),
    CONSTRAINT unit_price_must_be_positive CHECK (unit_price > 0)
);

-- インデックスの作成
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_is_available ON products(is_available);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_payment_status ON orders(payment_status);
CREATE INDEX idx_orders_ordered_at ON orders(ordered_at);

まとめ

本記事では、PostgreSQLにおけるテーブル設計の基礎から実践までを解説しました。

テーブル設計で押さえるべきポイントを振り返ります。

CREATE TABLE文でテーブルを定義し、適切なデータ型とカラム構成を設計する
制約（PRIMARY KEY/FOREIGN KEY/UNIQUE/NOT NULL/CHECK/DEFAULT）でデータの整合性を保証する
正規化（第1〜第3正規形）でデータの冗長性を排除し、更新異常を防ぐ
ER図でテーブル間のリレーションシップを可視化し、設計をドキュメント化する
ベストプラクティス（命名規則、共通カラム、インデックス設計）で保守性の高いテーブル構造を実現する

適切なテーブル設計は、システムの品質と保守性に大きく影響します。本記事で学んだ知識を活用し、堅牢なデータベース設計を実践してください。

はじめに#

前提条件#

CREATE TABLE文によるテーブル定義#

基本構文#

シンプルなテーブル作成例#

テーブル制約の種類と設定方法#

PRIMARY KEY（主キー制約）#

FOREIGN KEY（外部キー制約）#

UNIQUE（一意制約）#

NOT NULL（非NULL制約）#

CHECK（検査制約）#

DEFAULT（デフォルト値）#

正規化の理論と実践#

なぜ正規化が必要なのか#

第1正規形（1NF）#

第2正規形（2NF）#

第3正規形（3NF）#

正規化の実践例#

非正規化の判断基準#

ER図の読み書き#

エンティティの表現#

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

ER図からDDLへの変換手順#

テーブル設計のベストプラクティス#

命名規則#

共通カラムの設計#

論理削除と物理削除#

インデックスを考慮した設計#

テーブル設計チェックリスト#

実践演習：ECサイトのテーブル設計#

要件#

ER図#

DDL#

まとめ#

参考リンク#

はじめに

前提条件

CREATE TABLE文によるテーブル定義

基本構文

シンプルなテーブル作成例

テーブル制約の種類と設定方法

PRIMARY KEY（主キー制約）

FOREIGN KEY（外部キー制約）

UNIQUE（一意制約）

NOT NULL（非NULL制約）

CHECK（検査制約）

DEFAULT（デフォルト値）

正規化の理論と実践

なぜ正規化が必要なのか

第1正規形（1NF）

第2正規形（2NF）

第3正規形（3NF）

正規化の実践例

非正規化の判断基準

ER図の読み書き

エンティティの表現

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

ER図からDDLへの変換手順

テーブル設計のベストプラクティス

命名規則

共通カラムの設計

論理削除と物理削除

インデックスを考慮した設計

テーブル設計チェックリスト

実践演習：ECサイトのテーブル設計

要件

ER図

DDL

まとめ

参考リンク