Database design - table relationship question

I am designing schema for a simple quiz application. It has 2 tables - "Question" and "Answer Choices". Question table has 'question ID', 'question text' and 'answer id' columns. "Answer Choices" table has 'question ID', 'answer ID' and 'answer text' columns. With this simple schema it is obvious that a question can have multiple answer choic开发者_StackOverflow社区es & hence the need for the answer choices table. However, a question can have only one correct answer and hence the need for the 'answer ID' in the question table. However, this 'answer ID' column in the question table provides a illusion as though there can be multiple questions for a single answer which is not correct. The other alternative to eliminate this illusion is to have another table just for correct answer that will have just 2 columns namely the question ID and the answer ID with a 1-1 relationship between the two tables. However, I think this is redundant. Any recommendation on how best to design this thereby enforcing the rules that a question can have multiple answer choices but only one correct answer? Many Thanks.

How about just naming the column CorrectAnswerId? I doubt anybody would mistake that for anything else.

Lose the AnswerID column from the Question table. You're creating a (sort-of) circular reference. Instead, have an IsCorrect bit (boolean) column in the Answer table. This will also afford you flexibility to have multiple correct answers in the future should you need that facility.

Per Matti's point, a trigger on INSERT/UPDATE of an Answer-record will enforce the zero-or-one correct answer per question rule.

Three tables (because you can have many "Yes" "No", why duplicate that, and you can change later a "No" to a "Nop" easily). the questions_answers table would map some answers to some questions. (It's a many to many relationship).

questions:

• id
• description
• correct_answer_id

answers:

• id
• description

questions_answers:

• question_id
• answer_id

questions
- id
- question_text

1, "left or right?"

answer_choices
- id
- question_id
- answer_text
- correct

1, 1, "left", 1
2, 1, "right", 0

answers
- id
- question_id
- user_id
- answer_choices_id

1, 1, 1234, 2 // wrong!
1, 1, 5678, 1 // yay

So, basically you first query the question from the questions table, and then query the possible answers from the answer_choices table that are linked to the question by question_id. When an answer has been given, you take the selected answer_choices_id and check it against the answer_choices table to see if correct is 1 or 0.