数据库设计
2025/12/3大约 14 分钟
数据库设计
1. 数据库概述
本文档详细描述了 Gupt Management System 项目的数据库设计方案，包括用户管理、课程管理、社团管理、宿舍管理和信息管理等核心模块的表结构设计。数据库采用 MySQL 关系型数据库，使用 InnoDB 存储引擎，以支持事务处理和外键约束。
1.1 数据库配置
- 数据库类型：MySQL
- 字符集：UTF-8mb4
- 排序规则：utf8mb4_general_ci
- 端口：3306
1.2 命名规范
- 表名：使用小写字母和下划线命名，格式为
模块名_实体名 - 字段名：使用小写字母和下划线命名
- 主键：通常命名为
id，使用自增整数类型 - 外键：通常命名为
关联表名_id或关联实体名_id
2. 索引策略
2.1 索引设计原则
- 对经常用于查询条件、排序和分组的字段创建索引
- 对用于外键关联的字段创建索引
- 避免在经常修改的字段上创建索引
- 合理设计复合索引，遵循最左前缀原则
2.2 推荐索引
- 主键字段自动创建索引
- 外键字段创建索引
- 常用查询条件字段创建索引
- 高选择性字段优先创建索引
3. 表结构设计
3.1 用户与身份管理模块
3.1.1 user_student（学生表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 学生ID |
| student_code | varchar(15) | NOT NULL | 学号（登录账号） |
| password | varchar(100) | NOT NULL | 密码 |
| id_card_number | varchar(18) | NOT NULL | 身份证号码 |
| name | varchar(100) | NOT NULL | 学生姓名 |
| gender | char | NULL | 性别（M=男，F=女） |
| college_id | int | NOT NULL | 学院ID |
| college_name | varchar(50) | NULL | 冗余字段：学院名称 |
| major_id | int | NOT NULL | 专业ID |
| major_name | varchar(50) | NULL | 冗余字段：专业名称 |
| class_id | int | NOT NULL | 班级ID |
| class_name | varchar(50) | NULL | 冗余字段：班级名称 |
| registration_date | date | NOT NULL | 注册日期 |
| political_status | tinyint | DEFAULT 3, NOT NULL | 政治面貌：0中共党员 1预备党员 2团员 3群众 |
| position | varchar(20) | DEFAULT '学生', NULL | 学生职务 |
| phone | varchar(18) | NULL | 手机号 |
| varchar(50) | NULL | 邮箱 | |
| head_teacher | varchar(50) | NULL | 班主任 |
| counselor | varchar(50) | NULL | 辅导员 |
| avatar | varchar(100) | NULL | 头像地址 |
| role_code | varchar(20) | DEFAULT 'ROLE_STUDENT', NOT NULL | 主角色编码 |
| role_names | varchar(50) | NOT NULL | 拥有的身份/角色列表 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
索引建议：
- 在 student_code 字段上创建唯一索引
- 在 id_card_number 字段上创建唯一索引
- 在 college_id、major_id、class_id 字段上创建索引
3.1.2 user_teacher（教师表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 教师ID |
| name | varchar(50) | NOT NULL | 教师姓名 |
| gender | char | NULL | 性别（M=男，F=女） |
| entry_date | date | NULL | 入职日期 |
| varchar(100) | NULL | 邮箱 | |
| phone | varchar(20) | NULL | 联系电话 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
索引建议：
- 在 name 字段上创建索引，以加快查询速度
- 在 email 字段上创建唯一索引
3.2 课程管理模块
3.2.1 course（课程表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 课程ID |
| name | varchar(100) | NOT NULL | 课程名称 |
| teacher_id | bigint | NOT NULL | 授课教师ID |
| location | varchar(100) | NULL | 上课地点 |
| credit | int | DEFAULT 0, NULL | 学分 |
| created_at | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| updated_at | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
外键约束：
- teacher_id 外键关联 user_teacher.id
索引建议：
- 在 name 字段上创建索引
- 在 teacher_id 字段上创建索引
3.2.2 course_grade（学生成绩表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 主键ID |
| student_id | bigint | NOT NULL | 学生ID |
| course_id | bigint | NOT NULL | 课程ID |
| academic_year | varchar(20) | NOT NULL | 学年，如2023-2024 |
| semester | tinyint | NOT NULL | 学期（1=第一学期，2=第二学期） |
| score | decimal(5,2) | NOT NULL | 成绩 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 更新时间 |
外键约束：
- student_id 外键关联 user_student.id
- course_id 外键关联 course.id
索引建议：
- 在 student_id 和 course_id 上创建复合索引
- 在 academic_year 和 semester 上创建复合索引
3.2.3 course_schedule（课程安排表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 课程安排ID |
| course_id | bigint | NOT NULL | 课程ID |
| week | int | NOT NULL | 第几周 |
| week_day | int | NOT NULL | 星期几（1-7） |
| start_slot | int | NOT NULL | 开始节次 |
| end_slot | int | NOT NULL | 结束节次 |
| location | varchar(100) | NULL | 上课地点 |
外键约束：
- course_id 外键关联 course.id
索引建议：
- 在 course_id 字段上创建索引
- 在 week, week_day, start_slot 上创建复合索引，便于查询特定时间段的课程
3.3 社团管理模块
3.3.1 club（社团信息表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 社团ID |
| name | varchar(100) | NOT NULL | 社团名称 |
| description | text | NULL | 社团简介 |
| logo_url | varchar(255) | NULL | 社团LOGO图片URL |
| create_time | timestamp | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | timestamp | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
| create_date | date | NULL | 创建日期 |
索引建议：
- 在 name 字段上创建唯一索引
3.3.2 club_members（社团成员关系表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 社团成员ID |
| club_id | bigint | NOT NULL | 所属社团ID |
| user_id | bigint | NOT NULL | 用户ID（外键，关联用户表） |
| join_time | timestamp | DEFAULT CURRENT_TIMESTAMP, NULL | 加入社团时间 |
外键约束：
- club_id 外键关联 club.id
- user_id 外键关联 user_student.id 或 user_teacher.id（取决于系统设计）
索引建议：
- 在 club_id 和 user_id 上创建复合唯一索引，确保一个用户在一个社团中只能有一条记录
3.4 宿舍管理模块
3.4.1 dormitory（宿舍表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 宿舍ID |
| campus | varchar(50) | NOT NULL | 所属校区 |
| building | varchar(50) | NOT NULL | 宿舍楼 |
| room | varchar(50) | NOT NULL | 房间号 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
索引建议：
- 在 campus, building, room 上创建复合唯一索引
3.4.2 dormitory_member（宿舍成员表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 记录ID |
| dormitory_id | bigint | NOT NULL | 对应宿舍ID |
| student_id | int | NOT NULL | 学生ID |
| student_name | varchar(20) | NOT NULL | 成员姓名 |
| is_head | tinyint(1) | DEFAULT 0, NULL | 是否是舍长 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
外键约束：
- dormitory_id 外键关联 dormitory.id
- student_id 外键关联 user_student.id
索引建议：
- 在 dormitory_id 字段上创建索引
- 在 student_id 字段上创建唯一索引，确保一个学生只能属于一个宿舍
3.4.3 dormitory_canteen（食堂表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 食堂ID |
| name | varchar(50) | NOT NULL | 食堂名称 |
| status | varchar(20) | NOT NULL | 营业状态：营业中/休息中 |
| business_hours | varchar(50) | NOT NULL | 营业时间 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
索引建议：
- 在 name 字段上创建唯一索引
- 在 status 字段上创建索引
3.4.4 dormitory_repair_order（宿舍设备报修单）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY | 报修单号 |
| dormitory_id | bigint | NULL | 宿舍ID，外键关联 dormitory.id |
| campus | varchar(10) | NOT NULL | 校区（冗余） |
| building | varchar(10) | NOT NULL | 楼栋（冗余） |
| room_number | varchar(5) | NOT NULL | 宿舍号（冗余） |
| repair_type | varchar(50) | NOT NULL | 维修类型 |
| content | text | NOT NULL | 维修内容 |
| status | varchar(20) | DEFAULT '待处理', NULL | 维修状态（待处理 / 处理中 / 已完成） |
| submit_time | datetime | NOT NULL | 提交时间 |
| finish_time | datetime | NULL | 完成时间 |
| message | varchar(100) | DEFAULT '未留言', NULL | 留言 |
| feedback | text | NULL | 维修反馈 |
| repairer_id | bigint | NULL | 维修人员ID |
| repairer_name | varchar(50) | NULL | 维修人员姓名 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
| is_deleted | tinyint(1) | DEFAULT 0, NULL | 逻辑删除（0=未删除，1=已删除） |
| user_id | mediumtext | NOT NULL | 报修人ID |
| user_phone | varchar(20) | NOT NULL | 报修人联系电话 |
外键约束：
- dormitory_id 外键关联 dormitory.id
索引建议：
- 在 dormitory_id 字段上创建索引
- 在 status 字段上创建索引
- 在 submit_time 字段上创建索引
3.4.5 dormitory_score_item（评分项定义表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 评分项ID |
| name | varchar(50) | NOT NULL | 评分项名称，如：地面整洁、物品摆放 |
| full_score | decimal(4,2) | NOT NULL | 评分项满分值 |
| description | varchar(255) | NULL | 评分项描述 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
索引建议：
- 在 name 字段上创建唯一索引
3.4.6 dormitory_daily_score（宿舍每日评分主表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 记录ID |
| dormitory_id | bigint | NOT NULL | 宿舍ID |
| score_date | date | NOT NULL | 评分日期 |
| total_points | decimal(5,2) | NOT NULL | 总得分 |
| grade | varchar(1) | NULL | 评级 A/B/C/D |
| comment | varchar(255) | NULL | 总评评语 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
外键约束：
- dormitory_id 外键关联 dormitory.id
索引建议：
- 在 dormitory_id 字段上创建索引
- 在 score_date 字段上创建索引
- 在 dormitory_id 和 score_date 上创建复合唯一索引
3.5 信息管理模块
3.5.1 info_notice（通知表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 通知ID |
| title | varchar(255) | NOT NULL | 通知标题 |
| content | text | NULL | 通知内容 |
| photo_url | varchar(500) | NULL | 图片URL |
| author | varchar(10) | NULL | 作者 |
| date | datetime | DEFAULT (now()), NULL | 发布日期 |
| create_date | datetime | DEFAULT CURRENT_TIMESTAMP, NOT NULL | 创建时间 |
| update_date | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL | 更新时间 |
索引建议：
- 在 title 字段上创建索引
- 在 date 字段上创建索引
3.5.2 info_news（新闻表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 新闻ID |
| title | varchar(255) | NOT NULL | 新闻标题 |
| content | text | NULL | 新闻内容 |
| photo_url | varchar(500) | NULL | 图片URL |
| author | varchar(10) | NULL | 作者 |
| date | datetime | DEFAULT (now()), NULL | 发布日期 |
| create_date | datetime | DEFAULT CURRENT_TIMESTAMP, NOT NULL | 创建时间 |
| update_date | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NOT NULL | 更新时间 |
索引建议：
- 在 title 字段上创建索引
- 在 date 字段上创建索引
3.5.3 info_article_type（文章类型表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | int | PRIMARY KEY, AUTO_INCREMENT | 类型ID |
| name | varchar(100) | NOT NULL, UNIQUE | 类型名称 |
索引建议：
- name 字段已有唯一索引
3.6 其他表
3.6.1 emails（邮件表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | bigint | PRIMARY KEY, AUTO_INCREMENT | 邮件ID，唯一标识一封邮件 |
| subject | varchar(255) | NULL | 邮件主题 |
| sender_id | bigint | NULL | 发件人ID，引用 users 表的 id |
| sender | varchar(30) | NULL | 发送者 |
| recipient_id | bigint | NULL | 收件人ID，引用 users 表的 id |
| recipient | varchar(30) | NULL | 接收者 |
| body | text | NULL | 邮件内容 |
| sent_at | datetime | NULL | 邮件发送时间 |
| status | enum('sent','draft','received','deleted') | NULL | 邮件状态，标识邮件是已发送、草稿、已接收还是已删除 |
| is_starred | tinyint(1) | DEFAULT 0, NULL | 是否为星标邮件，默认为否 |
| create_time | datetime | DEFAULT CURRENT_TIMESTAMP, NULL | 创建时间 |
| update_time | datetime | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, NULL | 更新时间 |
| is_deleted | tinyint(1) | DEFAULT 0, NULL | 逻辑删除字段 |
| original_status | enum('sent','draft','received','deleted') | NULL | 原状态 |
| is_read | tinyint(1) | DEFAULT 0, NULL | 是否已读，0为未读，1为已读 |
| received_at | datetime | NULL | 接收时间 |
外键约束：
- sender_id 外键关联 user_student.id 或 user_teacher.id
- recipient_id 外键关联 user_student.id 或 user_teacher.id
索引建议：
- 在 sender_id 和 recipient_id 上创建索引
- 在 status 和 is_deleted 上创建复合索引
- 在 sent_at 和 received_at 上创建索引
3.6.2 faq（常见问题表）
| 字段名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| id | int | PRIMARY KEY, AUTO_INCREMENT | 问题ID |
| question_pattern | varchar(255) | NULL | 问题模式（支持通配符） |
| answer | text | NOT NULL | 答案 |
| source_url | varchar(255) | NULL | 来源URL |
索引建议：
- 在 question_pattern 字段上创建全文索引
4. 数据库关系图
5. 约束条件
5.1 主键约束
- 所有表都应有主键，用于唯一标识记录
- 主键通常为自增整数类型（AUTO_INCREMENT）
5.2 外键约束
- 建立表之间的关联关系，确保数据一致性
- 外键字段应与被引用表的主键字段类型一致
- 建议使用 ON DELETE CASCADE 或 ON UPDATE CASCADE 来维护数据完整性
5.3 唯一约束
- 确保某些字段的值在表中是唯一的
- 如用户名、邮箱、学号等字段应设置唯一约束
5.4 非空约束
- 确保必要字段必须有值
- 如用户姓名、课程名称等关键字段应设置非空约束
5.5 默认值约束
- 为某些字段设置默认值，减少数据插入时的工作量
- 如创建时间、更新时间、状态等字段可设置默认值
6. 数据类型选择
6.1 整数类型
- tinyint：用于表示有限范围的整数值，如性别、状态等
- smallint：用于表示较小范围的整数值
- int：用于表示一般范围的整数值
- bigint：用于表示大范围的整数值，如ID、数量等
6.2 字符串类型
- varchar：用于存储可变长度的字符串，如姓名、描述等
- char：用于存储固定长度的字符串，如性别代码等
- text：用于存储较长的文本内容，如文章内容、描述等
6.3 日期和时间类型
- date：用于存储日期（年-月-日）
- time：用于存储时间（时:分:秒）
- datetime：用于存储日期和时间
- timestamp：用于存储时间戳，自动记录行修改时间
6.4 其他类型
- decimal：用于存储精确的小数值，如金额、成绩等
- enum：用于存储枚举值，限制字段只能从预定义的列表中选择
- boolean：用于存储布尔值（true/false），在MySQL中通常使用tinyint(1)实现
7. 数据库优化建议
7.1 查询优化
- 避免使用 SELECT *，只查询需要的字段
- 使用索引优化查询性能
- 避免在 WHERE 子句中对索引字段进行函数操作
- 使用 JOIN 代替子查询，优化多表查询
7.2 表结构优化
- 合理设计字段类型，避免使用过大的数据类型
- 使用适当的存储引擎（如InnoDB支持事务）
- 规范化与反规范化结合，平衡查询性能和数据一致性
- 对于频繁查询的字段，可考虑添加冗余字段
7.3 索引优化
- 为经常用于查询条件、排序和分组的字段创建索引
- 避免在经常修改的字段上创建索引
- 合理设计复合索引，遵循最左前缀原则
- 定期分析和优化索引，移除不必要的索引
7.4 事务管理
- 使用事务保证数据的一致性和完整性
- 保持事务简短，减少锁定时间
- 避免在事务中执行耗时操作
8. 数据安全与维护
8.1 数据安全
- 密码加密存储，使用加盐哈希算法
- 限制数据库用户权限，遵循最小权限原则
- 定期备份数据库，确保数据安全
- 使用参数化查询，防止SQL注入攻击
8.2 数据维护
- 定期清理过期数据，保持数据库性能
- 定期优化表结构和索引
- 监控数据库性能，及时发现和解决问题
- 建立完善的日志记录机制
9. 总结
本文档详细描述了 Gupt Management System 项目的数据库设计方案，包括用户管理、课程管理、社团管理、宿舍管理和信息管理等核心模块的表结构设计。通过合理的表结构设计、索引策略和约束条件，可以确保数据库的性能、可靠性和安全性，为系统的稳定运行提供坚实的基础。