[RESOLVED] Storing questions and choices

[akhileshbc]

Hi guys

I am a bit stuck at this db design. The situation is, I have to store questions, their choices (fixed number - 4 choices) and the correct choice.

Example:

What is your name ?
- Hamsa
- Koya
- Akhilesh
- Sasi

I have come with three designs:

1. Store the questions and answers in the same table

Table:Questions

Questions(TEXT-2000) || Choice1(TEXT-500) || Choice2(TEXT-500) || Choice3(TEXT-500) || Choice4(TEXT-500) || Answer(VARCHAR-1)

"Answer" will hold one of these values "1", "2", "3", or "4", which is the correct choice to that particular question.

2. Store the choices separate and store the id of the choice in questions table

Table:Questions

Questions(TEXT-2000) || Choice1(INT) || Choice2(INT) || Choice3(INT) || Choice4(INT) || Answer(INT)

Table:Choices

id(INT) || Choice(TEXT-500)

3. Store the choices separate and include the question id in the choices table for the relation

Table:Questions

Question_ID(INT) || Questions(TEXT-2000)

Table:Choices

Choice(TEXT-500) || Question_ID(INT) || is_answer(ENUM - 'true','false')

Which one do you think as the best design ?

I am using MySQL and in the above mentioned table designs, field names are followed by field type and size.

I am expecting for a reason on accepting/rejecting each design(atleast a sentence), from your viewpoint.

Thanks in advance

[techgnome]

tisk, sigh... for EACH? okay...
1) least flexible... doesn't allow for more or less choices.
2) Yeah, I didn't get that design
3) most flexible - allows for varying amount of choices AND lends itself to randomization of the choices... all you need to know is which one is the right answer... you really don't (or at least you shouldn't) care if it is A or B or E....

-tg

[akhileshbc]

Thanks

tisk, sigh... for EACH? okay...
1) least flexible... doesn't allow for more or less choices.
-tg

Yeah, but there would be only 4 choices(fixed number).

tisk, sigh... for EACH? okay...
2) Yeah, I didn't get that design
-tg

Yeah, that's a stupid design that I came up with.
The idea was to separate choice into separate table and use that choiceID in the questions table.

tisk, sigh... for EACH? okay...
3) most flexible - allows for varying amount of choices AND lends itself to randomization of the choices... all you need to know is which one is the right answer... you really don't (or at least you shouldn't) care if it is A or B or E....
-tg