Database design - table relationship question

views:

answers:

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 choices & 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.

+2 A:

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.

Bob Kaufman 2010-05-18 22:21:00

That, however, requires you to create additional rules to enforce that only one answer for each question can be marked as correct.

Matti Virkkunen 2010-05-18 22:22:01

@Matti - good point. An INSERT/UPDATE trigger would do the trick.

Bob Kaufman 2010-05-18 22:24:49

the IsCorrect bit field in the choices table is the most flexible approach as you can, if required in the future, have multiple correct answers for a question simply by toggling a field value 0 or 1.

f00 2010-05-18 23:05:12

This was the approach I thought initially but did not want to implement 'coz it will leave too many gaps in the Answer choices table 'coz clearly there can only be one correct answer to any question.

naivnomore 2010-05-19 02:15:18

@iama - could you clarify what you mean by "gaps"? Thanks!

Bob Kaufman 2010-05-19 02:56:33

@Bob - By gaps I mean most of the answer choices will have false or null as the value since there can be only one correct answer.

naivnomore 2010-05-19 03:01:25

@iama - This doesn't quite fit, but "entropy" may be the term we're grasping for. Nothing wrong with an underutilized field, just like student's sex at The Citidel (http://www.citadel.edu/main/) Read more about entropy here: http://en.wikipedia.org/wiki/Entropy_%28information_theory%29

Bob Kaufman 2010-05-19 03:20:17

+2 A:

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

Matti Virkkunen 2010-05-18 22:21:15

+1 Simpler than my solution!

Bob Kaufman 2010-05-18 22:27:48

@Bob +1 for acknowledging this, not common on SO

Alexander 2010-05-18 22:29:09

I am assuming you are asking me to rename the column name in the question table to "CorrectAnswerID". I can certainly do that but I guess it still does not change the original issue. It will still appear as though answer choices table has 1:N relationship with the question table. This is not a big deal. I just wanted to know if there was any better design given my requirements.

naivnomore 2010-05-19 02:41:47

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

Nicolas Viennot 2010-05-18 22:25:09

you may also consider another table to identify users and another to identify what answers a given user selected...

Randy 2010-05-19 23:00:34

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.

Alec 2010-05-18 22:26:34

What does the ID on the answers table do?

Stephanie Page 2010-05-18 22:42:48

Simply a unique identifier for a user's answer. Whether it's useful depends on the use. You can also drop it and use `question_id` combined with `user_id` to identify it.

Alec 2010-05-18 22:50:19

Having correct field in the answer choices table leaves too many gaps since there can be only one correct answer to any question. Instead you could have the correct field in the question table or create another table with just the question ID and the correct field. However, my problem is not that. How best can we design these tables so that there is no redundancy or duplicates.

naivnomore 2010-05-19 02:45:40

ansaurus

tags:

views:

answers:

Database design - table relationship question

related questions