Database Design For Developing ‘Quiz’ Web Application using PHP and MySQL

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

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.