I'm currently working on a quiz system and am trying to make the database design first. I've read some threads about different quizzes but they are all unique on some parts.
The system will use a MySQL database with PHP.
The goals of this quiz system:
- Be able to have different "quizzes" inside it
- Timetrack on the full quiz and for each question (thinking ahead for a challenge part between people)
- Be able to handle a lot of participants in the quiz
- Have a good base so it can be developed further with new, more complex functions without the need to rebuild the whole system (the database design that is).
Image of the database design so far. I've written the field on each "relationship".
I'm quite sure most of it is mostly self-explanatory, but I've added notes on the less obvious ones.
In the start at least it won't be any "live" contest. More likely that you do the quiz, get the time for it and then you can send a link to a friend and compete. But I'm still thinking a bit for that part.
I realise that with this design it's a bit more work to get the complete results etc than a basic quiz design where you put everything together in a table or two. But I try to divide it into different tables to be able to scale it better and make it more complex for later.
I'm thinking about adding some kind of support so a question can be used in different quizzes as well. Just thinking about how I should do it best.
A key feature in this is also to be able to load everything fast.
Any tip is welcome!
wp_quiz (Here we store all the different quizzes)
- quiz_id
- quiz_name
- quiz_text
- quiz_site (internal thingy, nothing important for the quiz itself)
- quiz_difficulty
wp_quiz_questions
- quiz_question_id
- quiz_id
- quiz_question
- quiz_question_difficulty
- quiz_question_multianswer (In case it's a special question that has more than one correct answer, then we use for example selects intead of radiobuttons).
wp_quiz_question_answers
- quiz_question_answer_id
- quiz_question_id
- quiz_question_answer
- quiz_question_answer_correct
wp_quiz_participants
- quiz_participant_id
- quiz_id
- quiz_participant_name
- quiz_participant_email
- quiz_participant_userid
- quiz_participant_time_start
- quiz_participant_time_end
wp_quiz_participant_answers
- quiz_participant_answers_id
- quiz_id
- quiz_question_id
- quiz_participants_id
- quiz_question_answer_id
- quiz_question_time_start
- quiz_question_time_end
Best Answer
Pretty good overall, only a few minor changes I would suggest:
quiz_id is redundant in the wp_quiz_participant_answers table, you can get it via JOINs to either the wp_quiz_questions or wp_quiz_participants tables
If you have any plans to allow something resembling a login or ability to keep taking quizzes as the same user you should probably break Users into their own table that wp_quiz_participants references. Kinda looks like you might be thinking along those lines with the quiz_participant_userid?
Performance is going to be based around your queries, pay close attention to the performance and add indexing when necessary.