表关系与约束

引用文件

本文引用的文件

引言

本文件旨在系统化梳理 yudao-cloud 项目的数据库表关系与约束设计，重点阐述业务表之间的关联关系（一对一、一对多、多对多）、外键约束设计原则、参照完整性保障机制、级联更新与删除策略，并结合实际业务场景（如用户-角色、角色-菜单、设备-心跳、任务-状态等）给出完整的关系图与约束定义示例。

项目结构

该项目采用模块化架构，系统功能主要分布在多个子模块中，数据库层面通过统一的 SQL 脚本进行初始化与维护。核心系统模块包含用户管理、角色权限、菜单管理、字典管理、日志审计等功能，这些模块在数据库层通过清晰的表结构与约束实现业务逻辑。

Mermaid Diagram Code:

graph TB
subgraph "系统模块"
SYS_USER["用户表<br/>system_user"]
SYS_ROLE["角色表<br/>system_role"]
SYS_MENU["菜单表<br/>system_menu"]
SYS_DEPT["部门表<br/>system_dept"]
SYS_DICT["字典数据表<br/>system_dict_data"]
SYS_LOG["API访问日志表<br/>infra_api_access_log"]
end
SYS_USER --> SYS_DEPT
SYS_USER --> SYS_ROLE
SYS_ROLE --> SYS_MENU
SYS_MENU --> SYS_DICT
SYS_LOG --> SYS_USER

图表来源

章节来源

核心组件

本节聚焦于与表关系和约束设计密切相关的核心表，包括部门、字典、API 访问日志等，它们共同支撑了用户、角色、菜单等业务实体之间的关联关系。

部门表（system_dept）
- 主键：id
- 关键字段：parent_id（自关联，表示父子层级）
- 用途：组织架构与人员归属的基础表
字典数据表（system_dict_data）
- 主键：id
- 关键字段：dict_type（字典类型），value（字典键值）
- 用途：为系统提供统一的枚举与状态管理
API 访问日志表（infra_api_access_log）
- 主键：id
- 关键字段：user_id（用户标识），application_name（应用名）
- 用途：审计与追踪用户操作行为

章节来源

架构概览

系统采用“模块化 + 统一数据库”的架构，业务表之间通过外键与字典机制建立稳定的关系。下图展示了关键表之间的关系与约束方向：

Mermaid Diagram Code:

erDiagram
SYSTEM_DEPT {
bigint id PK
bigint parent_id
varchar name
int sort
bigint leader_user_id
varchar phone
varchar email
tinyint status
varchar creator
datetime create_time
varchar updater
datetime update_time
bit deleted
bigint tenant_id
}
SYSTEM_DICT_DATA {
bigint id PK
int sort
varchar label
varchar value
varchar dict_type
tinyint status
varchar color_type
varchar css_class
varchar remark
varchar creator
datetime create_time
varchar updater
datetime update_time
bit deleted
}
INFRA_API_ACCESS_LOG {
bigint id PK
varchar trace_id
bigint user_id
tinyint user_type
varchar application_name
varchar request_method
varchar request_url
text request_params
text response_body
varchar user_ip
varchar user_agent
varchar operate_module
varchar operate_name
tinyint operate_type
datetime begin_time
datetime end_time
int duration
int result_code
varchar result_msg
varchar creator
datetime create_time
varchar updater
datetime update_time
bit deleted
bigint tenant_id
}
SYSTEM_DEPT ||--o{ SYSTEM_DEPT : "parent-child"
SYSTEM_DICT_DATA ||--o{ SYSTEM_DICT_DATA : "type-value"
INFRA_API_ACCESS_LOG }o--|| SYSTEM_DEPT : "user_id -> leader_user_id"

图表来源

详细组件分析

1) 部门表（system_dept）与组织层级

关系类型：一对多（父子部门）
外键设计：parent_id 指向自身 id，形成树形结构
参照完整性：建议启用外键约束，确保 parent_id 的存在性
级联策略：删除父部门时，子部门应保持或迁移至新的父节点；更新 parent_id 时需保证层级一致性
业务影响：支撑用户归属、数据权限继承与组织架构展示

Mermaid Diagram Code:

