# Nop Database Design

> (opencode-project - Skill) Nop平台数据库设计规范。定义表命名、列命名、主键设计、索引设计、通用字段、域定义、关系设计等规范。触发词：数据库设计、表设计、DDL、ORM模型、字段命名。

- Skill: `entropy-cloud/nop-database-design` (Agent Skill)
- Install (CLI): `npx skillmds@latest add entropy-cloud/nop-database-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/entropy-cloud/nop-database-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: entropy-cloud (https://skillmd.com/u/entropy-cloud)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/entropy-cloud/nop-database-design

---


# 数据库设计规范

## 1. 总则

### 1.1 目的
本文档定义数据库设计的通用规范，确保数据模型的一致性、可维护性和可扩展性。

### 1.2 适用范围
适用于所有关系型数据库设计，包括但不限于 MySQL、PostgreSQL、Oracle、SQLite。
---

## 2. 命名规范
### 2.1 表命名

| 规则 | 示例 |
|------|------|
| 使用 snake_case，全小写 | `nop_auth_user`, `nop_auth_role` |
| **必须**使用单数形式 | `nop_auth_user` 而不是 `nop_auth_users` |
| **必须**添加模块前缀 | `nop_auth_user`, `nop_audit_client`, `nop_audit_finding` |
| 前缀格式：`{vendor}_{module}_{entity}` | `nop_auth_user`, `nop_audit_request` |
| 避免使用保留字 | 禁止: `user`, `order`, `group`; 推荐: `nop_auth_user`, `nop_audit_order` |

**示例**：
- `nop_auth_user` - 认证模块用户表
- `nop_auth_role` - 认证模块角色表
- `nop_audit_client` - 审计模块客户表
- `nop_audit_request` - 审计模块请求表

### 2.2 列命名

| 规则 | 示例 |
|------|------|
| 数据库列名使用小写 snake_case | `user_id`, `user_name`, `create_time` |
| 代码属性名使用 camelCase | `userId`, `userName`, `createTime` |
| 外键列使用 `_id` 后缀 | `dept_id`, `role_id`, `parent_id` |
| 布尔列使用 `is_` 或 `has_` 前缀 | `is_active`, `is_deleted`, `has_permission` |
| 时间列使用 `_time` 或 `_at` 后缀 | `create_time`, `expire_at`, `login_time` |
| 日期列使用 `_date` 后缀 | `birth_date`, `start_date`, `end_date` |

### 2.3 索引命名

| 类型 | 命名规则 | 示例 |
|------|----------|------|
| 主键 | `pk_{表名}` | `pk_nop_auth_user` |
| 唯一键 | `uk_{表名}_{列名}` | `uk_nop_auth_user_name` |
| 普通索引 | `ix_{表名}_{列名}` | `ix_nop_auth_user_dept` |
| 组合索引 | `ix_{表名}_{列1}_{列2}` | `ix_nop_auth_role_resource` |
| 外键 | `fk_{表名}_{引用表名}` | `fk_nop_auth_user_dept` |

---

## 3. 主键设计

### 3.1 设计原则

**必须**使用应用层生成的主键，**禁止**使用数据库自增（AUTO_INCREMENT）或数据库序列（SEQUENCE）。

**原因**：
1. **分布式支持**：多节点部署时，数据库自增会导致主键冲突
2. **跨数据库迁移**：不同数据库的自增机制不兼容
3. **数据合并**：多源数据整合时，自增主键会冲突
4. **离线生成**：应用层可以在插入前生成ID

### 3.2 主键类型

| 类型 | 适用场景 |
|------|----------|
| **VARCHAR(32)** | UUID 去掉横线（默认推荐） |
| **VARCHAR(50)** | 雪花算法ID、自定义编码 |

**禁止使用**：BIGINT 自增、数据库 SEQUENCE、AUTO_INCREMENT

### 3.3 主键命名

```sql
-- 推荐：使用 {实体}_id 格式
user_id VARCHAR(32) PRIMARY KEY
role_id VARCHAR(50) PRIMARY KEY

