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