创建这个数据库的最佳方式是什么

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
• ID
• Question
• 问题
• Right_Answer_ID
• Right_Answer_ID

Choices Table:

选择表:

• ID
• ID
• Choice
• 选择
• Question_ID
• 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 long process and i'm sure that there is a better way to achieve that, please HELP.

我使用PHP和MYSQL,如果我用这个方式将很难将信息插入数据库,因为我必须插入问题,插入所有的选择和正确的选择ID修改Right_Answer_ID字段ID,它是一个漫长的过程,我相信有一个更好的方法来实现,请帮助。

Thanks

谢谢

6 个解决方案

#1

What about this?

这是什么?

Question

问题

• ID
• ID
• Question
• 问题

Choice

选择

• ID
• ID
• Question_ID
• Question_ID
• Choice
• 选择
• Is_Right_Answer
• Is_Right_Answer

#2

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.

与本书不同的是，存在一个真正的漏洞:简单的方法是生成自己的id，而不是依赖于自动递增和其他sql提供的id。

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)

尽管如此，除了提醒您应用程序生成的键和标识符有时比系统提供的键和标识符更可取之外，这也是对数据库设计进行反思的好机会。例如杰夫的回答,这表明移动的“正确答案”信息问题表中“Right_Answer_ID”列的“IsCorrect”列反应表,不仅解决了插入循环引用问题,但也引入了一个更通用的数据模型:我们可能有多个正确的反应对于一个给定的问题(或可能通过改变“IsCorrect”的数值类型,一个反应可能滑动规模的“正确”)

#3

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.

如果选项的数量是可变的，这看起来是最好的方法——它是正确的规范化和易于查询的。一个额外的for循环插入选择不会折断你的背。您甚至可以为所有选项构造一个查询并执行一次。

#4

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.

您的设计是一个很好的入门，但是如果问题有不止一个正确的答案，即使现在不是这样，但是应用程序趋向于发展。这将导致Jeff的回答，以及mjv提出的保存值的问题。

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.

因此，当在问题表中只保存“ID”和“question”时，您可以使用question_id保存选项，并且在保存选项和更新问题表时不需要获得正确答案的ID。

#5

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.

如果选择的数量是可变的，或者将来可以改变，那么你提出的设计是正确的。如果选择的数量是不会改变,那么我想说,它将足以与id,一个表的问题,choice1,choice2,....， choicen, correct_choice作为其中的字段。

#6

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

如果您需要选择表上的主键，可以将索引与Question_ID组合在一起。这样,在你所使用的html表单编写新的问题和答案,你可以有一个额外的输入显示索引号的正确的选择,因此当你插入你的问题你有你需要的所有信息(添加了正确的选择记录之前)。