电商库表设计实战

第 7 章 · 电商库表设计实战

本章目标：在 db-demo 项目中完成 shop_db 电商核心库的完整 DDL walkthrough；逐表讲解 categories、products、users、orders、order_lines、carts 的字段含义、外键约束与索引策略；理解 slug + is_published + price（分） 字段约定；输出与 django-web ch05/ch07 的字段对照表，为 ch08～ch12 的 Python 访问层打基础。

学时建议：5～6 小时（含 2 小时 DDL 跟练与 ER 图绘制）

前置：完成 python-database ch01～ch06（SQL 查询、JOIN、事务、DDL、ER 与范式）；了解基本电商业务名词。

7.1 场景说明：从 ER 图到可执行 DDL

ch06 已将 shop_db 抽象为 ER 模型。本章把模型落地为 MySQL 8 / SQLite 3 均可执行的 DDL，作为 db-demo 与后续 shop-db 毕业项目（ch17） 的 schema 基线。

说明：教学项目路径 ~/python-learn/db-demo；数据库名 shop_db；对外 API 域名 https://api.example.com 仅为占位，不涉及任何企业内部仓库。

字段演进约定（全模块统一）：

7.2 全局 ER 关系图

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│ categories  │ 1   N │  products   │ N   M │    tags     │  （ch10 扩展，本章预留）
│ id, slug    │◄──────│ category_id │       │  (可选)     │
└─────────────┘       └──────┬──────┘       └─────────────┘
                             │
         ┌───────────────────┼───────────────────┐
         │ N                 │ N                 │ N
         ▼                   ▼                   ▼
┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│ cart_items  │       │ order_lines │       │  (reviews)  │  ch17 扩展
│ cart_id     │       │ order_id    │       └─────────────┘
│ product_id  │       │ product_id  │
└──────┬──────┘       └──────┬──────┘
       │ N                 │ N
       ▼ 1                 ▼ 1
┌─────────────┐       ┌─────────────┐
│   carts     │       │   orders    │
│ user_id     │       │ user_id     │
└──────┬──────┘       └──────┬──────┘
       │ N                 │ N
       └─────────┬─────────┘
                 ▼ 1
          ┌─────────────┐
          │    users    │
          │ id, email   │
          └─────────────┘

关系摘要：

7.3 建库与字符集

MySQL：

CREATE DATABASE IF NOT EXISTS shop_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE shop_db;

SQLite（db-demo 默认）：

mkdir -p ~/python-learn/db-demo/data
touch ~/python-learn/db-demo/data/shop_db.sqlite3

SQLite 无 CREATE DATABASE；文件即库。本章 DDL 以 SQLite 语法为主，MySQL 差异在 §7.10 说明。

7.4 表 1：categories（商品分类）

CREATE TABLE IF NOT EXISTS categories (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL,
    slug        TEXT    NOT NULL UNIQUE,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_categories_sort
    ON categories (sort_order, id);

设计要点：

• slug 全局唯一，供 /catalog/{slug}/ 路由使用（对照 django-web Category.slug）。
• 不在分类表存商品数量；用 COUNT(*) 或冗余字段（ch13 优化再议）。

7.5 表 2：products（商品）

CREATE TABLE IF NOT EXISTS products (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id   INTEGER NOT NULL,
    title         TEXT    NOT NULL,
    slug          TEXT    NOT NULL UNIQUE,
    description   TEXT,
    price         INTEGER NOT NULL CHECK (price >= 0),
    stock         INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    is_published  INTEGER NOT NULL DEFAULT 0 CHECK (is_published IN (0, 1)),
    created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at    TEXT    NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_products_category_published
    ON products (category_id, is_published, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_products_published_price
    ON products (is_published, price);

价格（分）示例：

索引策略 §7.5.1：

7.6 表 3：users（用户）

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    email         TEXT    NOT NULL UNIQUE,
    password_hash TEXT    NOT NULL,
    nickname      TEXT    NOT NULL DEFAULT '',
    is_active     INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
    created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_users_active
    ON users (is_active);

django-web 使用 Django 内置 User 模型字段更多（is_staff、last_login 等）；本章为 db-demo 最小子集。

7.7 表 4：orders 与 order_lines（订单）

7.7.1 orders

CREATE TABLE IF NOT EXISTS orders (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER NOT NULL,
    order_no    TEXT    NOT NULL UNIQUE,
    status      TEXT    NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total_cents INTEGER NOT NULL DEFAULT 0 CHECK (total_cents >= 0),
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_orders_user_created
    ON orders (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_orders_status
    ON orders (status, created_at DESC);

7.7.2 order_lines

CREATE TABLE IF NOT EXISTS order_lines (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id     INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   INTEGER NOT NULL CHECK (unit_price >= 0),
    line_total   INTEGER NOT NULL CHECK (line_total >= 0),
    FOREIGN KEY (order_id) REFERENCES orders(id)