What database streucture must I use to store multiple choice question texts?

Question

I have two tables: 'questions' and 'answers' - but each one question has four possible answers - so how do I relate the primary key on the 'questions' table to the 4 answers on the 'answers' table? Millions of imaginary dollars for the correct structure!!!

Answer 1

Hmm.. I need to ask a question:



1. Are you going to "randomly generate answer list", i.e. the possible answer list is randomized but it'll contain exactly one correct answer?



If you say no to this, it is simple. Just use this data structure:

QuestionTable:

- QuestionID: Primary Key AutoNumber

- Question: String

- AnswerA: String

- AnswerB: String

- AnswerC: String

- AnswerD: String

- Solution: String/Char in "ABCD"



but if you say "yes", it needs to be a bit complex. You need to have two tables, one for questions and the other for answers. Another answerer has outlined the data structure. In the program, you'd then query the answer table for answers that is directed for a certain question. From that query, find the correct answer and ensure it is one of the possible solution and pick three other wrong solution. Randomize this short-list.



Tips: always choose the simplest solution that fits your requirement

Answer 2

Make a primary key in the Questions table. Then make that Primary Key a Foreign Key in the Answers table. You have to select a primary key in the Answers table too. It might be a composite key depending on the columns you have.

Answer 3

Questions:

* QuestionNumber - integer - indexed - Primary key

* QuestionText - string



Answers:

* QuestionNumber - Foreign Key - index - allow duplicates

* AnswerText - string

* Correct - boolean



And then you can get the information as:



select questions.QuestionText. answers.AnswerText, answers.Correct

from questions, answers

where questions.QuestionNumber = answers.QuestionNumber and

questions.QuestionNumber = ;