flowchart TD
START(["开始：部门操作"]) --> CHECK_PARENT["检查 parent_id 是否存在"]
CHECK_PARENT --> PARENT_EXISTS{"parent_id 存在？"}
PARENT_EXISTS --> |否| ERROR["报错：父部门不存在"]
PARENT_EXISTS --> |是| PROCEED["继续执行插入/更新"]
PROCEED --> END(["结束"])
ERROR --> END

图表来源

system_dept 表结构

章节来源

system_dept 表结构

2) 字典数据表（system_dict_data）与状态枚举

关系类型：一对多（同一 dict_type 下的多个 value）
外键设计：无显式外键，通过 dict_type + value 实现逻辑关联
参照完整性：建议在应用层或数据库层增加约束，防止重复与非法组合
级联策略：字典变更时，需同步更新引用该字典类型的业务表
业务影响：统一状态码、权限类型、业务枚举，降低耦合度

Mermaid Diagram Code:

flowchart TD
START(["开始：字典变更"]) --> VALIDATE["验证 dict_type + value 唯一性"]
VALIDATE --> UNIQUE{"唯一且合法？"}
UNIQUE --> |否| ERROR["报错：重复或非法"]
UNIQUE --> |是| UPDATE_REFS["更新引用该字典的业务记录"]
UPDATE_REFS --> END(["结束"])
ERROR --> END

图表来源

system_dict_data 表结构

章节来源

system_dict_data 表结构

3) API 访问日志表（infra_api_access_log）与用户审计

关系类型：一对多（一个用户对应多条日志）
外键设计：user_id 与系统用户表关联（外部表）
参照完整性：建议启用外键约束，保证 user_id 的有效性
级联策略：用户删除时，建议保留日志以满足审计需求；更新用户信息时可级联更新
业务影响：提供完整的操作轨迹与安全审计能力

Mermaid Diagram Code:

sequenceDiagram
participant U as "用户"
participant L as "API访问日志表"
participant S as "系统用户表"
U->>L : 写入一条访问日志
L->>S : 校验 user_id 是否存在
S-->>L : 返回存在性结果
L-->>U : 记录成功/失败

图表来源

infra_api_access_log 表结构

章节来源

infra_api_access_log 表结构

4) 用户-角色、角色-菜单的关联设计

关系类型：多对多（用户-角色、角色-菜单）
设计建议：引入中间表（如 system_user_role、system_role_menu）实现解耦
外键设计：中间表的字段均指向各自主表的主键
参照完整性：启用外键约束，确保引用对象存在
级联策略：删除角色或菜单时，建议先清理中间表再删除目标对象；删除用户时可级联清理其角色映射
业务影响：灵活配置权限矩阵，支持复杂权限组合与继承

Mermaid Diagram Code:

