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 tables:
* 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 thought on this table design is:
- table User is for storing registered user.
- table Question 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_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.
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.
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 :)
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.
P.S.: do not store passwords in database, like in the picture above - store passwords hashes instead