I am developing an application which is based on the quiz contest for competetive exams. The user can give different categories of tests, which will contain different types of questions of following types:

  1. Single select,
  2. Multiple select,
  3. Match making,
  4. Comprehension (the quetion will be a paragraph and on the basis of that there will be questions),
  5. Free text (the user will have to type the answer),
  6. Range (the user have to select a number from 1 to 10).

My concern is that while creating questions from admin site, how can I manage the database for above type of questions?

I have one answer column in the question table but there are six type of questions so what should be the convenient way for saving answers in the databse so that when user will give answer, I can easily check if the answer matches?

I am thinking of saving the answer in the form of XML tags. Will this be a good approach?


Every type of question will have to be managed differently in your application and will use a different selection of data, it would be best to create multiple tables for each part of the question with that are all related to a central question table, rather than try to have one massive table.

you will want at a minimum a question table, a MC option table, a table for reading selections, a table to store rules on how to grade free text answers; or a table with enough columns for all that.

  • if iwill use mulitple tables then won't it be more difficult for me to match the answer? Feb 14 '12 at 19:17
  • 1
    no, basic SQL will allow you to join the tables together and questionid would be a good FK to join on. the alternative is to have a massive table where many columns will be null for each entry, which gets problematic.
    – Ryathal
    Feb 14 '12 at 19:25

You will probably have an answers table. This table should have a question_id so you know what question is being answered, and it should also help you figure out what kind of data is in the answer, assuming that the questions table has some sort of question_type_id that refers to one of the question types above.

Then, the simplest (IMO) solution: For most of these answer types, you could have a single text field to store the answer, probably no longer than the max length you'd allow for Free Text answers (assuming no more than a hundred characters or so). For the longer answers for the Comprehension type, you may want a separate table, call it large_answers, for holding very large text objects and store the answer there, and in your main answers table refer to the large_answers by an optional large_answer_id field.

I'm not sure why XML would be needed for this.


  question_id (unique key)
  (other fields that don't have an impact on this solution)

Answers Answer_id (unique key) question_id (refers to questions.question_id) answer_text (non-null, but for large_answers, maybe just store the first n characters, as a preview) large_answer_id (optional, refers to large_answers.large_answer_id)

large answers large_answer_id (unique key) large_answer_text (CLOB type)

Or you could do it like this:

  (as before)

Answers Answer_id (unique key) question_id (refers to questions.question_id) answer_text (nullable)

large answers large_answer_id (unique key) answer_id (refers to answers.answer_id) large_answer_text (CLOB type, non-null)

In the above schema, you'd have the large_answers referring back to the answer they are associated with, instead of the answers having a nullable reference to a large_answer. Hmmm the more I think about it the more I like the second schema as I don't like the idea of a nullable large_answer_id, but I'm sure there's good arguments to be made for both sides... ;)

  • That's a great solution i have start working to implement it thank you very much Feb 14 '12 at 19:16
  • The OP didn't say anything about extraordinarily large answers. The "large_answer" table is a solution in search of a problem. Feb 14 '12 at 22:52
  • @kevincline: The "Comprehension" type says the answer could be a paragraph, and if it's a short paragraph, the large_answer isn't really needed. It depends on how long the paragraph(s) are. Essay-length answers and it might be better to split it. Feb 15 '12 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.