3、数据库设计（上）：ER图绘制、范式与反范式、MySQL/SQLite表结构设计实战

数据库设计，说白了就是给数据找个好房子住。房子设计得好，数据存取就快，后期维护也省心。我见过太多项目，前期图省事随便建表，结果上线半年后，每次加个功能都要改表结构，改得程序员想骂娘。

这一章，咱们就聊聊怎么把数据库设计得既规范又实用。我会结合自己踩过的坑，带你从ER图开始，一路走到MySQL和SQLite的表结构实战。

3.1 从业务需求到ER图：画图是门手艺

拿到一个需求，别急着写SQL。先画图。ER图（实体-关系图）就是你和业务方沟通的桥梁。

实体是什么？ 说白了就是业务中的对象。比如用户、订单、商品。每个实体都有属性，比如用户有姓名、邮箱、手机号。

关系是什么？ 实体之间的联系。一个用户可以有多个订单，这叫一对多。一个学生可以选多门课，一门课也可以被多个学生选，这叫多对多。

我个人习惯用 Chen氏表示法 或者 乌鸦脚表示法。乌鸦脚更直观，适合给非技术人员看。

举个例子，一个简单的电商系统：

• 用户(User)：用户ID、用户名、邮箱、注册时间
• 订单(Order)：订单ID、用户ID、总金额、下单时间
• 商品(Product)：商品ID、商品名、价格、库存
• 订单项(OrderItem)：订单ID、商品ID、数量、单价

关系：用户和订单是1对N，订单和商品是M对N（通过订单项实现）。

嗯，这里要注意：多对多关系一定要拆成两个一对多。我在项目中遇到过有人直接在订单表里存商品ID列表，用逗号分隔。结果查某个商品卖了多少单时，SQL写得跟天书一样。千万别这么干。

3.2 范式与反范式：规范与性能的博弈

范式是数据库设计的理论指导。但理论归理论，实战中得灵活变通。

3.2.1 三大范式速览

范式	核心要求	通俗理解
第一范式(1NF)	每列不可再分	一个字段只存一个值，别搞列表
第二范式(2NF)	非主键列完全依赖于主键	联合主键时，别让某列只依赖主键的一部分
第三范式(3NF)	非主键列不传递依赖于主键	别存冗余数据，比如用户姓名在订单表里又存一遍

第一范式：比如用户表里有个“兴趣爱好”字段，存成“篮球,游泳,读书”。这就违反了1NF。应该拆成单独的兴趣表。

第二范式：比如订单明细表，主键是(订单ID, 商品ID)。如果你把“商品名称”也放进来，它就只依赖于商品ID，不依赖于订单ID。这就违反了2NF。

第三范式：比如订单表里存了“用户姓名”。明明可以通过用户ID查到，为什么还要存？这就是冗余。除非你有特殊理由。

3.2.2 反范式：什么时候该打破规则？

范式化减少了冗余，但增加了查询时的JOIN操作。JOIN多了，性能就下来了。

我曾经做过一个报表系统，数据量上千万。完全按第三范式设计，每次跑报表要JOIN七八张表，耗时十几秒。后来我把一些常用字段冗余到主表里，查询时间降到了1秒以内。

什么时候该反范式？

• 读多写少：比如商品详情页，频繁查询，很少修改。可以把分类名称、品牌名称冗余到商品表里。
• 统计报表：预计算一些汇总字段，比如“订单总数”、“总销售额”，直接存到用户表里。
• 避免复杂JOIN：如果某个查询要JOIN超过3张表，考虑冗余。

3.3 MySQL vs SQLite：选型与实战

MySQL和SQLite都是关系型数据库，但定位完全不同。

特性	MySQL	SQLite
部署方式	客户端-服务器模式	嵌入式，单文件
并发能力	高并发，多用户	低并发，单用户为主
适用场景	Web应用、企业系统	移动端、桌面端、小工具
数据类型	丰富（JSON、空间数据等）	基础类型（INTEGER、TEXT、REAL）
存储限制	取决于硬件，可达TB级	通常2GB以内（理论140TB）

我个人习惯：Web后端用MySQL，原型验证或小工具用SQLite。你想想看，如果只是做个个人博客，用MySQL还得装个服务，SQLite一个文件搞定，多省事。

3.4 表结构设计实战：从ER图到DDL

好，理论讲完了，咱们来点实际的。假设我们要设计一个简单的博客系统，包含用户、文章、评论。

3.4.1 用户表 (users)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(255),
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

注意几个点：

• UNIQUE约束：用户名和邮箱必须唯一，这是业务要求。
• password_hash：千万别存明文密码！存哈希值。
• created_at和updated_at：每个表都应该有这两个字段，方便排查问题。

3.4.2 文章表 (posts)

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('draft', 'published', 'deleted') DEFAULT 'draft',
    view_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

这里我用了 ENUM 来表示文章状态。为什么不用TINYINT？因为ENUM可读性更好，查数据时直接看到是'draft'还是'published'，不用猜0和1代表什么。

嗯，这里要注意：ON DELETE CASCADE 表示删除用户时，他的文章也会被删。如果你不想这样，可以用 ON DELETE SET NULL 或者干脆不加外键约束。我在项目中遇到过因为外键约束导致删除失败的情况，后来干脆在应用层处理逻辑删除。

3.4.3 评论表 (comments)

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    parent_id INT DEFAULT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

parent_id 是用来实现嵌套评论的。如果评论是回复某条评论，parent_id就指向那条评论的ID。如果是最顶层评论，parent_id为NULL。

这种设计叫 邻接表模型，简单直观。但查询多层嵌套时性能会差一些。如果你需要频繁查询评论树，可以考虑用 嵌套集模型 或者 闭包表。不过对于大多数博客系统，邻接表已经够用了。

3.4.4 索引设计：别让查询变慢

表建好了，但数据一多，查询就慢。这时候需要索引。

-- 为文章表的 user_id 加索引，方便查询某个用户的所有文章
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 为评论表的 post_id 加索引，方便查询某篇文章的所有评论
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- 为评论表的 parent_id 加索引，方便查询某条评论的子评论
CREATE INDEX idx_comments_parent_id ON comments(parent_id);

索引不是越多越好。每个索引都会增加写入时的开销。我见过有人给每个字段都加了索引，结果写入速度慢得像蜗牛。记住：只为查询频繁的字段加索引。

3.5 SQLite版本：轻量级实现

如果你用SQLite，DDL基本一样，只是有些细微差别：

-- SQLite 不支持 ENUM，用 TEXT 加 CHECK 约束替代
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    status TEXT CHECK(status IN ('draft', 'published', 'deleted')) DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- SQLite 的索引创建方式一样
CREATE INDEX idx_posts_user_id ON posts(user_id);

注意：SQLite的 AUTOINCREMENT 和MySQL略有不同。SQLite默认就会自增，加上AUTOINCREMENT只是保证ID不会重复使用。我个人习惯不加，除非有特殊需求。

3.6 避坑指南：我踩过的那些坑

3.7 小结

数据库设计没有银弹。范式化保证数据一致性，反范式化提升查询性能。关键是要根据业务场景做权衡。

我个人建议：先按第三范式设计，上线后通过监控发现慢查询，再针对性地做反范式优化。别一开始就过度设计，也别完全不管规范。

下一章，咱们聊聊数据库设计的下半部分：索引优化、查询优化、以及分库分表策略。到时候我会分享一些真实的性能调优案例，保证让你收获满满。