如何设计数据库Schema

设计数据库 Schema（模式/表结构）是系统开发中非常关键的一步，直接决定性能、可维护性和扩展能力。下面我按通用流程 + 实战示例 + 常见坑来系统说明。

一、设计数据库 Schema 的基本流程

1. 需求分析

先搞清楚：

• 系统有哪些核心业务对象（用户、订单、商品…）
• 对象之间有什么关系（1对1 / 1对多 / 多对多）
• 需要支持哪些查询场景（按时间查、按用户查、统计类？）

2. 概念模型（ER 图）

用实体–关系模型表达：

• 实体（Entity）
• 属性（Attribute）
• 关系（Relationship）

3. 逻辑模型（表结构）

把 ER 模型转成：

• 表（Table）
• 字段（Column）
• 主键 / 外键
• 约束（NOT NULL、UNIQUE）

4. 物理模型（数据库实现）

考虑：

• 存储引擎（InnoDB / MyISAM）
• 索引设计
• 分区 / 分表策略

二、核心设计原则

1. 范式 vs 反范式

三大范式（简化版）

• 1NF：字段不可再分
• 2NF：非主键字段依赖完整主键
• 3NF：非主键字段不传递依赖

✅ 优点：减少冗余
❌ 缺点：多表 JOIN 多

反范式（适度冗余）

• 常用统计字段直接冗余（如 order 表里存 user_name）
• 读多写少场景非常适合

2. 主键设计

• 优先使用 自增 ID（简单、索引友好）
• 分布式系统可用 雪花 ID
• 避免使用业务字段做主键（如手机号）

3. 外键 vs 逻辑关联

• 小型系统：可用外键保证一致性
• 中大型系统：通常不用外键，在代码层维护

4. 索引设计

• 高频查询字段建索引
• 联合索引遵循最左前缀
• 避免过多索引（影响写入）

三、实战示例：电商系统

实体

• 用户（User）
• 商品（Product）
• 订单（Order）
• 订单明细（OrderItem）

表结构示例

-- 用户表
CREATE TABLE user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE product (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

-- 订单表
CREATE TABLE `order` (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  total_price DECIMAL(10,2),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id)
);

-- 订单明细（多对多）
CREATE TABLE order_item (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  INDEX idx_order (order_id)
);

四、常见设计坑

❌ 字段类型过大（用 TEXT 存短字符串）
❌ 所有字段都建索引
❌ 用字符串存日期
❌ 忽略软删除（is_deleted）
❌ 不分环境（测试/生产混用）

五、进阶建议

• 使用 迁移工具（Flyway / Liquibase）
• 写 数据字典
• 对核心表做 审计字段（created_by / updated_at）

如果你有具体业务场景（如社交、物联网、风控），我可以帮你直接画 ER 图或写完整 Schema。