バッチャンネル

データベース設計の原則：正規化から実践的なスキーマ設計まで

データベース設計正規化SQLスキーマ

データベース設計の基本原則から正規化理論、実際のプロジェクトで使える設計パターンまで詳しく解説します。

データベース設計の原則

効率的で保守性の高いアプリケーションを構築するために、適切なデータベース設計は不可欠です。基本原則から実践的なテクニックまで学びましょう。

データベース設計の基本概念

エンティティとリレーションシップ

-- ユーザーエンティティ
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- プロフィールエンティティ（1対1関係）
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY,
    bio TEXT,
    avatar_url VARCHAR(255),
    birth_date DATE,
    location VARCHAR(100),
    website VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 投稿エンティティ（1対多関係）
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- タグエンティティ（多対多関係）
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 投稿とタグの中間テーブル
CREATE TABLE post_tags (
    post_id INTEGER,
    tag_id INTEGER,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

正規化理論

第 1 正規形（1NF）

-- 非正規化テーブル（悪い例）
CREATE TABLE orders_bad (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    products VARCHAR(500), -- 'Product1,Product2,Product3'
    quantities VARCHAR(100) -- '2,1,3'
);

-- 第1正規形（良い例）
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    product_name VARCHAR(100),
    quantity INTEGER,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

第 2 正規形（2NF）

-- 第1正規形だが第2正規形違反（悪い例）
CREATE TABLE order_details_bad (
    order_id INTEGER,
    product_id INTEGER,
    product_name VARCHAR(100), -- product_idに部分従属
    product_category VARCHAR(50), -- product_idに部分従属
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- 第2正規形（良い例）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    description TEXT
);

CREATE TABLE order_details (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

第 3 正規形（3NF）

-- 第2正規形だが第3正規形違反（悪い例）
CREATE TABLE employees_bad (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER,
    department_name VARCHAR(100), -- department_idに推移従属
    department_location VARCHAR(100) -- department_idに推移従属
);

-- 第3正規形（良い例）
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100),
    budget DECIMAL(12,2)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    position VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

インデックス設計

基本的なインデックス

-- 主キーインデックス（自動作成）
-- PRIMARY KEY制約により自動的に作成される

-- ユニークインデックス
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 単一カラムインデックス
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at);

-- 複合インデックス
CREATE INDEX idx_posts_user_status ON posts(user_id, status);
CREATE INDEX idx_posts_status_published ON posts(status, published_at);

-- 部分インデックス
CREATE INDEX idx_posts_published ON posts(published_at)
WHERE status = 'published';

-- 式インデックス
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

インデックス使用例とクエリ最適化

-- インデックスが効果的なクエリ
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE user_id = 123 AND status = 'published'
ORDER BY published_at DESC;

-- インデックスが効果的でないクエリ（改善が必要）
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE content LIKE '%keyword%'; -- 前方一致でない

-- 改善版：全文検索インデックス
CREATE INDEX idx_posts_content_fulltext ON posts
USING GIN(to_tsvector('japanese', content));

SELECT * FROM posts
WHERE to_tsvector('japanese', content) @@ to_tsquery('keyword');

実践的な設計パターン

ブログシステムの完全なスキーマ

-- ユーザー管理
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'user',
    is_active BOOLEAN DEFAULT true,
    email_verified_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- カテゴリ（階層構造）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id INTEGER,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 投稿
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    category_id INTEGER,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    excerpt TEXT,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft',
    featured_image VARCHAR(255),
    meta_title VARCHAR(255),
    meta_description TEXT,
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    comment_count INTEGER DEFAULT 0,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- コメント（階層構造）
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL,
    user_id INTEGER,
    parent_id INTEGER,
    author_name VARCHAR(100),
    author_email VARCHAR(100),
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

-- タグ
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    color VARCHAR(7), -- HEXカラーコード
    post_count INTEGER DEFAULT 0
);

-- 投稿とタグの関連
CREATE TABLE post_tags (
    post_id INTEGER,
    tag_id INTEGER,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- いいね機能
CREATE TABLE post_likes (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(post_id, user_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

E-commerce システムの設計例

-- 商品カテゴリ
CREATE TABLE product_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    parent_id INTEGER,
    description TEXT,
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    sort_order INTEGER DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES product_categories(id)
);

-- 商品
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INTEGER,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    short_description TEXT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2),
    stock_quantity INTEGER DEFAULT 0,
    weight DECIMAL(8,2),
    dimensions VARCHAR(50),
    is_active BOOLEAN DEFAULT true,
    is_featured BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES product_categories(id)
);

-- 商品バリエーション
CREATE TABLE product_variants (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock_quantity INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- 商品属性（サイズ、色など）
CREATE TABLE product_attributes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    type VARCHAR(20) DEFAULT 'text' -- text, select, color, etc.
);

CREATE TABLE product_attribute_values (
    id SERIAL PRIMARY KEY,
    attribute_id INTEGER NOT NULL,
    value VARCHAR(100) NOT NULL,
    FOREIGN KEY (attribute_id) REFERENCES product_attributes(id)
);

