系统后台管理系统数据库表结构设计系统后台管理系统数据库表结构设计设计概述根据系统后台管理的需求，我设计了一套完整的数

系统后台管理系统数据库表结构设计

设计概述

根据系统后台管理的需求，我设计了一套完整的数据库表结构，主要包含以下模块：

用户管理
角色管理
权限管理
菜单管理
系统日志
组织架构管理

这些模块采用RBAC（基于角色的访问控制）模型设计，确保权限管理的灵活性和可扩展性。

核心表结构设计

1. 用户表（sys_user）

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(100) NOT NULL COMMENT '密码',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `gender` tinyint(4) DEFAULT NULL COMMENT '性别(0:未知,1:男,2:女)',
  `status` tinyint(4) DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  `dept_id` bigint(20) DEFAULT NULL COMMENT '所属部门ID',
  `login_ip` varchar(50) DEFAULT NULL COMMENT '最后登录IP',
  `login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(4) DEFAULT 0 COMMENT '删除标志(0:正常,1:删除)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_dept_id` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表';

2. 部门表（sys_dept）

CREATE TABLE `sys_dept` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `dept_name` varchar(50) NOT NULL COMMENT '部门名称',
  `parent_id` bigint(20) DEFAULT 0 COMMENT '父部门ID',
  `dept_level` tinyint(4) DEFAULT 1 COMMENT '部门级别',
  `order_num` int(11) DEFAULT 0 COMMENT '排序',
  `leader` bigint(20) DEFAULT NULL COMMENT '部门负责人ID',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `status` tinyint(4) DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_leader` (`leader`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

3. 角色表（sys_role）

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(50) NOT NULL COMMENT '角色名称',
  `role_key` varchar(50) NOT NULL COMMENT '角色权限键',
  `role_sort` int(11) DEFAULT 0 COMMENT '显示顺序',
  `data_scope` tinyint(4) DEFAULT 1 COMMENT '数据范围(1:全部数据权限,2:自定义数据权限,3:本部门数据权限,4:本部门及以下数据权限,5:仅本人数据权限)',
  `status` tinyint(4) DEFAULT 1 COMMENT '状态(0:禁用,1:启用)',
  `remark` varchar(200) DEFAULT NULL COMMENT '备注',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_key` (`role_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

4. 菜单表（sys_menu）

CREATE TABLE `sys_menu` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
  `menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
  `parent_id` bigint(20) DEFAULT 0 COMMENT '父菜单ID',
  `menu_type` tinyint(4) NOT NULL COMMENT '菜单类型(0:目录,1:菜单,2:按钮)',
  `order_num` int(11) DEFAULT 0 COMMENT '显示顺序',
  `path` varchar(200) DEFAULT '' COMMENT '路由地址',
  `component` varchar(255) DEFAULT NULL COMMENT '组件路径',
  `perms` varchar(100) DEFAULT NULL COMMENT '权限标识',
  `icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',
  `visible` tinyint(4) DEFAULT 1 COMMENT '是否可见(0:隐藏,1:可见)',
  `status` tinyint(4) DEFAULT 1 COMMENT '菜单状态(0:禁用,1:启用)',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_menu_type` (`menu_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单权限表';

5. 权限表（sys_permission）

CREATE TABLE `sys_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `permission_name` varchar(50) NOT NULL COMMENT '权限名称',
  `permission_key` varchar(100) NOT NULL COMMENT '权限标识',
  `permission_type` tinyint(4) DEFAULT 0 COMMENT '权限类型(0:菜单权限,1:操作权限)',
  `menu_id` bigint(20) DEFAULT NULL COMMENT '关联菜单ID',
  `description` varchar(200) DEFAULT NULL COMMENT '权限描述',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者ID',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_permission_key` (`permission_key`),
  KEY `idx_menu_id` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

6. 用户角色关联表（sys_user_role）

CREATE TABLE `sys_user_role` (
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

7. 角色权限关联表（sys_role_permission）

CREATE TABLE `sys_role_permission` (
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  `permission_id` bigint(20) NOT NULL COMMENT '权限ID',
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_role_id` (`role_id`),
  KEY `idx_permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

8. 角色菜单关联表（sys_role_menu）

CREATE TABLE `sys_role_menu` (
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  `menu_id` bigint(20) NOT NULL COMMENT '菜单ID',
  PRIMARY KEY (`role_id`,`menu_id`),
  KEY `idx_role_id` (`role_id`),
  KEY `idx_menu_id` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单关联表';

9. 操作日志表（sys_oper_log）

CREATE TABLE `sys_oper_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `title` varchar(50) DEFAULT '' COMMENT '模块标题',
  `business_type` tinyint(4) DEFAULT 0 COMMENT '业务类型(0:其他,1:新增,2:修改,3:删除,4:查询,5:导入,6:导出)',
  `method` varchar(100) DEFAULT '' COMMENT '方法名称',
  `request_method` varchar(10) DEFAULT '' COMMENT '请求方式',
  `operator_type` tinyint(4) DEFAULT 0 COMMENT '操作类别(0:其他,1:后台用户,2:手机端用户)',
  `oper_name` varchar(50) DEFAULT '' COMMENT '操作人员',
  `dept_name` varchar(50) DEFAULT '' COMMENT '部门名称',
  `oper_url` varchar(255) DEFAULT '' COMMENT '请求URL',
  `oper_ip` varchar(50) DEFAULT '' COMMENT '操作IP',
  `oper_location` varchar(255) DEFAULT '' COMMENT '操作地点',
  `oper_param` varchar(2000) DEFAULT '' COMMENT '请求参数',
  `json_result` varchar(2000) DEFAULT '' COMMENT '返回参数',
  `status` tinyint(4) DEFAULT 0 COMMENT '操作状态(0:正常,1:异常)',
  `error_msg` varchar(2000) DEFAULT '' COMMENT '错误消息',
  `oper_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  PRIMARY KEY (`id`),
  KEY `idx_oper_time` (`oper_time`),
  KEY `idx_oper_name` (`oper_name`),
  KEY `idx_business_type` (`business_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';

10. 登录日志表（sys_login_log）

CREATE TABLE `sys_login_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `ipaddr` varchar(50) DEFAULT '' COMMENT '登录IP地址',
  `login_location` varchar(255) DEFAULT '' COMMENT '登录地点',
  `browser` varchar(50) DEFAULT '' COMMENT '浏览器类型',
  `os` varchar(50) DEFAULT '' COMMENT '操作系统',
  `status` tinyint(4) NOT NULL COMMENT '登录状态(0:成功,1:失败)',
  `msg` varchar(255) DEFAULT '' COMMENT '提示消息',
  `login_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间',
  PRIMARY KEY (`id`),
  KEY `idx_login_time` (`login_time`),
  KEY `idx_username` (`username`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志表';

表关系图

sys_user 1 ------ N sys_user_role N ------ 1 sys_role 1 ------ N sys_role_permission N ------ 1 sys_permission
   |                    |                     |
   |                    |                     |
   |                    |                     N
   |                    |                  sys_role_menu
   |                    |                     |
   |                    |                     |
   N                    |                     N
 sys_dept              sys_oper_log         sys_menu

核心业务SQL示例

1. 查询用户的所有角色

SELECT r.id, r.role_name, r.role_key
FROM sys_user u
LEFT JOIN sys_user_role ur ON u.id = ur.user_id
LEFT JOIN sys_role r ON ur.role_id = r.id
WHERE u.id = #{userId} AND u.del_flag = 0 AND r.status = 1;

2. 查询角色的所有权限

SELECT p.id, p.permission_name, p.permission_key, p.permission_type
FROM sys_role r
LEFT JOIN sys_role_permission rp ON r.id = rp.role_id
LEFT JOIN sys_permission p ON rp.permission_id = p.id
WHERE r.id = #{roleId};

3. 查询用户的菜单权限树

SELECT DISTINCT m.id, m.parent_id, m.menu_name, m.menu_type, m.path, 
       m.component, m.perms, m.icon, m.visible, m.order_num
FROM sys_user u
LEFT JOIN sys_user_role ur ON u.id = ur.user_id
LEFT JOIN sys_role r ON ur.role_id = r.id
LEFT JOIN sys_role_menu rm ON r.id = rm.role_id
LEFT JOIN sys_menu m ON rm.menu_id = m.id
WHERE u.id = #{userId} AND m.status = 1
ORDER BY m.parent_id, m.order_num;

4. 检查用户是否有权限执行操作

SELECT COUNT(1) > 0 AS has_permission
FROM sys_user u
LEFT JOIN sys_user_role ur ON u.id = ur.user_id
LEFT JOIN sys_role r ON ur.role_id = r.id
LEFT JOIN sys_role_permission rp ON r.id = rp.role_id
LEFT JOIN sys_permission p ON rp.permission_id = p.id
WHERE u.id = #{userId} AND p.permission_key = #{permissionKey};

设计亮点

完整的RBAC权限模型：实现了用户-角色-权限的完整分离，支持细粒度的权限控制
灵活的数据范围控制：角色可以配置不同的数据访问范围，满足不同级别管理员的权限需求
全面的日志记录：记录登录日志和操作日志，便于系统审计和问题排查
支持多级部门结构：部门表支持树形结构，方便企业组织架构管理
菜单与权限分离：菜单管理和权限管理分开，支持更灵活的权限配置

扩展建议

根据实际业务需求，可以添加数据字典表、系统配置表等辅助表
对于大型系统，可以考虑添加数据权限表，实现更精细的数据访问控制
如果系统支持多租户，可以添加租户表和租户相关的权限隔离机制
为了提高性能，建议对频繁查询的字段添加合适的索引
对于高并发系统，考虑对日志表进行分库分表或者定期归档处理

这套后台系统表结构设计遵循了数据库设计的三范式原则，同时兼顾了性能和扩展性，可以作为大多数后台管理系统的基础架构。根据具体业务需求，可以在此基础上进行适当的调整和扩展。