第二章：数据库设计与建模

说实话，数据库设计这事儿，看着简单，但坑特别多。我见过太多项目，前期图省事随便建几张表，结果业务一跑起来，各种查询慢、数据对不上、改字段改到崩溃。今天咱们就拿这个在线学习投资系统来练练手，把用户表、课程表、交易记录表、资产表这四张核心表给整明白。

2.1 设计原则：别急着写SQL

我个人的习惯是，先想清楚业务场景，再动手画ER图，最后才写建表语句。你想想看，如果连用户和课程的关系都没理清，直接写CREATE TABLE，后面肯定要返工。

这里有几个核心原则，我踩过坑后总结出来的：

  • 字段类型要抠门：能用INT就别用BIGINT，能用VARCHAR(32)就别给255。我在一个项目里见过有人把性别字段设成VARCHAR(255)，那叫一个浪费。
  • 主键用自增ID：别整什么UUID当主键，查询性能差得离谱。除非你有分布式需求，否则老老实实用INT AUTO_INCREMENT。
  • 时间字段统一用DATETIME：别混用TIMESTAMP和DATETIME，否则后面做报表统计时你会想哭。
  • 预留扩展字段：比如加个extra JSON字段，方便以后业务扩展。我曾经因为没预留，被迫改过表结构，那叫一个痛苦。
重点提醒： 数据库设计不是一次性的。业务在变，表结构也要跟着迭代。但核心表（比如用户表）的改动成本极高，所以一开始就要想清楚。

2.2 四张核心表的设计

2.2.1 用户表（users）

用户表是系统的基石。我建议把用户的基本信息和认证信息分开，但为了演示方便，咱们先放一起。

字段名 类型 说明
id INT UNSIGNED AUTO_INCREMENT 主键
username VARCHAR(32) NOT NULL UNIQUE 用户名，唯一索引
password_hash VARCHAR(128) NOT NULL 密码哈希值，别存明文
email VARCHAR(64) 邮箱，用于找回密码
phone VARCHAR(16) 手机号
status TINYINT DEFAULT 1 状态：1正常 0禁用
created_at DATETIME 注册时间
updated_at DATETIME 最后更新时间
小技巧： 密码字段长度给128，是因为我用的是bcrypt加密，输出长度固定。如果你用MD5，64位就够了。但别用MD5，真的不安全。

2.2.2 课程表（courses）

课程表相对简单，但要注意价格字段。我建议用DECIMAL(10,2)，别用FLOAT，否则算账时会有精度问题。

字段名 类型 说明
id INT UNSIGNED AUTO_INCREMENT 主键
title VARCHAR(128) NOT NULL 课程标题
description TEXT 课程描述
price DECIMAL(10,2) NOT NULL 价格，单位元
instructor_id INT UNSIGNED 讲师ID，关联用户表
status TINYINT DEFAULT 1 状态：1上架 0下架
created_at DATETIME 创建时间
注意： 讲师ID这里用了外键关联用户表。但实际项目中，如果数据量大了，外键会影响写入性能。我一般会在应用层做校验，数据库层面只建索引，不建外键约束。

2.2.3 交易记录表（transactions）

这张表最核心，也最容易出问题。我建议把交易类型、金额、状态都设计成枚举值，方便统计。

字段名 类型 说明
id BIGINT UNSIGNED AUTO_INCREMENT 主键，用BIGINT因为交易量可能很大
user_id INT UNSIGNED NOT NULL 用户ID
course_id INT UNSIGNED 课程ID，可为空（比如充值）
type TINYINT NOT NULL 类型：1购买 2退款 3充值
amount DECIMAL(10,2) NOT NULL 交易金额
status TINYINT DEFAULT 0 状态：0待支付 1成功 2失败
trade_no VARCHAR(64) UNIQUE 业务订单号，唯一
created_at DATETIME 创建时间
关键点： trade_no一定要唯一，而且要在业务层生成。我曾经因为并发问题，出现过重复订单号，导致对账对了一整天。后来改成「时间戳+随机数+用户ID」的组合，再没出过问题。

2.2.4 资产表（assets）

资产表记录每个用户的余额。注意，这里只存当前余额，历史流水靠交易记录表来追溯。

字段名 类型 说明
id INT UNSIGNED AUTO_INCREMENT 主键
user_id INT UNSIGNED NOT NULL UNIQUE 用户ID，唯一
balance DECIMAL(10,2) DEFAULT 0.00 当前余额
frozen DECIMAL(10,2) DEFAULT 0.00 冻结金额（比如退款处理中）
updated_at DATETIME 最后更新时间
经验之谈： 更新余额时，一定要用UPDATE ... SET balance = balance + ? WHERE user_id = ?这种原子操作，别先查再改。否则并发下会出现数据不一致。

2.3 ER图：理清关系

画ER图其实不难，关键是搞清楚实体之间的关系。咱们这四张表的关系是这样的：

  • 一个用户可以有多个交易记录（1:N）
  • 一个用户只有一个资产记录（1:1）
  • 一个课程可以被多个用户购买（M:N），但咱们通过交易记录来关联

下面我用SVG画个简单的ER图，帮你直观理解：

用户表 (users) 资产表 (assets) 交易记录表 (transactions) 课程表 (courses) 1:1 1:N 1:N 用户相关 交易相关 课程相关

你看，用户表和资产表是1:1关系，因为一个用户只有一个账户余额。用户和交易记录是1:N，一个用户可以有多次购买记录。课程和交易记录也是1:N，一门课程可以被多次购买。

2.4 MySQL建表实战

好了，理论说完了，咱们直接上SQL。我习惯把建表语句写在一个文件里，方便版本管理。

-- 用户表
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(32) NOT NULL UNIQUE,
    password_hash VARCHAR(128) NOT NULL,
    email VARCHAR(64),
    phone VARCHAR(16),
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 课程表
CREATE TABLE courses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(128) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    instructor_id INT UNSIGNED,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_instructor (instructor_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 交易记录表
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    course_id INT UNSIGNED,
    type TINYINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 0,
    trade_no VARCHAR(64) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_trade_no (trade_no),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 资产表
CREATE TABLE assets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL UNIQUE,
    balance DECIMAL(10,2) DEFAULT 0.00,
    frozen DECIMAL(10,2) DEFAULT 0.00,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
避坑指南： 我曾经在生产环境忘了加索引，结果交易记录表查用户历史订单时，直接全表扫描，把数据库CPU干到100%。所以建表时顺手把索引加上，别偷懒。

2.5 设计时的几个常见问题

嗯，这里再聊几个我经常被问到的问题：

  • 要不要用外键？ 我个人建议，小项目可以用，但大项目最好别用。外键会影响写入性能，而且分布式场景下根本没法用。在应用层做数据一致性校验更灵活。
  • 金额字段为什么用DECIMAL？ 因为FLOAT和DOUBLE有精度问题。你存个0.1，实际可能是0.0999999... 做金融系统，一分钱都不能差。
  • 时间字段用DATETIME还是TIMESTAMP？ 我统一用DATETIME。TIMESTAMP有2038年问题，而且受时区影响。DATETIME更直观，范围也更大。

好了，数据库设计这块就聊到这儿。记住，好的设计是改出来的，不是一次想出来的。先把这四张表建好，后面业务跑起来，你自然会知道哪里需要优化。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →