Abundnce10 Abundnce10 - 12 days ago 4x
MySQL Question

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!


I would start with 4 simple table:

 * 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')
  • 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.
  • 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.