[RESOLVED] Storing questions and choices

Hi guys :wave:

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:

Code:

---

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

---

I have come with three designs:

1. Store the questions and answers in the same table

Code:

---

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

Code:

---

Table:Questions

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

---

Code:

---

Table:Choices

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

---

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

Code:

---

Table:Questions

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

---

Code:

---

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 :wave:

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

Thanks :wave:

Quote:

---

Originally Posted by techgnome

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

Quote:

---

Originally Posted by techgnome

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. :D
The idea was to separate choice into separate table and use that choiceID in the questions table.

Quote:

---

Originally Posted by techgnome

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

---

:thumb: