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

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表单，允许用户输入基本信息，比如:last_name、first_name、email、生日、性别。

My problem is I 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个问题的小测验设计表格，我会很感激的。谢谢!

4 个解决方案

#1

I would start with 4 simple table:

我将从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 though on this table design is:

我的想法是:

table User is for storing registered user.
表用户用于存储已注册用户。
table Question is for storing all your question. It has is_active so that you can selectively display only active question (using WHERE is_active = '1')
表格问题是用来储存你所有的问题。它具有is_active，因此您可以选择性地显示活动问题(使用is_active = '1')
table question_choices is for storing all available options. It has is_right_choice which define what choice is the right answer for particular question.
表question_choices是用于存储所有可用选项的。它有is_right_choice，定义了什么选项是特定问题的正确答案。
Table User_question_answer is for storing answer from your user. It has is_right for faster lookup, to see whether that particular question and answer choice is right (based on is_right_choice previously defined). It also has answer_time just to note when that particular user answer the question.
表User_question_answer用于存储用户的答案。它有is_right用于快速查找，以查看特定的问题和答案选择是否正确(基于前面定义的is_right_choice)。它还有answer_time，只注意特定用户何时回答问题。

#2

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 Symfony for 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.

我个人在php项目中使用Symfony，我建议您查看他们的指南和教程。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 :)

希望帮助:)

#3

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违例:问题n。如果你做得对的话，你会根据它们的意思来命名这些列，而不仅仅是它们是哪个问题。但是规范化这实际上是迈向可扩展的表单的下一步，但是要跟踪历史。

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_answers above 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.

这在这一点上是很正常的。您可以看到，查询起来也有点困难。为了把所有的问题都解答出来，你必须从测验到问题。您可以从那里连接到答案上，执行一个大查询，以获得构建表单所需的所有数据(以及必须进行更多的后处理)，或者您可以为每个问题再访问一次数据库，减少后处理。我可以用任何一种方式进行辩论。要得到所有特定用户的答案，您必须从user_submission元素中选择这个问题的submission_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范围内的所有整数，那么它将使您更容易看到连接的工作方式。

#4

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

注::不要在数据库中存储密码，如上图所示——而是存储密码散列

#1