[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