Required Tables

The following tables are essential for the College Complaint Management System database

Table Name Primary Key Key Attributes Description
Students student_id Name, Email, Password,
department_id(FK) Stores students credentials and
basic information for students.
Departments department_id department_name Stores college department
details.
Complaints complaint_id Title, description, created_at,
student_id (FK),
department_id(FK) Stores details of all complaints
registered in the system.
Complaint_Status status_id Status,complaint_id(FK) Stores each complaint’s
current status and admin
remarks for progress tracking

Full Details of Tables

1. Students Tables

Field Type
student_id INT (PK)
name VARCHAR
email VARCHAR (Unique)
password VARCHAR
department_id FK

2. Department

Field Type
department_id INT (PK)
department_name VARCHAR

3. Complaints

Field Type
complaint_id INT (PK)
title VARCHAR
description TEXT
created_at DATETIME
student_id FK
department_id FK

4. Complaint_Status

Field Type
status_id INT (PK)
status VARCHAR
complaint_id FK

Relationships Between Tables

1. Departments ↔ Students

Relationship Type: One-to-Many (1:N)

● One department can have many students. ● Each student belongs to only one department.

Foreign Key: Students.department_id → Departments.department_id

2. Students ↔ Complaints

Relationship Type: One-to-Many (1:N)

● One student can submit multiple complaints. ● Each complaint is submitted by only one student.

Foreign Key: Complaints.student_id → Students.student_id

3. Departments ↔ Complaints