Guide To Design Database For Quiz In MySQL

Guide To Design Database For Quiz In MySQL

A complete guide to designing a database in MySQL for quiz based websites and applications. It also covers the tracking of user activities to declare the quiz results.

December 29, 2019

This tutorial provides complete steps to design a database schema of online tests and quiz systems to manage the users, quizzes, questions, answers, and takes. It can be further used to develop online testing or quiz based websites or applications.

The Entity Relationship Diagram or visual database design is shown below.

Quiz Database Design

Fig 1

Notes: It restricts to only logged in users to take the quiz to avoid spamming. The quizzes are considered as short as compared to tests.

You can also visit the popular tutorials including How To Install MySQL 8 on Ubuntu, How To Install MySQL 8 on Windows, RBAC Database in MySql, Blog Database in MySql, Learn Basic SQL Queries In MySQL.

Quiz Database

The very first step is to create the Quiz Database. It can be created using the query as shown below.

CREATE SCHEMA `quiz` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I have used the character set utf8mb4 to support a wide range of characters.

User Table

In this section, we will design the User Table to store user information. The same table can be used to relate the quiz hosts so that the users can manage their own quizzes and track the takes. Below mentioned is the description of all the columns of the User Table.

Id The unique id to identify the user.
First Name The first name of the user.
Middle Name The middle name of the user.
Last Name The last name of the user.
Mobile The mobile number of the user. It can be used for login and registration purposes.
Email The email of the user. It can be used for login and registration purposes.
Password Hash The password hash generated by the appropriate algorithm. We must avoid storing plain passwords.
Host The flag to identify whether the user can host a quiz.
Registered At This column can be used to calculate the life of the user with the application.
Last Login It can be used to identify the last login of the user.
Intro The brief introduction of the Host User to be displayed on the Test or Quiz Page.
Profile The owner details to be displayed on the Test or Quiz Page.

The User Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `passwordHash` VARCHAR(32) NOT NULL,
  `host` TINYINT(1) NOT NULL DEFAULT 0,
  `registeredAt` DATETIME NOT NULL,
  `lastLogin` DATETIME NULL DEFAULT NULL,
  `intro` TINYTEXT NULL DEFAULT NULL,
  `profile` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_mobile` (`mobile` ASC),
  UNIQUE INDEX `uq_email` (`email` ASC) );

Quiz Table

In this section, we will design the Quiz Table to store the quiz data. Below mentioned is the description of all the columns of the Quiz Table.

Id The unique id to identify the quiz.
Host Id The host id to identify the quiz host.
Title The quiz title to be displayed on the Quiz Page and the lists.
Meta Title The meta title to be used for browser title and SEO.
Slug The slug to form the URL.
Summary The summary to mention the key highlights.
Type The type to distinguish between the test and the quiz.
Score The total score of the quiz.
Published It can be used to identify whether the test/quiz is publicly available.
Created At It stores the date and time at which the test/quiz is created.
Updated At It stores the date and time at which the test/quiz is updated.
Published At It stores the date and time at which the test/quiz is published.
Starts At It stores the date and time at which the test/quiz starts and opens up for takes.
Ends At It stores the date and time at which the test/quiz closes for takes.
Content The column used to store the test/quiz data.

The Quiz Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`quiz` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `hostId` BIGINT NOT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL,
  `slug` VARCHAR(100) NOT NULL,
  `summary` TINYTEXT NULL,
  `type` SMALLINT(6) NOT NULL DEFAULT 0,
  `score` SMALLINT(6) NOT NULL DEFAULT 0,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `publishedAt` DATETIME NULL DEFAULT NULL,
  `startsAt` DATETIME NULL DEFAULT NULL,
  `endsAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC),
  INDEX `idx_quiz_host` (`hostId` ASC),
  CONSTRAINT `fk_quiz_host`
    FOREIGN KEY (`hostId`)
    REFERENCES `quiz`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Quiz Meta

The Quiz Meta Table can be used to store additional information of tests or quiz including the quiz banner URL etc. Below mentioned is the description of all the columns of the Quiz Meta Table.

Id The unique id to identify the quiz meta.
Quiz Id The quiz id to identify the parent test/quiz.
Key The key identifying the meta.
Content The column used to store the quiz metadata.

The Quiz Meta Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`quiz_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `quizId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_quiz` (`quizId` ASC),
  UNIQUE INDEX `uq_quiz_meta` (`quizId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_quiz`
    FOREIGN KEY (`quizId`)
    REFERENCES `quiz`.`quiz` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Quiz Question Table

The Quiz Question Table can be used to store the questions related to tests and quizzes. Below mentioned is the description of all the columns of the Quiz Question Table.

Id The unique id to identify the quiz question.
Quiz Id The quiz id to identify the parent test/quiz.
Type The type of question. The type can be a single choice(Yes/No), multiple-choice or select. We can also have type as input and textarea in case the quiz result need manual checks.
Active Flag to identify whether the question is active. A quiz can have several questions, but only selective questions remain active at a time.
Level The level of the question to identify whether it's easy, medium, or difficult.
Score The score of an individual question. We should make sure that only selective questions are active at a time and the total score of the active questions is equal to the quiz score before publishing the quiz.
Created At It stores the date and time at which the question is created.
Updated At It stores the date and time at which the question is updated.
Content The column used to store the question.

The Quiz Question Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`quiz_question` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `quizId` BIGINT NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `level` SMALLINT(6) NOT NULL DEFAULT 0,
  `score` SMALLINT(6) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_question_quiz` (`quizId` ASC),
  CONSTRAINT `fk_question_quiz`
    FOREIGN KEY (`quizId`)
    REFERENCES `quiz`.`quiz` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Quiz Answer Table

