-- 学员评议模块(MySQL 5.7+/8.0) -- 执行前提:class_subject、class、class_student、user 等基础业务表已经存在。 -- 设计说明:一个评价任务对应一节具体课程;evaluation_record 在发起时固化该班级的目标学员。 create table if not exists evaluation_task ( id varchar(32) not null comment '评价任务ID(雪花算法字符串)', subject_id int not null comment '关联课次 class_subject.id', status varchar(16) not null comment 'PENDING/COMPLETED/EXPIRED/REVOKED', active_subject_id int generated always as ( case when status = 'REVOKED' then null else subject_id end ) stored comment '未撤回任务的课次ID,用于并发防重;撤回后为NULL', deadline datetime(3) not null comment '评价截止时间', created_by varchar(64) not null comment '发起管理员 user.id', created_time datetime(3) not null, update_time datetime(3) not null, revoked_at datetime(3) null comment '撤回时间', revoked_by varchar(64) null comment '撤回管理员 user.id', primary key (id), unique key uk_evaluation_task_active_subject (active_subject_id), key idx_evaluation_task_subject (subject_id), key idx_evaluation_task_status_deadline (status, deadline), key idx_evaluation_task_created (created_time), constraint fk_evaluation_task_subject foreign key (subject_id) references class_subject (id), constraint fk_evaluation_task_creator foreign key (created_by) references user (id), constraint fk_evaluation_task_revoker foreign key (revoked_by) references user (id) ) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci comment='学员评议任务'; create table if not exists evaluation_record ( id varchar(32) not null comment '学员评价记录ID', task_id varchar(32) not null comment '评价任务ID', student_id varchar(64) not null comment '目标学员 user.id;发起时固化且不匿名', status varchar(16) not null default 'PENDING' comment 'PENDING/SUBMITTED', teaching_attitude varchar(8) null comment '教学态度:GOOD/FAIR/OK/POOR', teaching_content varchar(8) null comment '教学内容:GOOD/FAIR/OK/POOR', teaching_method varchar(8) null comment '教学方法:GOOD/FAIR/OK/POOR', teaching_means varchar(8) null comment '教学手段:GOOD/FAIR/OK/POOR', demonstration varchar(8) null comment '讲解示范:GOOD/FAIR/OK/POOR', guidance varchar(8) null comment '巡回指导:GOOD/FAIR/OK/POOR', class_hour_completion varchar(8) null comment '课时完成情况:GOOD/FAIR/OK/POOR', comment varchar(1000) null comment '意见与建议', submitted_at datetime(3) null comment '提交时间', created_time datetime(3) not null, update_time datetime(3) not null, primary key (id), unique key uk_evaluation_record_task_student (task_id, student_id), key idx_evaluation_record_student (student_id, status, task_id), key idx_evaluation_record_task_status (task_id, status), constraint fk_evaluation_record_task foreign key (task_id) references evaluation_task (id), constraint fk_evaluation_record_student foreign key (student_id) references user (id) ) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci comment='学员实名评价记录及任务目标快照';