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

This was also the first project I did in PHP/MySQL about 8 years ago.

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.

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.

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:

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.

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.

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.

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.

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 :)