数据库设计实战：在线学习系统库表设计与SQL优化-开发者社区

简介：这份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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='答题记录表';

逻辑说明：user和course是基础实体，enrollment用联合唯一键防止重复选课，learning_record同样用联合唯一键保证一个用户对一个章节只有一条进度记录，更新时直接ON DUPLICATE KEY UPDATE即可。question的options用 JSON 存，是因为选项数量不固定，用 JSON 比拆子表更省事，但要注意 MySQL 5.7 以下不支持 JSON 类型。

参数说明：utf8mb4是为了支持中文和 emoji；InnoDB是为了事务和行锁；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 CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci，建表时也带上DEFAULT CHARSET=utf8mb4，连接串里加characterEncoding=utf8。已经建错的表用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 时返回 NULL，ROUND结果也是 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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 用 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，导致进度被算成全班平均，排查了半天。所以每次改视图，我都会先拿一个学生的数据手工算一遍，再和视图结果对。这个习惯帮我省了很多后悔药。希望帮到你。

本文还有配套的精品资源，点击获取