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 表命名

示例：

• nop_auth_user - 认证模块用户表
• nop_auth_role - 认证模块角色表
• nop_audit_client - 审计模块客户表
• nop_audit_request - 审计模块请求表

2.2 列命名

2.3 索引命名

3. 主键设计

3.1 设计原则

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

原因：

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

3.2 主键类型

禁止使用：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 主键生成策略

4. 通用字段（标准列）

每个业务表必须包含以下标准字段：

4.1 审计字段（必须）

4.2 乐观锁字段（推荐）

4.3 逻辑删除字段（推荐）

4.4 备注字段（可选）

5. 域定义（标准数据类型）

5.1 标识类

5.2 联系方式类

5.3 内容类

5.4 布尔与状态类

5.5 时间类

5.6 文件类

6. 关系设计

6.1 外键关系

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

6.2 关系类型

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 索引类型选择

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,
    
    -- 可选扩展字段
    -- 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 敏感字段

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：常用数据类型对照表

附录 B：设计检查清单

• 表名使用单数形式
• 表名包含模块前缀
• 列名符合命名规范
• 主键设计合理
• 包含所有必须的标准字段
• 外键关系正确
• 索引设计合理
• 敏感字段已加密
• 考虑了性能优化
• 编写了迁移脚本