核心表结构

1. 用户表（users）

CREATE TABLE users (user_id         BIGINT       PRIMARY KEY AUTO_INCREMENT,username        VARCHAR(50)  NOT NULL UNIQUE,password        VARCHAR(255) NOT NULL,email          VARCHAR(100) UNIQUE,real_name      VARCHAR(50),phone          VARCHAR(20),status         TINYINT      DEFAULT 1 COMMENT '1:启用,0:禁用',created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,updated_at     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,last_login     DATETIME,deleted_at     DATETIME     COMMENT '软删除时间',INDEX idx_username (username),INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2. 角色表（roles）

CREATE TABLE roles (role_id        BIGINT       PRIMARY KEY AUTO_INCREMENT,role_name      VARCHAR(50)  NOT NULL UNIQUE,role_code      VARCHAR(50)  NOT NULL UNIQUE COMMENT '角色编码',description    VARCHAR(200),status         TINYINT      DEFAULT 1 COMMENT '1:启用,0:禁用',created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,updated_at     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_role_code (role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

3. 权限表（permissions）

CREATE TABLE permissions (permission_id   BIGINT       PRIMARY KEY AUTO_INCREMENT,parent_id      BIGINT       DEFAULT 0 COMMENT '父权限ID',perm_name      VARCHAR(50)  NOT NULL,perm_code      VARCHAR(100) NOT NULL UNIQUE COMMENT '权限编码',perm_type      TINYINT     DEFAULT 1 COMMENT '1:菜单,2:按钮,3:接口',path           VARCHAR(200) COMMENT '前端路由路径',component      VARCHAR(200) COMMENT '前端组件路径',icon           VARCHAR(50)  COMMENT '图标',sort_order     INT         DEFAULT 0 COMMENT '排序',status         TINYINT     DEFAULT 1 COMMENT '1:启用,0:禁用',created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,updated_at     DATETIME    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_parent_id (parent_id),INDEX idx_perm_code (perm_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

4. 用户角色关联表（user_roles）

CREATE TABLE user_roles (user_id        BIGINT,role_id        BIGINT,created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (user_id, role_id),CONSTRAINT fk_ur_user_id FOREIGN KEY (user_id) REFERENCES users(user_id),CONSTRAINT fk_ur_role_id FOREIGN KEY (role_id) REFERENCES roles(role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

5. 角色权限关联表（role_permissions）

CREATE TABLE role_permissions (role_id        BIGINT,permission_id  BIGINT,created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (role_id, permission_id),CONSTRAINT fk_rp_role_id FOREIGN KEY (role_id) REFERENCES roles(role_id),CONSTRAINT fk_rp_permission_id FOREIGN KEY (permission_id) REFERENCES permissions(permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

6. 部门表（departments）- 可选

CREATE TABLE departments (dept_id        BIGINT       PRIMARY KEY AUTO_INCREMENT,parent_id      BIGINT       DEFAULT 0,dept_name      VARCHAR(50)  NOT NULL,dept_code      VARCHAR(50)  NOT NULL UNIQUE,leader_id      BIGINT       COMMENT '部门负责人ID',sort_order     INT          DEFAULT 0,status         TINYINT      DEFAULT 1 COMMENT '1:启用,0:禁用',created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,updated_at     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_parent_id (parent_id),INDEX idx_dept_code (dept_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

扩展表结构

1. 操作日志表（operation_logs）

CREATE TABLE operation_logs (log_id         BIGINT       PRIMARY KEY AUTO_INCREMENT,user_id        BIGINT       NOT NULL,operation      VARCHAR(200) NOT NULL COMMENT '操作描述',method         VARCHAR(100) COMMENT '请求方法',params         TEXT         COMMENT '请求参数',ip             VARCHAR(50)  COMMENT '操作IP',location       VARCHAR(100) COMMENT '操作地点',created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,INDEX idx_user_id (user_id),INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';

2. 数据权限表（data_permissions）- 可选

CREATE TABLE data_permissions (id             BIGINT       PRIMARY KEY AUTO_INCREMENT,role_id        BIGINT       NOT NULL,dept_id        BIGINT       NOT NULL,data_type      TINYINT     DEFAULT 1 COMMENT '1:本部门数据,2:本部门及下级数据,3:全部数据',created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uk_role_dept (role_id, dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据权限表';

设计说明

1. RBAC模型说明

• 采用基于角色的访问控制（RBAC）模型
• 用户与角色是多对多关系
• 角色与权限是多对多关系
• 权限支持菜单、按钮、接口三种类型

2. 核心功能支持

• 用户管理：支持用户的增删改查、启用禁用
• 角色管理：支持角色的增删改查、权限分配
• 权限管理：支持权限的增删改查、树形结构展示
• 部门管理：支持组织架构管理（可选）
• 操作日志：记录用户操作轨迹
• 数据权限：支持按部门划分数据权限（可选）

3. 性能优化建议

1. 索引优化

   • 主键使用自增ID
   • 为常用查询字段创建索引
   • 避免过度建立索引

2. 查询优化

   • 使用JOIN代替子查询
   • 合理使用缓存
   • 分页查询大数据量

4. 安全性建议

1. 密码安全

   • 密码加密存储
   • 定期修改密码
   • 密码强度验证

2. 访问控制

   • Session管理
   • Token验证
   • 防SQL注入

3. 日志记录

   • 记录关键操作
   • 定期备份日志
   • 异常监控告警

示例查询

1. 获取用户的所有角色

SELECT r.* 
FROM roles r 
JOIN user_roles ur ON r.role_id = ur.role_id 
WHERE ur.user_id = ?;

2. 获取角色的所有权限

SELECT p.* 
FROM permissions p 
JOIN role_permissions rp ON p.permission_id = rp.permission_id 
WHERE rp.role_id = ?;

3. 获取用户的所有权限

SELECT DISTINCT p.* 
FROM permissions p 
JOIN role_permissions rp ON p.permission_id = rp.permission_id 
JOIN user_roles ur ON rp.role_id = ur.role_id 
WHERE ur.user_id = ?;

缓存策略

1. 权限缓存

// 示例缓存key设计
String userPermissionKey = "user:permissions:" + userId;
String rolePermissionKey = "role:permissions:" + roleId;

2. 缓存更新策略

• 权限变更时更新缓存
• 定时刷新缓存
• 采用多级缓存架构

接口设计建议

1. RESTful API

GET    /api/users           # 获取用户列表
POST   /api/users           # 创建用户
PUT    /api/users/{id}      # 更新用户
DELETE /api/users/{id}      # 删除用户

2. 权限检查注解

@RequiresPermissions("user:view")
public Response getUserList() {// 实现代码
}

部署建议

1. 数据库部署

• 主从复制
• 定期备份
• 监控告警

2. 应用部署

• 集群部署
• 负载均衡
• 容器化管理