統合プロジェクト：Eコマースデータベースの設計と実装

Q: 注文番号の一意性をどのように確保しますか？

タイムスタンプと乱数を結合する（例： ORD202401151430520387 ）、UUIDを使用する、またはRedisの自動インクリメントシーケンスを利用するといった方法があります。注文の保存処理中に注文番号を CONCAT + DATE_FORMAT + RAND のように生成し、バックアップとしてUNIQUE制約を使用します。

Q: 在庫がオーバーセールになった場合はどうすればよいですか？

2つのアプローチがあります。ペシミスティックロックの場合は、 SELECT ... FOR UPDATE を使用して在庫行をロックし、トランザクション内で数量を差し引きます。オプティミスティックロックの場合は、バージョン UPDATE products SET stock = stock - N, version = version + 1 WHERE id = X AND version = V を使用します。高同時実行環境では、Redis を活用した事前差し引きと組み合わせたオプティミスティックロックの使用を推奨します。

Q: 注文テーブルが大きすぎませんか？

3つの対策があります。1. テーブルを月ごとに分割し、 orders_202401 と orders_202402 に分ける。2. MySQLのパーティショニングを使用する PARTITION BY RANGE (TO_DAYS(created_at)) 。3. アクティブなデータと非アクティブなデータを分離し、1年以上経過した注文を履歴テーブルにアーカイブする。クエリに時間条件を追加して、適切なパーティションを対象とする。

Q: 無限階層の分類構造はどのように設計すればよいですか？

parent_id を自己参照の外部キーとして使用し、最上位カテゴリとして parent_id = 0 を設定します。再帰的なCTE WITH RECURSIVE cat_tree AS (SELECT * FROM categories WHERE id = 1 UNION ALL SELECT c.* FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id) SELECT * FROM cat_tree; を使用してサブツリーをクエリします。

アリスが設立したECプラットフォーム「ShopEasy」は、このほど資金調達に成功し、Excelベースの管理体制からMySQLへの移行が必要となった。彼女は、ユーザー登録からデータ分析に至るまでの全プロセスを網羅する8つのモジュールからなるEC用データベースを、一から設計した。サービス開始以来、このプラットフォームは1日あたり5,000件以上の注文を処理しているが、データベースは依然として堅牢な状態を維持している。

1. プロジェクトの概要

8つの主要モジュール（ユーザー、商品、注文、ショッピングカート、決済、統計、権限、バックアップ）を網羅しています
各モジュールには、テーブルの作成やビジネスロジックを実現するための完全なSQL文が含まれています
ストアドプロシージャは注文処理のロジックをカプセル化し、トランザクションによってデータの整合性が確保されます。
ビューは、データの統計情報と、セキュリティのためのアクセス制御を提供します
バックアップスクリプトにより、データの復元性が確保されます

(1) モジュールおよび表の一覧

モジュール	コアテーブル	主な機能
ユーザーシステム	ユーザー／住所／ユーザーログ	登録・ログイン、住所管理、操作監査
製品システム	カテゴリ / 製品 / 製品画像	階層制限なしの分類、製品のCRUD操作、複数画像
注文システム	orders / order_items	注文の受付、在庫の減算、ステータスの更新
ショッピングカート	カート	ショッピングカートの追加、削除、編集、表示、統合
支払い履歴	支払い	支払い状況、返金
統計	閲覧数	売れ筋商品、売上ランキング、月次レポート
権限管理	MySQL ユーザー	読み取り専用／アプリケーション／管理者
バックアップ戦略	mysqldump	日次バックアップおよび保存期間ポリシー

(2) 完全なER図

