nop-database-design

数据库设计规范

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：设计检查清单

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