Project Tracking System

PROJECT TRACKING SYSTEM
=======================


Tables:
========
User: (ID, username, email, password, first_name, last_name, role_id)
Project: (ID, name, description, start_date, end_date, agency_id, department_id)
Category: (ID, name, description)
Task: (ID, name, description, due_date, project_id, category_id, assigned_user_id)
Comment: (ID, content, timestamp, user_id, task_id)
Report: (ID, title, content, timestamp, user_id, project_id)


Relationships:
===============
One User can have one Role (many-to-one).
One Agency can have many Projects (one-to-many).
One Department can belong to one Agency (many-to-one).
One Project can have many Categories (many-to-many).
One Category can belong to many Projects (many-to-many).
One Project can have many Tasks (one-to-many).
One Task can have one Category (one-to-many).
One Task can be assigned to one User (one-to-many).
One User can create many Comments (one-to-many).
One User can create many Reports (one-to-many).
One Task can have many Comments (one-to-many).
One Project can have many Reports (one-to-many).

Unused Tables and Redundancies:
===============================
The contributors table seems redundant. You can add a user_id column to the task table and directly link users to tasks.
The notifications table could be implemented as a separate system or within the user model depending on its complexity and functionality.