将测试结果保存到数据库：保持数据库整洁[关闭]

Question

I've created a test system using Wordpress, jQuery and PHP which is working great. The area that I don't have much experience in is mysql databases.

I'm not really sure how to save the results in a neat and tidy way.

The things I want to save are the following:

1. Users name.
2. Test name.
3. Questions.
4. Answers.
5. Score.
6. Pass/fail.

Points 1, 2, 5 & 6 are fairly straight forward to save the data. The confusion comes with the questions and answers.

How can I save these in a neat way to the mysql database?

Say there are 10 questions, each with multiple choice answers, how can I save each of those in the database but have it linked to the user that filled them in? I'm not sure how to keep the database neat and this could quickly spiral out of control.

In the future I may want to do some searching on the those questions and answers.

Answer 1

You need to normalize your database structure.

**Table: users**
id - int
username - varchar
password - varchar
active - enum (y,n)

**Table: questions**
id - int
question - varchar/text

**Table: answers**
id - int
question_id - int (foreign key questions.id)
answer - varchar/text
is_correct_anser - enum (y, n)

**Table: test_master**
id - int
test_name - varchar/text
pass_score - int (minimum score required to pass the test)

**Table: test**
user_id - int (foreign key to users.id)
test_id - int (foreign key to test_master.id)
question_id - int (foreign key to questions.id)
answer_id - int (foreign key to answers.id)

Based on above tables, you can store all required information in in normalized way. Please note that, normalization will not always help and it depends on your application's requirement. It is also a better ideal to store total score in a separate table along with user_id and test_id once test is completed as this will avoid joins on multiple table.