Design of questionnaire module of database design 2

Design of questionnaire module of database design 1

2016/07/13 Update

The entire internship has come to an end, in fact, the entire Questionnaire module database design, in early June basically has been finalized and realized.

This summary is divided into two parts.

One, to the previous proposed a few legacy issues, resolved to do a note

Second, the final summary, the core of the questionnaire module: questionnaires, questions, options of the relationship between several entities how to express, truthfully recorded in the mental process, as well as some of the pits encountered.

One. Previous legacy issues

1. Result question Label

This result tag is not a difficult problem, but only the ID of the resulting label can be recorded.

The evaluation of a questionnaire can of course be obtained with several result labels. The same result label can also be held by a different questionnaire.

So the questionnaire: the result label is N: S relationship.

① can be implemented using association tables

② can use a string in the evaluation results record to record all the resulting label IDs

This tag module is then ready to be made into a generic module.

The more complicated logic is how to get the ID of the result label according to the user's answer and the evaluation rule.

But this is not the content of this post.

2. Question number

Many of this can be implemented by the front end.

But I did it at the time.

If the relationship of two entities is 1:n, the implementation is implemented by holding ID, which can be maintained in the N field directly.

For example, the number of options in the question (the final option holds the question ID)

If the relationship of the two entities is N, and the implementation is using an associative table, then the sequence number should be placed in the associated table

For example, the number of questions in the questionnaire

If it's just its own serial number, it's placed directly in the field itself.

For example, the serial number of the questionnaire, the type of the questionnaire

① Increase

Add a record, the ordinal value is, the query already has a record, and then count comes out.

② Delete

Delete a record, query to all records whose ordinal number is greater than the deleted record, and bulk subtract one of its ordinal fields.

③ Delete Multiple

When deleting a type, it is not certain which fields will be deleted and how many will be deleted because the delete type needs to be tombstoned with its subtypes.

The way to do this is to query all the remaining fields, sort them in ascending order, and then traverse the uniform to reset their ordinal numbers.

3. Fill in the blanks

Not resolved

4. Parent-child questions, options and hidden questions

Not implemented

5. Types of Pruning strategies

① Delete with Tombstone

Assigns a value to the field N_is_delete.

After deletion, the newly added type cannot see the deleted. The type of the questionnaire selection is not visible and has been deleted.

However, it has been selected and will not be affected by the deletion.

It is important to note that, in the hierarchy, the deletion needs to be deleted together with its subcategories.

② Edit

Content can certainly be edited, but hierarchies do not allow editing

Two. Design of Questionnaire Module

The most important is three entities: questionnaires (paper), test questions (question), Options (option)

1. Design a

Paper and question are related tables

Option at the beginning of the idea is that the same topic, in different paper, can have different options, and have a different score.

option was used to hold Paperid and QuestionID

Advantages:

① different papers, changes to the options on the same topic do not affect each other.

② the same topic can have different options, providing greater flexibility

Disadvantages:

If there are paper1 and Paper2 have questions 6, this time paper3 from Paper1 also imported test x, recorded as Ques1, also imported from the Paper2 test 6, recorded as Ques2.

The ① option is channeling

There are two different questions for Paper3,ques1 and Ques2.

However, because of the option design, holding Paperid and holding QuestionID, this time ques1 options and QUES2 options are shared.

The option to modify ques1 is fully reflected in the ques2.

This situation is difficult to come by, but because we want to provide a unified problem, the flexibility of different options, this bug is real.

② Importing questions is inconvenient

This time to import questions, you will find that you can only import the problem, import the questions, if you do not specify the test paper, imported questions are no option.

Comprehensive ①, ② we can only modify the design.

2. Design Two

Paper and question close unchanged

Option sacrifices part of the flexibility to bind with question.

That is, in addition to fractions, different papers are imported into the same question, with the same problem and all options.

Such questions and options are 1:n relationships, with the option to hold the question ID.

Since it is still necessary to maintain different scores of the same option for different test papers,

The test paper has a number of options, the options can also correspond to different questionnaires, so it is n/a relationship, so this time the questionnaire, options also need an association table.

The associated table fields are Paperid, optionID, score

Advantages:

After the ① option is bound to the question, it avoids some problems, such as the crosstalk problem mentioned earlier.

② when importing questions, you can use the questionnaire, because the option content is only relevant to the question.

Disadvantages:

After the ① option is bound to the question, it also brings new drawbacks. Different questionnaires are imported into the same question, which will affect each other.

And the impact itself is good and bad.

If you want to change all the questionnaires together, of course, it is convenient.

If you don't want other people's changes to affect yourself, you can do nothing.

② the score of the display option is a lot of trouble.

A. Through the questionnaire ID, query all the question ID. With a question ID, query all the option IDs and display the contents of the options.

B. With the questionnaire ID, the option ID in the associated table, query the score of the option.

C. If you need to query the database once for an option, the cost is too high.

If you query the scores of all the options in the questionnaire, match the scores with the corresponding imaginations, which is unusually complex.

Workaround:

For ②, the solution is to modify the Questionnaire-Option Association table

Change the field inside to Paperid,questionid,optionname,score

This will separate the scores by the name of the option (A,B,C,D) and avoid pairing problems.

Because it is stored as an option name, it is not possible to distinguish between options for different questions, so the QuestionID field is added

For ①, we started with a judgment (by querying the count for a question in the correlation table to see if it equals 1).

If a question is not imported by another questionnaire, you can change it directly.

If a number of questionnaires are imported, then the decision to the user, each time the changes are made to inform users that such changes may affect other papers.

3. Design Three

In fact, in the design of the second, the approach to ① there is still an intolerable problem.

If a user creates a new topic, it has not yet created an option.

This time he had something to go, but other users imported the topic.

Later the user comes back to start creating an option, and this time, it will be asked when each option is created that this change will affect others and whether or not to modify it.

The experience is intolerable.

In this design, the questions and options are bound,

Then there are 4 things you can do to change the question:

① changes to the contents of the test, such as modifying the problem.

② Add options

③ Modifying option Contents

④ Delete option

And in this different questionnaire sharing the design of the problem has been a unresolved contradiction:

For one person to change, everyone is shared.
With the things that I quote, do not want others to modify the impact of their own not sharing
Contradictions between shared and non-common use

Finally, the compromise solution I adopted was as follows:

Topic Join a Creator field
Topic Add a Permission field: ① not import (the title of this permission does not appear in the Import list) ② can be imported, can only be modified by themselves ③ can be imported, anyone can modify


Permission edits are separate. Only the current operator = Topic Creator, you can edit the permissions.
General Editor:
(1) If the test permission is not imported, directly modify
(2) If the test permission can be imported, you can only modify
① operator = Topic Creator, Direct modification
② operator! = Topic Creator, popup hint cannot be modified
(3) If the test permission is can be imported, anyone can modify, directly modify


The import is divided into two types:
(1) Associated Import
Import associates the current questionnaire directly with a topic. Copy the questionnaire-Question-Option association table record.
The creator of the associated import does not change. Permissions are not changed.
(2) Independent import
Copy a question, save it separately, associate it with the current quiz paper, and then copy the questionnaire-Question-Option association table.
The problem creator is modified to import the operator, and the permissions are modified by default to not import.

The addition of independent import features, so that others can be set to import, can only create a person's own permission to modify the test without editing permissions, and you want to edit, you can import, to edit.

Permission editing function, so that can not be imported at any time to share the questions to others.

Under the authority control, all can modify can be directly modified, the modification caused by the other paper changes, by the user's own responsibility.

Design of questionnaire module of database design 2