How to design MySQL table structure to support online examination system?

How to design the MySQL table structure to support the online examination system?

With the rapid development of the Internet, more and more educational institutions and companies have begun to use online examination systems to conduct examinations. An efficient and reliable online examination system is inseparable from a reasonable database design. This article will introduce how to design the MySQL table structure to support the online examination system and provide specific code examples.

In order to support the functions of the online examination system, we need to design the following tables: user table, examination table, question table, answer record table and score table. The design of each table will be introduced below.

1. User table
   The user table is mainly used to store user information of the examination system, including user name, password, name, email, etc. Users can be divided into different roles, such as students and teachers. The table structure is as follows:

CREATE TABLE `user` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `role` ENUM('student', 'teacher') NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Exam table
   The exam table is used to store exam information, including exam name, exam time, exam duration, etc. Each exam can contain multiple questions. The table structure is as follows:

CREATE TABLE `exam` (
  `exam_id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_name` VARCHAR(255) NOT NULL,
  `exam_time` DATETIME NOT NULL,
  `duration` INT(11) NOT NULL,
  PRIMARY KEY (`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Question table
   The question table is used to store question information for each exam, including question content, answer options, correct answers, etc. The table structure is as follows:

CREATE TABLE `question` (
  `question_id` INT(11) NOT NULL AUTO_INCREMENT,
  `exam_id` INT(11) NOT NULL,
  `content` TEXT NOT NULL,
  `options` TEXT NOT NULL,
  `answer` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`question_id`),
  FOREIGN KEY (`exam_id`) REFERENCES `exam`(`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Answer record table
   The answer record table is used to store the user's answer records, including user ID, question ID, user-selected answers, answer time, etc. The table structure is as follows:

CREATE TABLE `answer_record` (
  `record_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `question_id` INT(11) NOT NULL,
  `selected_answer` VARCHAR(255) NOT NULL,
  `answer_time` DATETIME NOT NULL,
  PRIMARY KEY (`record_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`),
  FOREIGN KEY (`question_id`) REFERENCES `question`(`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5. Score table
   The score table is used to store the user's test scores, including user ID, test ID, scores, etc. The table structure is as follows:

CREATE TABLE `score` (
  `score_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `exam_id` INT(11) NOT NULL,
  `score` INT(11) NOT NULL,
  PRIMARY KEY (`score_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`),
  FOREIGN KEY (`exam_id`) REFERENCES `exam`(`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The above are the basic ideas and code examples for designing the MySQL table structure to support the online examination system. By rationally designing the database table structure, the performance and maintainability of the system can be improved, making the online examination system more efficient, secure and stable. I hope this article will help you design the database structure of your online examination system.

The above is the detailed content of How to design MySQL table structure to support online examination system?. For more information, please follow other related articles on the PHP Chinese website!