all-project/docs/设计文档/database/03-logistics-module.sql

/*
 后勤管理模块数据库结构
 
 业务域：后勤管理相关表
 包含表：hq_hall_info, hq_hall_reservation, hq_sat_answer, hq_sat_category,
        hq_sat_detail, hq_sat_option, hq_sat_question, hq_sat_record,
        hq_sat_survey, hq_sat_survey_question
 
 模块说明：
 - 报告厅预约管理：hq_hall_info, hq_hall_reservation
 - 满意度调查系统：hq_sat_* 相关表
 
 创建时间：2025-01-08
 维护人员：项目开发团队
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for hq_hall_info
-- ----------------------------
DROP TABLE IF EXISTS `hq_hall_info`;
CREATE TABLE `hq_hall_info` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `hall_name` varchar(100) NOT NULL COMMENT '报告厅名称',
  `hall_image` text COMMENT '报告厅图片url',
  `hall_status` char(1) NOT NULL DEFAULT '1' COMMENT '报告厅状态：0-不可用，1-正常',
  `hall_description` text COMMENT '报告厅描述',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_hall_status` (`hall_status`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告厅信息表';

-- ----------------------------
-- Table structure for hq_hall_reservation
-- ----------------------------
DROP TABLE IF EXISTS `hq_hall_reservation`;
CREATE TABLE `hq_hall_reservation` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `hall_id` bigint(20) NOT NULL COMMENT '报告厅ID，关联hq_hall_info.id',
  `hall_name` varchar(100) NOT NULL COMMENT '报告厅名称',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `reservation_purpose` varchar(200) NOT NULL COMMENT '预约用途',
  `applicant_id` bigint(20) NOT NULL COMMENT '申请人id，关联sys_user.id',
  `applicant_name` varchar(50) NOT NULL COMMENT '申请人姓名',
  `applicant_phone` varchar(20) DEFAULT NULL COMMENT '申请人电话',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '预约状态：0-待审核，1-预约成功，2-预约失败',
  `audit_by` bigint(20) DEFAULT NULL COMMENT '审核人id，关联sys_user.id',
  `audit_name` varchar(50) DEFAULT NULL COMMENT '审核人姓名',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `audit_remark` varchar(500) DEFAULT NULL COMMENT '审核备注',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_hall_id` (`hall_id`),
  KEY `idx_status` (`status`),
  KEY `idx_applicant_id` (`applicant_id`),
  KEY `idx_time_range` (`start_time`,`end_time`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报告厅预约表';

-- ----------------------------
-- Table structure for hq_sat_answer
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_answer`;
CREATE TABLE `hq_sat_answer` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `record_id` bigint(20) NOT NULL COMMENT '答题记录ID，关联hq_sat_record.id',
  `question_id` bigint(20) NOT NULL COMMENT '题目ID，关联hq_sat_question.id',
  `question_type` char(1) NOT NULL COMMENT '题目类型（1单选题 2多选题 3问答题）',
  `option_id` bigint(20) DEFAULT NULL COMMENT '选项ID，关联hq_sat_option.id（单选题、多选题用，问答题为NULL）',
  `option_value` varchar(50) DEFAULT NULL COMMENT '选项值（A、B、C、D等）',
  `option_text` varchar(500) DEFAULT NULL COMMENT '选项文本内容',
  `answer_value` text COMMENT '答案值（单选题存选项值，多选题存逗号分隔选项值，问答题存文本内容）',
  `answer_score` int(11) DEFAULT '0' COMMENT '该答案得分',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_record_id` (`record_id`),
  KEY `idx_question_id` (`question_id`),
  KEY `idx_question_type` (`question_type`),
  KEY `idx_option_id` (`option_id`),
  KEY `idx_record_question` (`record_id`,`question_id`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='答题答案表';

-- ----------------------------
-- Table structure for hq_sat_category
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_category`;
CREATE TABLE `hq_sat_category` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `category_code` varchar(50) NOT NULL COMMENT '分类编码',
  `category_name` varchar(100) NOT NULL COMMENT '分类名称',
  `category_desc` varchar(500) DEFAULT NULL COMMENT '分类描述',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序',
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_category_code` (`category_code`,`tenant_id`,`deleted`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_status` (`status`),
  KEY `idx_sort_order` (`sort_order`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目分类表';

-- ----------------------------
-- Table structure for hq_sat_detail
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_detail`;
CREATE TABLE `hq_sat_detail` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `record_id` bigint(20) NOT NULL COMMENT '答题记录ID，关联hq_sat_record.id',
  `question_id` bigint(20) NOT NULL COMMENT '题目ID，关联hq_sat_question.id',
  `answer_score` int(11) DEFAULT '0' COMMENT '答案分数',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_record_question` (`record_id`,`question_id`,`deleted`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_record_id` (`record_id`),
  KEY `idx_question_id` (`question_id`),
  KEY `idx_answer_score` (`answer_score`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='答题详情表';

-- ----------------------------
-- Table structure for hq_sat_option
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_option`;
CREATE TABLE `hq_sat_option` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `option_text` varchar(50) NOT NULL COMMENT '选项文本',
  `option_value` int(11) NOT NULL COMMENT '选项分值',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序',
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_status` (`status`),
  KEY `idx_sort_order` (`sort_order`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评分选项表';

-- ----------------------------
-- Table structure for hq_sat_question
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_question`;
CREATE TABLE `hq_sat_question` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `question_title` varchar(200) NOT NULL COMMENT '题目标题',
  `question_content` text COMMENT '题目内容（支持富文本）',
  `category_id` bigint(20) NOT NULL COMMENT '所属分类ID，关联hq_sat_category.id',
  `question_type` char(1) DEFAULT '1' COMMENT '题目类型（1单选题 2多选题 3问答题）',
  `is_required` tinyint(1) DEFAULT '1' COMMENT '是否必填（0否 1是）',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序',
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_status` (`status`),
  KEY `idx_question_type` (`question_type`),
  KEY `idx_sort_order` (`sort_order`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题库表';

-- ----------------------------
-- Table structure for hq_sat_record
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_record`;
CREATE TABLE `hq_sat_record` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `survey_id` bigint(20) NOT NULL COMMENT '问卷ID，关联hq_sat_survey.id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '答题用户ID，关联sys_user.id（匿名时为NULL）',
  `user_type` char(1) NOT NULL COMMENT '用户类型（1学生 2教职工 3超级管理员）',
  `user_dept` varchar(100) DEFAULT NULL COMMENT '用户部门（匿名时为NULL）',
  `submit_time` datetime NOT NULL COMMENT '提交时间',
  `total_score` decimal(10,2) DEFAULT '0.00' COMMENT '总分',
  `avg_score` decimal(5,2) DEFAULT '0.00' COMMENT '平均分',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_survey_id` (`survey_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_type` (`user_type`),
  KEY `idx_submit_time` (`submit_time`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='答题记录表';

-- ----------------------------
-- Table structure for hq_sat_survey
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_survey`;
CREATE TABLE `hq_sat_survey` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `survey_code` varchar(50) NOT NULL COMMENT '问卷编号',
  `survey_title` varchar(200) NOT NULL COMMENT '问卷标题',
  `survey_desc` varchar(1000) DEFAULT NULL COMMENT '问卷描述',
  `survey_instruction` text COMMENT '填写说明',
  `target_group` varchar(100) NOT NULL COMMENT '目标群体（1学生 2教职工 3全体）',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `max_submit_times` int(11) DEFAULT '1' COMMENT '最大提交次数（0不限制）',
  `is_anonymous` tinyint(1) DEFAULT '0' COMMENT '是否匿名（0否 1是）',
  `is_published` tinyint(1) DEFAULT '0' COMMENT '是否发布（0否 1是）',
  `status` char(1) DEFAULT '0' COMMENT '状态（0草稿 1进行中 2已结束）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_survey_code` (`survey_code`,`tenant_id`,`deleted`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_target_group` (`target_group`),
  KEY `idx_time_range` (`start_time`,`end_time`),
  KEY `idx_status` (`status`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问卷表';

-- ----------------------------
-- Table structure for hq_sat_survey_question
-- ----------------------------
DROP TABLE IF EXISTS `hq_sat_survey_question`;
CREATE TABLE `hq_sat_survey_question` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `tenant_id` varchar(20) NOT NULL DEFAULT '000000' COMMENT '租户编号',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
  `survey_id` bigint(20) NOT NULL COMMENT '问卷ID，关联hq_sat_survey.id',
  `question_id` bigint(20) NOT NULL COMMENT '题目ID，关联hq_sat_question.id',
  `sort_order` int(11) DEFAULT '0' COMMENT '题目顺序',
  `weight` decimal(5,2) DEFAULT '1.00' COMMENT '题目权重',
  `is_required` tinyint(1) DEFAULT '1' COMMENT '是否必填（0否 1是）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_survey_question` (`survey_id`,`question_id`,`deleted`),
  KEY `idx_tenant_id` (`tenant_id`),
  KEY `idx_survey_id` (`survey_id`),
  KEY `idx_question_id` (`question_id`),
  KEY `idx_sort_order` (`sort_order`),
  KEY `idx_deleted` (`deleted`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问卷题目关联表';

SET FOREIGN_KEY_CHECKS = 1;