[Spec DB] Database

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

Database Management System (DBMS)

Freshemy use MySql to store and management data.

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

Data items

• User

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)
dob	User's day of birth	date
created_at	Day of creating account	date
update_at	Update's day inf (password)	date
url_avatar	Url of avatar	text
description	User's description. This field default is blank. Users can write this when creating an account.	text

Rating

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
content	Comment about this course	text
created_at	Day of create 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

Categories

Associated Data	Description	Format
id (PK)	Identifier code	int
title	Title for the course: "Life", "Sport",...	varchar(100)

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

sections

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

Lesson

Associated Data	Description	Format
id (PK)	Identifier code	int
section_id (FK)	section id 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