| name | nop-database-design |
| description | (opencode-project - Skill) Nop平台数据库设计规范。定义表命名、列命名、主键设计、索引设计、通用字段、域定义、关系设计等规范。触发词:数据库设计、表设计、DDL、ORM模型、字段命名。 |
数据库设计规范
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)。
原因:
- 分布式支持:多节点部署时,数据库自增会导致主键冲突
- 跨数据库迁移:不同数据库的自增机制不兼容
- 数据合并:多源数据整合时,自增主键会冲突
- 离线生成:应用层可以在插入前生成ID
3.2 主键类型
| 类型 | 适用场景 |
|---|
| VARCHAR(32) | UUID 去掉横线(默认推荐) |
| VARCHAR(50) | 雪花算法ID、自定义编码 |
禁止使用:BIGINT 自增、数据库 SEQUENCE、AUTO_INCREMENT
3.3 主键命名
user_id VARCHAR(32) PRIMARY KEY
role_id VARCHAR(50) PRIMARY KEY
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 外键关系
dept_id VARCHAR(50),
parent_id VARCHAR(50),
role_id VARCHAR(50),
6.2 关系类型
| 关系类型 | 实现方式 | 示例 |
|---|
| 一对一 | 外键 + 唯一约束 | nop_auth_user - nop_auth_ext_login |
| 一对多 | 外键(多方持有) | nop_auth_dept - nop_auth_user |
| 多对多 | 中间表 | nop_auth_user_role |
6.3 中间表设计规范
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 树形结构设计
CREATE TABLE nop_auth_dept (
dept_id VARCHAR(50) PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL,
parent_id VARCHAR(50),
order_no INTEGER DEFAULT 0,
FOREIGN KEY (parent_id) REFERENCES nop_auth_dept(dept_id)
);
7. 索引设计
7.1 索引原则
- 主键自动创建索引
- 外键列必须建索引
- 高频查询条件列建索引
- 排序字段考虑建索引
- 组合索引遵循最左前缀原则
7.2 索引类型选择
| 场景 | 索引类型 |
|---|
| 唯一约束 | UNIQUE INDEX |
| 外键关系 | NORMAL INDEX |
| 文本搜索 | FULLTEXT INDEX(MySQL) |
| 高频查询 | NORMAL INDEX |
| 组合查询 | COMPOSITE INDEX |
7.3 索引示例
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 通用状态码
8.2 删除标志
8.3 性别
8.4 是/否标志
9. 表设计模板
9.1 标准业务表模板
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)
);
完整示例:
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 关联表模板
CREATE TABLE {prefix}_{module}_{entity1}_{entity2} (
{entity1}_id VARCHAR(50) NOT NULL,
{entity2}_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 ({entity1}_id, {entity2}_id)
);
完整示例:
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 日志表模板
CREATE TABLE {prefix}_{module}_{entity}_log (
log_id VARCHAR(32) PRIMARY KEY,
{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,
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)
);
完整示例:
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 表设计
- 避免过宽的表:单表字段数建议不超过 50 个
- 大字段分离:TEXT/BLOB 考虑单独存储
- 冷热数据分离:历史数据归档
11.2 索引设计
- 避免过多索引:单表索引数建议不超过 10 个
- 组合索引顺序:高选择性列在前
- 定期维护:重建碎片化严重的索引
11.3 查询优化
- 避免
SELECT *
- 合理使用分页
- 大表查询使用覆盖索引
12. 版本控制
12.1 Schema 变更管理
- 所有 DDL 变更必须通过迁移脚本管理
- 迁移脚本命名:
V{版本号}__{描述}.sql
- 示例:
V1.0.1__add_user_status_column.sql
12.2 向后兼容
- 新增列使用默认值
- 删除列前确保无引用
- 类型变更需评估数据迁移
附录 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 |
附录 B:设计检查清单