Skip to content

[Spec DB] Database

Jump to bottom
Nguyen Bui Thien Dat edited this page Jul 12, 2023 · 15 revisions

Database

Purpose

The database is used to store all Freshemy data. More specifically, the database stores the following entity:

  • users
  • ratings
  • enrolled
  • categories
  • courses
  • courses_categories
  • sections
  • lessons

zzzz drawio

Database Management System (DBMS)

Freshemy uses MySql to store and manage data.

  • MySql is a SQL DBMS
  • MySql is a relational database
  • MySql stores data entities with tables and records

Data items

  • users
Associated Data Description Format
id (PK) Identifier code int
first_name User's first name varchar(25)
last_name User's last name varchar(25)
email User's email varchar(50)
password User's password text
dob User's day of birth date
created_at Day of creating account date
updated_at Update's day inf (password) date
url_avatar Url of avatar text
token Store token to verify Email and forget password text
is_verify data field checks that the user has verified his email or not Boolean
description User's description. This field default is blank. Users can write this when creating an account. text

ratings

Associated Data Description Format
id (PK) Identifier code AI int
course_id (FK) Id of the assessed course Int
user_id (FK) Id of user create the rating Int
score User's course ratings Int
content Comment about this course text
created_at Day of create this rating date
updated_at Day of update this rating date

enrolled

Associated Data Description Format
id (PK) Identifier code int
user_id (FK) User id enrolled this course int
course_id (FK) Course id was enrolled int
created_at Day when user was enrolled date
updated_at Day when user was update enrolled date

categories

Associated Data Description Format
id (PK) Identifier code int
title Title for the course: "Life", "Sport",... varchar(100)
created_at Day of create this category date
updated_at Day of update this category date

courses

Associated Data Description Format
id (PK) Identifier code int
user_id (FK) Id of the user who created this course int
slug Slug for video varchar(110)
tille title for course varchar(100)
summary Summary about this course varchar(255)
url_thumbnail Url for thumbnail of video varchar(100)
description More detail about this course text
is_delete Var to show soft delete Boolean (soft delete: 1), (default : 0)
status Status of course: Boolean (finish: 1), (updating : 0)
created_at Day of creating this course date
updated_at Day of update this course date

courses_categories

Associated Data Description Format
id (PK) Identifier code int
course_id (FK) Course id have category_id int
category_id (FK) Category id was assign for course_id int
created_at Day of create course date
updated_at Day of update course date

sections

Associated Data Description Format
id (PK) Identifier code int
course_id (FK) Course id include this Sections int
title Title of Sections varchar(100)
is_delete Var to show soft delete Boolean (soft delete: 1), (default : 0)
created_at Day of create this Sections date
updated_at Day of updating this Sections date

lessons

Associated Data Description Format
id (PK) Identifier code int
section_id (FK) Sectionsid include this lesson int
title title for lesson varchar(100)
is_deleted Var to show soft delete Boolean (soft delete: 1), (default : 0)
url_video Url of video in lesson varchar(100)
created_at Day of create this lesson date
updated_at Day of updating this lesson date
Clone this wiki locally