Database specification

1. Users
2. Questions
3. Quizzes
4. Categories
5. Quiz_questions
6. Participants
7. User_questions
8. Rooms
9. Room_participants
10. References between tables

1. Database Specification

We choose MySQL as our database management system. The database is hosted on AWS RDS. The database is designed with 9 tables: users, questions, categories, rooms, participants, quizzes, user_questions, quiz_questions, room_participants. The database diagram is shown below:

ER diagram

1. Users

DDL command for creating users table:

CREATE TABLE `users` (
    `id` VARCHAR(191) NOT NULL,
    `displayName` VARCHAR(191) NOT NULL,
    `password` VARCHAR(191) NULL,
    `email` VARCHAR(191) NOT NULL,
    `avatar` TEXT NOT NULL,
    `isLogin` BOOLEAN NOT NULL,
    `role` VARCHAR(191) NOT NULL,
    `status` INTEGER NOT NULL DEFAULT 1,
    `authId` VARCHAR(191) NULL,
    `loginFrom` VARCHAR(191) NULL,
    `token` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each user.
`displayName`	VARCHAR(191)		Display name of the user.
`password`	VARCHAR(191)		Password of the user.
`email`	VARCHAR(191)		Email of the user.
`avatar`	TEXT		Avatar of the user.
`isLogin`	BOOLEAN		Indicates whether the user is logged in.
`role`	VARCHAR(191)		Role of the user (admin, user).
`status`	INTEGER		User status (0: Inactive, 1: Active).
`authId`	VARCHAR(191)		Authentication ID of the user (if applicable).
`loginFrom`	VARCHAR(191)		Login from (if applicable).
`token`	TEXT		Token of the user (if applicable).
`createdAt`	DATETIME(3)		Date and time of user creation.
`updatedAt`	DATETIME(3)		Date and time of last user update.

2. Questions

DDL command for creating questions table:

CREATE TABLE `questions` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `categoryId` VARCHAR(191) NOT NULL,
    `title` TEXT NOT NULL,
    `options` TEXT NOT NULL,
    `answers` TEXT NOT NULL,
    `image` TEXT NULL,
    `type` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `questions` ADD CONSTRAINT `questions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `questions` ADD CONSTRAINT `questions_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each question.
`userId`	VARCHAR(191)	Foreign key	Identifier of the user who created the question .
`categoryId`	VARCHAR(191)	Foreign key	Identifier of the category to which the question belongs.
`title`	TEXT		Question's title.
`options`	TEXT		Options for the question.
`answers`	TEXT		Correct answers for the question.
`image`	TEXT		Image associated with the question.
`type`	INTEGER		Question type (MCQ: 0, SCQ: 1, Written: 2, True/False: 3).
`createdAt`	DATETIME(3)		Date and time of question creation.
`updatedAt`	DATETIME(3)		Date and time of last question update.

3. Quizzes

DDL command for creating quizzes table:

CREATE TABLE `quizzes` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `title` TEXT NOT NULL,
    `numberQuestions` INTEGER NOT NULL,
    `description` TEXT NOT NULL,
    `image` TEXT NOT NULL,
    `durationMins` INTEGER NOT NULL,
    `isRandom` BOOLEAN NOT NULL,
    `isRandomOption` BOOLEAN NOT NULL,
    `attempts` INTEGER NOT NULL,
    `point` INTEGER NOT NULL,
    `passingPoint` INTEGER NOT NULL,
    `passed` BOOLEAN NOT NULL,
    `difficultyLevel` INTEGER NOT NULL DEFAULT 0,
    `startDate` DATETIME(3) NOT NULL,
    `endDate` DATETIME(3) NOT NULL,
    `isActivated` BOOLEAN NOT NULL,
    `isShared` BOOLEAN NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `quizzes` ADD CONSTRAINT `quizzes_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each quiz.
`userId`	VARCHAR(191)	Foreign key	Identifier of the user who created the quiz.
`title`	TEXT		Quiz's title.
`numberQuestions`	INTEGER		Number of questions in the quiz.
`description`	TEXT		Quiz's description.
`image`	TEXT		Image associated with the quiz.
`durationMins`	INTEGER		Quiz's duration in minutes.
`isRandom`	BOOLEAN		Indicates whether the questions in the quiz are randomized.
`isRandomOption`	BOOLEAN		Indicates whether the options in the questions are randomized.
`attempts`	INTEGER		Number of attempts allowed for the quiz.
`point`	INTEGER		Total points for the quiz.
`passingPoint`	INTEGER		Passing point for the quiz.
`passed`	BOOLEAN		Indicates whether the user has passed the quiz.
`difficultyLevel`	INTEGER		Difficulty level of the quiz (0: Easy, 1: Medium, 2: Hard).
`startDate`	DATETIME(3)		Date and time when the quiz starts.
`endDate`	DATETIME(3)		Date and time when the quiz ends.
`isActivated`	BOOLEAN		Indicates whether the quiz is activated.
`isShared`	BOOLEAN		Indicates whether the quiz is shared.
`createdAt`	DATETIME(3)		Date and time of quiz creation.
`updatedAt`	DATETIME(3)		Date and time of last quiz update.

4. Categories

DDL command for creating categories table:

CREATE TABLE `categories` (
    `id` VARCHAR(191) NOT NULL,
    `parentId` VARCHAR(191) NULL,
    `name` VARCHAR(191) NOT NULL,
    `icon` TEXT NOT NULL,
    `status` INTEGER NOT NULL DEFAULT 1,
    `sortOrder` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each category.
`parentId`	VARCHAR(191)	Foreign key	Identifier of the parent category (if applicable).
`name`	VARCHAR(191)		Category's name.
`icon`	TEXT		Icon associated with the category.
`status`	INTEGER		Category status (0: Inactive, 1: Active).
`sortOrder`	INTEGER		Sort order for the category.
`createdAt`	DATETIME(3)		Date and time of category creation.
`updatedAt`	DATETIME(3)		Date and time of last category update.

5. Quiz_questions

DDL command for creating quiz_questions table:

CREATE TABLE `quiz_questions` (
    `id` VARCHAR(191) NOT NULL,
    `quizId` VARCHAR(191) NOT NULL,
    `questionId` VARCHAR(191) NOT NULL,
    `sortOrder` INTEGER NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `quiz_questions` ADD CONSTRAINT `quiz_questions_quizId_fkey` FOREIGN KEY (`quizId`) REFERENCES `quizzes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `quiz_questions` ADD CONSTRAINT `quiz_questions_questionId_fkey` FOREIGN KEY (`questionId`) REFERENCES `questions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each quiz-question relationship.
`quizId`	VARCHAR(191)	Foreign key	Identifier of the quiz.
`questionId`	VARCHAR(191)	Foreign key	Identifier of the question.
`sortOrder`	INTEGER		Sort order for the question in the quiz.

6. Participants

DDL command for creating participants table:

CREATE TABLE `participants` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `quizId` VARCHAR(191) NOT NULL,
    `questions` INTEGER NULL,
    `correct` INTEGER NULL,
    `totalAttempt` INTEGER NOT NULL DEFAULT 1,
    `point` INTEGER NOT NULL,
    `startedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `completedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `participants` ADD CONSTRAINT `participants_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `participants` ADD CONSTRAINT `participants_quizId_fkey` FOREIGN KEY (`quizId`) REFERENCES `quizzes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each participant.
`userId`	VARCHAR(191)	Foreign key	Identifier of the user.
`quizId`	VARCHAR(191)	Foreign key	Identifier of the quiz.
`questions`	INTEGER		Number of questions answered by the participant.
`correct`	INTEGER		Number of correct answers given by the participant.
`totalAttempt`	INTEGER		Total number of attempts made by the participant.
`point`	INTEGER		Total points earned by the participant.
`startedAt`	DATETIME(3)		Date and time when the participant started the quiz.
`completedAt`	DATETIME(3)		Date and time when the participant completed the quiz.
`createdAt`	DATETIME(3)		Date and time of participant creation.
`updatedAt`	DATETIME(3)		Date and time of last participant update.

7. User_questions

DDL command for creating user_questions table:

CREATE TABLE `user_questions` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `participantId` VARCHAR(191) NOT NULL,
    `questionId` VARCHAR(191) NOT NULL,
    `question` VARCHAR(191) NOT NULL,
    `image` TEXT NULL,
    `options` TEXT NOT NULL,
    `correct` TEXT NOT NULL,
    `givenAnswers` TEXT NOT NULL,
    `score` INTEGER NOT NULL,
    `timestamp` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `user_questions` ADD CONSTRAINT `user_questions_participantId_fkey` FOREIGN KEY (`participantId`) REFERENCES `participants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `user_questions` ADD CONSTRAINT `user_questions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `user_questions` ADD CONSTRAINT `user_questions_questionId_fkey` FOREIGN KEY (`questionId`) REFERENCES `questions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each user-question relationship.
`userId`	VARCHAR(191)	Foreign key	Identifier of the user.
`participantId`	VARCHAR(191)	Foreign key	Identifier of the participant.
`questionId`	VARCHAR(191)	Foreign key	Identifier of the question.
`question`	VARCHAR(191)		Question's title.
`image`	TEXT		Image associated with the question.
`options`	TEXT		Options for the question.
`correct`	TEXT		Correct answers for the question.
`givenAnswers`	TEXT		Answers given by the user.
`score`	INTEGER		Score earned by the user for the question.
`timestamp`	DATETIME(3)		Date and time when the user answered the question.

8. Rooms

DDL command for creating rooms table:

CREATE TABLE `rooms` (
    `id` VARCHAR(191) NOT NULL,
    `PIN` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `quizId` VARCHAR(191) NOT NULL,
    `count` INTEGER NOT NULL,
    `isStarted` BOOLEAN NOT NULL,
    `isPublic` BOOLEAN NOT NULL,
    `type` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `rooms` ADD CONSTRAINT `rooms_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `rooms` ADD CONSTRAINT `rooms_quizId_fkey` FOREIGN KEY (`quizId`) REFERENCES `quizzes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each room.
`userId`	VARCHAR(191)	Foreign key	Identifier of the user who created the room.
`quizId`	VARCHAR(191)	Foreign key	Identifier of the quiz.
`PIN`	VARCHAR(191)		PIN of the room.
`count`	INTEGER		Maximum number of participants in the room.
`isStarted`	BOOLEAN		Indicates whether the room has started.
`isPublic`	BOOLEAN		Indicates whether the room is public.
`type`	INTEGER		Room type (0: Solo, 1: Group).
`createdAt`	DATETIME(3)		Date and time of room creation.

9. Room_participants

DDL command for creating room_participants table:

CREATE TABLE `room_participants` (
    `id` VARCHAR(191) NOT NULL,
    `roomId` VARCHAR(191) NOT NULL,
    `participantId` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `room_participants` ADD CONSTRAINT `room_participants_participantId_fkey` FOREIGN KEY (`participantId`) REFERENCES `participants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `room_participants` ADD CONSTRAINT `room_participants_roomId_fkey` FOREIGN KEY (`roomId`) REFERENCES `rooms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Columns description:

Column	Type	Key	Description
`id`	VARCHAR(191)	Primary key	Unique identifier for each room-participant relationship.
`roomId`	VARCHAR(191)	Foreign key	Identifier of the room.
`participantId`	VARCHAR(191)	Foreign key	Identifier of the participant.

10. References between tables

DDL command for adding foreign key constraints between tables:

ALTER TABLE `questions` ADD CONSTRAINT `questions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `questions` ADD CONSTRAINT `questions_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `quizzes` ADD CONSTRAINT `quizzes_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `quiz_questions` ADD CONSTRAINT `quiz_questions_quizId_fkey` FOREIGN KEY (`quizId`) REFERENCES `quizzes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `quiz_questions` ADD CONSTRAINT `quiz_questions_questionId_fkey` FOREIGN KEY (`questionId`) REFERENCES `questions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `participants` ADD CONSTRAINT `participants_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `participants` ADD CONSTRAINT `participants_quizId_fkey` FOREIGN KEY (`quizId`) REFERENCES `quizzes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_questions` ADD CONSTRAINT `user_questions_participantId_fkey` FOREIGN KEY (`participantId`) REFERENCES `participants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_questions` ADD CONSTRAINT `user_questions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_questions` ADD CONSTRAINT `user_questions_questionId_fkey` FOREIGN KEY (`questionId`) REFERENCES `questions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `rooms` ADD CONSTRAINT `rooms_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `rooms` ADD CONSTRAINT `rooms_quizId_fkey` FOREIGN KEY (`quizId`) REFERENCES `quizzes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `room_participants` ADD CONSTRAINT `room_participants_participantId_fkey` FOREIGN KEY (`participantId`) REFERENCES `participants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `room_participants` ADD CONSTRAINT `room_participants_roomId_fkey` FOREIGN KEY (`roomId`) REFERENCES `rooms`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

Foreign key constraints description:

Table name	Column name	Referenced table	Referenced column
`questions`	`userId`	`users`	`id`
`questions`	`categoryId`	`categories`	`id`
`quizzes`	`userId`	`users`	`id`
`quiz_questions`	`quizId`	`quizzes`	`id`
`quiz_questions`	`questionId`	`questions`	`id`
`participants`	`userId`	`users`	`id`
`participants`	`quizId`	`quizzes`	`id`
`user_questions`	`participantId`	`participants`	`id`
`user_questions`	`userId`	`users`	`id`
`user_questions`	`questionId`	`questions`	`id`
`rooms`	`userId`	`users`	`id`
`rooms`	`quizId`	`quizzes`	`id`
`room_participants`	`participantId`	`participants`	`id`
`room_participants`	`roomId`	`rooms`	`id`

Navigation Menu

Uh oh!

Database specification

1. Database Specification

1. Users

2. Questions

3. Quizzes

4. Categories

5. Quiz_questions

6. Participants

7. User_questions

8. Rooms

9. Room_participants

10. References between tables

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally