The backend uses TypeORM to handle setting up the database. This document provides an overview of the database schema setup by TypeORM.
mockData.sql contains some mock data used to populate the database for testing and demo purposes.
This table keeps track of users registered with the system and how they have interacted with other objects.
| Name | Type | Description | Other |
|---|---|---|---|
| userId | string | The user's id. Generated by Auth0 and is unique for each user in the Auth0 tenant. | Primary Key |
| role | number | The user's role. 0 is a regular user and 1 is an admin. | |
| picture | string | A url to the user's profile picture. Taken from Auth0 the first time a user logs in. | |
| rated | json | A json blob containing a list of course ratings done by the user, where the rating has the format{ courseCode: string, stars: number }. |
|
| upvoted | number[] | A list of comment ids upvoted by the user. | |
| downvoted | number[] | A list of comment ids downvoted by the user. |
This table keeps track of the courses available in the system.
| Name | Type | Description | Other |
|---|---|---|---|
| courseCode | string | The shortened code for a course i.e. CSSE6400. | Primary Key |
| courseName | string | The name of the course i.e. Software Architecture. | |
| courseDescription | string | A description for the course. | |
| university | string | The university that runs the course. | |
| stars | number | The current rating of the course. | |
| votes | number | The number of ratings a course has received. |
This table keeps track of the exams available in the system.
| Name | Type | Description | Other |
|---|---|---|---|
| courseCode | string | The course code of the course the exam belongs to. | Foreign Key course.courseCode |
| examId | number | A unique id for the exam. | Primary Key |
| examYear | number | The year the exam was available. | |
| examSemester | number | The semester the exam was available. 1 is semester 1, 2 is semester 2 and 3 is summer semester. | |
| examType | string | The type of exam i.e. Final, Midsem, Quiz, etc. |
This table keeps track of the questions available in the system.
| Name | Type | Description | Other |
|---|---|---|---|
| examId | number | The exam id of the exam the question belongs to. | Foreign Key exam.examId |
| questionId | number | A unique id for the question. | Primary Key |
| questionText | string | The question's textual content. | |
| questionPNG | string | The url to an image uploaded to accompany the question. | |
| questionType | string | The type of question i.e. Short Answer, Multiple Choice, etc. | |
| createdAt | timestamp | When the question was created in the system. | |
| updatedAt | timestamp | When the question was updated in the system. |
This table keeps track of the comments available in the system.
| Name | Type | Description | Other |
|---|---|---|---|
| questionId | number | The question id of the question the comment belongs to. | Foreign Key question.questionId |
| userId | string | The user id of the user who made the comment. | Foreign Key user.userId |
| commentId | number | A unique id for the comment. | Primary Key |
| parentCommentId | number | The comment id of the parent, if any. | |
| commentText | string | The comment's textual content. | |
| commentPNG | string | The url to an image uploaded to accompany the comment. | |
| isCorrect | boolean | Whether the question has been verified by course staff. | |
| isDeleted | boolean | Whether the comment has been deleted. | |
| upvotes | number | The number of upvotes the comment has. | |
| downvotes | number | The number of downvotes the comment has. | |
| createdAt | timestamp | When the comment was created in the system. | |
| updatedAt | timestamp | When the comment was updated in the system. |