概述

subjects 和 courses 表构成了教学管理的核心模块，用于管理学科分类和课程信息。subjects 表管理学科分类，courses 表管理具体的课程，classes 表管理课程下的班级。

表设计

subjects 表 - 学科表

DDL 定义

CREATE TABLE IF NOT EXISTS public.subjects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段说明

字段名	数据类型	约束	默认值	描述
id	UUID	PRIMARY KEY	gen_random_uuid()	学科唯一标识
name	VARCHAR(100)	NOT NULL	-	学科名称（如：数学、物理）
code	VARCHAR(20)	NOT NULL, UNIQUE	-	学科代码（如：MATH、PHYS）
description	TEXT	NULLABLE	NULL	学科描述
is_active	BOOLEAN	NOT NULL	true	学科激活状态
created_at	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	记录最后更新时间

courses 表 - 课程表

DDL 定义

CREATE TABLE IF NOT EXISTS public.courses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subject_id UUID NOT NULL REFERENCES public.subjects(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    cover_image_url VARCHAR(255),
    difficulty_level INTEGER CHECK (difficulty_level BETWEEN 1 AND 5),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段说明

字段名	数据类型	约束	默认值	描述
id	UUID	PRIMARY KEY	gen_random_uuid()	课程唯一标识
subject_id	UUID	NOT NULL, FOREIGN KEY	-	所属学科ID，关联subjects表
name	VARCHAR(200)	NOT NULL	-	课程名称
code	VARCHAR(50)	NOT NULL, UNIQUE	-	课程代码
description	TEXT	NULLABLE	NULL	课程描述
cover_image_url	VARCHAR(255)	NULLABLE	NULL	课程封面图片URL
difficulty_level	INTEGER	CHECK (1-5)	NULL	难度等级：1(简单)-5(困难)
is_active	BOOLEAN	NOT NULL	true	课程激活状态
created_at	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	记录最后更新时间

classes 表 - 班级表

DDL 定义

CREATE TABLE IF NOT EXISTS public.classes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL,
    description TEXT,
    max_students INTEGER DEFAULT 30,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(course_id, code) -- 同一课程内班级代码唯一
);

字段说明

字段名	数据类型	约束	默认值	描述
id	UUID	PRIMARY KEY	gen_random_uuid()	班级唯一标识
course_id	UUID	NOT NULL, FOREIGN KEY	-	所属课程ID，关联courses表
name	VARCHAR(100)	NOT NULL	-	班级名称
code	VARCHAR(50)	NOT NULL	-	班级代码（在同一课程内唯一）
description	TEXT	NULLABLE	NULL	班级描述
max_students	INTEGER	NULLABLE	30	最大学生数量
is_active	BOOLEAN	NOT NULL	true	班级激活状态
created_at	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	记录最后更新时间

外键关系

1. courses.subject_id → subjects.id

REFERENCES public.subjects(id) ON DELETE CASCADE

• 关联表: subjects
• 关联字段: id
• 删除策略: CASCADE（级联删除）
• 说明: 当学科被删除时，其下所有课程也会被删除

2. classes.course_id → courses.id

REFERENCES public.courses(id) ON DELETE CASCADE

• 关联表: courses
• 关联字段: id
• 删除策略: CASCADE（级联删除）
• 说明: 当课程被删除时，其下所有班级也会被删除

索引

基础索引

-- courses 表索引
CREATE INDEX IF NOT EXISTS idx_courses_subject_id ON public.courses(subject_id);
CREATE INDEX IF NOT EXISTS idx_courses_active ON public.courses(is_active);
CREATE INDEX IF NOT EXISTS idx_courses_code ON public.courses(code);

-- classes 表索引
CREATE INDEX IF NOT EXISTS idx_classes_course_id ON public.classes(course_id);
CREATE INDEX IF NOT EXISTS idx_classes_active ON public.classes(is_active);

索引说明

索引名	表	字段	用途
idx_courses_subject_id	courses	subject_id	按学科查询课程
idx_courses_active	courses	is_active	过滤激活状态的课程
idx_courses_code	courses	code	按课程代码查询
idx_classes_course_id	classes	course_id	按课程查询班级
idx_classes_active	classes	is_active	过滤激活状态的班级

约束条件

唯一约束

1. subjects.code: 学科代码全局唯一
2. courses.code: 课程代码全局唯一
3. classes(course_id, code): 同一课程内班级代码唯一

检查约束

1. courses.difficulty_level: 值必须在1-5之间
2. classes.max_students: 正整数值

RLS策略

-- 学科访问策略
CREATE POLICY "已认证用户可以查看学科" ON public.subjects
    FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "管理员教师管理学科" ON public.subjects
    FOR ALL USING (get_user_role(auth.uid()) IN ('admin', 'teacher'));

-- 课程访问策略
CREATE POLICY "已认证用户可以查看课程" ON public.courses
    FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "管理员教师管理课程" ON public.courses  
    FOR ALL USING (get_user_role(auth.uid()) IN ('admin', 'teacher'));

-- 班级访问策略  
CREATE POLICY "班级访问控制" ON public.classes
    FOR SELECT USING (
        -- 管理员可以查看所有班级
        get_user_role(auth.uid()) = 'admin' OR
        -- 教师可以查看自己授课的班级
        EXISTS (
            SELECT 1 FROM public.teacher_course_assignments tca
            JOIN public.courses c ON c.id = tca.course_id
            WHERE tca.teacher_id = auth.uid() 
                AND c.id = classes.course_id
        )
    );

触发器

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

CREATE TRIGGER update_courses_updated_at 
    BEFORE UPDATE ON public.courses 
    FOR EACH ROW 
    EXECUTE PROCEDURE update_updated_at_column();

CREATE TRIGGER update_classes_updated_at 
    BEFORE UPDATE ON public.classes 
    FOR EACH ROW 
    EXECUTE PROCEDURE update_updated_at_column();

数据库函数

update_updated_at_column()

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

版权所有

版权归属：Evoliant

许可证：MIT