Quiz database design 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
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.