Database Tables and snapshots for Complaint Management Project

Complaint Management System Database Approach

As we all know database is very important part of your big or mini website system. We are going to create table structures for CMS project.

If someone want to make any project and he /she is well planed about it means database is planned. After considering database tables in a project it become very easy to start with coding. 

Here in this post we are going to educate about tables consideration in a CMS. Here cms stands for complaint management system.   

So lets come to the topic directly without wasting your precious time.

  

There are 7 tables are considered in a common complaint management system. Each table has relation with at least single another table. Below are some table considerations in a common CMS system :

1. Complainer Registration Table
   
   
2. Complaint Register Table
   
   
3. Admin Credentials Table
   
   
4. Company Registration Table
   
   
5. Comments on Complaint Table
   
   
6. Page Visit Counts Table
   
   
7. Online Visitors Table

Although tables are self explanatory , but we will understand each field requirement and alternatives.

   So lets start one by one : 

1. Complainer Registration Table
   

As usual this is a registration of a user.

Above image only a smallest approach for registration table.

If you noticed there is no mobile number in snapshot , but mobile number is must in this era. 

Mobile number is a general need of people so all websites assume it as mandatory field in registration process.

A lot of field still can be added in this table , like user_image , mobile_number , alternate mobile number , alternate email id , first name , last name ,mid name , user address , any attachment etc.

And most important for registration process , we can add field like is_verified_user , is_email_verified , is_mobile_verified .

In registration table first field is complainer_id. We can also add a different field like generatedUserId. By doing this we can handle ids. 

                                   

2. Complaint Register Table

A complainer will register itself then he start to fill the complaint register i.e. a online html form.

The data complainer will provide , store in this table which is complaint_register.

One field can be added in this table. This field is status of a complaint.

By default status of a complaint will be open status.

A status has four different values. First is open , second is resolved ,third is closed and fourth one is in_process mode.

3. Admin Credentials Table

Although someone may hard code for admin credential , if only one admin. But in most of web application there are layers of admins. Like superAdmin , subAdmin etc. It's good to create this table for admins. 

You can also add ten's of more field in this table. But the core structure will contain username and password atleast.

4. Company Registration Table

This is simply a registration table which contain data comes from company registration process. 

A complaint system works for Against company .

 As a complainer complains for any product or service , then he/she will provide company name or another data . 

Company registration data will link with this company registration table records.

5. Comments on Complaint Table

Comments ! Comment section is really very important of any web page of a website.

 In complaint system a complainer can comment on any complaint after login.

 Same way a company representative can comment on their complaint. 

This table has  foreign key as complaint_id.  And in field number 4 commenter  may be a complainer or company.

6. Page Visit Count Table

This page visit table is optional table . You can create this table if you want to keep the track of page wise visit counts. Suppose you have 100 pages in your website. You want to know about page views of particular page , you can access from this table. 

So , how data will come in this table i.e. page_visitor_counter. As soon as any page is opened by anyone it's count related field , here field number 3 visit_count will increase by one. 

7.Online Visitors Table

Visitor table looks very simple right. But you know , you can make a do a big analysis behalf of this small table. 

Here we are not discussing about where and how deep you can use this table in your any web project or website.

Download all tables at once Complaint Management Project in mysql DB.

Click above download link to get all above tables at once.