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

Question

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.

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.

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.

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

Answer 1

I would start with 4 simple tables:

Users

- user_id        auto integer - regtime        datetime - username       varchar - useremail      varchar - userpass       varchar 

Questions

- question_id    auto integer - question       varchar - is_active      enum(0,1) 

Question_choices

- choice_id        auto integer - question_id      Questions.question_id - is_right_choice  enum(0,1) - choice           varchar 

User_question_answers

- user_id        Users.user_id - question_id    Questions.question_id - choice_id      Question_choices.choice.id - is_right       enum(0,1) - answer_time    datetime 

My thought on this table design is:

• table Users is for storing registered user.
• table Questions is 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_choices is for storing all available options. It has is_right_choice which defines what choice is the right answer for particular question.
• Table User_question_answers 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.