Waste Management with Reward System Free Database Design Tutorial

Introduction

Members will be able to register with the system and search for junkshops and recycling centers to dispose of their recyclable waste products in a more convenient manner. In addition, the suggested system will record waste collection statistics, which will be used to determine whether or not to award rewards. Communities can benefit from the system by reusing and recycling waste that poses a risk or is hazardous to their health and safety.

In addition, the system will function as a platform to promote the recycling of recyclable materials such as plastic, paper, and metal, among others. It is anticipated that consumers would be able to compare the pricing of various recyclables and bargain for better offers. The system will also act as a platform for the recycling of hazardous and non-recyclable materials that are detrimental to society’s overall well-being. It is still in its early stages, but the planned system will be implemented more quickly and efficiently by collaborating with other service providers such as waste management organizations, trading companies, transportation companies, banks and manufacturers.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_member – this table will store the information of the members registered in the system.

• member_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• last_name – the last name of the member
• first_name – the first name of the member
• middle_name – the member’s middle name
• gender – the member’s gender (male or female)
• age – the age of the member
• contact- the contact number of the member, preferrably mobile or cellphone number.
• email_address – the email address of the member
• profile_picture – this will hold the profile photo of the member
• username – the preferred username of the member for his/her account. Combined with the password to login to the system
• password – the desired password of the member, combined with the username to login to the system.
• account_status – active, inactive

Create SQL Statement – the statement below is used to create the tbl_member, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_member` (
`member_id` int(11) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`gender` int(1) NOT NULL,
`age` int(3) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`profile_picture` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_junkshop – the junkshop’s details and information in the system are stored in this table.

• shop_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• shop_name – the name of the junkshop
• address – the address where the junkshop is located
• contact – the contact number of the junkshop, preferrably mobile or cellphone number.
• email_address – the email address of the junkshop
• google_map_location – the googla map of where the junkshop is located

Create SQL Statement – the statement below is used to create the tbl_junkshop, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_junkshop` (
`shop_id` int(11) NOT NULL,
`shop_name` varchar(50) NOT NULL,
`address` varchar(200) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`google_map_location` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_garbage_type – this table will hold the information of the garbage type in the system.

• type_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• name – the name of the garbage type
• description – description or additional information about the garbage’s type
• reward – reward for the specific garbage type collected

Create SQL Statement – the statement below is used to create the tbl_garbage_type, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_garbage_type` (
`type_id` int(11) NOT NULL,
`name` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL,
`reward` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_collection_record – the collection records of the system are stored in this table.

• record_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• member_id – this is a foreign key that points out to the member.
• shop_id – this is a foreign key that links to the junkshop table.
• type_id – this is a foreign key that points out to the garbage type.
• quantity – the quantity of the garbage collected
• unit – the unit of the garbage collected
• total_amount – the total amount of the garbage collection award
• date – the date the record was made
• processed_by (user_id) – this is a foreign key that points out to the user that processed the record.

Create SQL Statement – the statement below is used to create the tbl_collection_record, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_collection_record` (
`record_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`shop_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
`quantity` int(4) NOT NULL,
`unit` varchar(15) NOT NULL,
`total_amount` float NOT NULL,
`date` date NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table will store the information of personnel who can access the records of the system.

• user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• username – username of the personnel used to login together with the password.
• password – password of the personnel used to login together with the username.
• avatar – this will hold the profile image of the user.
• fullname – the complete name of the personnel or user.
• contact – contact number of the personnel (mobile/cellphone number).
• email – email address of the personnel/user.
• user_category_id – the user group or category of the user.
• status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`avatar` longblob NOT NULL,
`fullname` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` int(30) NOT NULL,
`user_category_id` int(11) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.

• user_group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• group_name – name of the category or user group (this field will group the user based on their designation).
• description – information on what the user group is all about.
• allow_add – this column is to allow or prevent user from adding a record.
• allow_edit – this column is to allow or prevent user from editing or updating a record.
• allow_delete – this column is to allow or prevent user from removing or deleting a record.
• allow_print – this column is to allow or prevent user from printing a report.
• allow_import – this column is to allow or prevent user from importing records to the system.
• allow_export – this column is to allow or prevent user from exporting records from the system.

Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user_group` (
`user_group_id` int(11) NOT NULL,
`group_name` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL,
`allow_add` int(1) NOT NULL,
`allow_edit` int(1) NOT NULL,
`allow_delete` int(1) NOT NULL,
`allow_print` int(1) NOT NULL,
`allow_import` int(1) NOT NULL,
`allow_export` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

The system will aid and assist the local government in their campaign for waste management, and it will encourage the public to reuse and recycle their recyclable garbage by offering incentives to those who do so.

To pursue that project, we need first to plan the database design of the said project. We hope that this article has provided you with the idea and information on how to prepare the database tables needed for the waste management with reward system.

Please watch the video tutorial that will be posted on our YouTube Channel.

DOWNLOAD SQL FILE

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive  free capstone projects resources and computer programming tutorials.

Hire our team to do the project.

Related Topics and Articles:

Waste Management with Reward System

Online E-Waste Management System Capstone Proposal