【MySQL(MariaDB)】実運用を想定したECサイト用のER図設計

MySQL(MariaDB)で実運用を想定したECサイトのデータベース設計についての記事です

実運用前提・論理削除あり・拡張しやすいEC向け設計として整理します。

以下は 「実務でよく使われる・破綻しにくい構成」 です。

① users（一般利用者）テーブル

ポイント
作成・更新日時は 必須
論理削除は今回は不要（退会＝物理削除 or 別途対応が多い）

Create_User.sql

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

② orders（注文）テーブル

ポイント
status は ENUM か VARCHAR
実務では ENUM より VARCHAR + 定数管理 が多い
合計金額は 確定値として保存

Create_Order.sql

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  total INT NOT NULL,
  status VARCHAR(30) NOT NULL, 
  order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

status例

Status	Statusの意味
pending	注文受付
paid	支払い完了
shipped	発送済み
completed	完了
cancelled	キャンセル

③ order_items（注文明細）テーブル

ポイント
購入時点の価格を保存（超重要）
数量 × 価格 ＝ 合計算出

Create_Order_Items.sql

CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id VARCHAR(50) NOT NULL,
  quantity INT NOT NULL,
  price INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

products（商品）テーブル ※論理削除対応

ポイント
物理削除しない
is_active：販売中かどうか
deleted_at：論理削除フラグ
在庫は 現在の在庫数

Create_Product.sql

CREATE TABLE products (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255),
  price INT NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,

  -- 論理削除
  deleted_at DATETIME DEFAULT NULL,

  -- 管理者トラッキング
  created_by VARCHAR(50) DEFAULT NULL,
  updated_by VARCHAR(50) DEFAULT NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (created_by) REFERENCES adminusers(id),
  FOREIGN KEY (updated_by) REFERENCES adminusers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

商品の登録・更新イメージ
1️⃣ 商品登録時

INSERT_Product.sql

INSERT INTO products (
  id, name, price, stock, created_by, updated_by
) VALUES (
  'P001', '商品A', 1000, 10, 'admin001', 'admin001'
);

2️⃣ 商品更新時

UPDATE_Product.sql

UPDATE products
SET price = 1200,
    updated_by = 'admin002'
WHERE id = 'P001';

3️⃣ 誰が操作したか確認

CHECK_UPDATE_Product.sql

SELECT
  p.name,
  cu.name AS created_admin,
  uu.name AS updated_admin
FROM products p
LEFT JOIN adminusers cu ON p.created_by = cu.id
LEFT JOIN adminusers uu ON p.updated_by = uu.id;

product_photos（商品写真）テーブル

ポイント
商品が論理削除されても注文履歴の参照は壊れない
1商品に複数画像OK

CREATE_Product_Photos.sql

CREATE TABLE product_photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id VARCHAR(50) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

管理者テーブル

CREATE_AdminUsers.sql

CREATE TABLE adminusers (
  id VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  gender CHAR(1) NOT NULL COMMENT 'M:男性 F:女性 O:その他',
  office VARCHAR(20) NOT NULL,
  admin_role TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_adminusers_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

管理者の写真テーブル

CREATE_AdminUser_Photos.sql

CREATE TABLE adminuser_photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  adminuser_id VARCHAR(50) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  sort_order INT NOT NULL,
  FOREIGN KEY (adminuser_id) REFERENCES adminusers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ER関係（管理者を含めた全体像）

adminusers 1 ── n products（created_by / updated_by）
adminusers 1 ── n adminuser_photos

users 1 ── n orders
orders 1 ── n order_items
products 1 ── n order_items
products 1 ── n product_photos

サイト

【Spring Boot入門】#9 1対多の関係テーブルにおける登録機能を作成する