使用 PHP 和 MySQL 开发“测验”Web 应用程序的数据库设计

Database Design For Developing 'Quiz' Web Application using PHP and MySQL

提问by Abundnce10

So, I'm trying to learn PHP and MySQL (I have a basic understanding of both; I've read the first half of both Head First SQL and Head First PHP & MySQL) and I figure the best way to solidify my knowledge is by building something rather than reading.

所以，我正在尝试学习 PHP 和 MySQL（我对两者都有基本的了解；我已经阅读了 Head First SQL 和 Head First PHP & MySQL 的前半部分），我认为巩固我的知识的最佳方法是通过建造东西而不是阅读。

With that in mind, I would like to create a basic webpage that connects to a MySQL database on a server. I will build a basic HTML form and allow users to input basic information, such as: last_name, first_name, email, birthday, gender.

考虑到这一点，我想创建一个连接到服务器上的 MySQL 数据库的基本网页。我将构建一个基本的 HTML 表单并允许用户输入基本信息，例如：姓氏、名字、电子邮件、生日、性别。

My problem isI don't know how to design a database that will record the results of a basic quiz - I want just 5 multiple-choice problems. Eventually, I would like to display the results of the user versus the previous users' results.

我的问题是我不知道如何设计一个数据库来记录基本测验的结果 - 我只想要 5 个多项选择题。最后，我想显示用户的结果与以前用户的结果。

If you could help me understand how to design table(s) for a 5-question Quiz I'd appreciate it. Thanks!

如果您能帮助我了解如何为 5 个问题的测验设计表格，我将不胜感激。谢谢！

回答by ariefbayu

I would start with 4 simple tables:

我将从 4 个简单的表开始：

 * User
   - user_id    auto integer
   - regtime    datetime
   - username   varchar
   - useremail  varchar
   - userpass   varchar
 * Question
   - question_id   auto integer
   - question      varchar
   - is_active     enum(0,1)
 * Question_choices
   - choice_id        auto integer
   - question_id      integer
   - is_right_choice  enum(0,1)
   - choice           varchar
 * User_question_answer
   - user_id      integer
   - question_id  integer
   - choice_id    integer
   - is_right     enum(0,1)
   - answer_time  datetime

My thought on this table design is:

我对这个桌子设计的想法是：

• table Useris for storing registered user.
• table Questionis for storing all your questions. It has is_active so that you can selectively display only active questions (using WHERE is_active= '1')
• table question_choicesis for storing all available options. It has is_right_choicewhich defines what choice is the right answer for particular question.
• Table User_question_answeris for storing answer from your user. It has is_rightfor faster lookup, to see whether that particular question and answer choice is right (based on is_right_choicepreviously defined). It also has answer_timejust to note when that particular user answer the question.

• 表User用于存储注册用户。
• 表Question用于存储您的所有问题。它具有 is_active 以便您可以有选择地仅显示活动问题（使用 WHERE is_active= '1'）
• tablequestion_choices用于存储所有可用选项。它is_right_choice定义了哪个选择是特定问题的正确答案。
• 表User_question_answer用于存储用户的答案。它具有is_right更快的查找速度，以查看特定的问题和答案选择是否正确（基于is_right_choice先前定义的）。它还answer_time只需注意特定用户何时回答问题。

回答by 0x6A75616E

I am not sure how new you are to programming in general, but even if you are just getting started, I would recommend you use a framework.

我不确定您对编程的总体了解程度，但即使您刚刚开始，我也建议您使用框架。

Using a framework will guide you by providing best-practice implementations of the tools you'll need in your project.

使用框架将通过提供项目中所需工具的最佳实践实现来指导您。

I personally use Symfonyfor php projects, and I would suggest you check out their guides and tutorials. Symfony is a well-established framework and it's based on widely accepted designs.

我个人将Symfony用于 php 项目，我建议您查看他们的指南和教程。Symfony 是一个完善的框架，它基于广泛接受的设计。

