Design of questionnaire module of database design 1

These two weeks in the design of a survey database, now do a summary.

Usually as a user, feel very simple a module, now participate in the design, although the feeling there are countless imperfect places, but the design time, or consider a lot of.

Design of Questionnaire Module
The main entities are: questionnaires, questions, options, question types


Questionnaires and test questions are many-to-many relationships, using association tables
The questionnaire and the question as a whole, called the volume problem, the volume question and the option is a one-to-many relationship, so take the option to hold a questionnaire ID and question ID

Between the question and the question type to be a many-to-one relationship, the use of test questions to hold the type ID


The difficulty of the question type is the hierarchy structure, which uses the addition of a PID field to represent the parent-child relationship
Then add a varchar-type path for easy querying, and path records the path from the top-level node to that node.
In the query direct child node is with where pid = XXX can

Queries all child nodes, using the like to match the path field.


The key issues involved
1. Because the questionnaire is equivalent to the question Bank test questions, if one of the questionnaires modified or deleted a question on how to deal with other questionnaires.
(1) Since the questionnaire is a quoted question, delete the question from the questionnaire, just delete the records in the associated table, the question itself will not be physically deleted. Of course, because the options are separate questionnaires + questions stored separately, should be deleted.
(2) Processing of modifications. Two types of interfaces are given
① associated modifications. After the modification, all references to this topic will be modified together.
② independently modified. The change is equivalent to creating a new question, and the original option is copied and linked to the newly created question.


2. Since the independent revision is so troublesome, why not use all the questionnaires to store the questions independently.
(1) If there are many questionnaires that cite some of the same problems, they can cause a lot of redundancy. And if you import a question from a topic you already have in another paper, it will be a hassle.
(2) There is no way to implement the associated changes, it is difficult to change a place to achieve multiple test paper changes.

3. Why the choice of the question to be used, a single test paper to be stored in one copy. Instead of a single test paper.

If the option is completely determined by the question, then if the two questions, the same title, the options are different, it will be counted as two questions, if you add the option to the questionnaire, you can provide greater flexibility.

And the score and the option is one by one corresponding, and even the same problem, the same option, in different questionnaires, the score may be different, it is necessary to provide such flexibility.


4. Choice of question entry
The entry of questions is mainly divided into the basic information (such as the topic content) and the input of the option (the score is included in the different options)
(1) Manual input
Separate basic information and options
(2) Import from other papers
Imported questions, the user to modify the basic information will be mentioned above the two different changes.
However, since the options are unique to each topic of each quiz paper, random changes will not affect other papers.
(3) Arbitrary retrieval of input in the question bank
Since we adopt the option of the storage structure is, the option is related to the questionnaire + questions, if only to retrieve the questions in the bank, copying over no option, but also to enter options, or is very troublesome.
This time we can improve (2) the function, so that users can select the test paper, and then arbitrarily select the topic, thereby making up (3) deficiencies.


5. New Questionnaire
To create a new questionnaire, you need to fill in the basic information, create new questions, add options to the questions, and add a new result label.
Also in the new question, due to the need to display in real time the added questions, and the options of the question.
There are two ways to show things that have been added
(1) will have added questions, options stored in the front-end of the table what, and then save the questionnaire, then store the questionnaire, get the questionnaire ID, then the question, get the question ID, then the question with the questionnaire, then save the options, and then fill in the option of the questionnaire ID and question ID.
(2) When starting a new questionnaire, save a questionnaire to the database, and then create a new problem, there has been a questionnaire ID, and then directly stored questions, to obtain the question ID then, add a questionnaire-question association, and then have been added to the question can be found through the table display, the same option.
But this has a disadvantage, if the user points in, give up editing, you still set up a questionnaire record, but the Cancel button is not able to send a message to delete the database records, even if possible, the user may also go to the upper right corner of the X to exit.
(3) Adopt pagination
Split a large new survey page into several pages. The first page, only the basic information of the questionnaire, when the user chose to save a new questionnaire to create a record and jump to the next page.
So for the questionnaire, there is indeed a save and go to the next step will really produce a questionnaire record, the user experience will be much better.


