database online test

ot.sql

DROP TABLE IF EXISTS `question_bank`; 
CREATE TABLE `question_bank` (
  `id` bigint(33) NOT NULL AUTO_INCREMENT,
  `satker_id` int(11) NOT NULL,
  `topic` varchar(255) NOT NULL,
  `level` int(1) DEFAULT '0' COMMENT 'tingkat kesulitan soal',
  `type` int(1) NOT NULL DEFAULT '1' COMMENT '1:multiple choice, 2:essay',
  `salt` varchar(255) NOT NULL,
  `question` text NOT NULL,
  `question_file` varchar(255) DEFAULT NULL,
  `options` text COMMENT 'json pilihan jawaban',
  `options_files` text COMMENT 'json file pilihan jawaban',
  `answer_key` varchar(255) COMMENT 'json kunci jawaban',
  `status` int(3) NOT NULL,
  `created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `question_package`;
CREATE TABLE `question_package` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `satker_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `question_ids` text NOT NULL,
  `status` int(3) NOT NULL,
  `created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `program_test`;
CREATE TABLE `program_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `satker_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `question_package` int(11) NOT NULL,
  `question_count` TEXT NOT NULL,
  `duration` int(11) NOT NULL,
  `question_random` int(1) NOT NULL DEFAULT '0' COMMENT '0:standard;1:random_question_by_topic_level;2:random_question_by_topic;2:random_question',
  `answer_random` int(1) NOT NULL DEFAULT '0',
  `result_show` tinyint(1) NOT NULL COMMENT 'tampilkan hasil ke peserta',
  `enrol_singleton` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'satu user yang dapat enrol',
  `enrol_auth` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'enrol with token?',
  `enrol_token` varchar(255) DEFAULT NULL COMMENT 'mass token if enrol_auth',
  `ip_allowed` text NOT NULL,
  `timezone` int(2) DEFAULT '7' COMMENT '7: WIB;8:WITA;9:WIT',
  `status` int(3) NOT NULL,
  `created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `program_test_participant`;
CREATE TABLE `program_test_participant` (
  `id` bigint(33) NOT NULL AUTO_INCREMENT,
  `program_test_id` int(11) NOT NULL,
  `participant_id` int(11) NOT NULL,
  `question_order` text,
  `answer` text,
  `value` decimal(5,2) DEFAULT NULL,
  `answer_validation` text,
  `feedback` text,
  `do_test` tinyint(1) DEFAULT '0',
  `time_start` datetime DEFAULT NULL,
  `time_elapsed` decimal(5,2) DEFAULT NULL,
  `enrol_token` varchar(255) DEFAULT NULL,
  `status` int(3) NOT NULL,
  `created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `program_test_participant_id` (`program_test_id`,`participant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;