preface

Recently I am doing a set of answer system, referring to the design of an educational institution. This chapter will talk to you about the core of the exam system – how to test?

Simply put, the so-called examination system is the answer system, through the answer to complete the score after returning the answer results that complete the whole process.

Of course, some data need to be stored in the process, and some can be queried and calculated in the later stage (personal understanding). Please spray as soon as possible if there is any misleading information

As shown in the figure above, a set of papers has N questions, and each question is composed of questions, options and answers, so as to gather a complete set of answers (examination) system.

The examination paper

An exam (questionnaire) uses a set of papers, the relationship between the exam (questionnaire) and the exam is one-to-one, and the relationship between the paper and the exam (questionnaire) is many-to-many

CREATE TABLE `company_paper` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '试卷名称',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

This is just a simple design, depending on the actual requirements, if it is exam related requirements need to be added

• Test Time
• Pass grade

And similar fields should not actually be added to the exam paper, it has been said that the exam paper and the exam are many-to-many relationship, so the above fields should be added to the exam table.

CREATE TABLE `company_examine` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'paper_id' int(11) NOT NULL COMMENT '平 均 ',' start_time 'int(11) NOT NULL COMMENT' 平 均 ', 'time_limit' int(11) NOT NULL COMMENT '时 间 ',' score 'double NOT NULL COMMENT' 时 间 ', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Copy the code

The above is the examination table, and the examination table is bound with corresponding papers by paper_id.

The question bank

The question bank has nothing to do with the examination paper, but is a classified management of the examination questions. It is more convenient to select test questions when adding test papers after classifying them.

Of course, he is also a many-to-many relationship.

CREATE TABLE `company_question_database` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '题库名称',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

As the above table structure, the question bank is classified, so there is no over-design.

Test questions

Test questions should be a more tedious part of the whole system design process. Let’s look at the data table

CREATE TABLE `company_question` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'database_id' int(11) NOT NULL COMMENT '主 题 ',' title 'varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT' 主 题 ', 'option' text COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'option ', 'answer' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'answer ', 'type' tinyint(4) NOT NULL DEFAULT '0' COMMENT 'created_at' timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Copy the code

First of all, according to the traditional examination papers will be divided into three parts

• Subject (topic)
• options
• The answer

These three parts are summarized as one question, and the options and answers here are stored in serialization mode

/ / options > > > serialize ([" A "= >" option A "and" B "= >" option B "]) = > "A: 2: {s: 1:" A "; S: 7: "option A"; s:1:"B"; S: 7: "option B"; // answer >>> serialize (["A"]) => "A :1:{I :0; s:1:"A"; }"Copy the code

There are two reasons for not using JSON storage. I think mysql’s JSON query is not perfect enough, SQL is too complex, and the latter is not strong enough scalability and incompatible with the lower version.

mark

The grading step is 80% of the test, which is impossible, and it’s less than 50% of the test. Still quote that sentence from the e-commerce related article

Save as much as you can

So here’s how we do it

CREATE TABLE `company_user_paper` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'user_id' int(11) NOT NULL COMMENT 'user_id' int(11) NOT NULL COMMENT 'user_id' int(11) NOT NULL COMMENT 'user_id' int(11) NOT NULL COMMENT 'paper_id ', 'answer' text COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'answer ', Double (8,2) NOT NULL DEFAULT '0.00' COMMENT 'correct' double(8,2) NOT NULL DEFAULT '0.00' COMMENT 'correct ', 'date_length' int(11) NOT NULL DEFAULT '0' COMMENT 'timestamp ',' created_at 'timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Copy the code

As shown in the above table, all the details of the user’s actions in each test are recorded for easy query. The serialization method is still used in the answer part. When checking the answer, direct deserialization computs the array difference set not only completes the evaluation, but also has no problem obtaining the intersection.

$answerArr = unserialize($answer)
$successAnswerArr = unserialize($successAnswer)
array_diff($answerArr,$successAnswerArr)
Copy the code

After all, the exam is not so simple as one question, you can choose to iterate to complete. If I have a large number of questions. Or the users are relatively concentrated, it is recommended to use queues to asynchronously complete the grading operation and notify the client (Web end) through socket or other means.

Thank you

That’s the end of this chapter. Thank you for reading it, and I hope it helped you. Thank you very much!

communication

There is life, there is code.

Spread the positive energy of technology and continue to learn new knowledge.