---
name: design-db
description: "[PRD2PLAN] 数据库表结构设计规范。涵盖命名约定、数据类型、索引设计、约束、关系映射、软删除、迁移管理。适用于所有后端技术栈的数据库设计与评审。"
whenToUse: 设计新的数据库表结构、评审现有数据库设计、制定数据库规范或检查表结构是否符合最佳实践时使用。适用于独立于 PRD2PLAN 流程的数据库设计或评审任务。
allowed-tools: 
disable: false
---

# 数据库设计规范

## 一、命名约定

### 表命名
- 小写 + 下划线：`users`、`order_items`
- 复数名词表示集合：`orders`、`projects`、`attachments`
- 关联表：`{表A}_{表B}`（按字母序）：`role_user`、`project_tag`
- 可按模块加前缀：`{module}_xxx`（如 `biz_orders`、`sys_config`），项目内统一即可
- **禁止**：中文字段名、大小写混用、`table1` 类无意义命名

### 列命名
- 小写 + 下划线：`user_name`、`created_at`
- 主键统一：`id`（自增 BIGINT）或 UUID 字符串（如 `id VARCHAR(36)`）
- 外键：`{表名单数}_id`：`user_id` → `users.id`
- 布尔标记：`is_xxx`：`is_active`、`is_deleted`
- 时间：`xxx_at`：`created_at`、`approved_at`
- 金额：`xxx_amount`，单位分或保留 2 位小数

### 索引命名

| 类型 | 格式 | 示例 |
|------|------|------|
| 普通索引 | `idx_{表}_{列}` | `idx_orders_status` |
| 组合索引 | `idx_{表}_{列1}_{列2}` | `idx_orders_user_status` |
| 唯一索引 | `uk_{表}_{列}` | `uk_users_email` |

---

## 二、必备字段

### 每张业务表建议包含

| 字段 | 类型 | 说明 |
|------|------|------|
| `id` | `BIGINT NOT NULL AUTO_INCREMENT` | 主键。或 UUID 字符串 |
| `created_at` | `DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP` | 创建时间 |
| `updated_at` | `DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` | 更新时间 |
| `created_by` | `BIGINT / VARCHAR(64)` | 创建人（审计需要则必填） |
| `updated_by` | `BIGINT / VARCHAR(64)` | 修改人（审计需要则必填） |

### 审计字段选型

| 风格 | 适用场景 |
|------|----------|
| `created_at` + `created_by` + `updated_at` + `updated_by` | 新项目首选，语义直观 |
| `create_time` + `creator` + `modify_time` + `modifier` | 中文团队常见风格 |
| `gmt_create` + `gmt_modified` | 阿里系风格 |

**规则**：一个项目内只能选一种，禁止混用。审计字段名从项目现有约定中提取，新项目自由选择。

---

## 三、数据类型

| 场景 | 推荐类型 | 反例 |
|------|----------|------|
| 主键、数量 | `BIGINT` | 用 `INT` 存超 21 亿的值 |
| 短文本（≤255） | `VARCHAR(n)`，给具体长度 | `VARCHAR(255)` 不给原因 |
| 长文本 | `TEXT` / `MEDIUMTEXT` | 用 `VARCHAR(10000)` |
| 布尔/开关/状态 | `TINYINT` + COMMENT 注释 | `ENUM` 类型（扩展需 ALTER TABLE） |
| JSON 数据 | `JSON` 类型 | 用 TEXT 存 JSON |
| 金额 | `DECIMAL(18,2)` | `FLOAT` / `DOUBLE`（精度丢失） |
| 日期时间 | `DATETIME` | `TIMESTAMP`（2038 问题） |
| 日期（无时间） | `DATE` | 用 DATETIME 存纯日期 |
| 百分比 | `DECIMAL(5,2)` | `FLOAT` |
| 文件大小 | `BIGINT`（字节） | `VARCHAR` |

### CHAR vs VARCHAR
- `CHAR` 仅用于固定长度值（如 MD5: `CHAR(32)`、手机号、身份证号）
- 其余一律 `VARCHAR`

---

## 四、索引设计

### 必须建索引的场景
- WHERE 条件列
- JOIN 的 ON 列（外键）
- ORDER BY 列
- GROUP BY 列
- 唯一业务键（UNIQUE 约束天然是索引）

### 组合索引原则
1. 等值条件在前，范围条件在后
2. 区分度高的列在前
3. 最左前缀匹配

```sql
-- ✅ 查询: WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at);

-- ❌ 不符合最左前缀
CREATE INDEX idx_orders_created_user_status ON orders(created_at, user_id, status);
```

### 索引禁忌
- 不要为每个列单独建索引（浪费空间、拖慢写入）
- 不要在低基数列（如性别、type≤3）上建单列索引
- 不要对大字段建索引（TEXT、长 VARCHAR）
- 组合索引建议不超过 5 列

### 覆盖索引
查询只需索引中的列时，避免回表：
```sql
CREATE INDEX idx_orders_user_status_id_amount ON orders(user_id, status, id, amount);
-- 查询 SELECT id, amount FROM orders WHERE user_id=? AND status=? 时覆盖
```

---

## 五、约束

