题库模块

约 1695 字大约 6 分钟

2025-08-11

概述

题库模块用于管理教学题目的创建、分类、存储和检索。系统支持多种题型（单选、多选、判断、主观题），提供灵活的题目管理功能，支持知识点关联和自动评分机制。

表设计

题库表 (question_banks)

CREATE TABLE IF NOT EXISTS public.question_banks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    is_public BOOLEAN NOT NULL DEFAULT false,
    created_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段名	数据类型	约束	默认值	描述
`id`	UUID	PRIMARY KEY	gen_random_uuid()	题库唯一标识
`course_id`	UUID	NOT NULL, FOREIGN KEY	-	所属课程ID
`name`	VARCHAR(200)	NOT NULL	-	题库名称
`description`	TEXT	NULLABLE	NULL	题库描述
`is_public`	BOOLEAN	NOT NULL	false	是否为公开题库
`created_by`	UUID	NOT NULL, FOREIGN KEY	-	创建者ID
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	记录最后更新时间

题目表 (questions)

CREATE TYPE question_type AS ENUM ('single_choice', 'multiple_choice', 'true_false', 'subjective');
CREATE TYPE difficulty_level AS ENUM ('easy', 'medium', 'hard');

CREATE TABLE IF NOT EXISTS public.questions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question_bank_id UUID NOT NULL REFERENCES public.question_banks(id) ON DELETE CASCADE,
    type question_type NOT NULL,
    title VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    options JSONB,
    correct_answers JSONB NOT NULL,
    explanation TEXT,
    difficulty difficulty_level NOT NULL DEFAULT 'medium',
    points INTEGER NOT NULL DEFAULT 1,
    estimated_time INTEGER,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段名	数据类型	约束	默认值	描述
`id`	UUID	PRIMARY KEY	gen_random_uuid()	题目唯一标识
`question_bank_id`	UUID	NOT NULL, FOREIGN KEY	-	所属题库ID
`type`	question_type	NOT NULL	-	题目类型
`title`	VARCHAR(500)	NOT NULL	-	题目标题
`content`	TEXT	NOT NULL	-	题目内容
`options`	JSONB	NULLABLE	NULL	选择题选项
`correct_answers`	JSONB	NOT NULL	-	正确答案
`explanation`	TEXT	NULLABLE	NULL	答案解释
`difficulty`	difficulty_level	NOT NULL	'medium'	难度等级
`points`	INTEGER	NOT NULL	1	题目分值
`estimated_time`	INTEGER	NULLABLE	NULL	预计答题时间（秒）
`is_active`	BOOLEAN	NOT NULL	true	题目激活状态
`created_by`	UUID	NOT NULL, FOREIGN KEY	-	创建者ID
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	记录最后更新时间

题目知识点关联表 (question_knowledge_points)

CREATE TABLE IF NOT EXISTS public.question_knowledge_points (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question_id UUID NOT NULL REFERENCES public.questions(id) ON DELETE CASCADE,
    knowledge_point_id UUID NOT NULL REFERENCES public.knowledge_points(id) ON DELETE CASCADE,
    relevance_weight DECIMAL(3,2) DEFAULT 1.0 CHECK (relevance_weight BETWEEN 0 AND 1),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(question_id, knowledge_point_id)
);

字段名	数据类型	约束	默认值	描述
`id`	UUID	PRIMARY KEY	gen_random_uuid()	关联记录唯一标识
`question_id`	UUID	NOT NULL, FOREIGN KEY	-	题目ID
`knowledge_point_id`	UUID	NOT NULL, FOREIGN KEY	-	知识点ID
`relevance_weight`	DECIMAL(3,2)	CHECK(0-1)	1.0	关联度权重
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	记录创建时间

题目类型和难度枚举

题目类型 (question_type)

枚举值	中文名称	描述
`single_choice`	单选题	从多个选项中选择一个正确答案
`multiple_choice`	多选题	从多个选项中选择多个正确答案
`true_false`	判断题	判断对错
`subjective`	主观题	需要文字回答的题目

难度等级 (difficulty_level)

枚举值	中文名称	描述
`easy`	简单	基础概念题目
`medium`	中等	需要理解和应用的题目
`hard`	困难	需要综合分析的题目

外键关系

question_banks 表外键关系

REFERENCES public.courses(id) ON DELETE CASCADE
REFERENCES auth.users(id) ON DELETE CASCADE

课程关联: 题库属于特定课程，课程删除时题库也被删除
创建者关联: 记录题库创建者，创建者删除时题库也被删除

questions 表外键关系

REFERENCES public.question_banks(id) ON DELETE CASCADE
REFERENCES auth.users(id) ON DELETE CASCADE

题库关联: 题目属于特定题库，题库删除时题目也被删除
创建者关联: 记录题目创建者，创建者删除时题目也被删除

question_knowledge_points 表外键关系

REFERENCES public.questions(id) ON DELETE CASCADE
REFERENCES public.knowledge_points(id) ON DELETE CASCADE

题目关联: 题目删除时关联记录也被删除
知识点关联: 知识点删除时关联记录也被删除

索引

