Database Tables

The database is deployed at

http://cpanel.speedlabspowai.in/phpmyadmin/index.php?route=/database/structure&server=1&db=qjzcohmy_ekart.

Database table attributes their datatypes and constraints are given below.

Pre-requisites

• Xampp Stack/LAMP Stack installed on localhost.
• Mysql 8.1 version installed on localhost.

NOTE: Create tables in the same order as mentioned below.

Database Tables

Table: institute_details

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
name	varchar(300)	NOT NULL
city	varchar(20)	NOT NULL
email	varchar(62)	NOT NULL, UNIQUE

Table: user_detail

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
user_hash	varchar(255)	NOT NULL
user_inst_id	int
user_college_id	int
user_email	varchar(255)	NOT NULL, UNIQUE
user_first_name	varchar(255)
user_last_name	varchar(255)
zip_code	int	NOT NULL

Table: user_carts

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
user_id	user_id	NOT NULL, REFERENCE user_detail(id)
total_amt	decimal(8,2)	NOT NULL
last_modified	datetime	NOT NULL, current_timestamp(), ON UPDATE CURRENT_TIMESTAMP()
created_at	datetime	NOT NULL, CURRENT_TIMESTAMP()

Table: cart_contents

Attribute Name	Datatype	Constraints
cart_id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
product_id	int	PRIMARY KEY, NOT NULL, REFERENCE products(id)
coupon_code	varchar(62)
net_price	decimal(8,2)
added_at	datetime	NOT NULL, CURRENT_TIMESTAMP()
coupon_id	int
price_before_coupon	decimal(8,2)

Table: categories

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
name	varchar(62)	NOT NULL
parent_id	int
created_by	int	NOT NULL, REFERENCES categories(id)
modified_at	timestamp	NOT NULL, CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP()

Table: coupons

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
tot_coupons_used	int
coupon_quantity	int	NOT NULL
coupon_code	varchar(30)	NOT NULL
institute_id	int
on_course_id	int	NOT NULL, REFERENCES products(id)
description	varchar(200)	NOT NULL
label	varchar(13)	NOT NULL
discount_percent	int
valid_from	date	NOT NULL
valid_till	date	NOT NULL
terms_and_conditions	varchar(10000)
modified_at	timestamp	NOT NULL, CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP()

Table: products

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
issued_by	int
image_name	varchar(100)	NOT NULL
image_url	varchar(1000)	NOT NULL
product_name	varchar(100)	NOT NULL
creator_name	varchar(62)	NOT NULL
product_type	varchar(32)	NOT NULL
category	int	NOT NULL, REFERENCES categories(id)
sub_category	int
label	varchar(32)
status	varchar(32)	NOT NULL
tot_students	int	NOT NULL
description	text	NOT NULL
you_will_learn	text
this_includes	text
pre_requisites	text
set_currency	varchar(1)	NOT NULL
price	decimal(6,2)	NOT NULL
discount	int	NOT NULL, DEFAULT(0)
course_rating	decimal(6,2)	NOT NULL
tot_ratings	int	NOT NULL
created_at	timestamp	NOT NULL, CURRENT_TIMESTAMP()
modified_at	timestamp	NOT NULL, CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP()
short_description	text

Table: resources

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
product_id	int	REFERENCES products(id)
resource_order	int
section_order	int
resource_type	varchar(32)	NOT NULL
is_paid	tinyint(4)	NOT NULL
section_name	varchar(100)	NOT NULL
image_url	varchar(100)
resource_name	varchar(100)	NOT NULL
resource_url	varchar(1000)	NOT NULL
rating	decimal(7,5)
uploaded_at	timestamp	NOT NULL, CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP()

Table: orders

Attribute Name	Datatype	Constraints
id	int	PRIMARY KEY, NOT NULL, AUTO_INCREMENT
order_id	varchar(256)
invoice_number	varchar(100)
receipt	varchar(200)
currency	varchar(5)
payment_id	varchar(200)
user_id	int
published_by	int
product_id	int
order_status	varchar(30)	NOT NULL
method_of_payment	varchar(30)	NOT NULL
from_bank_ac	varchar(100)
to_bank_ac	varchar(100)
coupon_id	int
price_before_coupon	decimal(8,2)
amount	decimal(8,2)
issued_on	timestamp	NOT NULL, CURRENT_TIMESTAMP()