Can you recommend a database design for quiz questions and answers that would allow for an extensible number of question types? [closed]

Question

Example Question Types So Far:

• Multiple Choice Single Answer (think radio button)

• Multiple Choice Multiple Answer (think checkbox)

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

I want to store them in a database structure that could be used for these and many more types of questions in the future, so I'm trying think of a way for it to be... expandable?

Right now I have (your recommendation does not have to be constrained by this however):

• Questions table
• Answers table
• ChosenAnswers table
• Users table

One Question can have many Answers. One User/Question pair can have many ChosenAnswers.

However I can't make this work well with Matching as far as I can tell, and I'm also not 100% convinced it will work easily for multi-answer, easily meaning the least amount of logic needed when calculating a persons total score, etc.

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

An example of future question types could be 'put these list of things in the proper sequential order'... etc.

There has got to be a way, however complicated it may seem, to account for all of these different possible question types while still letting me use a fairly straight forward way of calculating total score for a quiz for reporting purposes, and so forth.

Please let me know in the comments here if there is any details I am missing, but it may be best to just assume that I have not even considered a detail you are thinking of, because I've put everything I have in the question already, but I will add clarity as it is requested.

Answer 1

In the design below we will use a series of flags in the questions table to indicate what type of question it is. We also indicate the level of the question. The answer table is linked to the question table via the foreign key. The complet test table will hold the results of complet tests taken. Only one incomplete test is allowed per user. In the incomplete test table we will link the question back to the questions table (oops I missed the relationship line), which will link to the possible answers we record the answers give by the user in a string column of the incomplete test table.

Answer 2

Single answer is like multiple answer with a constraint, so those can easily be stored in the same table. So what you have works for both of those already.

I would advise against trying to consolidate too much, though, because if you ever need to add another type of question, you may well end up having to refactor your paradigm, which not only will be a headache for the old types but also more, or just as much, work for the new type. That is, add special tables for the matching type questions.

You can always combine the results using a view. That way individual types are easy to maintain, but the combined results are easy to generate too. Let the database handle the complexity, not your own cleverness. (If you disagree with that you might as well write a custom engine rather than use SQL in the first place. :-) )