### 必须声明
- `NOT NULL`：业务必填字段
- `DEFAULT`：有默认值的字段（避免 NULL 带来的判断负担）
- `UNIQUE`：业务唯一键
- `PRIMARY KEY`：每表必须有主键

### 外键策略

| 方式 | 说明 |
|------|------|
| 物理外键 `FOREIGN KEY` | 强一致性，但影响写入和迁移。单库、项目初期可用 |
| 逻辑外键 + 文档 | 高并发、分库分表首选。在代码层保证引用完整性 |

**规则**：无论用哪种，外键列必须建索引。

### CHECK 约束
```sql
-- 限制枚举范围
status TINYINT NOT NULL CHECK (status IN (0,1,2,3,4))
-- 限制数值范围
age INT CHECK (age > 0 AND age <= 150)
```

---

## 六、表设计

### 范式原则
- 默认遵循 3NF：消除冗余、依赖传递
- 有意识地反范式：高频查询 JOIN 过多时，冗余一两个字段
- **反范式必须有注释说明原因**

### 纵向拆分
```
大表 → 热门字段（高频查询）+ 冷门字段（低频率）
                ↓ 1:1 JOIN               ↓ 独立表
```

### 横向拆分 / 分区
- 日志类、时序数据：按时间分区
- 多租户：按 tenant_id 分区
- 超大数据量：分表（按时间或 ID hash）

---

## 七、关系映射

| 关系 | 实现 | 示例 |
|------|------|------|
| 1:1 | FK + UNIQUE 或同表 | `user_profile.user_id` UNIQUE → `users.id` |
| 1:N | FK 在多方 | `orders.user_id` → `users.id` |
| N:M | 中间关联表 | `role_user(role_id, user_id)` |
| 树形 | `parent_id` + 物化路径 `path` | `parent_id=0, path="/1/3/7"` |
| 版本化 | `version` + 复合主键 | `PRIMARY KEY (doc_id, version)` |

### 关联表规则
- 关联表必须包含两方 FK + 关系属性（如有）
- 主键：两 FK 的联合主键，或独立 `id`
- 必须带 `created_at`

```sql
CREATE TABLE role_user (
    id BIGINT NOT NULL AUTO_INCREMENT,
    role_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_role_user (role_id, user_id),
    KEY idx_role_user_user_id (user_id)
);
```

---

## 八、软删除

### 方案选型

| 方式 | 字段 | 查询 |
|------|------|------|
| 标记位 | `is_deleted TINYINT DEFAULT 0` | `WHERE is_deleted = 0` |
| 时间戳 | `deleted_at DATETIME DEFAULT NULL` | `WHERE deleted_at IS NULL` |

- `deleted_at` 可恢复，信息更多
- `is_deleted` + 唯一约束需包含 `is_deleted`：`UNIQUE (email, is_deleted)`

### ORM 自动过滤
- 多数 ORM 支持逻辑删除自动过滤（如 MyBatis-Plus `@TableLogic`、GORM `gorm.DeletedAt`）
- 手写 SQL 必须手动加 `WHERE is_deleted = 0` 条件
- 查询视图用 `CREATE VIEW active_users AS SELECT ... WHERE is_deleted = 0`

---

## 九、迁移管理

### 文件命名
```
V1.0.0__init_schema.sql
V1.0.1__add_user_phone.sql
V1.0.2__create_report_tables.sql
```

### 铁律
- 所有 DDL 进版本控制
- 迁移前向兼容：新加字段给 `DEFAULT`，不删旧字段
- 破坏性变更多步走：`add column` → 代码适配 → `drop column`（分版本）
- 生产禁删表、删列、改名（除非确认无依赖）

---

## 十、常见设计模式

### 配置驱动表
```sql
-- 主业务表不存配置细节，通过 type_code 关联
CREATE TABLE config_type (
    id BIGINT PRIMARY KEY,
    type_code VARCHAR(32) NOT NULL,
    name VARCHAR(100),
    enabled TINYINT DEFAULT 1,
    UNIQUE KEY uk_type_code (type_code)
);
```

### 多对多 + 额外属性
```sql
CREATE TABLE entity_relation (
    id BIGINT PRIMARY KEY,
    entity_a_id BIGINT NOT NULL,
    entity_b_id BIGINT NOT NULL,
    sort_order INT DEFAULT 1,    -- 排序
    is_active TINYINT DEFAULT 1, -- 状态
    UNIQUE KEY uk_entity_relation (entity_a_id, entity_b_id)
);
```

### 审计日志表
```sql
CREATE TABLE audit_log (
    id BIGINT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    record_id BIGINT NOT NULL,
    action VARCHAR(16) NOT NULL,  -- INSERT / UPDATE / DELETE
    changed_by BIGINT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_value JSON,
    new_value JSON,
    KEY idx_audit_log_table_record (table_name, record_id)
);
```

---

## 严禁清单

- 没主键 | `SELECT *` | `ENUM` 类型
- `VARCHAR` 不给长度 | 外键无索引
- 金额用 `FLOAT` / `DOUBLE` | 低基数列建单独索引
- 生产中直接删表/删列/改名
- NULL 满天飞导致 `WHERE` 条件遗漏 `IS NULL`
- 一张表超过 50 列不拆分 | 组合索引超过 5 列