Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
if iwill use mulitple tables then won't it be more difficult for me to match the answer? –  Sumit Neema 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.

So:

Questions
  question_id (unique key)
  question_type_id
  (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:

Questions
  (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... ;)

share|improve this answer
    
That's a great solution i have start working to implement it thank you very much –  Sumit Neema 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. –  kevin cline 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. –  FrustratedWithFormsDesigner Feb 15 '12 at 5:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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