从概念模型到物理表结构的数据库设计方法

前言

写 CURD 之前的第一步是设计表结构。表设计不好，后面写代码处处别扭——要么数据冗余得到处 UPDATE，要么查一个东西要 JOIN 七八张表。

这篇文章从实际设计流程出发，从需求分析到最终建表语句。

一、数据库设计的四个阶段

1
2
3

需求分析 → 概念设计 → 逻辑设计 → 物理设计
 (搞清楚     (ER 图)    (ER 图转   (建表语句、
  有什么)               表结构)     索引、分区)

二、需求分析：把业务描述变成数据需求

以一个简化版电商系统为例，业务描述如下：

用户可以注册账号，一个用户可以有多个收货地址。商品有名称、价格、库存、分类。用户下单时从购物车生成订单，一个订单包含多个商品项。支付后订单状态变更。

从这段描述中提取实体和关系：

实体（Entity）：用户、收货地址、商品、商品分类、订单、订单项（订单行）

关系（Relationship）：

用户 1:N 收货地址
商品 N:1 商品分类
用户 1:N 订单
订单 1:N 订单项
订单项 N:1 商品

三、概念设计：画 ER 图

用文字描述也可以，但 ER 图更直观。核心要素只有两个：

1
2
3

实体（矩形） ── 关系（菱形） ── 实体（矩形）
                │
          1:1 / 1:N / M:N

电商系统的 ER 图（文字版）：

[用户] ──1:N── [收货地址]
[用户] ──1:N── [订单]
[订单] ──1:N── [订单项] ──N:1── [商品]
[商品] ──N:1── [商品分类]

关键动作：检查所有 M:N 关系——这种关系在逻辑设计阶段需要拆成中间表。

四、逻辑设计：ER 图转表结构

4.1 实体 → 表

每个实体变成一张表：

用户表（users）：用户 ID、用户名、密码、手机号、注册时间

商品表（products）：商品 ID、分类 ID、名称、价格、库存、描述、状态

分类表（categories）：分类 ID、分类名、父分类 ID、排序

订单表（orders）：订单 ID、用户 ID、订单号、总金额、状态、创建时间

订单项表（order_items）：订单项 ID、订单 ID、商品 ID、数量、单价

收货地址表（addresses）：地址 ID、用户 ID、收货人、电话、省市区、详细地址

4.2 1:N 关系 → 外键

1 的一端主键作为 N 的一端的外键。比如 orders.user_id 指向 users.id。

4.3 M:N 关系 → 中间表

如果出现 M:N 关系（比如学生和课程是多对多），需要额外建一张中间表。

五、范式设计：三范式就够了

第一范式（1NF）：列不可再分

1 2	❌ address: "广东省深圳市南山区" -- 一串字符串 ✅ province: "广东省", city: "深圳市", district: "南山区" -- 分开存

第二范式（2NF）：非主键列完全依赖于主键

❌ 订单项表: (order_id, product_id, product_name, quantity)
           product_name 只依赖 product_id，不依赖 order_id（部分依赖）

✅ 拆成两张表：order_items 只存 (order_id, product_id, quantity)
              products 里有 product_name

第三范式（3NF）：非主键列不传递依赖于主键

❌ 订单表: (id, user_id, user_name, user_phone)
           user_name 依赖 user_id，user_id 依赖 id（传递依赖）

✅ 订单表只存 user_id，用户信息在 users 表里

六、什么时候该反范式

规范化不是越彻底越好。有些场景故意冗余数据，换取查询性能。

典型反范式场景：

-- 订单项表存了当时的商品单价
CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    order_id BIGINT,
    product_id BIGINT,
    product_name VARCHAR(200),   -- 冗余，但如果商品改名不影响历史订单
    price DECIMAL(10,2),          -- 冗余，存下单时的价格
    quantity INT
);

商品名和单价在 products 表里有，但这里也存了一份。原因是：订单是历史快照——商品后来改名字或涨价，不应该影响已下单的订单记录。

反范式的判断标准：这个字段的值需要”定格在某个时间点”吗？如果需要，就应该冗余存下来。

七、完整建表语句

-- 用户表
CREATE TABLE users (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone         VARCHAR(20)  NOT NULL,
    avatar        VARCHAR(255) DEFAULT '',
    created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 商品分类表
CREATE TABLE categories (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    parent_id   BIGINT DEFAULT 0 COMMENT '0=顶级分类',
    sort_order  INT DEFAULT 0,
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

-- 商品表
CREATE TABLE products (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL,
    title       VARCHAR(200) NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    stock       INT DEFAULT 0,
    description TEXT,
    status      TINYINT DEFAULT 1 COMMENT '1=上架 0=下架',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_status_price (status, price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 收货地址表
CREATE TABLE addresses (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT NOT NULL,
    receiver_name VARCHAR(50)  NOT NULL,
    phone         VARCHAR(20)  NOT NULL,
    province      VARCHAR(50),
    city          VARCHAR(50),
    district      VARCHAR(50),
    detail        VARCHAR(255),
    is_default    TINYINT DEFAULT 0,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表';

-- 订单表
CREATE TABLE orders (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no    VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    user_id     BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
    status      VARCHAR(20) DEFAULT 'pending'
                COMMENT 'pending=待支付 paid=已支付 shipped=已发货 completed=已完成 cancelled=已取消',
    address_snapshot TEXT COMMENT '下单时的地址快照（JSON）',
    paid_at     DATETIME,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单项表
CREATE TABLE order_items (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id    BIGINT NOT NULL,
    product_id  BIGINT NOT NULL,
    product_title VARCHAR(200) COMMENT '下单时商品名（冗余）',
    price       DECIMAL(10,2) NOT NULL COMMENT '下单时单价',
    quantity    INT NOT NULL,
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单项表';