关系型数据库SQL多对多关系设计：如何关联学生与课程会话？

处理lesson_session和student之间的多对多关系，核心方案是创建一张中间联结表（也叫关联表、junction table），这是关系型数据库处理多对多关联的标准做法，能完全避免数据重复。

步骤1：创建中间关联表 #

这张表仅存储lesson_session和student的主键，用来建立两者的关联关系。可以命名为session_attendance（直观体现考勤记录的含义）：

CREATE TABLE IF NOT EXISTS session_attendance (
    lesson_session_id INTEGER,
    student_id INTEGER,
    -- 可选：添加考勤状态（比如是否到场、迟到等）
    attendance_status TEXT DEFAULT 'present',
    -- 设置复合主键，确保同一名学生不会重复关联到同一个会话
    PRIMARY KEY (lesson_session_id, student_id),
    -- 外键约束，保证关联的记录真实存在
    FOREIGN KEY (lesson_session_id) REFERENCES lesson_session(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE
);

• 复合主键(lesson_session_id, student_id)能防止同一学生重复报名同一场会话，彻底杜绝冗余数据。
• ON DELETE CASCADE是可选配置：如果某场会话被删除，关联的考勤记录会自动删除；同理学生账号删除时，其所有考勤记录也会被清理，可根据业务需求调整。

步骤2：使用示例 #

插入考勤记录

当学生参加某场课程会话时，只需在中间表添加对应记录：

-- 学生ID 123参加会话ID 456
INSERT INTO session_attendance (lesson_session_id, student_id) VALUES (456, 123);
-- 多名学生参加同一场会话，添加多条记录即可
INSERT INTO session_attendance (lesson_session_id, student_id) VALUES (456, 124), (456, 125);

查询某场会话的所有学生

通过JOIN关联表，获取会话对应的学生详情：

SELECT s.id, s.name, sa.attendance_status
FROM lesson_session ls
JOIN session_attendance sa ON ls.id = sa.lesson_session_id
JOIN student s ON sa.student_id = s.id
WHERE ls.id = 456;

查询某名学生参加的所有会话

SELECT ls.id, ls.starttime, ls.endtime, t.name AS teacher_name
FROM student s
JOIN session_attendance sa ON s.id = sa.student_id
JOIN lesson_session ls ON sa.lesson_session_id = ls.id
JOIN teacher t ON ls.teacher_id = t.id
WHERE s.id = 123;

补充优化建议 #

你的现有表结构中lesson表和lesson_session的关联缺失，建议给lesson_session添加外键关联lesson.id，明确每场会话对应的课程：

ALTER TABLE lesson_session ADD COLUMN lesson_id INTEGER;
ALTER TABLE lesson_session ADD FOREIGN KEY (lesson_id) REFERENCES lesson(id);

内容的提问来源于stack exchange，提问作者Matt Conway