-- 或使用通用 sid (surrogate id)
sid VARCHAR(32) PRIMARY KEY
```

### 3.4 主键生成策略

| 策略 | 推荐度 | 说明 |
|------|--------|------|
| **UUID v7** | ★★★★★ | 有序 UUID，索引友好，推荐使用 |
| **雪花算法** | ★★★★☆ | 有序、全局唯一、含时间信息 |
| **NanoID** | ★★★★☆ | 短小、URL 安全、可自定义长度 |
| **ULID** | ★★★★☆ | 有序、全局唯一、字典排序友好 |
| **UUID v4** | ★★★☆☆ | 随机 UUID，无序、索引效率较低 |

---

## 4. 通用字段（标准列）
每个业务表**必须**包含以下标准字段：

### 4.1 审计字段（必须）
| 列名 | 类型 | 必须 | 说明 |
|------|------|------|------|
| `created_by` | VARCHAR(50) | 是 | 创建人ID/用户名 |
| `create_time` | TIMESTAMP/DATETIME | 是 | 创建时间 |
| `updated_by` | VARCHAR(50) | 是 | 最后修改人ID/用户名 |
| `update_time` | TIMESTAMP/DATETIME | 是 | 最后修改时间 |

### 4.2 乐观锁字段（推荐）
| 列名 | 类型 | 必须 | 说明 |
|------|------|------|------|
| `version` | INTEGER | 推荐 | 数据版本号，每次更新+1 |

### 4.3 逻辑删除字段（推荐）
| 列名 | 类型 | 必须 | 说明 |
|------|------|------|------|
| `del_flag` | TINYINT(1) | 推荐 | 0=未删除，1=已删除 |
| `del_version` | BIGINT | 可选 | 删除版本，一般对应于删除时间（软删除）。初始值为0 |

### 4.4 备注字段（可选）
| 列名 | 类型 | 说明 |
|------|------|------|
| `remark` | VARCHAR(200) | 简短备注 |
| `description` | VARCHAR(1000) | 详细描述 |

---

## 5. 域定义（标准数据类型）
### 5.1 标识类
| 域名 | 类型 | 精度 | 说明 |
|------|------|------|------|
| `userId` | VARCHAR | 50 | 用户ID |
| `roleId` | VARCHAR | 50 | 角色ID |
| `deptId` | VARCHAR | 50 | 部门ID |
| `tenantId` | VARCHAR | 32 | 租户ID |
| `sid` | VARCHAR | 32 | 代理主键 |

### 5.2 联系方式类
| 域名 | 类型 | 精度 | 说明 |
|------|------|------|------|
| `userName` | VARCHAR | 50 | 用户名 |
| `email` | VARCHAR | 100 | 邮箱 |
| `phone` | VARCHAR | 50 | 电话 |
| `realName` | VARCHAR | 50 | 真实姓名 |

### 5.3 内容类
| 域名 | 类型 | 精度 | 说明 |
|------|------|------|------|
| `remark` | VARCHAR | 200 | 简短备注 |
| `description` | VARCHAR | 1000 | 描述 |
| `json-1k` | VARCHAR | 1000 | JSON配置（1K） |
| `json-4k` | VARCHAR | 4000 | JSON配置（4K） |
| `xml-4k` | VARCHAR | 4000 | XML配置 |
| `text` | TEXT | - | 长文本 |

### 5.4 布尔与状态类
| 域名 | 类型 | 说明 |
|------|------|------|
| `boolFlag` | TINYINT(1) | 布尔标志：0=否，1=是 |
| `status` | INTEGER | 状态码（建议用枚举字典） |
| `delFlag` | TINYINT(1) | 删除标志：0=正常，1=已删除 |

### 5.5 时间类
| 域名 | 类型 | 说明 |
|------|------|------|
| `createTime` | TIMESTAMP | 创建时间 |
| `updateTime` | TIMESTAMP | 更新时间 |
| `date` | DATE | 日期 |
| `datetime` | DATETIME | 日期时间 |

### 5.6 文件类
| 域名 | 类型 | 精度 | 说明 |
|------|------|------|------|
| `file` | VARCHAR | 100 | 单个文件路径 |
| `file-list` | VARCHAR | 500 | 多文件路径（JSON数组） |
| `image` | VARCHAR | 100 | 图片路径 |

---

## 6. 关系设计
### 6.1 外键关系

```sql
-- 外键列命名：{引用实体}_id
dept_id VARCHAR(50),        -- 引用 nop_auth_dept 表
parent_id VARCHAR(50),      -- 引用自身（树形结构）
role_id VARCHAR(50),        -- 引用 nop_auth_role 表
```

### 6.2 关系类型
| 关系类型 | 实现方式 | 示例 |
|----------|----------|------|
| **一对一** | 外键 + 唯一约束 | nop_auth_user - nop_auth_ext_login |
| **一对多** | 外键（多方持有） | nop_auth_dept - nop_auth_user |
| **多对多** | 中间表 | nop_auth_user_role |

### 6.3 中间表设计规范

```sql
-- 多对多关系中间表
CREATE TABLE nop_auth_user_role (
    user_id VARCHAR(50) NOT NULL,
    role_id VARCHAR(50) NOT NULL,
    version INTEGER NOT NULL DEFAULT 0,
    created_by VARCHAR(50) NOT NULL,
    create_time TIMESTAMP NOT NULL,
    updated_by VARCHAR(50) NOT NULL,
    update_time TIMESTAMP NOT NULL,
    remark VARCHAR(200),
    PRIMARY KEY (user_id, role_id)
);
```

### 6.4 树形结构设计

```sql
-- 树形结构（如部门、菜单）
CREATE TABLE nop_auth_dept (
    dept_id VARCHAR(50) PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    parent_id VARCHAR(50),           -- 父节点ID
    order_no INTEGER DEFAULT 0,       -- 排序号
    -- 标准字段...
    FOREIGN KEY (parent_id) REFERENCES nop_auth_dept(dept_id)
);
```

---

## 7. 索引设计
### 7.1 索引原则

1. **主键自动创建索引**
2. **外键列必须建索引**
3. **高频查询条件列建索引**
4. **排序字段考虑建索引**
5. **组合索引遵循最左前缀原则**

### 7.2 索引类型选择
| 场景 | 索引类型 |
|------|----------|
| 唯一约束 | UNIQUE INDEX |
| 外键关系 | NORMAL INDEX |
| 文本搜索 | FULLTEXT INDEX（MySQL） |
| 高频查询 | NORMAL INDEX |
| 组合查询 | COMPOSITE INDEX |

### 7.3 索引示例

```sql
-- 唯一索引
CREATE UNIQUE INDEX uk_nop_auth_user_name ON nop_auth_user(user_name);

