CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designdatabase-schema
Zestyy99
Zestyy99

Reputation: 307

Quiz database design

Hi i'm working on a quiz system and i'm stuck on the last table user_quiz on how to link it to other tables? I want it to keep track of the score the user got on the quiz and also the total number of quizzes that the user has taken.

The userid is the primary key, but im stuck on what the foreign key should be, so that I could link the tables together. I have also been trying to normalize my database, so if anyone could help me with what the foreign key should be, and if the database isn't normalised, could help me normalise it. Thank you:)

Users

    +-----------+-------------+-----------+-------------+
    |userid     | email       |username   |password     |
    +-----------+-------------+-----------+-------------+
    |  1        |             |           |             |
    |  2        |             |           |             |
    +-----------+-------------+-----------+-------------+


    Module
    +-----------+-------------+
    | module_id | module_name |
    +-----------+-------------+
    |  1        | Forces      |
    |  2        | Magnetism   |
    |  3        | Electricity |
    |  4        | Gravitation |
    +-----------+-------------+

    Questions
    +------------+-----------+-----------------+
    | question_id| module_id | question        |
    +------------+-----------+-----------------+
    |  1         | 1         | Define a Newton |
    |  2         | 1         | Define Work Done|
    |  3         | 2         | Define Magnetism|
    |  4         | 2         | Define a Tesla  |
    |  5         | 3         | Define Current  |
    +------------+-----------+-----------------+
    Choices
    +----------+-------------+-------------------+----------+
    | choice_id| question_id | choices           |is_correct|
    +----------+-------------+-------------------+----------+
    |  1       | 1           | Answer 1          |  0       |
    |  2       | 1           | Answer 2          |  0       |
    |  3       | 1           | Answer 3          |  0       |
    |  4       | 1           | Answer 4          |  1       |
    |  5       | 2           | Answer 1          |  0       |
    |  6       | 2           | Answer 2          |  0       |
    |  7       | 2           | Answer 3          |  0       |
    |  8       | 2           | Answer 4          |  1       |
    |  9       | 3           | Answer 1          |  0       |
    |  10      | 3           | Answer 2          |  0       |
    |  11      | 3           | Answer 3          |  0       |
    |  12      | 3           | Answer 4          |  1       |
    +----------+-------------+-------------------+----------+

    user_quiz
    +-----------+-------------+---------------------+------------+
    |userid     |  score      | total_quizzes_taken |     ?      |
    +-----------+-------------+---------------------|------------+
    |  1        |             |                     |            |
    |  2        |             |                     |            |
    +-----------+-------------+---------------------+------------+

Upvotes: 3

Views: 2522

Answers (1)

Rick James
Rick James

Reputation: 142528

  • A FOREIGN KEY is not required for linking two tables.
  • The total number of quizzes is computable, don't store it.
  • UserQuiz is a many:many mapping table? Then it has user_id, quiz_id and score. See this for optimal schema a many:many table.

Number of quizzes taken by user_id = 123:

SELECT COUNT(*)
    FROM UserQuiz
    WHERE user_id = 123;

But there is a potential problem. I assume entries are added as Questions are answered and graded? That COUNT will probably give you the number of quizzes started, not completed. If that is not satisfactory, you need to explain how you know that a quiz is completed.

Upvotes: 1

Related Questions