技术栈
bash
1. 前端：Vue + VueElementuiplus + Axios + Echarts
2. 后端 nestjs + Prisma + MySQL
3. 数据库 mysql + redis
4. Api文档 seagger
5. 服务器：Docker + Nginx + Linux + PM2数据库设计
RBAC 权限设计
- 凡是 RBAC 都会涉及到这 5 张表.
sql
1. 用户表：user
2. 角色表：role
3. 权限表：permission
4. 用户角色关联表：user_role
5. 角色权限关联表：role_permissionusers
- 用户表
sql
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户密码',
`nick_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户昵称',
`email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '邮箱',
`head_pic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户头像',
`phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手机号',
`is_frozen` int NOT NULL DEFAULT '2' COMMENT '是否被冻结 1是 2否',
`is_admin` int NOT NULL DEFAULT '2' COMMENT '是否是管理员 1是 2 否',
`creat_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;meeting_rooms
- 会议室表
sql
CREATE TABLE `meeting_rooms` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '会议室ID',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '会议室名字',
`capacity` int NOT NULL COMMENT '会议室容量',
`location` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '会议室位置',
`equipment` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备',
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '会议室描述',
`is_booked` int NOT NULL DEFAULT '1' COMMENT '是否被预订 1就是没有预定 2 就是已经被预定',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`upadte_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;bookings
- 预定表
sql
CREATE TABLE `bookings` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '预定ID',
`user_id` int NOT NULL COMMENT '预定用户ID',
`room_id` int NOT NULL COMMENT '会议室ID',
`start_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '会议开始时间',
`end_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '会议结束时间',
`status` int NOT NULL DEFAULT '0' COMMENT '状态(申请中 0 ,审批通过 1,审批驳回 2 ,已解除3)',
`note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;booking_attendees
预订-参会人表
sql
CREATE TABLE `booking_attendees` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`user_id` int NOT NULL COMMENT '参会用户ID',
`booking_id` int NOT NULL COMMENT '预定会议ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;roles
角色表
sql
CREATE TABLE `roles` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '角色名',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;permissions
权限表
sql
CREATE TABLE `permissions` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '权限代码',
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '权限描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;user_roles
用户角色关联表
sql
CREATE TABLE `user_roles` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` int DEFAULT NULL COMMENT '用户ID',
`role_id` int DEFAULT NULL COMMENT '角色ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;role_permissions
角色权限关联表
sql
CREATE TABLE `role_permissions` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`role_id` int DEFAULT NULL COMMENT '角色id',
`permission_id` int DEFAULT NULL COMMENT '权限id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;模块划分
用户管理模块
| 接口路径 | 请求方式 | 描述 |
|---|---|---|
| /user/login | POST | 普通用户登录 |
| /user/register | POST | 普通用户注册 |
| /user/update | POST | 普通用户个人信息修改 |
| /user/update_password | POST | 普通用户修改密码 |
| /user/admin/login | POST | 管理员登录 |
| /user/admin/update_password | POST | 管理员修改密码 |
| /user/admin/update | POST | 管理员个人信息修改 |
| /user/list | GET | 用户列表 |
| /user/freeze | GET | 冻结用户 |
会议室管理模块
| 接口路径 | 请求方式 | 描述 |
|---|---|---|
| /meeting_room/list | GET | 会议室列表 |
| /meeting_room/delete/:id | DELETE | 会议室删除 |
| /meeting_room/update/:id | PUT | 会议室更新 |
| /meeting_room/create | POST | 会议室新增 |
| /meeting_room/search | GET | 会议室搜索 |
预定管理模块
| 接口路径 | 请求方式 | 描述 |
|---|---|---|
| /booking/list | GET | 预订列表 |
| /booking/approve | POST | 审批预订申请 |
| /booking/add | POST | 申请预订 |
| /booking/apply/:id | GET | 通过预订 |
| /booking/reject/:id | GET | 取消预订 |
| /booking/unbind/:id | GET | 解除预订 |
| /booking/history | GET | 预订历史 |
| /booking/urge | GET | 催办 |
统计模块
| 接口路径 | 请求方式 | 描述 |
|---|---|---|
| /statistics/meeting_room_usage_frequency | GET | 会议室使用频率统计 |
| /statistics/user_booking_frequency | GET | 用户预订频率统计 |
总共 4 个模块
用户管理模块
会议室管理模块
预定管理模块
统计管理模块
角色划分
- 第二部分
- 第三部分
对应的 prisma 数据结构
booking_attendees.prisma
bash
model booking_attendees {
id Int @id @default(autoincrement())
user_id Int
booking_id Int
user users @relation(fields: [user_id], references: [id])
booking bookings @relation(fields: [booking_id], references: [id])
}bookings.prisma
bash
model bookings {
id Int @id @default(autoincrement())
user_id Int
room_id Int
start_time String @default("") @db.VarChar(255)
end_time String @default("") @db.VarChar(255)
status Int @default(0)
note String? @db.VarChar(255)
create_time DateTime @default(now()) @db.Timestamp(0)
update_time DateTime @default(now()) @db.Timestamp(0)
booking_attendees booking_attendees[]
}meeting_rooms.prisma
bash
model meeting_rooms {
id Int @id @default(autoincrement())
name String @db.VarChar(50)
capacity Int
location String @db.VarChar(50)
equipment String? @db.VarChar(50)
description String? @db.VarChar(255)
is_booked Int @default(1)
create_time DateTime @default(now()) @db.Timestamp(0)
upadte_time DateTime @default(now()) @db.Timestamp(0)
}permissions.prisma
bash
model permissions {
id Int @id @default(autoincrement())
code String @default("") @db.VarChar(255)
description String? @db.VarChar(255)
roles_permissions role_permissions[]
}role_permissions.prisma
bash
model role_permissions {
id Int @id @default(autoincrement())
role_id Int?
permission_id Int?
role roles? @relation(fields: [role_id], references: [id])
permission permissions? @relation(fields: [permission_id], references: [id])
}roles.prisma
bash
model roles {
id Int @id @default(autoincrement())
name String @default("") @db.VarChar(20)
user_roles user_roles[]
roles_permissions role_permissions[]
}schema.prisma (prisma 系统)
bash
generator client {
provider = "prisma-client"
output = "../generated/prisma"
moduleFormat = "cjs"
}
datasource db {
provider = "mysql"
}user_roles.prisma
ts
model user_roles {
id Int @id @default(autoincrement())
user_id Int?
role_id Int?
user users? @relation(fields: [user_id], references: [id])
role roles? @relation(fields: [role_id], references: [id])
}users.prisma
bash
model users {
id Int @id @default(autoincrement())
username String @db.VarChar(50)
password String @db.VarChar(50)
nick_name String @db.VarChar(50)
email String @db.VarChar(50)
head_pic String @db.VarChar(255)
phone_number String @db.VarChar(20)
is_frozen Int @default(2)
is_admin Int @default(2)
creat_time DateTime @default(now()) @db.Timestamp(0)
update_time DateTime @default(now()) @db.Timestamp(0)
user_roles user_roles[]
booking_attendees booking_attendees[]
}