To answer your question more directly, though, I would suggest something like this for your application:

不过，为了更直接地回答您的问题，我会为您的应用程序提出以下建议：

 - user
  - id (PK)
  - last_name
  - first_name
  - email
  - gender


 - quiz
  - id (PK)
  - title


 - quiz_question
  - id (PK)
  - quiz_id (FK)
  - text

 - quiz_question_option
  - id (PK)
  - quiz_question_id (FK)
  - text
  - is_correct

 - quiz_user_answer
   - id (PK)
   - quiz_question_id (FK)
   - quiz_question_option_id  // this is the answer.

The above would allow you to define multiple quizes each having multiple questions and create answer sets (a user's set of answers to a quiz) and record each answer.

以上将允许您定义多个测验，每个测验都有多个问题并创建答案集（用户对测验的答案集）并记录每个答案。

Hope that helps :)

希望有帮助:)

回答by Daniel Lyons

This was also the first project I did in PHP/MySQL about 8 years ago.

这也是我大约 8 年前用 PHP/MySQL 做的第一个项目。

Your first solution is to code the database to exactly match your form. So, you want to record users and quiz submissions, so it's going to look something like this:

您的第一个解决方案是对数据库进行编码以与您的表单完全匹配。所以，你想记录用户和测验提交，所以它看起来像这样：

CREATE TABLE users (
  username VARCHAR(16) PRIMARY KEY, 
  password VARCHAR(8), 
  email VARCHAR(255), 
  birthday DATE, 
  gender ENUM('M', 'F')
);

CREATE TABLE quiz_answers (
  username VARCHAR(16) REFERENCES users,
  question1 VARCHAR(10),
  question2 INT,
  question3 ENUM('YES', 'NO', 'MAYBE'),
  question4 BOOLEAN,
  question5 VARCHAR(25),
  submitted_at DATETIME,
  PRIMARY KEY (username, submitted_at)
);

So this is just recording the bare minimum: the user and the quiz submissions. I've given types for the answers which you would have to make specific to your actual quiz. I've also made a response keyed off the user and the moment they submitted it; you're be more likely to use a surrogate key (AUTO_INCREMENT), but I like to resist surrogates as much as possible.

所以这只是记录最低限度：用户和测验提交。我已经给出了答案的类型，您必须针对您的实际测验做出具体的回答。我还做出了一个响应，关闭了用户和他们提交的那一刻；你更有可能使用代理键 ( AUTO_INCREMENT)，但我喜欢尽可能地抵制代理。

Right off the bat there is a 1NF violation: questionN. If you were doing this right, you would name these columns after what they mean, not just which question they are. But normalizing this is really the next step towards forms that are extensible, but track history.

马上就有一个 1NF 违规：questionN. 如果你这样做是对的，你会根据它们的含义来命名这些列，而不仅仅是它们是哪个问题。但标准化这实际上是迈向可扩展但跟踪历史的形式的下一步。

So the next thing you would notice is that really a quiz is a collection of questions, each of which has a collection of possible answers. And then a form submission really relates a set of selected answers to their questions, on a particular quiz form, by a particular quiz user. This sounds like a four-way relationship: user, quiz, question, answer. You can trim out one of those if you don't mind repeating questions on different quizes, but for the sake of completeness, let's go down this road. Replace quiz_answersabove with this:

所以接下来你会注意到的是，真正的测验是一系列问题，每个问题都有一系列可能的答案。然后，表单提交实际上将一组选定的答案与特定测验用户在特定测验表单上的问题相关联。这听起来像是一种四向关系：用户、测验、问题、答案。如果您不介意在不同的测验中重复问题，您可以删减其中一个，但为了完整起见，让我们沿着这条路走下去。quiz_answers用这个替换上面的：

CREATE TABLE questions (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  question TEXT
);

CREATE TABLE answers (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  question_id INTEGER REFERENCES questions,
  answer VARCHAR(255)
);

