5

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!

databasedesign

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
  • 3
    I can promise you that its not homework, its a project I'm building. And I've never went to any programming school at all :) – user2957788 Feb 29 '16 at 19:34
1

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.

  • Hello, and thanks for your answer. English aint my main language and I'm not that used to every database terms. Haveing abit problem understanding how you mean in your first dot. But I think you mean that its a unnecessary and now when I look at it I agree! Yeah I forgot to mention that, I'm planning to allow people be logged in into our current "community"-like system. Thats why I added the userid. So that system is already built and up running with around 50k+ users. Thanks for your toughts! – user2957788 Feb 29 '16 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.