I'm currently working on a project where I'm essentially creating a quiz application. It will have the ability to take quizzes that have either multiple choice or short answer questions, between 10-20 questions. It needs to have the ability to check the user's answers against the correct answers, and then keep score of the user's answers.
Later on, I may implement a back-end feature to create quizzes all online, but for now I'll leave that up to directly working with the database.
Now for my question.. I'm pretty lost on how to design my database and tables to allow for all the features. Here is how I currently have it set up:
Quizzes Table
Questions Table
Answers Table
So each quiz will have its own id, then each question will have its own id as well as its parent quiz id, and then the answers will have a parent quiz id and a parent question id.
As of right now, I have the "correct answer" problem functioning this way: If they choose the correct answer, the if_correct_answer row will have a value greater than 0, then at the end the value of all their answers will be added up and that's how many they got right.
Is this a good way to design my database? I saw the basic idea on another post here, but I'm not sure if I fully understand it or am using it correctly. I basically just end up confused about how to coordinate all the different ids. (I'm a beginner to mySQL and PHP, which probably doesn't help)
Your table structure looks good to me. You need another table to store the user's answer as well.
user's answer table - Rough structure
And you need yet another table to store the basic info of user like name, user_id, phone, etc.
Just an idea, you could make a linking table for questions to quizzes, then you can build up a collection of questions that could be used to build new quizzes. Rough outline:
Quizzes Table
Question To Quiz Table
Questions Table
Answers Table
user's answer table - Rough structure