erDiagram
SYSTEM_USER {
bigint id PK
varchar username
varchar nickname
varchar avatar
tinyint status
bigint dept_id
varchar phone
varchar email
varchar sex
bigint tenant_id
}
SYSTEM_ROLE {
bigint id PK
varchar name
varchar code
int sort
tinyint status
varchar data_scope
bigint dept_id
bigint tenant_id
}
SYSTEM_MENU {
bigint id PK
varchar name
int sort
tinyint type
bigint parent_id
varchar path
varchar component
tinyint status
tinyint visible
tinyint keep_alive
tinyint always_show
bigint tenant_id
}
SYSTEM_USER_ROLE {
bigint user_id FK
bigint role_id FK
}
SYSTEM_ROLE_MENU {
bigint role_id FK
bigint menu_id FK
}
SYSTEM_USER ||--o{ SYSTEM_USER_ROLE : "拥有"
SYSTEM_ROLE ||--o{ SYSTEM_USER_ROLE : "被拥有"
SYSTEM_ROLE ||--o{ SYSTEM_ROLE_MENU : "授权"
SYSTEM_MENU ||--o{ SYSTEM_ROLE_MENU : "被授权"

图表来源

章节来源

5) 设备-心跳、任务-状态的扩展设计

设备-心跳：建议引入设备表与心跳记录表，通过设备 ID 建立一对多关系；心跳记录可按时间序列存储，便于分析设备在线状态
任务-状态：建议引入任务表与状态历史表，通过任务 ID 建立一对多关系；状态历史用于审计与回溯
外键设计：心跳记录与任务记录均引用主表 ID
参照完整性：启用外键约束，确保引用对象存在
级联策略：删除任务或设备时，建议先清理历史记录再删除主记录

Mermaid Diagram Code:

erDiagram
DEVICE {
bigint id PK
varchar sn
varchar model
varchar status
datetime last_heartbeat
bigint tenant_id
}
DEVICE_HEARTBEAT {
bigint id PK
bigint device_id FK
datetime heartbeat_time
json payload
datetime created_at
}
TASK {
bigint id PK
varchar name
varchar status
bigint assignee_id
datetime created_at
datetime updated_at
}
TASK_STATUS_HISTORY {
bigint id PK
bigint task_id FK
varchar old_status
varchar new_status
varchar operator
datetime operated_at
}
DEVICE ||--o{ DEVICE_HEARTBEAT : "产生"
TASK ||--o{ TASK_STATUS_HISTORY : "变更"

图表来源

章节来源

依赖分析

模块间依赖：系统模块内的表通过公共字段（如 tenant_id、dept_id）实现租户隔离与组织归属
外键依赖：部门表的 parent_id 依赖自身；日志表依赖用户表；字典表依赖业务枚举
参照完整性：建议在数据库层启用外键约束，配合索引提升查询性能
级联策略：根据业务需求选择 RESTRICT、CASCADE 或 SET NULL，避免脏数据

Mermaid Diagram Code:

graph LR
DEPT["部门表"] --> |parent_id| DEPT
LOG["日志表"] --> |user_id| USER["用户表"]
DICT["字典表"] --> |dict_type/value| ENUM["业务枚举"]
MENU["菜单表"] --> |parent_id| MENU

图表来源

章节来源

性能考虑

索引优化：为外键字段（如 parent_id、user_id、dict_type）建立索引，加速关联查询
分区策略：针对日志表按时间分区，提升大数据量下的查询与归档效率
缓存策略：热点字典与菜单数据可引入缓存，减少数据库压力
批量操作：在导入或迁移场景中，合理控制事务大小，避免锁竞争

故障排除指南

外键冲突：当尝试插入或更新引用不存在的对象时，数据库会抛出外键约束错误。排查步骤：
- 确认被引用表是否存在对应记录
- 检查字段类型与长度是否一致
- 核对租户隔离字段（如 tenant_id）是否匹配
参照完整性破坏：删除被其他表引用的记录会导致约束失败。解决思路：
- 先清理中间表或下游表的引用
- 使用级联删除前评估数据影响
查询性能问题：若关联查询缓慢，检查外键字段是否建有索引，必要时添加复合索引

章节来源

结论

通过对 yudao-cloud 项目的数据库表关系与约束设计进行系统化梳理，可以发现：

组织层级通过部门表的自关联实现，具备清晰的一对多关系
字典数据通过 dict_type + value 提供统一的枚举管理，支撑多业务场景
审计与追踪通过 API 访问日志表实现，强调用户与操作的可追溯性
权限矩阵（用户-角色、角色-菜单）建议通过中间表实现多对多关系，提升灵活性
设备-心跳、任务-状态等扩展场景可通过一对多关系与历史表实现完整生命周期管理

在实际落地中，应坚持“先设计、后实现”的原则，明确外键约束与参照完整性，合理选择级联策略，并结合索引与分区策略持续优化性能。

目录​

引言​

项目结构​

核心组件​

架构概览​

详细组件分析​

1) 部门表（system_dept）与组织层级​

2) 字典数据表（system_dict_data）与状态枚举​

3) API 访问日志表（infra_api_access_log）与用户审计​

4) 用户-角色、角色-菜单的关联设计​

5) 设备-心跳、任务-状态的扩展设计​

依赖分析​

性能考虑​

故障排除指南​

结论​

目录

引言

项目结构

核心组件

架构概览

详细组件分析

1) 部门表（system_dept）与组织层级

2) 字典数据表（system_dict_data）与状态枚举

3) API 访问日志表（infra_api_access_log）与用户审计

4) 用户-角色、角色-菜单的关联设计

5) 设备-心跳、任务-状态的扩展设计

依赖分析

性能考虑

故障排除指南

结论