-- 题库表索引
CREATE INDEX IF NOT EXISTS idx_question_banks_course_id ON public.question_banks(course_id);
CREATE INDEX IF NOT EXISTS idx_question_banks_created_by ON public.question_banks(created_by);
CREATE INDEX IF NOT EXISTS idx_question_banks_public ON public.question_banks(is_public);

-- 题目表索引
CREATE INDEX IF NOT EXISTS idx_questions_bank_id ON public.questions(question_bank_id);
CREATE INDEX IF NOT EXISTS idx_questions_type ON public.questions(type);
CREATE INDEX IF NOT EXISTS idx_questions_difficulty ON public.questions(difficulty);
CREATE INDEX IF NOT EXISTS idx_questions_active ON public.questions(is_active);
CREATE INDEX IF NOT EXISTS idx_questions_created_by ON public.questions(created_by);

-- 关联表索引
CREATE INDEX IF NOT EXISTS idx_question_knowledge_question ON public.question_knowledge_points(question_id);
CREATE INDEX IF NOT EXISTS idx_question_knowledge_point ON public.question_knowledge_points(knowledge_point_id);

-- JSONB 索引
CREATE INDEX IF NOT EXISTS idx_questions_options_gin ON public.questions USING GIN (options);
CREATE INDEX IF NOT EXISTS idx_questions_answers_gin ON public.questions USING GIN (correct_answers);

-- 全文搜索索引
CREATE INDEX IF NOT EXISTS idx_questions_content_search ON public.questions USING GIN (to_tsvector('simple', title || ' ' || content));

RLS策略

-- 题库访问控制
CREATE POLICY "查看题库" ON public.question_banks
    FOR SELECT USING (
        -- 管理员可以查看所有题库
        get_user_role(auth.uid()) = 'admin' OR
        -- 教师可以查看自己创建的题库
        created_by = auth.uid() OR
        -- 学生可以查看公开题库或已选修课程的题库
        (is_public = true) OR
        EXISTS (
            SELECT 1 FROM public.student_course_enrollments sce
            WHERE sce.student_id = auth.uid() 
                AND sce.course_id = question_banks.course_id
                AND sce.status = 'enrolled'
        )
    );

CREATE POLICY "管理题库" ON public.question_banks
    FOR ALL USING (
        -- 管理员可以管理所有题库
        get_user_role(auth.uid()) = 'admin' OR
        -- 教师可以管理自己创建的题库
        created_by = auth.uid()
    );

-- 题目访问控制
CREATE POLICY "查看题目" ON public.questions
    FOR SELECT USING (
        -- 管理员可以查看所有题目
        get_user_role(auth.uid()) = 'admin' OR
        -- 教师可以查看自己创建的题目
        created_by = auth.uid() OR
        -- 学生可以通过题库权限查看题目
        EXISTS (
            SELECT 1 FROM public.question_banks qb
            LEFT JOIN public.student_course_enrollments sce ON sce.course_id = qb.course_id
            WHERE qb.id = questions.question_bank_id
                AND (
                    qb.is_public = true OR
                    (sce.student_id = auth.uid() AND sce.status = 'enrolled')
                )
        )
    );

CREATE POLICY "管理题目" ON public.questions
    FOR ALL USING (
        -- 管理员可以管理所有
        get_user_role(auth.uid()) = 'admin' OR
        -- 教师可以管理自己创建的题目
        created_by = auth.uid()
    );

-- 题目知识点关联访问控制
CREATE POLICY "查看题目知识点关联" ON public.question_knowledge_points
    FOR SELECT USING (get_user_role(auth.uid()) IN ('admin', 'teacher'));

CREATE POLICY "管理题目知识点关联" ON public.question_knowledge_points
    FOR ALL USING (get_user_role(auth.uid()) IN ('admin', 'teacher'));

触发器

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

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

-- 题目数据验证触发器
CREATE TRIGGER validate_question_data_trigger
    BEFORE INSERT OR UPDATE ON public.questions
    FOR EACH ROW
    EXECUTE FUNCTION validate_question_data();

-- 题目创建者验证触发器
CREATE TRIGGER validate_question_creator_trigger
    BEFORE INSERT ON public.questions
    FOR EACH ROW
    EXECUTE FUNCTION validate_question_creator();

CREATE TRIGGER validate_question_bank_creator_trigger
    BEFORE INSERT ON public.question_banks
    FOR EACH ROW
    EXECUTE FUNCTION validate_question_creator();

数据库函数

update_updated_at_column()

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

validate_question_data()

用途: 验证题目数据的完整性和正确性
触发时机: BEFORE INSERT OR UPDATE
返回类型: TRIGGER
验证规则:
- 选择题必须有选项
- 答案必须在选项范围内
- 判断题答案必须为true或false
- 分值必须为正数
应用表: questions

validate_question_creator()

用途: 验证题目/题库创建者权限
触发时机: BEFORE INSERT
返回类型: TRIGGER
验证规则: 只有激活的教师或管理员才能创建题目/题库
应用表: questions, question_banks

版权所有

版权归属：Evoliant

许可证：MIT