-- 普通索引
CREATE INDEX ix_nop_auth_user_dept ON nop_auth_user(dept_id);

-- 组合索引
CREATE INDEX ix_nop_auth_resource_site ON nop_auth_resource(site_id, order_no);
```

---

## 8. 数据字典
### 8.1 通用状态码
| 状态值 | 含义 |
|--------|------|
| 0 | 禁用/无效 |
| 1 | 启用/有效 |

### 8.2 删除标志
| 值 | 含义 |
|----|------|
| 0 | 正常（未删除） |
| 1 | 已删除 |

### 8.3 性别
| 值 | 含义 |
|----|------|
| 0 | 未知 |
| 1 | 男 |
| 2 | 女 |

### 8.4 是/否标志
| 值 | 含义 |
|----|------|
| 0 | 否 |
| 1 | 是 |

---

## 9. 表设计模板
### 9.1 标准业务表模板

```sql
CREATE TABLE {prefix}_{module}_{entity} (
    -- 主键
    {entity}_id VARCHAR(32) PRIMARY KEY,
    
    -- 业务字段
    -- ...
    
    -- 树形结构（可选）
    parent_id VARCHAR(32),
    order_no INTEGER DEFAULT 0,
    
    -- 审计字段
    version INTEGER NOT NULL DEFAULT 0,
    del_flag TINYINT(1) NOT NULL DEFAULT 0,
    created_by VARCHAR(50) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50) NOT NULL,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remark VARCHAR(200),
    
    -- 索引
    INDEX ix_{entity}_parent (parent_id)
);
```

**完整示例**：

```sql
CREATE TABLE nop_audit_client (
    client_id VARCHAR(32) PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    industry VARCHAR(50),
    status INTEGER NOT NULL DEFAULT 1,
    
    version INTEGER NOT NULL DEFAULT 0,
    del_flag TINYINT(1) NOT NULL DEFAULT 0,
    created_by VARCHAR(50) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50) NOT NULL,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remark VARCHAR(200)
);
```

### 9.2 关联表模板

```sql
CREATE TABLE {prefix}_{module}_{entity1}_{entity2} (
    -- 联合主键
    {entity1}_id VARCHAR(50) NOT NULL,
    {entity2}_id VARCHAR(50) NOT NULL,
    
    -- 可选扩展字段
    -- include_child TINYINT(1) DEFAULT 0,
    
    -- 审计字段
    version INTEGER NOT NULL DEFAULT 0,
    created_by VARCHAR(50) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50) NOT NULL,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remark VARCHAR(200),
    
    PRIMARY KEY ({entity1}_id, {entity2}_id)
);
```

**完整示例**：

```sql
CREATE TABLE nop_auth_user_role (
    user_id VARCHAR(50) NOT NULL,
    role_id VARCHAR(50) NOT NULL,
    
    version INTEGER NOT NULL DEFAULT 0,
    created_by VARCHAR(50) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50) NOT NULL,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remark VARCHAR(200),
    
    PRIMARY KEY (user_id, role_id)
);
```

### 9.3 日志表模板

```sql
CREATE TABLE {prefix}_{module}_{entity}_log (
    log_id VARCHAR(32) PRIMARY KEY,
    
    -- 关联ID
    {entity}_id VARCHAR(50) NOT NULL,
    
    -- 操作信息
    operation VARCHAR(100),
    action_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    used_time BIGINT,                    -- 耗时（毫秒）
    result_status INTEGER NOT NULL,      -- 0=成功，其他=失败
    error_code VARCHAR(200),
    ret_message VARCHAR(1000),
    
    -- 请求/响应（可选）
    op_request VARCHAR(8000),
    op_response VARCHAR(4000),
    
    -- 索引
    INDEX ix_log_{entity} ({entity}_id),
    INDEX ix_log_time (action_time)
);
```

**完整示例**：

```sql
CREATE TABLE nop_auth_op_log (
    log_id VARCHAR(32) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    user_name VARCHAR(50) NOT NULL,
    session_id VARCHAR(100),
    operation VARCHAR(100),
    action_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    used_time BIGINT,
    result_status INTEGER NOT NULL,
    error_code VARCHAR(200),
    ret_message VARCHAR(1000),
    op_request VARCHAR(8000),
    op_response VARCHAR(4000),
    
    INDEX ix_log_user (user_id),
    INDEX ix_log_time (action_time)
);
```

---

## 10. 安全考虑
### 10.1 敏感字段
| 字段类型 | 存储要求 |
|----------|----------|
| 密码 | 加密存储（BCrypt/Argon2），禁止明文 |
| 盐值 | 单独字段存储 |
| 身份证号 | 加密或脱敏存储 |
| 手机号 | 可脱敏存储 |
| 银行卡号 | 加密存储 |
| 地址 | 可加密存储 |

### 10.2 审计追踪
- 所有关键业务表必须包含 `created_by`, `create_time`, `updated_by`, `update_time`
- 敏感操作应记录操作日志
- 数据变更可使用审计表或CDC机制

---

## 11. 性能考虑
### 11.1 表设计
1. **避免过宽的表**：单表字段数建议不超过 50 个
2. **大字段分离**：TEXT/BLOB 考虑单独存储
3. **冷热数据分离**：历史数据归档

### 11.2 索引设计
1. **避免过多索引**：单表索引数建议不超过 10 个
2. **组合索引顺序**：高选择性列在前
3. **定期维护**：重建碎片化严重的索引

### 11.3 查询优化
1. 避免 `SELECT *`
2. 合理使用分页
3. 大表查询使用覆盖索引

---

## 12. 版本控制
### 12.1 Schema 变更管理
1. 所有 DDL 变更必须通过迁移脚本管理
2. 迁移脚本命名：`V{版本号}__{描述}.sql`
3. 示例：`V1.0.1__add_user_status_column.sql`

### 12.2 向后兼容
1. 新增列使用默认值
2. 删除列前确保无引用
3. 类型变更需评估数据迁移

---

## 附录 A：常用数据类型对照表
| 用途 | MySQL | PostgreSQL | Oracle | SQLite |
|------|-------|------------|--------|--------|
| 主键ID | VARCHAR(32) | VARCHAR(32) | VARCHAR2(32) | TEXT |
| 字符串 | VARCHAR(100) | VARCHAR(100) | VARCHAR2(100) | TEXT |
| 长文本 | TEXT | TEXT | CLOB | TEXT |
| 布尔 | TINYINT(1) | BOOLEAN | NUMBER(1) | INTEGER |
| 整数 | INTEGER | INTEGER | NUMBER(10) | INTEGER |
| 长整数 | BIGINT | BIGINT | NUMBER(19) | INTEGER |
| 小数 | DECIMAL(18,4) | DECIMAL(18,4) | NUMBER(18,4) | REAL |
| 日期 | DATE | DATE | DATE | TEXT |
| 时间戳 | TIMESTAMP | TIMESTAMP | TIMESTAMP | TEXT |
| JSON | JSON | JSONB | CLOB | TEXT |

---

## 13. 类型简化原则

### 13.1 避免 tinyint / smallint，统一使用 int

存储空间已不再是瓶颈。使用 `TINYINT` 或 `SMALLINT` 会导致 Java 代码中每次赋值常量都需要强制转型（如 `(short) STATUS_CREATED`），增加噪音且容易出错。

| 场景 | 禁止 | 推荐 |
|------|------|------|
| 状态码 | `TINYINT` / `SMALLINT` → Java `short` | `INTEGER` → Java `int` |
| 数量 | `SMALLINT` → Java `short` | `INTEGER` → Java `int` |
| 排序号 | `SMALLINT` → Java `short` | `INTEGER` → Java `int` |
| 布尔标记 | `TINYINT` → Java `byte` | 直接使用 `BOOLEAN` 类型 |

### 13.2 布尔字段

布尔标记字段直接使用 `BOOLEAN` 类型（ORM 中 `stdSqlType="BOOLEAN"` + `stdDataType="boolean"`），不要用 `TINYINT` 模拟布尔。

---

## 附录 B：设计检查清单
- [ ] 表名使用单数形式
- [ ] 表名包含模块前缀
- [ ] 列名符合命名规范
- [ ] 主键设计合理
- [ ] 包含所有必须的标准字段
- [ ] 外键关系正确
- [ ] 索引设计合理
- [ ] 敏感字段已加密
- [ ] 整数字段统一使用 INTEGER（避免 TINYINT/SMALLINT）
- [ ] 布尔字段使用 BOOLEAN 类型
- [ ] 考虑了性能优化
- [ ] 编写了迁移脚本