简介这份Word文档面向计算机专业学生与数据库课程设计者系统讲解数据库类在线学习系统的数据库设计全过程可帮助读者完成课程设计、毕业设计或自学数据库建模。资源包共1个doc文件约625KB内容为完整可编辑的Word版设计文档便于直接参考与修改。文档从系统功能需求分析入手将系统划分为在线学习、在线交流、在线测试和后台管理四大模块并给出各模块的结构功能图随后进入概念结构设计识别教师、学生、公告、教程、试题、成绩、帖子七个实体绘制整体E-R图与单个实体属性图逻辑结构设计阶段将E-R图转化为关系模型并进一步给出tb_teacher、tb_bulletin、tb_course、tb_tiezi、tb_reply、tb_exam、tb_student、tb_result等数据表的字段、数据类型与主外键说明。已有71人学习适合需要完整数据库设计范例与建表参考的读者。1. 从一份“数据库类在线学习系统”的库表设计说起为什么它值得你花时间很多人第一次接触数据库设计是在课程设计任务里被要求交一份“数据库类在线学习系统的数据库设计.doc”。听起来像交作业但真正做过线上学习平台的人都知道这套库表结构一旦定歪后面题库、选课、学习进度、成绩统计全都会跟着翻车。它要解决的核心问题很具体一个用户能选多门课、一门课有多个章节、章节下挂视频和题库、学习行为要能追踪、成绩要能回算。适合谁正在做课程设计的学生、要搭内部培训系统的后端、以及准备把“数据库设计”从纸面落到 MySQL 或 PostgreSQL 的工程师。这一章先把需求边界讲清楚后面几章再拆表、写 SQL、填数据、排坑。2. 需求到实体在线学习系统到底该拆出哪几张表2.1 先分清“人、课、内容、行为”四类实体做数据库设计最怕一上来就画 ER 图结果画到一半发现漏了“学习行为”这条线。我的习惯是先把需求按四类实体归位人用户、角色、教师、课课程、分类、选课关系、内容章节、视频、题库、题目、行为学习记录、答题记录、成绩。这四类之间是多对多和一对多的混合关系比如一个用户选多门课一门课有多个章节一个章节有多道题。把实体归位之后表名基本就出来了不会出现“课程里塞了学习进度”这种耦合。常见做法是先写一份字段清单每个字段标注类型、是否为空、默认值、索引需求。这一步别偷懒因为后面建表语句、增删改查、并发锁都依赖它。字段清单里要特别标出哪些是外键、哪些是状态字段、哪些是时间字段这三类是后期最容易出问题的地方。2.2 核心表结构与字段设计下面这套表结构是我在多个学习系统里反复用过的精简版覆盖用户、课程、章节、选课、学习记录、题库、答题记录七张核心表。字段类型以 MySQL 8.x 为准PostgreSQL 只需把AUTO_INCREMENT换成SERIAL或GENERATED。-- 用户表区分学生、教师、管理员 CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户主键, username VARCHAR(64) NOT NULL COMMENT 登录名唯一, password_hash CHAR(60) NOT NULL COMMENT bcrypt 哈希不存明文, role TINYINT NOT NULL DEFAULT 0 COMMENT 0学生 1教师 2管理员, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; -- 课程表课程基本信息 CREATE TABLE course ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(128) NOT NULL COMMENT 课程名, teacher_id BIGINT UNSIGNED NOT NULL COMMENT 授课教师, category VARCHAR(32) DEFAULT NULL COMMENT 课程分类, status TINYINT NOT NULL DEFAULT 1 COMMENT 1上架 0下架, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_teacher (teacher_id), KEY idx_category_status (category, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程表; -- 章节表课程下的内容单元 CREATE TABLE chapter ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, course_id BIGINT UNSIGNED NOT NULL, title VARCHAR(128) NOT NULL, sort_no INT NOT NULL DEFAULT 0 COMMENT 排序号越小越靠前, video_url VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id), KEY idx_course_sort (course_id, sort_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT章节表; -- 选课表用户与课程的多对多关系 CREATE TABLE enrollment ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, course_id BIGINT UNSIGNED NOT NULL, enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_course (user_id, course_id), KEY idx_course (course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT选课表; -- 学习记录表记录每个用户在每个章节的进度 CREATE TABLE learning_record ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, chapter_id BIGINT UNSIGNED NOT NULL, progress TINYINT NOT NULL DEFAULT 0 COMMENT 0-100 百分比, last_view_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_chapter (user_id, chapter_id), KEY idx_chapter (chapter_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学习记录表; -- 题目表题库 CREATE TABLE question ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, chapter_id BIGINT UNSIGNED NOT NULL, content TEXT NOT NULL COMMENT 题干, options JSON DEFAULT NULL COMMENT 选项JSON 数组, answer VARCHAR(16) NOT NULL COMMENT 正确答案, score INT NOT NULL DEFAULT 1, PRIMARY KEY (id), KEY idx_chapter (chapter_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT题目表; -- 答题记录表每次作答都落一条 CREATE TABLE answer_record ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, question_id BIGINT UNSIGNED NOT NULL, user_answer VARCHAR(16) NOT NULL, is_correct TINYINT NOT NULL DEFAULT 0, answered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_time (user_id, answered_at), KEY idx_question (question_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT答题记录表;逻辑说明user和course是基础实体enrollment用联合唯一键防止重复选课learning_record同样用联合唯一键保证一个用户对一个章节只有一条进度记录更新时直接ON DUPLICATE KEY UPDATE即可。question的options用 JSON 存是因为选项数量不固定用 JSON 比拆子表更省事但要注意 MySQL 5.7 以下不支持 JSON 类型。参数说明utf8mb4是为了支持中文和 emojiInnoDB是为了事务和行锁BIGINT UNSIGNED是防止用户量大了之后主键溢出password_hash用CHAR(60)是因为 bcrypt 固定 60 字符别用VARCHAR(255)浪费空间。索引方面enrollment的uk_user_course既做唯一约束又做查询索引learning_record的uk_user_chapter同理。2.3 建表顺序与外键取舍建表顺序要按依赖关系来先user、course再chapter然后enrollment、learning_record、question最后answer_record。如果你要用外键约束就在建表时加上FOREIGN KEY但很多线上系统为了性能和分库分表方便会故意不加外键改由应用层保证一致性。我的建议是课程设计阶段加上外键方便理解关系生产环境如果 QPS 高就去掉外键用应用层校验加定期对账。提示如果老师或评审要求“必须有外键”就在建表语句里补上CONSTRAINT fk_xxx FOREIGN KEY (xxx) REFERENCES xxx(id) ON DELETE CASCADE但要注意级联删除的风险删课程会连带删章节和题目。3. 把设计落成可跑的库建库、导数据、跑通增删改查3.1 建库与执行建表脚本拿到上面的建表语句后先建库再执行。命令行里用mysql客户端或者dbx数据库工具都行核心是字符集要统一。# 登录 MySQL注意替换成你自己的账号 mysql -u root -p # 建库字符集和排序规则要和表一致 CREATE DATABASE online_learning DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE online_learning; # 然后依次执行上面的 CREATE TABLE 语句 # 如果保存成了 schema.sql可以直接 source SOURCE /path/to/schema.sql;逻辑说明utf8mb4_0900_ai_ci是 MySQL 8.0 的默认排序规则大小写不敏感适合用户名、课程名这类字段。如果你用的是 MySQL 5.7换成utf8mb4_general_ci。执行完用SHOW TABLES;确认七张表都在再用SHOW CREATE TABLE course\G检查字段和索引是否符合预期。参数说明SOURCE命令后面跟绝对路径别用相对路径否则容易找不到文件。如果用的是dbx数据库工具或 Navicat 这类图形客户端直接打开 SQL 文件点执行即可但要注意客户端默认字符集别让中文变成乱码。3.2 插入测试数据并验证关联建完表先插一批测试数据验证外键和唯一键是否生效。下面这段 SQL 覆盖了用户、课程、章节、选课、题目、答题记录。-- 插入用户一个教师、一个学生 INSERT INTO user (username, password_hash, role) VALUES (teacher_zhang, $2b$12$abcdefghijklmnopqrstuv, 1), (student_li, $2b$12$zyxwvutsrqponmlkjihgfe, 0); -- 插入课程teacher_id 对应上面教师的主键 1 INSERT INTO course (title, teacher_id, category, status) VALUES (数据库系统概论, 1, 计算机, 1); -- 插入章节 INSERT INTO chapter (course_id, title, sort_no, video_url) VALUES (1, 第一章 绪论, 1, https://example.com/video/1.mp4), (1, 第二章 关系模型, 2, https://example.com/video/2.mp4); -- 学生选课 INSERT INTO enrollment (user_id, course_id) VALUES (2, 1); -- 插入题目 INSERT INTO question (chapter_id, content, options, answer, score) VALUES (1, 数据库系统的核心是, [A. 数据, B. 数据库管理系统, C. 数据库管理员, D. 硬件], B, 2); -- 学生答题 INSERT INTO answer_record (user_id, question_id, user_answer, is_correct) VALUES (2, 1, B, 1);逻辑说明插入顺序必须遵守依赖关系先user再course再chapter否则外键会报错。enrollment的联合唯一键保证同一个学生不能重复选同一门课你可以试着再插一条(2, 1)会看到Duplicate entry错误这就是唯一键在起作用。参数说明password_hash这里只是占位真实系统里要用 bcrypt 或 argon2 生成别存明文。options字段是 JSON 数组插入时用标准 JSON 格式查询时可以用JSON_EXTRACT或-取值。3.3 增删改查的典型语句与索引命中学习系统里最高频的查询是“某用户选了哪些课”“某课程有哪些章节”“某用户在某课程的答题正确率”。下面这几条 SQL 覆盖了这些场景并且都能命中索引。-- 查询某学生选的所有课程命中 uk_user_course 的左前缀 SELECT c.id, c.title, c.category FROM enrollment e JOIN course c ON c.id e.course_id WHERE e.user_id 2; -- 查询某课程的所有章节按排序号升序命中 idx_course_sort SELECT id, title, sort_no, video_url FROM chapter WHERE course_id 1 ORDER BY sort_no ASC; -- 更新学习进度存在则更新不存在则插入利用唯一键 INSERT INTO learning_record (user_id, chapter_id, progress) VALUES (2, 1, 80) ON DUPLICATE KEY UPDATE progress VALUES(progress), last_view_at NOW(); -- 统计某学生在某课程的答题正确率 SELECT COUNT(*) AS total, SUM(is_correct) AS correct, ROUND(SUM(is_correct) / COUNT(*) * 100, 2) AS accuracy FROM answer_record ar JOIN question q ON q.id ar.question_id JOIN chapter ch ON ch.id q.chapter_id WHERE ar.user_id 2 AND ch.course_id 1; -- 删除一条选课记录注意先删学习记录和答题记录否则外键会拦 DELETE FROM enrollment WHERE user_id 2 AND course_id 1;逻辑说明第一条查询用enrollment的联合唯一键左前缀user_id命中索引再回表查course。第二条用idx_course_sort直接命中排序。第三条用ON DUPLICATE KEY UPDATE是 MySQL 特有的写法PostgreSQL 要用INSERT ... ON CONFLICT ... DO UPDATE。第四条是多表 JOIN 聚合数据量大时要注意answer_record的idx_user_time是否被用上。参数说明VALUES(progress)在 MySQL 8.0.20 之后被标记为废弃推荐用别名写法AS new ON DUPLICATE KEY UPDATE progress new.progress。ROUND的第二个参数是小数位数按需调整。注意删除操作一定要按依赖倒序来先删answer_record、learning_record再删enrollment最后删chapter、course。如果加了ON DELETE CASCADE删课程会自动级联但生产环境慎用。4. 避坑与排查数据库设计里最容易翻车的五个地方4.1 现象中文课程名插入后变成问号原因建库或建表时字符集用了latin1或utf8三字节而中文和 emoji 需要utf8mb4。解决建库时显式指定DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci建表时也带上DEFAULT CHARSETutf8mb4连接串里加characterEncodingutf8。已经建错的表用ALTER TABLE course CONVERT TO CHARACTER SET utf8mb4;转换。4.2 现象选课表出现重复记录原因只建了普通索引没建唯一索引或者应用层没做幂等判断。解决给enrollment加UNIQUE KEY uk_user_course (user_id, course_id)插入时用INSERT IGNORE或ON DUPLICATE KEY UPDATE。如果历史数据已经有重复先用DELETE t1 FROM enrollment t1 JOIN enrollment t2 ON t1.user_id t2.user_id AND t1.course_id t2.course_id AND t1.id t2.id;清理。4.3 现象统计答题正确率时结果偏大原因answer_record里同一个用户对同一道题可能答了多次直接COUNT(*)会把重复作答算进去。解决要么在应用层限制每题只记最后一次要么在 SQL 里用子查询取最新一条SELECT ... FROM (SELECT user_id, question_id, MAX(answered_at) AS last_at FROM answer_record GROUP BY user_id, question_id) t JOIN answer_record ar ON ar.user_id t.user_id AND ar.question_id t.question_id AND ar.answered_at t.last_at。4.4 现象学习进度更新时出现死锁原因两个事务同时更新同一用户的多个章节记录加锁顺序不一致。解决统一按chapter_id升序更新或者把learning_record的更新放到同一个事务里按固定顺序执行。MySQL 8.0 可以用SELECT ... FOR UPDATE显式加锁但要注意锁范围。更稳妥的做法是用ON DUPLICATE KEY UPDATE单条更新减少事务持有锁的时间。4.5 现象删课程时外键报错删不掉原因chapter、enrollment等表还有引用该课程的数据。解决要么按依赖倒序手动删要么在建表时加ON DELETE CASCADE。如果不想级联删就先把课程status置为 0下架逻辑删除而不是物理删除。这也是很多线上系统的做法保留数据方便追溯。5. 进阶技巧用视图和定时任务把成绩统计做成“后悔药”前面四章把表建好、数据跑通了但真实学习系统里成绩统计和进度汇总不能每次都靠手写 JOIN。我的习惯是建两个视图再加一个定时任务把常用统计固化下来。这样即使后面表结构微调只要视图接口不变上层代码就不用改。先建一个“学生课程成绩视图”把每个学生在每门课的正确率、答题数、学习进度一次算好。CREATE OR REPLACE VIEW v_student_course_score AS SELECT e.user_id, e.course_id, c.title AS course_title, COUNT(DISTINCT ar.id) AS answer_count, SUM(ar.is_correct) AS correct_count, ROUND(SUM(ar.is_correct) / NULLIF(COUNT(DISTINCT ar.id), 0) * 100, 2) AS accuracy, ROUND(AVG(lr.progress), 2) AS avg_progress FROM enrollment e JOIN course c ON c.id e.course_id LEFT JOIN chapter ch ON ch.course_id c.id LEFT JOIN learning_record lr ON lr.chapter_id ch.id AND lr.user_id e.user_id LEFT JOIN question q ON q.chapter_id ch.id LEFT JOIN answer_record ar ON ar.question_id q.id AND ar.user_id e.user_id GROUP BY e.user_id, e.course_id, c.title;逻辑说明用LEFT JOIN是为了保证即使学生没答题、没看视频也能出现在视图里accuracy用NULLIF防止除零。COUNT(DISTINCT ar.id)避免多表 JOIN 导致的重复计数。这个视图可以直接给报表用也可以作为定时任务的源。参数说明NULLIF(COUNT(DISTINCT ar.id), 0)在答题数为 0 时返回 NULLROUND结果也是 NULL前端显示成“暂无数据”即可。AVG(lr.progress)只对有学习记录的章节求平均没记录的章节不计入。再建一个定时任务每天凌晨把视图结果落到一张汇总表里避免每次查询都跑大 JOIN。-- 汇总表每天全量刷新 CREATE TABLE IF NOT EXISTS score_summary ( user_id BIGINT UNSIGNED NOT NULL, course_id BIGINT UNSIGNED NOT NULL, accuracy DECIMAL(5,2) DEFAULT NULL, avg_progress DECIMAL(5,2) DEFAULT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (user_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 用 REPLACE INTO 做全量覆盖简单粗暴但有效 REPLACE INTO score_summary (user_id, course_id, accuracy, avg_progress) SELECT user_id, course_id, accuracy, avg_progress FROM v_student_course_score;逻辑说明REPLACE INTO会先删后插适合每天全量刷新的场景。如果数据量大改成INSERT ... ON DUPLICATE KEY UPDATE只更新变化行。定时任务可以用 MySQL 的EVENT也可以用外部调度工具我一般用外部调度方便监控和重试。参数说明DECIMAL(5,2)表示总共 5 位、小数 2 位最大 999.99足够存百分比。updated_at用ON UPDATE CURRENT_TIMESTAMP自动记录刷新时间排查数据延迟时很有用。最后说个验证方法建完视图和汇总表后手动跑一遍SELECT * FROM v_student_course_score;和SELECT * FROM score_summary;对比两边数据是否一致。如果不一致大概率是 JOIN 条件写漏了user_id或course_id。我踩过最坑的一次是learning_record的 JOIN 忘了带user_id导致进度被算成全班平均排查了半天。所以每次改视图我都会先拿一个学生的数据手工算一遍再和视图结果对。这个习惯帮我省了很多后悔药。希望帮到你。本文还有配套的精品资源点击获取