How to design the MySQL table structure to support test question management of the online examination system?

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

Online examination systems usually require the management of test questions, including the addition, deletion, modification and query of test questions. In order to support these operations, we need to design a reasonable MySQL table structure to store test question data. The following will introduce how to design this table structure and give corresponding code examples.

First, we need to create a table named "questions" to store the basic information of the test questions, including the test question ID, title, options, answers, etc. The structure of the table can be designed as follows:

CREATE TABLE questions(

id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
option1 VARCHAR(255) NOT NULL,
option2 VARCHAR(255) NOT NULL,
option3 VARCHAR(255) NOT NULL,
option4 VARCHAR(255) NOT NULL,
answer VARCHAR(255) NOT NULL,
PRIMARY KEY (id)

);

In this table, the id field is the unique identifier of the test question. By setting AUTO_INCREMENT, the database Automatically generate test question IDs. The title field is used to store the title of the test question, the option1 to option4 fields store the options of the test question respectively, and the answer field stores the answer to the test question.

Next, we can create a table named "exams" to store the test paper information, including the test paper ID, name, description, etc. We can also add a foreign key to this table to associate the question ID with the test paper. This makes it easy to find the questions included in the exam paper. The structure of the table is as follows:

CREATE TABLE exams(

id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
PRIMARY KEY (id)

);

In this table, the id field is the unique identifier of the exam paper, which can be automatically generated by the database by setting AUTO_INCREMENT The ID of the test paper. The name field stores the name of the test paper, and the description field stores the description of the test paper.

Next, we can create a table named "exam_questions" to store the question information in the exam paper. This table can contain the ID of the test paper and the ID of the test question, which is used to represent the questions included in the test paper. The structure of the table is as follows:

CREATE TABLE exam_questions(

exam_id INT NOT NULL,
question_id INT NOT NULL,
FOREIGN KEY (exam_id) REFERENCES exams(id),
FOREIGN KEY (question_id) REFERENCES questions(id)

);

In this table, the exam_id field and question_id field represent the ID of the test paper and the ID of the question respectively. Create associations with the exams table and questions table by setting FOREIGN KEY.

Through the above design, we can realize the question management function of the online examination system. You can add test questions and test papers by inserting data, and obtain information about test questions and test papers through query statements. Some sample code is given below:

1. Insert INTO questions:

INSERT INTO questions (title, option1, option2, option3, option4, answer) VALUES ('Question 1' , 'Option A', 'Option B', 'Option C', 'Option D', 'Answer A');

2. Insert test paper:

INSERT INTO exams (name, description) VALUES ('Exam Paper A', 'This is the description of Exam Paper A');

3. Insert questions in the exam paper:

INSERT INTO exam_questions (exam_id, question_id) VALUES (1, 1);

4. Query test question information:

SELECT * FROM questions;

5. Query test paper Questions in:

SELECT questions.title FROM exam_questions
JOIN questions ON exam_questions.question_id = questions.id
WHERE exam_questions.exam_id = 1;

Pass the above With the sample code, we can implement the management and query functions of test questions and papers.

To sum up, designing the MySQL table structure to support the question management of the online examination system requires the creation of three tables: questions table, exams table and exam_questions table. These tables can be related through primary keys and foreign keys to realize the management and query functions of test questions and test papers. The sample code is given above, I hope it will be helpful for you to understand and implement the test question management of the online examination system.

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