what is the best way to create this database

what is the best way to create a database for Questions and Answers Script

the user can submit a question with a set of choices among which the right answer exists

the database must save the question, all the choices and the right answer.

the best way that comes to my mind is to make 2 tables:

Questions Table:

• ID
• Question
• Right_Answer_ID

Choices Table:

• ID
• Choice
• Question_ID

I'm using PHP and MYSQL for that so if i use this way it will be a little hard to insert information into my database, because i have to insert the Question and insert all choices and take the right choice ID modify the Right_Answer_ID field to that ID, it's a lo开发者_开发知识库ng process and i'm sure that there is a better way to achieve that, please HELP.

Thanks

What about this?

Question

• ID
• Question

Choice

• ID
• Question_ID
• Choice
• Is_Right_Answer

Reminds me of Catch-22 by Joseph Heller...

Unlike with the book, there is a true loophole:
The easy way out is to produce your own IDs, rather than relying on auto-incremented and other SQL-supplied IDs.

This said, and aside from being a reminder that application-generated keys and identifiers are sometimes preferable to their system-supplied counterparts, it is also a good opportunity to reflect on the database design.
For example Jeff's answer, which suggests moving the "correct response" info from the "Right_Answer_ID" column in Questions table to an "IsCorrect" column in the Responses table, not only addresses the INSERT circular reference problem, but also introduces a more versatile data model: one where we may have multiple correct responses for a given question (or possibly, by changing "IsCorrect" by a numeric value of sorts, one where Responses may be "correct" on a sliding scale)

This looks like the best approach if the number of choices is variable - it's properly normalized and easy to query. An extra for loop to insert the choices won't break your back. You could even construct a single query for all the choices and execute it once.

Your design is a good starter, but what about questions with more than one correct answer, even if that will not be the case right now, but applications tend to evolve. That will lead to Jeff's answer, as well as the problem posed by mjv for saving the values.

So when only saving "ID" and "question" in the questions table, then you can use that question_id to save the choices, and you do not need to get the id of the correct answer back when saving choices and update the questions table.

if the number of choices is either variable or can change in future, then the design you have proposed is the right way to go. if the number of choices is not going to change, then I would say it would suffice to have a single table with id, question, choice1, choice2, ...., choicen, correct_choice as the fields in it.

If you have two tables you will have to have two insert queries. You can avoid the last update query by following the design below:

Questions Table:

* ID
* Question
* Right_Answer_Index

Choices Table:

* Index
* Question_ID
* Choice

If you need to have a primary key on the choices table you can combine Index with Question_ID. This way, in the html form you use for authoring new question and answers, you can have an extra input to indicate the index number of the correct choice and thus when you are inserting your question you have all the information you need (before the correct choice record has been added).