6. Processing of ratings
The logic of this rating is implemented entirely by the database, thereby easing the burden on Java programs.
The method is to save all the possible points for each option.
Then the direct match will be able to get the score, so that the Java program does not need to do the logical judgment.
For example, a multi-choice, gives the a,b,c three options.
There will be a,b,c,ab,ac,bc,abc in your answer, and all possible and corresponding scores will be stored in the database.
Then add a is_show attribute to the option to indicate which is displayed when the topic is printed.
This, for example, shows only a,b,c.
Then according to the user's answer to match the option to get the user's score.


7. Revise your resume
This module is mainly for each modification (add, delete, change) are recorded.
My idea is that
(1) Recording according to the user's experience
For example, a user creates a questionnaire in which a question is created and 5 options are created.
Everything here will be recorded.
But delete a questionnaire, although also deleted the title records and options, but will only record deleted the questionnaire
Import a number of topics from a questionnaire, and will only record imported questions
(2) All modifications are stored in the varchar. But when stitching this action, if the entire statement is directly stitched together, it will be very cumbersome. So I used the method is to distinguish between questionnaires, questions, options 3 levels, to three interfaces, fill in different parameters, to distinguish the operator, operation mode (add, edit, delete).
At the same time, an additional interface is provided to write the entire statement, so that some additional actions can be recorded later.
For example, adjust the order of the two questions in a questionnaire.


Not yet implemented.
1. Result label entity
The result tag is how many points a user gets to tag the user.
For example, the exam, 100-80 excellent 79-60 good 60-0 failed
This excellent, good, fail is the label.
This module is actually quite similar to the question.
Because this tag has its own type, this type also implements the hierarchy.
Second, the label and the questionnaire are many-to-many relationships, but also to be implemented with association tables.


2. Question number
First of all, the test paper in the bank does have a primary key ID, but because of the characteristics of the database itself, deleted some records, the subsequent growth will be from the beginning has already occurred, and will not start from the current.
Like a. I have now deleted 3, the next record ID will be 4, not 3.
Second, the same topic, in different papers are originally different.
Therefore, to maintain a separate question in the questionnaire, a separate number.
Here are a few puzzles.
(1) Newly inserted question number
The way I think about it now is to check how many questions you have before inserting and then get what you should be.
(2) Change of number of questions
For example, insert a question into the middle, or delete a question, and the other numbers will change.


3. Fill in the blanks
Fill in the blanks if it is, according to the value of the range to score well said, essentially can be used as a choice question. When computing points, use Java code to achieve the score logic.


4. Parent-child questions, options and hidden questions
The parent question does not have the actual content, just plays the role of the classification, the actual question is still the sub-question.
The options and hidden questions are based on the content of the options to determine what will appear later.
For example, if one is a male and one is a female, the latter may not be the same.
One is the youth one is old age, the later question may also be different.


5. Question Type What is the strategy for deleting and changing the operation?

2016/05/27 Update

1. Questions about why a questionnaire is not used are the reason for independent storage.

My leader gave me 2 reasons.

(1) In order to realize the reuse of the question bank

(2) In order to be able to achieve a unified change, so as not to cause changes in the trouble

However, the use of association is actually a lot of pits, there is always a contradiction exists, want to re-use test questions and need to modify the question independently.

Once a thing has been reused, the modification (editing, deleting, adding a few appendages) to this thing affects the entity that references the object.

This is good when you want to make a uniform change. This is not good when you do not want to make a uniform change.

In fact, I still disapprove of the 2-point reason for leader.

(1) for the reuse of the question Bank, the ultimate goal is to provide users with a reusable experience instead of saying that they must be reused in the backend database.

As long as the user can import the existing questions, for the user, is the re-use of the test, and can modify their own questions at will, resulting in a lot of flexibility.

(2) Uniform modifications that need to be implemented.

It is true that this questionnaire module may have many questionnaires, but how many questionnaires have the same problem?

There may be several and certainly not too many, so there is absolutely no need to dig a pit to maintain it for the sake of easy modification.

Of course, this is my current idea, the use of the test paper for the questions are quoted, I went to achieve, there is a pit.

But for my idea, the questionnaire independently held a copy of the problem, I did not actually go to realize, maybe there is also a pit may, there is time to realize the bird.