测验程序的MySQL数据库设计 [英] MySql database design for a quiz program

问题描述

我目前正在一个项目上，我实际上正在创建一个测验应用程序.它将能够进行10-20个问题之间的多项选择题或简短回答题的测验.它需要具有检查用户答案与正确答案的能力，然后保持用户答案的​​分数.

I'm currently working on a project where I'm essentially creating a quiz application. It will have the ability to take quizzes that have either multiple choice or short answer questions, between 10-20 questions. It needs to have the ability to check the user's answers against the correct answers, and then keep score of the user's answers.

稍后，我可能会实现一个后端功能来在线创建所有测验，但是现在我将保留它，直接使用数据库.

Later on, I may implement a back-end feature to create quizzes all online, but for now I'll leave that up to directly working with the database.

现在是我的问题..我对如何设计数据库和表以支持所有功能一无所知.这是我目前的设置方式:

Now for my question.. I'm pretty lost on how to design my database and tables to allow for all the features. Here is how I currently have it set up:

测验表

• 名称行
• id行

问题表

• id行
• 对应的测验ID行
• 实际问题文本行

答案表

• 对应的测验ID行
• 相应的问题ID行
• 实际答案文本行
• if_correct_answer行

因此，每个测验将具有自己的ID，然后每个问题将具有其自身的ID和其父测验ID，然后答案将具有一个父测验ID和一个父问题ID.

So each quiz will have its own id, then each question will have its own id as well as its parent quiz id, and then the answers will have a parent quiz id and a parent question id.

截至目前，我遇到的正确答案"问题是这样运行的:如果他们选择正确的答案，则if_correct_answer行的值将大于0，那么最后所有答案的值将为加起来，那就是多少他们正确.

As of right now, I have the "correct answer" problem functioning this way: If they choose the correct answer, the if_correct_answer row will have a value greater than 0, then at the end the value of all their answers will be added up and that's how many they got right.

这是设计数据库的好方法吗?我在这里的另一篇文章中看到了基本思想，但是不确定我是否完全理解或正确使用了它.我基本上只是对如何协调所有不同的ID感到困惑. (我是mySQL和PHP的初学者，可能无济于事)

Is this a good way to design my database? I saw the basic idea on another post here, but I'm not sure if I fully understand it or am using it correctly. I basically just end up confused about how to coordinate all the different ids. (I'm a beginner to mySQL and PHP, which probably doesn't help)

推荐答案

您的表结构对我来说很好.您还需要另一个表来存储用户的答案.

Your table structure looks good to me. You need another table to store the user's answer as well.

用户的答案表-粗糙的结构

• id
• user_id
• quiz_id
• question_id
• answer_id
• answered_date

您还需要另一个表来存储用户的基本信息，例如姓名，user_id，电话等.

And you need yet another table to store the basic info of user like name, user_id, phone, etc.

这篇关于测验程序的MySQL数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！