概述

选课授课模块管理学生选课和教师授课的关系，包含学生选课记录、学习进度跟踪以及教师课程分配。该系统支持灵活的选课管理和班级分配。

表设计

student_course_enrollments 表 - 学生选课表

DDL 定义

-- 创建选课状态枚举
CREATE TYPE enrollment_status AS ENUM ('enrolled', 'completed', 'dropped', 'suspended');

CREATE TABLE IF NOT EXISTS public.student_course_enrollments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    student_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
    class_id UUID REFERENCES public.classes(id) ON DELETE SET NULL, -- 可选的班级分配
    status enrollment_status NOT NULL DEFAULT 'enrolled',
    enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    progress DECIMAL(5,2) DEFAULT 0.00 CHECK (progress BETWEEN 0 AND 100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(student_id, course_id) -- 学生不能重复选择同一门课程
);

字段说明

字段名	数据类型	约束	默认值	描述
id	UUID	PRIMARY KEY	gen_random_uuid()	选课记录唯一标识
student_id	UUID	NOT NULL, FOREIGN KEY	-	学生ID，关联auth.users表
course_id	UUID	NOT NULL, FOREIGN KEY	-	课程ID，关联courses表
class_id	UUID	FOREIGN KEY	NULL	班级ID，关联classes表（可选）
status	enrollment_status	NOT NULL	'enrolled'	选课状态
enrolled_at	TIMESTAMPTZ	NOT NULL	NOW()	选课时间
completed_at	TIMESTAMPTZ	NULLABLE	NULL	完成课程时间
progress	DECIMAL(5,2)	CHECK (0-100)	0.00	学习进度百分比
created_at	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	记录最后更新时间

选课状态枚举

枚举值	中文名称	描述	业务含义
enrolled	已选课	学生正常选课状态	可以正常学习和参与课程活动
completed	已完成	学生已完成课程	已获得学分，课程学习结束
dropped	已退课	学生主动退课	不再参与课程学习
suspended	已暂停	课程学习被暂停	临时无法继续学习（如请假）

teacher_course_assignments 表 - 教师授课表

DDL 定义

CREATE TABLE IF NOT EXISTS public.teacher_course_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    teacher_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
    class_id UUID REFERENCES public.classes(id) ON DELETE SET NULL, -- 可选的班级分配
    is_primary BOOLEAN NOT NULL DEFAULT false, -- 是否主讲教师
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(teacher_id, course_id, class_id) -- 防止重复分配
);

字段说明

字段名	数据类型	约束	默认值	描述
id	UUID	PRIMARY KEY	gen_random_uuid()	分配记录唯一标识
teacher_id	UUID	NOT NULL, FOREIGN KEY	-	教师ID，关联auth.users表
course_id	UUID	NOT NULL, FOREIGN KEY	-	课程ID，关联courses表
class_id	UUID	FOREIGN KEY	NULL	班级ID，关联classes表（可选）
is_primary	BOOLEAN	NOT NULL	false	是否为主讲教师
assigned_at	TIMESTAMPTZ	NOT NULL	NOW()	分配时间
created_at	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间

外键关系

student_course_enrollments 表的外键关系

-- 学生关联
student_id REFERENCES auth.users(id) ON DELETE CASCADE

-- 课程关联
course_id REFERENCES public.courses(id) ON DELETE CASCADE

-- 班级关联（可选）
class_id REFERENCES public.classes(id) ON DELETE SET NULL

teacher_course_assignments 表的外键关系

-- 教师关联
teacher_id REFERENCES auth.users(id) ON DELETE CASCADE

-- 课程关联
course_id REFERENCES public.courses(id) ON DELETE CASCADE

-- 班级关联（可选）
class_id REFERENCES public.classes(id) ON DELETE SET NULL

删除策略说明

关联表	删除策略	说明
auth.users	CASCADE	用户删除时，相关选课/授课记录也被删除
courses	CASCADE	课程删除时，相关选课/授课记录也被删除
classes	SET NULL	班级删除时，将关联字段设为NULL但保留记录

索引

基础索引