CREATE TABLE product_variant_attributes (
    variant_id INTEGER,
    attribute_value_id INTEGER,
    PRIMARY KEY (variant_id, attribute_value_id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE,
    FOREIGN KEY (attribute_value_id) REFERENCES product_attribute_values(id)
);

-- 注文管理
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    shipping_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'JPY',
    payment_status VARCHAR(20) DEFAULT 'pending',
    payment_method VARCHAR(50),
    shipping_address JSONB,
    billing_address JSONB,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    variant_id INTEGER,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id)
);

パフォーマンス最適化

適切なデータ型選択

-- 効率的なデータ型の選択
CREATE TABLE performance_example (
    -- 整数型：必要最小限のサイズを選択
    small_number SMALLINT,      -- -32,768 to 32,767
    medium_number INTEGER,      -- -2,147,483,648 to 2,147,483,647
    large_number BIGINT,        -- -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

    -- 文字列型：適切な長さを指定
    status CHAR(1),             -- 固定長（Y/N, M/F など）
    code VARCHAR(10),           -- 可変長（短い文字列）
    description TEXT,           -- 長い文字列

    -- 真偽値
    is_active BOOLEAN,          -- true/false

    -- 日時
    created_at TIMESTAMP,       -- タイムゾーン情報なし
    updated_at TIMESTAMPTZ,     -- タイムゾーン情報あり
    birth_date DATE,            -- 日付のみ

    -- 数値
    price DECIMAL(10,2),        -- 精密な小数点計算が必要
    ratio REAL,                 -- 近似値で十分

    -- JSON
    metadata JSONB              -- JSONより高性能
);

パーティショニング

-- 日付によるパーティショニング
CREATE TABLE logs (
    id SERIAL,
    created_at DATE NOT NULL,
    user_id INTEGER,
    action VARCHAR(50),
    data JSONB
) PARTITION BY RANGE (created_at);

-- パーティション作成
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE logs_2024_03 PARTITION OF logs
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- 各パーティションにインデックス作成
CREATE INDEX idx_logs_2024_01_user_id ON logs_2024_01(user_id);
CREATE INDEX idx_logs_2024_02_user_id ON logs_2024_02(user_id);
CREATE INDEX idx_logs_2024_03_user_id ON logs_2024_03(user_id);

マテリアライズドビュー

-- 集計データのマテリアライズドビュー
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as average_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- インデックス作成
CREATE UNIQUE INDEX idx_monthly_sales_month ON monthly_sales_summary(month);

-- 定期的な更新（cron jobなどで実行）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

セキュリティ考慮事項

アクセス制御

-- ユーザーとロールの作成
CREATE ROLE blog_read_only;
CREATE ROLE blog_write;
CREATE ROLE blog_admin;

-- 権限の付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO blog_read_only;
GRANT SELECT, INSERT, UPDATE ON posts, comments TO blog_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO blog_admin;

-- ユーザー作成と権限割り当て
CREATE USER blog_app WITH PASSWORD 'secure_password';
GRANT blog_write TO blog_app;

-- 行レベルセキュリティ
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_posts_policy ON posts
FOR ALL TO blog_app
USING (user_id = current_setting('app.current_user_id')::INTEGER);

データの暗号化

-- 機密データの暗号化
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    encrypted_ssn TEXT, -- 暗号化された社会保障番号
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- データの挿入時に暗号化
INSERT INTO sensitive_data (user_id, encrypted_ssn)
VALUES (1, crypt('123-45-6789', gen_salt('bf', 8)));

-- データの検索
SELECT id, user_id
FROM sensitive_data
WHERE encrypted_ssn = crypt('123-45-6789', encrypted_ssn);

バックアップと復旧戦略

バックアップスクリプト例

#!/bin/bash
# database_backup.sh

DB_NAME="myapp_production"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql"

# ディレクトリが存在しない場合は作成
mkdir -p $BACKUP_DIR

# データベースダンプ
pg_dump $DB_NAME > $BACKUP_FILE

# 圧縮
gzip $BACKUP_FILE

# 7日以上古いバックアップを削除
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${BACKUP_FILE}.gz"

復旧手順

-- データベースの復旧
-- 1. 新しいデータベース作成
CREATE DATABASE myapp_restored;

-- 2. バックアップからの復元
-- psql myapp_restored < backup_file.sql

-- 3. データ整合性チェック
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM posts;
SELECT COUNT(*) FROM comments;

-- 4. インデックス再構築（必要に応じて）
REINDEX DATABASE myapp_restored;

監視とメンテナンス

パフォーマンス監視クエリ

-- 実行時間が長いクエリの特定
SELECT
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- テーブルサイズの確認
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- インデックス使用状況
SELECT
    indexrelname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

定期メンテナンス

-- 統計情報の更新
ANALYZE;

-- 不要な領域の回収
VACUUM;

-- 完全なバキューム（テーブルロックが発生）
VACUUM FULL;

-- 自動バキュームの設定確認
SELECT name, setting
FROM pg_settings
WHERE name LIKE 'autovacuum%';

まとめ

効果的なデータベース設計は、アプリケーションの性能と保守性に大きく影響します。正規化理論を理解し、適切なインデックス設計を行い、セキュリティとパフォーマンスを考慮した設計を心がけましょう。

継続的な監視とメンテナンスにより、データベースの健全性を保ち、アプリケーションの成長に対応できる柔軟な設計を目指しましょう。

最後まで読んでいただきありがとうございました！てばさん（@basabasa8770）でした！

この記事をシェア

XFacebooknoteLINE