Can you recommend a database design for questions and answers to the quiz, which would expand the number of types of questions?

Question

Examples of question types:

• Single answer with multiple choices (think of a button)

• Multiple choice of multiple response (think checkbox)

• Matching (there are now many permutations of possible choices)

I want to keep them in a database structure that can be used for these and many other types of questions in the future, so I'm trying to come up with a way to expand it ...

Now I have (your recommendation should not be limited to this):

• Table of questions
• Answer Table
• ChosenAnswers table
• User table

One question can have many answers. One pair of users / questions may have many ChosenAnswers.

However, I can't do this job well with Matching, as far as I can tell, and I'm also not 100% sure that it will work easily for a multi-user answer, which easily means the least amount of logic needed to calculate the total score, etc. d.

Can anyone think of a design that would allow me to use all three of these types of quiz questions, as well as add future ones? I need inspiration, a paradigm shift, if you ...

An example of future types of questions might be "put this list of things in the correct sequential order" ... etc.

There must be a way, no matter how difficult it may seem, to take into account all these different possible types of questions, although I still allow me to use a fairly simple way to calculate the total score for the quiz for reporting purposes, and therefore

Please let me know in the comments here if there are any details that I am missing, but it might be best to assume that I didn’t even consider the details that you think about, because I asked everything that I have a question already, but I will add clarity as I request it.

Answer 1

In the project below, we will use a series of flags in the question table to indicate what type of question it has. We also indicate the level of the question. The answer table is linked to the question table through a foreign key. The completed test table will present the results of the tests performed. Only one incomplete test is allowed per user. In the incomplete test table, we will associate the question with the question table (if I missed the relationship line), which will refer to the possible answers that we write, the answers are given to the user in the row column of the incomplete test table.

Answer 2

One answer is similar to a multiple answer with a restriction, so they can be easily stored in one table. So, you have work for both of them.

I would advise you not to try to consolidate too much, because if you ever need to add another type of question, you may well need to reorganize your paradigm, which will not only be a headache for old types, but also work more or as much for new type. That is, add special tables for questions such as matching.

You can always combine results with a view. Thus, the individual types are easy to maintain, but the combined results are easy to generate. Let the database handle complexity, not your own intelligence. (If you do not agree that you can write your own engine, and not use SQL in the first place. :-))

Answer 3

You can easily think of “matching” with so many questions using the multiple-choice-single answer method

Instead of a switch, I would suggest a drop-down list. The layout will work better.

For more inspiration, check out how LON-CAPA does it.

Good luck