CREATE TABLE quizzes (
  name VARCHAR(255) PRIMARY KEY,
);

We don't really have any special metadata for a quiz, so it's just a name for now.

我们真的没有任何特殊的测验元数据，所以它现在只是一个名称。

So now you need a one-to-many relationship from questions to answers and from quizzes to questions.

因此，现在您需要从问题到答案以及从测验到问题的一对多关系。

CREATE TABLE question_answers (
  question_id INTEGER REFERENCES questions,
  answer_id INTEGER REFERENCES answers,
  idx INTEGER,
  PRIMARY KEY (question_id, answer_id)
);

CREATE TABLE quiz_questions (
  quiz_name VARCHAR(255) REFERENCES quizzes,
  question_id INTEGER REFERENCES questions,
  idx INTEGER,
  PRIMARY KEY (quiz_name, question_id)
);

The tricky part, like mentioned above, is the higher order relationship between the user and the form submission, and the link from the form questions to user answers. I've decided to separate this into two tables to avoid some repetition.

如上所述，棘手的部分是用户和表单提交之间的高阶关系，以及从表单问题到用户答案的​​链接。我决定把它分成两个表以避免重复。

CREATE TABLE quiz_submissions (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(16) REFERENCES users,
  quiz_name VARCHAR(255) REFERENCES quizzes,
  submitted_at DATETIME
);

CREATE TABLE submission_answer (
  submission_id INTEGER REFERENCES quiz_submissions,
  question_id INTEGER REFERENCES questions,
  answer_id INTEGER REFERENCES answers,
  PRIMARY KEY (submission_id, question_id)
);

This is pretty well normalized at this point. You can see that it is also going to be a bit harder to query. To get all the questions out for a quiz, you'll have to join from the quiz to the questions. You can either join from there onto the answers to do one big query to get all the data you need to build the form (along with having to do more post-processing) or you can hit the database one more time for each question and do less post-processing. I can argue either way. To get all of a particular users answers out, you're going to have to select from user_submissions with the quiz ID and the user name to the submission_answer table to the question and the answer the user chose. So the querying is going to get interesting quickly. You'll lose your fear of joins, if you have one.

这在这一点上已经很好地标准化了。您可以看到查询也会变得有点困难。要获得测验的所有问题，您必须从测验加入问题。您可以从那里加入答案以进行一次大查询以获取构建表单所需的所有数据（同时必须进行更多的后处理），或者您可以为每个问题再次访问数据库并执行更少的后期处理。无论哪种方式我都可以争论。要获得所有特定用户的答案，您必须从带有测验 ID 和用户名的 user_submissions 中选择问题和用户选择的答案的 submit_answer 表。所以查询很快就会变得有趣。如果您有连接，您将不再害怕连接。

I hope this won't put you off relational databases too much; by doing this you are, in effect, doing a relational model inside the relational model, albeit a restricted form.

我希望这不会让您过分远离关系数据库；通过这样做，您实际上是在关系模型中建立了一个关系模型，尽管形式有限。

I realize using a lot of natural keys like I have done above is a bit unorthodox these days. However, I recommend you try it, at least while you're getting started, because it will make it much easier to see how the joins have to work if they're not all integers in the range 1-10.

我意识到像我上面所做的那样使用很多自然键现在有点非正统。但是，我建议您尝试一下，至少在您刚开始时，因为如果它们不是 1-10 范围内的所有整数，它会更容易了解连接必须如何工作。

回答by Artem Zaytsev

Well, right now i'm in a development stage and still encounter some problems (i.e. synchronizing round states between server and client), but it may make some help to you

嗯，现在我处于开发阶段，仍然遇到一些问题（即同步服务器和客户端之间的循环状态），但它可能对您有所帮助

P.S.: do not store passwords in database, like in the picture above - store passwords hashes instead

PS：不要将密码存储在数据库中，如上图所示 - 存储密码哈希值