The Quiz Answer Table can be used to store the answers of single-choice, multiple-choice and select type questions. In the case of a single-choice question, the answers can be Yes and No. Below mentioned is the description of all the columns of the Quiz Answer Table.

Id The unique id to identify the quiz answer.
Quiz Id The quiz id to identify the parent test/quiz.
Question Id The question id to identify the parent question.
Active Flag to identify whether the answer is active.
Correct Flag to identify whether the answer is correct.
Created At It stores the date and time at which the answer is created.
Updated At It stores the date and time at which the answer is updated.
Content The column used to store the answer.

The Quiz Answer Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`quiz_answer` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `quizId` BIGINT NOT NULL,
  `questionId` BIGINT NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `correct` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_answer_quiz` (`quizId` ASC),
  CONSTRAINT `fk_answer_quiz`
    FOREIGN KEY (`quizId`)
    REFERENCES `quiz`.`quiz` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `quiz`.`quiz_answer` 
ADD INDEX `idx_answer_question` (`questionId` ASC);
ALTER TABLE `quiz`.`quiz_answer` 
ADD CONSTRAINT `fk_answer_question`
  FOREIGN KEY (`questionId`)
  REFERENCES `quiz`.`quiz_question` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Take Table

In this section, we will design the Take Table to track the enrollment and timing of user attempts to the quizzes. Below mentioned is the description of all the columns of the Take Table.

Id The unique id to identify the take.
User Id The user id to identify the quiz taker.
Quiz Id The quiz id to identify the quiz.
Status The status of the take. It can be enrolled, started, paused, finished, declared.
Score The total score obtained by the user.
Created At It stores the date and time at which the take is created.
Updated At It stores the date and time at which the take is updated.
Started At It stores the date and time at which the take started.
Finished At It stores the date and time at which the take is finished.
Content The column used to store the take remarks.

The Take Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`take` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NOT NULL,
  `quizId` BIGINT NOT NULL,
  `status` SMALLINT(6) NOT NULL DEFAULT 0,
  `score` SMALLINT(6) NOT NULL DEFAULT 0,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `startedAt` DATETIME NULL DEFAULT NULL,
  `finishedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_take_user` (`userId` ASC),
  CONSTRAINT `fk_take_user`
    FOREIGN KEY (`userId`)
    REFERENCES `quiz`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `quiz`.`take` 
ADD INDEX `idx_take_quiz` (`quizId` ASC);
ALTER TABLE `quiz`.`take` 
ADD CONSTRAINT `fk_take_quiz`
  FOREIGN KEY (`quizId`)
  REFERENCES `quiz`.`quiz` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Take Answer Table

The Take Answer Table can be used to store the answers selected by the user while taking the quiz. In the case of a multiple-choice question, there can be multiple answers. Below mentioned is the description of all the columns of the Take Answer Table.

Id The unique id to identify the take answer.
Take Id The take id to identify the quiz attempt.
Answer Id The answer id to identify the quiz answer.
Active Flag to identify whether the answer is active.
Created At It stores the date and time at which the answer is created.
Updated At It stores the date and time at which the answer is updated.
Content The column used to store the answer in case of input or textarea type of questions.

The Take Answer Table with the appropriate constraints is as shown below.

CREATE TABLE `quiz`.`take_answer` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `takeId` BIGINT NOT NULL,
  `questionId` BIGINT NOT NULL,
  `answerId` BIGINT NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_answer_take` (`takeId` ASC),
  CONSTRAINT `fk_answer_take`
    FOREIGN KEY (`takeId`)
    REFERENCES `quiz`.`take` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE `quiz`.`take_answer` 
ADD INDEX `idx_tanswer_question` (`questionId` ASC);
ALTER TABLE `quiz`.`take_answer` 
ADD CONSTRAINT `fk_tanswer_question`
  FOREIGN KEY (`questionId`)
  REFERENCES `quiz`.`quiz_question` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `quiz`.`take_answer` 
ADD INDEX `idx_tanswer_answer` (`answerId` ASC);
ALTER TABLE `quiz`.`take_answer` 
ADD CONSTRAINT `fk_tanswer_answer`
  FOREIGN KEY (`answerId`)
  REFERENCES `quiz`.`quiz_answer` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Summary

In this tutorial, we have discussed the database design of a Quiz system to store the users, quizzes, questions, answers, and quiz attempts in the form of takes.

You may submit your comments to join the discussion. You may also be interested in designing the database of the Blog and Poll & Survey applications.

The complete database schema is also available on GitHub.

Write a Comment
Click the captcha image to get new code.
Discussion Forum by DISQUS
Recent Posts
How to store pictures in MySQL database
Guide To Design Database For Task Manager In MySQL
Guide To Design Database For Social Network System In MySQL
Guide To Design Database For Employee Management System In MySQL
Guide To Design Database For Notifications In MySQL