-- student_course_enrollments 表索引
CREATE INDEX IF NOT EXISTS idx_enrollments_student_id ON public.student_course_enrollments(student_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_course_id ON public.student_course_enrollments(course_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_class_id ON public.student_course_enrollments(class_id);
CREATE INDEX IF NOT EXISTS idx_enrollments_status ON public.student_course_enrollments(status);

-- teacher_course_assignments 表索引
CREATE INDEX IF NOT EXISTS idx_teacher_assignments_teacher_id ON public.teacher_course_assignments(teacher_id);
CREATE INDEX IF NOT EXISTS idx_teacher_assignments_course_id ON public.teacher_course_assignments(course_id);
CREATE INDEX IF NOT EXISTS idx_teacher_assignments_class_id ON public.teacher_course_assignments(class_id);

复合索引

-- 复合索引优化常用查询
CREATE INDEX IF NOT EXISTS idx_enrollments_student_status ON public.student_course_enrollments(student_id, status);
CREATE INDEX IF NOT EXISTS idx_teacher_assignments_teacher_course ON public.teacher_course_assignments(teacher_id, course_id);

约束条件

唯一约束

1. student_course_enrollments(student_id, course_id): 学生不能重复选择同一门课程
2. teacher_course_assignments(teacher_id, course_id, class_id): 防止教师重复分配到同一课程的同一班级

检查约束

1. progress: 进度值必须在0-100之间

RLS策略

-- 学生选课访问控制
CREATE POLICY "学生查看自己的选课" ON public.student_course_enrollments
    FOR SELECT USING (
        -- 学生可以查看自己的选课记录
        student_id = auth.uid() OR
        -- 管理员可以查看所有选课记录
        get_user_role(auth.uid()) = 'admin'
    );

CREATE POLICY "学生管理自己的选课" ON public.student_course_enrollments
    FOR ALL USING (
        student_id = auth.uid() OR
        get_user_role(auth.uid()) = 'admin'
    );

-- 教师授课记录访问控制
CREATE POLICY "查看教师授课安排" ON public.teacher_course_assignments
    FOR SELECT USING (
        -- 管理员可以查看所有授课安排
        get_user_role(auth.uid()) = 'admin' OR
        -- 教师可以查看自己的授课安排
        teacher_id = auth.uid()
    );

CREATE POLICY "管理员管理教师授课" ON public.teacher_course_assignments
    FOR ALL USING (get_user_role(auth.uid()) = 'admin');

触发器

-- 自动更新时间戳触发器
CREATE TRIGGER update_enrollments_updated_at 
    BEFORE UPDATE ON public.student_course_enrollments 
    FOR EACH ROW 
    EXECUTE PROCEDURE update_updated_at_column();

-- 班级容量检查触发器
CREATE TRIGGER check_class_capacity_trigger
    BEFORE INSERT OR UPDATE ON public.student_course_enrollments
    FOR EACH ROW
    WHEN (NEW.class_id IS NOT NULL AND NEW.status = 'enrolled')
    EXECUTE FUNCTION check_class_capacity();

-- 用户角色验证触发器
CREATE TRIGGER validate_student_role_trigger
    BEFORE INSERT OR UPDATE ON public.student_course_enrollments
    FOR EACH ROW
    EXECUTE FUNCTION validate_user_role_for_enrollment();

CREATE TRIGGER validate_teacher_role_trigger
    BEFORE INSERT OR UPDATE ON public.teacher_course_assignments
    FOR EACH ROW
    EXECUTE FUNCTION validate_teacher_role_for_assignment();

数据库函数

update_updated_at_column()

• 用途: 自动更新记录的 updated_at 字段
• 触发时机: BEFORE UPDATE
• 返回类型: TRIGGER
• 应用表: student_course_enrollments

check_class_capacity()

• 用途: 检查班级容量限制，防止超员
• 触发时机: BEFORE INSERT OR UPDATE
• 返回类型: TRIGGER
• 业务逻辑: 检查班级当前学生数是否超过最大容量
• 应用表: student_course_enrollments

validate_user_role_for_enrollment()

• 用途: 验证用户角色，确保只有学生才能选课
• 触发时机: BEFORE INSERT OR UPDATE
• 返回类型: TRIGGER
• 验证规则: 检查用户是否为激活的学生用户
• 应用表: student_course_enrollments

validate_teacher_role_for_assignment()

• 用途: 验证用户角色，确保只有教师才能被分配授课
• 触发时机: BEFORE INSERT OR UPDATE
• 返回类型: TRIGGER
• 验证规则: 检查用户是否为激活的教师用户
• 应用表: teacher_course_assignments

版权所有

版权归属：Evoliant

许可证：MIT