Complaints Handling Management System Database Design

Complaints Handling Management System Database Design

Introduction

It is unavoidable for educational institutions to receive complaints as a result of unsatisfactory services or problems experienced from the standpoint of stakeholders. It is critical for schools to address and manage complaints properly; yet most complaints are not managed effectively due to a lack of instruments to promote the gathering of various complaints. The manual technique of resolving complaints in schools is ineffective in assisting the institution in developing an effective plan to resolve the stakeholders’ issues. If complaints are not addressed appropriately, stakeholders inside educational institutions will be dissatisfied with the services. As a result, the necessity for an efficient system to manage school-related complaints is evident.

The database design of the Complaints Handling Management System will be presented in this paper. The researchers will go over the various tables and fields in the system’s database design.

What is a complaint management system?

A Complaint Management System is a system developed to assist educational institutions in electronically handling and managing complaints. The system will improve the school’s management’s response time to complaints from kids, parents, staff, and other stakeholders.

A complaints handling management system is an important part of any business that wants to improve its customer service. It helps businesses to keep track of complaints, investigate them, and take corrective action. This system can also help businesses to identify and correct systemic problems that might be causing customer dissatisfaction.

There are many benefits to having a complaints handling management system in place. First, it can help businesses to resolve customer complaints quickly and efficiently. Second, it can help businesses to avoid potential legal problems. Third, it can help businesses to build a good reputation for their customer service.

In short, a complaints handling management system is an important tool that can help businesses to improve their customer service. It is important for businesses to implement such a system in order to ensure that their customers are happy and satisfied.

Importance of Database Design

A database design is critical in system development. The structuring of data according to a database model is known as database design. The designer decides what data must be saved and how the data elements interact with one another. With this knowledge, they can start fitting the data to the database model. A database design aids in the storage of important and necessary data.

Database Tables

tbl_admin_user – this table stores the information of the admin users of the system.

• admin_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).
• last_name – the last name of the admin
• first_name – the first name of the admin
• middle_name – the middle name of the admin
• designation – designation of the admin user
• contact – the contact number of the admin user
• email – the email address of the admin user
• username – the preferred username of the admin user used to log in to the system.
• password – the desired password of the admin for his/her account
• account status – activated, deactivated

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

CREATE TABLE `tbl_admin_user` (
`admin_user_id` int(11) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`designation` varchar(30) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_department_user – this table stores the information of the department users of the system.

• department_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).
• last_name – the department user’s last name
• first_name – the first name of the department user
• middle_name -the middle name of the department user
• department_id – this is a foreign key that points out to the department in which the user belong.
• contact – the contact number of the department user
• email – the email address of the department user
• username – the preferred username of the department user, this is combined with the password to log in to the system
• password – the password of the department user used to login to the system
• account_status – activated, deactivated

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

CREATE TABLE `tbl_department_user` (
`department_user_id` int(11) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`department_id` int(11) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_course – the details of the course will be stored in this table.

• course_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).
• course_code – this is a unique code given to a specific course
• course_name – the name of the course

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

CREATE TABLE `tbl_course` (
`course_id` int(11) NOT NULL,
`course_code` varchar(15) NOT NULL,
`course_name` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_stakeholder – the information of the stakeholders will be stored in this table.

• stakeholder_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 stakeholder
• first_name – the first name of the stakeholder
• middle_name – the middle name of the stakeholder
• course_id – this is a foreign key that points out to the course of the stakeholder
• stakeholder_type – the type of the stakeholder(student, parent, faculty, staff, others)
• contact – the contact number of the stakeholder
• email – the email address of the stakeholder
• username – the desired username of the stakeholder use to login to the system
• password – the password of the stakeholder for his or her account
• account_status – activated, deactivated

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

CREATE TABLE `tbl_stakeholder` (
`stakeholder_id` int(11) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`course_id` int(11) NOT NULL,
`stakeholder_type` int(1) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_department – this table stores the department information in the system.

• department_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).
• department_code – this is a unique code given to a specific department
• department_name – the name of the department

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

CREATE TABLE `tbl_department` (
`department_id` int(11) NOT NULL,
`department_code` varchar(15) NOT NULL,
`department_name` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_badwords_collection – this table stores the bad words collected from the complaints.

• bad_word_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).
• bad_word – the bad word collected

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

CREATE TABLE `tbl_badwords_collection` (
`bad_word_id` int(11) NOT NULL,
`bad_word` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_complaint – this table stores the complaints of the stakeholders in the system.

• complaint_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).
• complaint_code – this is a unique code given to a particular complaint
• stakeholder_id – the name of the stakeholder
• department_id – the name of the department to handle the complaint
• concern – the concern or the complaint
• complaint_name – the name of the complaint
• complaint_details – the details for the complaint
• attachment – the attachments for the complaint
• time_stamp – the time when the complaint is stamp
• status – pending, on-going, closed

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

CREATE TABLE `tbl_complaint` (
`complaint_id` int(11) NOT NULL,
`complaint_code` varchar(30) NOT NULL,
`stakeholder_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`concern` varchar(50) NOT NULL,
`complaint_name` varchar(150) NOT NULL,
`complaint_details` varchar(150) NOT NULL,
`attachment` longblob NOT NULL,
`time_stamp` datetime NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_complaint_details – this table stores the complaints details in the system.

• complaint_detail_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).
• complaint_code – this is a unique code given to a particular complaint
• complete_name – the complete name of the complainant
• update_message – this is used to update messages
• date_processed – the date when the complaint is processed
• reply_from_student – this is used to view reply from student
• status (pending, ongoing, closed)

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

CREATE TABLE `tbl_complaint_details` (
`complaint_detail_id` int(11) NOT NULL,
`complaint_code` varchar(30) NOT NULL,
`complete_name` varchar(100) NOT NULL,
`update_message` varchar(150) NOT NULL,
`date_processed` datetime NOT NULL,
`reply_from_student` varchar(150) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FREE DOWNLOAD SQL FILE

Summary

The capstone project, “Complaint Handling Management System” is a system designed to help educational institutions to handle and manage complaints electronically. The system will improve the response time of the school’s management in addressing the complaints of the students, parents, staff, and other stakeholders. This article will present the database design of the Complaints Handling Management System. The researchers will discuss the different tables and fields in the database design of the system.  Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate.

Readers are also interested in:

Complaint Management System Free Template in PHP and Bootstrap

IPO Model Conceptual Framework of Complaints Management System

Top 94 Capstone Project Ideas with Related Literature

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.