学科和课程管理模块
约 1193 字大约 4 分钟
2025-08-11
概述
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 | 过滤激活状态的班级 |
约束条件
唯一约束
- subjects.code: 学科代码全局唯一
- courses.code: 课程代码全局唯一
- classes(course_id, code): 同一课程内班级代码唯一
检查约束
- courses.difficulty_level: 值必须在1-5之间
- 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