nop-database-design

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）。

原因：

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

3.2 主键类型

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

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

3.3 主键命名

-- 推荐：使用 {实体}_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 外键关系

-- 外键列命名：{引用实体}_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 中间表设计规范

-- 多对多关系中间表
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),           -- 父节点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 索引示例

-- 唯一索引
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 标准业务表模板

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,
    
    -- 可选扩展字段
    -- 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)
);

完整示例：

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,
    
    -- 关联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)
);

完整示例：

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 类型
• 考虑了性能优化
• 编写了迁移脚本