简易版课程中心、考试中心数据库设计sql语句

CREATE TABLE `test_cat` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '考试分类名字',
  `p_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父类id',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0=正常1=隐藏',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '操作人ID',
  `sort` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：考试分类表';

CREATE TABLE `test_group_relation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '项目的ID',
  `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题',
  `sort` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `relation_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '0=项目，1=试卷',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：项目-标题关系表';

CREATE TABLE `test_paper` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `paper_name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '试卷名字',
  `cat_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '考试分类id',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0=正常1=停用',
  `use_num` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '被使用的次数',
  `total_score` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '试卷总分数',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '操作人ID',
  `creator_id` int(11) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：试卷表';

CREATE TABLE `test_project` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `duration` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '考试时长',
  `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '考试分类id',
  `pass_mark` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '及格线',
  `total_score` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '满分多少',
  `start_at` timestamp NULL DEFAULT NULL COMMENT '考试开始时间',
  `end_at` timestamp NULL DEFAULT NULL COMMENT '考试结束时间',
  `test_num_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '考试次数类型：0=不限制1=限制',
  `test_num` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '考试次数',
  `test_scope_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '考试范围类型：0=公开1=指定用户2=指定角色',
  `test_scope_ids` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '考试范围指定ID',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '操作人ID',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：考试项目表';

CREATE TABLE `test_record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '项目id',
  `userinfo_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '考试人user_info_ID',
  `score` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '考试分数',
  `param` text COLLATE utf8mb4_unicode_ci COMMENT '考试答案列表json',
  `start_at` timestamp NULL DEFAULT NULL COMMENT '考试开始时间',
  `end_at` timestamp NULL DEFAULT NULL COMMENT '考试结束时间',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：考试记录表';

CREATE TABLE `test_records` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `test_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `test_id` bigint(20) unsigned DEFAULT NULL COMMENT '考试中心id',
  `describe` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '记录描述',
  `type` tinyint(4) NOT NULL COMMENT '记录类型，物流，支付等',
  `subtype` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '子类型',
  `state` tinyint(3) unsigned DEFAULT NULL COMMENT '可用标志状态',
  `param` text COLLATE utf8mb4_unicode_ci COMMENT '记录参数',
  `editor_id` int(11) NOT NULL COMMENT '编辑人ID',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `test_id` (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：考试操作记录表';

CREATE TABLE `test_relation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `target_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '试卷-或者-项目的ID',
  `subject_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '题库ID',
  `relation_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '关系类型0=试卷关联题目1=项目关联题目',
  `score` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '分数',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `target_id` (`target_id`),
  KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=337 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：试卷或项目-题目关联表';

CREATE TABLE `test_subject` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sub_name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '题目标题',
  `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '考试分类id',
  `remark` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '答案解析',
  `answer` char(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '正确答案',
  `param` text COLLATE utf8mb4_unicode_ci COMMENT '选项Json',
  `type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0=单选1=判断题2=多选题3=其他',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0=发布1=删除2=停用',
  `use_num` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '考试次数',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '细胞ID',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '操作人ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试：考试题库表';