erDiagram
    USERS ||--o{ ADDRESSES : has
    USERS ||--o{ ORDERS : places
    USERS ||--o{ CART : adds
    USERS ||--o{ USER_LOGS : generates
    CATEGORIES ||--o{ CATEGORIES : parent
    CATEGORIES ||--o{ PRODUCTS : contains
    PRODUCTS ||--o{ PRODUCT_IMAGES : has
    PRODUCTS ||--o{ ORDER_ITEMS : included_in
    PRODUCTS ||--o{ CART : added_to
    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS ||--o| PAYMENTS : paid_by

    USERS {
        bigint id PK
        varchar username
        varchar email
        varchar password_hash
        varchar phone
        enum status
    }
    ADDRESSES {
        bigint id PK
        bigint user_id FK
        varchar receiver
        varchar phone
        varchar province
        varchar city
        varchar detail
        tinyint is_default
    }
    USER_LOGS {
        bigint id PK
        bigint user_id FK
        varchar action
        varchar ip_address
        datetime created_at
    }
    CATEGORIES {
        bigint id PK
        varchar name
        bigint parent_id FK
        int sort_order
    }
    PRODUCTS {
        bigint id PK
        varchar name
        decimal price
        int stock
        bigint category_id FK
        enum status
    }
    PRODUCT_IMAGES {
        bigint id PK
        bigint product_id FK
        varchar image_url
        tinyint is_main
        int sort_order
    }
    ORDERS {
        bigint id PK
        varchar order_no
        bigint user_id FK
        decimal total_amount
        enum status
    }
    ORDER_ITEMS {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
        decimal price
    }
    CART {
        bigint id PK
        bigint user_id FK
        bigint product_id FK
        int quantity
    }
    PAYMENTS {
        bigint id PK
        bigint order_id FK
        varchar payment_no
        decimal amount
        enum method
        enum status
    }

2. 要件分析とER設計

(1) 要件分析

ECシステムの主要な業務フロー：ユーザー登録 → 商品の閲覧 → カートへの追加 → 注文 → 決済 → 発送 → 完了。各ステップには、それに対応するテーブルと制約が必要です。

(2) 関係の説明

関係	種類	説明
ユーザー → 住所	1対多	1人のユーザーが複数の配送先住所を持つことができる
ユーザー → 注文	1対多	1人のユーザーが複数の注文を行うことができる
注文 → 注文明細	1対多	1つの注文には複数の品目が含まれる
商品 → 注文明細	1対多	1つの商品が複数の注文に含まれることがある
カテゴリ → カテゴリ	自己参照	無限階層の分類を実現するための parent_id
カテゴリ → 商品	1対多	1つのカテゴリに複数の商品
商品 → 画像	1対多	商品ごとに複数の画像
注文 → 支払い	1対1	注文ごとに1件の支払い記録

▶ 例：データベースの作成

SQL

CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ecommerce;

▶ 試してみよう

3. ユーザーシステム

(1) テーブル構造の設計

SQL

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(255) DEFAULT '/images/default_avatar.png',
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_phone (phone),
    INDEX idx_users_status (status)
) ENGINE=InnoDB COMMENT='User Table';

CREATE TABLE addresses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    receiver VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    district VARCHAR(30) NOT NULL,
    detail VARCHAR(200) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_addresses_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='List of Shipping Addresses';

CREATE TABLE user_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    action VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    detail JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs_user (user_id),
    INDEX idx_logs_action (action),
    INDEX idx_logs_created (created_at)
) ENGINE=InnoDB COMMENT='User Activity Log Table';

(2) 登録およびログインの確認

▶ 例：ユーザー登録

SQL

INSERT INTO users (username, email, password_hash, phone)
VALUES (
    'alice',
    'alice@example.com',
    SHA2('MySecret123!', 256),
    '13800138000'
);

INSERT INTO users (username, email, password_hash, phone)
VALUES (
    'bob_dev',
    'bob@example.com',
    SHA2('BobPass456!', 256),
    '13900139000'
);

▶ 試してみよう

▶ 例：ログイン認証

SQL

SELECT id, username, status FROM users
WHERE email = 'alice@example.com'
  AND password_hash = SHA2('MySecret123!', 256)
  AND status = 'active';

▶ 試してみよう

▶ 例：住所管理

SQL

INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Guangdong', 'Shenzhen', 'Nanshan', 'Tech Park Bldg 5 Room 301', 1);

INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Beijing', 'Beijing', 'Haidian', 'Zhongguancun St 88', 0);

SELECT * FROM addresses WHERE user_id = 1 ORDER BY is_default DESC;

▶ 試してみよう

▶ 例：ユーザーアクティビティログ

SQL

INSERT INTO user_logs (user_id, action, ip_address, user_agent, detail)
VALUES (1, 'LOGIN', '192.168.1.100', 'Chrome/120', '{"method": "email"}');

INSERT INTO user_logs (user_id, action, ip_address, detail)
VALUES (1, 'UPDATE_ADDRESS', '192.168.1.100', '{"address_id": 1}');

SELECT action, COUNT(*) AS cnt
FROM user_logs
WHERE user_id = 1
GROUP BY action
ORDER BY cnt DESC;

▶ 試してみよう

4. 製品システム

(1) 無限階層分類

SQL

CREATE TABLE categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id BIGINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    icon VARCHAR(255),
    is_visible TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_categories_parent (parent_id),
    INDEX idx_categories_sort (sort_order)
) ENGINE=InnoDB COMMENT='Product Category Table';

▶ 例：カテゴリカルデータ

SQL

INSERT INTO categories (id, name, parent_id, sort_order) VALUES
(1, 'Electronics', 0, 1),
(2, 'Clothing', 0, 2),
(3, 'Home & Living', 0, 3),
(4, 'Smartphones', 1, 1),
(5, 'Laptops', 1, 2),
(6, 'Men', 2, 1),
(7, 'Women', 2, 2),
(8, 'Kitchen', 3, 1);

SELECT c1.name AS category, c2.name AS sub_category
FROM categories c1
LEFT JOIN categories c2 ON c1.id = c2.parent_id
WHERE c1.parent_id = 0
ORDER BY c1.sort_order, c2.sort_order;

▶ 試してみよう

(2) 製品と画像

SQL

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    original_price DECIMAL(10,2),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    sales INT NOT NULL DEFAULT 0,
    category_id BIGINT NOT NULL,
    status ENUM('active', 'inactive', 'sold_out') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_products_category (category_id),
    INDEX idx_products_status (status),
    INDEX idx_products_sales (sales DESC),
    INDEX idx_products_created (created_at DESC),
    FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB COMMENT='Product List';

CREATE TABLE product_images (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_main TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_images_product (product_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Product Image Table';

▶ 例：製品のCRUD

SQL

INSERT INTO products (name, description, price, original_price, stock, category_id, status)
VALUES
('Galaxy S24 Ultra', 'Samsung flagship 2024', 1299.99, 1399.99, 200, 4, 'active'),
('MacBook Pro 16', 'Apple M3 Max laptop', 2499.00, 2499.00, 50, 5, 'active'),
('Cotton T-Shirt', 'Pure cotton casual tee', 29.99, 39.99, 500, 6, 'active'),
('Smart Rice Cooker', 'IoT enabled cooker', 89.99, 119.99, 300, 8, 'active');

INSERT INTO product_images (product_id, image_url, is_main, sort_order) VALUES
(1, '/images/galaxy_s24_1.jpg', 1, 1),
(1, '/images/galaxy_s24_2.jpg', 0, 2),
(2, '/images/macbook_pro_1.jpg', 1, 1),
(3, '/images/tshirt_white.jpg', 1, 1),
(4, '/images/cooker_main.jpg', 1, 1);

SELECT p.id, p.name, p.price, pi.image_url
FROM products p
LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_main = 1
WHERE p.category_id = 4 AND p.status = 'active'
ORDER BY p.sales DESC
LIMIT 10;

UPDATE products SET stock = stock - 1, sales = sales + 1 WHERE id = 1;

UPDATE products SET status = 'sold_out' WHERE stock <= 0;

▶ 試してみよう

5. 注文システム

(1) 注文および注文品目

SQL

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    pay_amount DECIMAL(12,2) DEFAULT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled', 'refunded')
        DEFAULT 'pending',
    receiver VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    shipping_address TEXT NOT NULL,
    remark VARCHAR(500),
    paid_at DATETIME DEFAULT NULL,
    shipped_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_created (created_at DESC),
    INDEX idx_orders_order_no (order_no),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='Orders Table';

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * price) STORED,
    INDEX idx_items_order (order_id),
    INDEX idx_items_product (product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB COMMENT='Order Details';

(2) 注文状況の変更

現在の状態	あり得る状態	トリガー条件
保留中	支払済み	支払完了
保留中	キャンセル	ユーザーによるキャンセル／タイムアウトによる未払い
有料	発送済み	出品者による発送
支払い済み	返金済み	返金をリクエスト
発送済み	完了	購入者による受領確認済み
完了	返金	アフターサービスによる返金

▶ 例：発注ストアドプロシージャ（在庫減算＋取引を含む）

SQL

DELIMITER //
CREATE PROCEDURE sp_create_order(
    IN p_user_id BIGINT,
    IN p_receiver VARCHAR(50),
    IN p_phone VARCHAR(20),
    IN p_address TEXT,
    IN p_remark VARCHAR(500)
)
BEGIN
    DECLARE v_order_no VARCHAR(32);
    DECLARE v_total DECIMAL(12,2) DEFAULT 0;
    DECLARE v_cart_item INT;
    DECLARE v_product_id BIGINT;
    DECLARE v_quantity INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;
    DECLARE v_name VARCHAR(200);
    DECLARE done INT DEFAULT 0;
    DECLARE cart_cursor CURSOR FOR
        SELECT c.product_id, c.quantity, p.price, p.name, p.stock
        FROM cart c
        JOIN products p ON c.product_id = p.id
        WHERE c.user_id = p_user_id AND p.status = 'active'
        FOR UPDATE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    START TRANSACTION;

    SET v_order_no = CONCAT(
        'ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
        LPAD(FLOOR(RAND() * 10000), 4, '0')
    );

    INSERT INTO orders (order_no, user_id, receiver, receiver_phone, shipping_address, remark)
    VALUES (v_order_no, p_user_id, p_receiver, p_phone, p_address, p_remark);

    OPEN cart_cursor;
    read_loop: LOOP
        FETCH cart_cursor INTO v_product_id, v_quantity, v_price, v_name, v_stock;
        IF done THEN LEAVE read_loop; END IF;

        IF v_stock < v_quantity THEN
            ROLLBACK;
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = CONCAT('Insufficient stock for: ', v_name);
        END IF;

        INSERT INTO order_items (order_id, product_id, product_name, quantity, price)
        VALUES (LAST_INSERT_ID(), v_product_id, v_name, v_quantity, v_price);

        UPDATE products
        SET stock = stock - v_quantity, sales = sales + v_quantity
        WHERE id = v_product_id;

        SET v_total = v_total + v_quantity * v_price;
    END LOOP;
    CLOSE cart_cursor;

    UPDATE orders SET total_amount = v_total WHERE order_no = v_order_no;
    DELETE FROM cart WHERE user_id = p_user_id;

    COMMIT;
END //
DELIMITER ;

▶ 試してみよう

▶ 例：注文の方法

SQL

CALL sp_create_order(
    1,
    'Alice',
    '13800138000',
    'Tech Park Bldg 5 Room 301, Nanshan, Shenzhen, Guangdong',
    'Please deliver before noon'
);

▶ 試してみよう

▶ 例：注文状況の更新

SQL

UPDATE orders SET status = 'paid', pay_amount = total_amount, paid_at = NOW()
WHERE id = 1 AND status = 'pending';

UPDATE orders SET status = 'shipped', shipped_at = NOW()
WHERE id = 1 AND status = 'paid';

UPDATE orders SET status = 'completed', completed_at = NOW()
WHERE id = 1 AND status = 'shipped';

▶ 試してみよう

6. ショッピングカート

(1) テーブル構造

SQL

CREATE TABLE cart (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    selected TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_cart_user_product (user_id, product_id),
    INDEX idx_cart_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Shopping Cart Table';

▶ 例：ショッピングカートのCRUD操作

SQL

INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 1, 2);

INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 2, 1)
ON DUPLICATE KEY UPDATE quantity = quantity + 1;

UPDATE cart SET quantity = 3 WHERE user_id = 1 AND product_id = 1;

DELETE FROM cart WHERE user_id = 1 AND product_id = 2;

SELECT c.product_id, p.name, p.price, c.quantity,
       p.price * c.quantity AS subtotal
FROM cart c
JOIN products p ON c.product_id = p.id
WHERE c.user_id = 1 AND c.selected = 1;

▶ 試してみよう

▶ 例：ショッピングカートの統合

SQL

DELIMITER //
CREATE PROCEDURE sp_merge_cart(
    IN p_source_user_id BIGINT,
    IN p_target_user_id BIGINT
)
BEGIN
    INSERT INTO cart (user_id, product_id, quantity, selected)
    SELECT p_target_user_id, product_id, quantity, selected
    FROM cart
    WHERE user_id = p_source_user_id
    ON DUPLICATE KEY UPDATE
        quantity = cart.quantity + VALUES(quantity),
        selected = 1;

    DELETE FROM cart WHERE user_id = p_source_user_id;
END //
DELIMITER ;

CALL sp_merge_cart(2, 1);

▶ 試してみよう

7. 支払い履歴

(1) テーブル構造

SQL

CREATE TABLE payments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    payment_no VARCHAR(64) NOT NULL UNIQUE,
    amount DECIMAL(12,2) NOT NULL,
    method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer') NOT NULL,
    status ENUM('pending', 'success', 'failed', 'refunded') DEFAULT 'pending',
    paid_at DATETIME DEFAULT NULL,
    refund_no VARCHAR(64) DEFAULT NULL,
    refund_amount DECIMAL(12,2) DEFAULT NULL,
    refund_reason VARCHAR(500) DEFAULT NULL,
    refunded_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payments_order (order_id),
    INDEX idx_payments_no (payment_no),
    INDEX idx_payments_status (status),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB COMMENT='Payment History Report';

(2) 支払状況の推移

現在の状態	あり得る状態	トリガー条件
処理中	成功	決済ゲートウェイのコールバックが成功しました
処理中	失敗	決済ゲートウェイのコールバックに失敗しました
成功	返金済み	返金手続き中

▶ 例：決済取引

SQL

INSERT INTO payments (order_id, payment_no, amount, method, status, paid_at)
VALUES (1, CONCAT('PAY', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000),4,'0')),
        3899.97, 'credit_card', 'success', NOW());

UPDATE orders SET status = 'paid', pay_amount = 3899.97, paid_at = NOW()
WHERE id = 1 AND status = 'pending';

SELECT p.payment_no, p.amount, p.method, p.status, p.paid_at
FROM payments p
WHERE p.order_id = 1;

▶ 試してみよう

▶ 例：返金手続き

SQL

UPDATE payments
SET status = 'refunded',
    refund_no = CONCAT('REF', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000),4,'0')),
    refund_amount = 3899.97,
    refund_reason = 'Defective product',
    refunded_at = NOW()
WHERE order_id = 1 AND status = 'success';

UPDATE orders SET status = 'refunded' WHERE id = 1;

▶ 試してみよう

8. データ統計ビュー

▶ 例：ベストセラー商品一覧

SQL

CREATE OR REPLACE VIEW v_hot_products AS
SELECT
    p.id,
    p.name,
    p.price,
    c.name AS category,
    p.sales,
    IFNULL(SUM(oi.quantity), 0) AS order_sold,
    IFNULL(SUM(oi.quantity * oi.price), 0) AS revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
GROUP BY p.id, p.name, p.price, c.name, p.sales
ORDER BY p.sales DESC;

SELECT * FROM v_hot_products LIMIT 10;

▶ 試してみよう

▶ 例：ユーザーの支出ランキング

SQL

CREATE OR REPLACE VIEW v_user_ranking AS
SELECT
    u.id,
    u.username,
    COUNT(DISTINCT o.id) AS total_orders,
    IFNULL(SUM(o.total_amount), 0) AS total_spent,
    IFNULL(AVG(o.total_amount), 0) AS avg_order_amount,
    MAX(o.created_at) AS last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

SELECT * FROM v_user_ranking LIMIT 20;

▶ 試してみよう

▶ 例：月次売上レポート

SQL

CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS revenue,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    SUM(CASE WHEN o.status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
    ROUND(
        SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) / COUNT(o.id) * 100,
        2
    ) AS completion_rate
FROM orders o
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month DESC;

SELECT * FROM v_monthly_sales;

▶ 試してみよう

9. 権限管理とバックアップポリシー

(1) 権限マトリックス

ユーザー	SELECT	INSERT	UPDATE	DELETE	EXECUTE	ALTER	DROP	CREATE
ecom_readonly	Y	-	-	-	-	-	-	-
ecom_app	Y	Y	Y	-	Y	-	-	-
ecom_admin	Y	Y	Y	Y	Y	Y	Y	Y

▶ 例：権限を持つユーザーの作成

SQL

CREATE USER 'ecom_readonly'@'%' IDENTIFIED BY 'ReadOnly2024!';
GRANT SELECT ON ecommerce.* TO 'ecom_readonly'@'%';

CREATE USER 'ecom_app'@'%' IDENTIFIED BY 'AppUser2024!';
GRANT SELECT, INSERT, UPDATE ON ecommerce.* TO 'ecom_app'@'%';
GRANT EXECUTE ON ecommerce.* TO 'ecom_app'@'%';

CREATE USER 'ecom_admin'@'127.0.0.1' IDENTIFIED BY 'Admin2024!Secure';
GRANT ALL PRIVILEGES ON ecommerce.* TO 'ecom_admin'@'127.0.0.1';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'ecom_app'@'%';

▶ 試してみよう

(2) バックアップ戦略

バックアップの種類	頻度	保存期間	ツール
完全バックアップ	毎日 02:00	30日間	mysqldump
バイナリログ	リアルタイム	7日間	binlog
テーブル構造のバックアップ	毎週日曜日	90日間	mysqldump --no-data

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

BASH

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DB_NAME="ecommerce"
DB_USER="ecom_admin"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

mysqldump -u $DB_USER -pAdmin2024!Secure \
    --single-transaction \
    --routines \
    --triggers \
    --views \
    $DB_NAME | gzip > ${BACKUP_DIR}/${DB_NAME}_full_${DATE}.sql.gz

mysqldump -u $DB_USER -pAdmin2024!Secure \
    --no-data \
    $DB_NAME | gzip > ${BACKUP_DIR}/${DB_NAME}_schema_${DATE}.sql.gz

find $BACKUP_DIR -name "${DB_NAME}_full_*.sql.gz" -mtime +30 -delete
find $BACKUP_DIR -name "${DB_NAME}_schema_*.sql.gz" -mtime +90 -delete

echo "Backup completed: ${DATE}"

▶ 例：データの復元

BASH

gunzip < /backup/mysql/ecommerce_full_20240115_020000.sql.gz | mysql -u ecom_admin -p ecommerce

10. インデックス設計チェックリスト

テーブル	インデックス名	列	データ型	用途
ユーザー	idx_users_phone	電話番号	BTREE	電話番号でログイン
ユーザー	idx_users_status	ステータス	BTREE	ステータスによるフィルタリング
アドレス	idx_addresses_user	user_id	BTREE	ユーザーアドレス一覧
カテゴリ	idx_categories_parent	parent_id	BTREE	サブカテゴリを検索
製品	idx_products_category	category_id	BTREE	カテゴリ別製品一覧
製品	idx_products_status	ステータス	BTREE	掲載ステータスで絞り込む
製品	idx_products_sales	売上 DESC	BTREE	ベストセラー順
製品	idx_products_created	created_at DESC	BTREE	新着順
注文	idx_orders_user	user_id	BTREE	ユーザーの注文一覧
注文	idx_orders_status	ステータス	BTREE	ステータスによるフィルタリング
注文	idx_orders_created	created_at DESC	BTREE	時間順
注文	idx_orders_order_no	order_no	UNIQUE	注文番号検索
注文品目	注文品目インデックス	注文ID	BTREE	注文詳細
注文アイテム	商品アイテムID	商品ID	BTREE	商品販売統計
cart	uk_cart_user_product	user_id,product_id	UNIQUE	ショッピングカートから重複を削除
支払い	idx_payments_order	order_id	BTREE	注文の支払い照会
支払	idx_payments_status	ステータス	BTREE	支払ステータスによるフィルタリング
user_logs	idx_logs_user	user_id	BTREE	ユーザーログ
user_logs	idx_logs_created	created_at	BTREE	時間範囲クエリ

❓ よくある質問

Q 注文番号の一意性をどのように確保しますか？

A タイムスタンプと乱数を結合する（例：ORD202401151430520387）、UUIDを使用する、またはRedisの自動インクリメントシーケンスを利用するといった方法があります。注文の保存処理中に注文番号をCONCAT + DATE_FORMAT + RANDのように生成し、バックアップとしてUNIQUE制約を使用します。

Q 在庫がオーバーセールになった場合はどうすればよいですか？

A 2つのアプローチがあります。ペシミスティックロックの場合は、SELECT ... FOR UPDATE を使用して在庫行をロックし、トランザクション内で数量を差し引きます。オプティミスティックロックの場合は、バージョン UPDATE products SET stock = stock - N, version = version + 1 WHERE id = X AND version = V を使用します。高同時実行環境では、Redis を活用した事前差し引きと組み合わせたオプティミスティックロックの使用を推奨します。

Q 注文テーブルが大きすぎませんか？

A 3つの対策があります。1. テーブルを月ごとに分割し、orders_202401とorders_202402に分ける。2. MySQLのパーティショニングを使用する PARTITION BY RANGE (TO_DAYS(created_at))。3. アクティブなデータと非アクティブなデータを分離し、1年以上経過した注文を履歴テーブルにアーカイブする。クエリに時間条件を追加して、適切なパーティションを対象とする。

Q 無限階層の分類構造はどのように設計すればよいですか？

A parent_idを自己参照の外部キーとして使用し、最上位カテゴリとしてparent_id = 0を設定します。再帰的なCTE

WITH RECURSIVE cat_tree AS (SELECT * FROM categories WHERE id = 1 UNION ALL SELECT c.* FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id) SELECT * FROM cat_tree;

を使用してサブツリーをクエリします。

Q ショッピングカートはデータベースに保存されますか、それともRedisに保存されますか？

A ログイン済みのユーザーの場合、ショッピングカートはデータベースに保存され（永続化のため）、Redisにキャッシュされます（高速な読み書き操作のため）。ログインしていないユーザーの場合、ショッピングカートはRedisまたはLocalStorageに保存され、ログイン時にデータベースに統合されます。ショッピングカートの操作は頻繁に行われるため、Redisを使用することでデータベースへの負荷を軽減できます。

Q パスワードのハッシュ化に、なぜMD5ではなくSHA-2を使うのですか？

A MD5は衝突攻撃に対して脆弱であることが証明されており、レインボーテーブルを使って容易に解読されてしまいます。SHA-2-256の方が安全性が高いため、本番環境では、ソルト値を含み、計算コストを調整できるbcryptやArgon2の使用を推奨します。

Q なぜ order_items に重複する product_name が含まれているのですか？

A 商品名は変更される場合がありますが、注文のスナップショットには注文が作成された時点の情報が保持される必要があります。これは「アンチ正規化」の典型的な例であり、商品名が変更された後に過去の注文が正しく表示されなくなるのを防ぐため、データの整合性を確保する代わりに、わずかなストレージ容量を犠牲にしているのです。

📖 まとめ

要件分析が出発点であり、ER図によってエンティティと関係が整理され、8つのモジュールがeコマースのプロセス全体を網羅しています。
ユーザーシステム：3つのテーブルによる連携――usersテーブルはIDを管理し、addressesテーブルは配送先住所を管理し、user_logsテーブルは監査ログを管理する
製品システム：parent_id の自己参照機能を用いて無限階層の分類を実現；product_images は複数の画像に対応
注文システムがその中核をなしています。ストアドプロシージャが注文処理のロジックをカプセル化し、FOR UPDATEが過剰販売を防ぎ、トランザクションが原子性を保証します。
ショッピングカート：ON DUPLICATE KEY UPDATE を使用して、商品のカートへの追加および統合を実装します。ユーザー間の統合はストアドプロシージャによって処理されます。
支払い履歴は注文とは別管理されており、ステータスの更新状況が明確に表示され、払い戻しの追跡に必要な項目がすべて揃っています
データ分析：ビューを使用して複雑な集計クエリをカプセル化し、ワンクリックでベストセラー、ランキング、月次レポートを生成する
権限管理とバックアップはセキュリティの基盤です：最小権限の原則＋毎日のフルバックアップ＋増分バイナリログによる復旧

📝 練習問題

基本問題（難易度：⭐）：Eコマース用データベースを作成し、4つのテーブル（users、addresses、categories、products）を作成するためのSQL文を記述し、テストデータを挿入してください。
上級演習（難易度 ⭐⭐）：ショッピングカート機能を完全に実装する。テーブルを作成し、CRUD操作のためのSQLクエリを記述し、sp_merge_cartストアドプロシージャを作成し、ON DUPLICATE KEY UPDATEを使用してカートへの重複追加を処理する。
実践演習（難易度：⭐⭐⭐）：注文処理ストアドプロシージャ sp_create_order を実装してください。これには、ショッピングカートの内容を一通り処理し、在庫を確認（FOR UPDATE）、注文とその詳細を登録し、在庫を差し引き、ショッピングカートをクリアする処理を含める必要があります。この一連の処理はすべてトランザクションで囲む必要があります。在庫が不足している場合は、トランザクションをロールバックし、エラーをスローしてください。
分析問題（難易度：⭐⭐⭐）：3つの統計ビュー（「ベストセラー商品」「ユーザー別支出ランキング」「月次売上レポート」）を作成し、ビューの結果を確認するためのクエリを記述するとともに、ordersテーブルの行数が1,000万行を超えた場合のビューのパフォーマンス問題を分析し、最適化策を提案してください。
課題（難易度：⭐⭐⭐⭐）：ShopEasyのeコマースデータベースを完全にデプロイしてください。これには、データベースおよびテーブルの作成、インデックス、ストアドプロシージャ、ビュー、ユーザー権限（読み取り専用／アプリ／管理者）に加え、毎日のバックアップスクリプトも含まれます。さらに、初期設定の手順や、日常の運用・保守における重要なポイントをまとめた、200文字のデプロイメントドキュメントを作成してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて

404 Not Found

統合プロジェクト：Eコマースデータベースの設計と実装

1. プロジェクトの概要

(1) モジュールおよび表の一覧

(2) 完全なER図

2. 要件分析とER設計

(1) 要件分析

(2) 関係の説明

▶ 例：データベースの作成

3. ユーザーシステム

(1) テーブル構造の設計

(2) 登録およびログインの確認

▶ 例：ユーザー登録

▶ 例：ログイン認証

▶ 例：住所管理

▶ 例：ユーザーアクティビティログ

4. 製品システム

(1) 無限階層分類

▶ 例：カテゴリカルデータ

(2) 製品と画像

▶ 例：製品のCRUD

5. 注文システム

(1) 注文および注文品目

(2) 注文状況の変更

▶ 例：発注ストアドプロシージャ（在庫減算＋取引を含む）

▶ 例：注文の方法

▶ 例：注文状況の更新

6. ショッピングカート

(1) テーブル構造

▶ 例：ショッピングカートのCRUD操作

▶ 例：ショッピングカートの統合

7. 支払い履歴

(1) テーブル構造

(2) 支払状況の推移

▶ 例：決済取引

▶ 例：返金手続き

8. データ統計ビュー

▶ 例：ベストセラー商品一覧

▶ 例：ユーザーの支出ランキング

▶ 例：月次売上レポート

9. 権限管理とバックアップポリシー

(1) 権限マトリックス

▶ 例：権限を持つユーザーの作成

(2) バックアップ戦略

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

▶ 例：データの復元

10. インデックス設計チェックリスト

❓ よくある質問

📖 まとめ

📝 練習問題