Step-by-Step Guide to Developing an Online Service Management System (OSMS) – Features, Modules, and Database Design

 

Online Service Management System

Overview

What is an Online Service Management System?

An Online Service Management System (OSMS) is a web-based application designed to manage and streamline service requests, complaints, and maintenance tasks in various industries. This system allows businesses, service providers, and customers to interact efficiently, track service requests, and manage workflow operations digitally.

It is widely used in industries such as IT support, home services (plumbing, electrical, cleaning), automobile service centers, repair and maintenance companies, and customer support organizations.

---

Objective  of the Project

The primary goal of this project is to provide an automated and centralized platform where users can register, request services, track service progress, and get solutions without the need for physical visits or phone calls.

Features of Online Service Management System

The Online Service Management System (OSMS) is designed to automate and manage service requests efficiently. Below are the key features of this project:

---

1. User Management

✅ User Registration & Login – Customers and service providers can sign up and log in securely.
✅ Role-Based Access – Different roles: Admin, Customer, Service Provider (Technician/Staff).

---

2. Service Request Management

✅ Service Request Form – Customers can submit service requests online.
✅ Service Categories – Allows multiple types of service requests (e.g., IT support, home maintenance, repairs).
✅ File Upload – Customers can attach images/documents related to their service issue.

---

3. Service Tracking & Updates

✅ Real-Time Status Updates – Users can track the status of their request (Pending, In Progress, Completed).
✅ Notification System – Email/SMS notifications for request status changes.
✅ Service History – Customers can view past service requests and their details.

---

4. Admin Dashboard

✅ Manage Users – Add, edit, delete customers, service providers, and staff.
✅ Assign Service Requests – Admins can assign service requests to available service providers.
✅ Monitor Service Status – Track service requests and update their progress.
✅ Generate Reports – View analytics on service performance, customer feedback, and provider efficiency.

---

5. Service Provider/Technician Module

✅ View Assigned Requests – Service providers can see and manage their assigned tasks.
✅ Update Service Progress – Mark requests as "In Progress" or "Completed."
✅ Service Notes – Add remarks or comments related to the service request.

---

6. Customer Feedback & Ratings

✅ Rate Service Providers – Customers can give ratings (e.g., 1-5 stars).
✅ Review System – Users can provide comments about the service quality.

---

7. Payment Integration (Optional)

✅ Online Payment Options – Customers can pay for services via PayPal, Razorpay, Stripe, or other gateways.
✅ Invoice Generation – Automated invoice creation for service payments.

---

8. Reports & Analytics

✅ Daily/Monthly Reports – Track the number of service requests completed.
✅ Performance Analytics – Analyze technician efficiency and customer satisfaction trends.

---

9. Security Features

✅ Secure Authentication – Uses password encryption and OTP verification.
✅ Role-Based Access Control – Ensures only authorized users can access specific features.
✅ Data Backup System – Periodic backups to prevent data loss.

---

10. Multi-Platform Accessibility

✅ Web-Based Application – Can be accessed via desktops, tablets, and mobile devices.
✅ Responsive UI – Mobile-friendly design for better user experience.

4. Technologies Used

• Frontend: HTML, CSS, JavaScript (React.js or Vue.js)
• Backend: PHP (Laravel), Node.js, or Java (Spring Boot)
• Database: MySQL or MongoDB
• Hosting: Apache (XAMPP) or Cloud-based services

5. Expected Outcome

• Improved customer satisfaction through faster response times
• Centralized service management for businesses
• Automation of service tracking and assignment
• Reduction in manual paperwork and phone calls

Modules of Online Service Management System (OSMS)

The Online Service Management System (OSMS) consists of multiple modules that handle different functionalities, ensuring seamless service management. Below are the key modules along with their functionalities and respective database tables.

---

1. User Management Module

🔹 Functionality:

• Users (Customers, Service Providers, Admin) can register, log in, and manage profiles.
• Role-based authentication ensures users access only relevant features.

🗂 Database Tables:

users

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique user ID
name	VARCHAR(255)	Full Name of the user
email	VARCHAR(255)	Email (unique)
password	VARCHAR(255)	Encrypted password
phone	VARCHAR(20)	Contact number
address	TEXT	User address
role	ENUM('Admin', 'Customer', 'ServiceProvider')	Role of the user
created_at	TIMESTAMP	Account creation time
updated_at	TIMESTAMP	Last update time

---

2. Service Request Management Module

🔹 Functionality:

• Customers can submit service requests based on categories.
• Admin assigns requests to available service providers.
• Service providers can accept or reject requests.

🗂 Database Tables:

service_requests

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique request ID
customer_id	INT (FK → users.id)	ID of the customer requesting service
category_id	INT (FK → service_categories.id)	Service category
service_description	TEXT	Details of the service request
status	ENUM('Pending', 'Assigned', 'In Progress', 'Completed', 'Rejected')	Current request status
created_at	TIMESTAMP	Request submission time
updated_at	TIMESTAMP	Last update time

service_categories

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique category ID
category_name	VARCHAR(255)	Name of the service category
created_at	TIMESTAMP	Category creation time

---

3. Service Provider Management Module

🔹 Functionality:

• Admin assigns service requests to registered service providers.
• Providers update the status of the assigned request.
• Customers get notifications on status updates.

🗂 Database Tables:

service_assignments

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique assignment ID
service_request_id	INT (FK → service_requests.id)	Assigned request ID
provider_id	INT (FK → users.id)	ID of the assigned service provider
assigned_at	TIMESTAMP	Assignment date
updated_at	TIMESTAMP	Last update time

---

4. Service Tracking Module

🔹 Functionality:

• Customers can track the progress of their requests.
• Service providers can update status as "In Progress" or "Completed."
• Admin can monitor pending and completed requests.

🗂 Database Tables:

service_status

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique status ID
service_request_id	INT (FK → service_requests.id)	Request ID
status	ENUM('Pending', 'Assigned', 'In Progress', 'Completed', 'Rejected')	Current request status
updated_by	INT (FK → users.id)	Who updated the status (Admin/Provider)
updated_at	TIMESTAMP	Last update time

---

5. Payment & Invoice Module (Optional Feature)

🔹 Functionality:

• Customers can make online payments for services.
• Generate invoices after service completion.

🗂 Database Tables:

payments

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique payment ID
service_request_id	INT (FK → service_requests.id)	Paid service request ID
customer_id	INT (FK → users.id)	ID of the customer
amount	DECIMAL(10,2)	Service cost
payment_status	ENUM('Pending', 'Paid', 'Failed')	Payment status
payment_method	ENUM('Credit Card', 'PayPal', 'Bank Transfer')	Mode of payment
created_at	TIMESTAMP	Payment date

---

6. Feedback & Rating Module

🔹 Functionality:

• Customers can rate service providers after completion.
• Admin can analyze customer satisfaction trends.

🗂 Database Tables:

service_reviews

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique review ID
service_request_id	INT (FK → service_requests.id)	Related request ID
customer_id	INT (FK → users.id)	ID of the customer providing feedback
provider_id	INT (FK → users.id)	ID of the service provider being reviewed
rating	INT(1) CHECK (rating BETWEEN 1 AND 5)	Star rating (1-5)
feedback	TEXT	Customer feedback
created_at	TIMESTAMP	Review date

---

7. Admin Dashboard Module

🔹 Functionality:

• Manage users (Customers, Service Providers, Admins).
• Monitor service requests, payments, and feedback.
• Generate reports on service performance.

🗂 Database Tables:

admin_reports

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique report ID
report_type	VARCHAR(255)	Type of report (Service, Payment, User Activity)
generated_at	TIMESTAMP	Report generation date

---

8. Notification Module

🔹 Functionality:

• Send email/SMS notifications for status updates.
• Notify service providers when a request is assigned.

🗂 Database Tables:

notifications

Column Name	Data Type	Description
id	INT (PK, AUTO_INCREMENT)	Unique notification ID
user_id	INT (FK → users.id)	User receiving the notification
message	TEXT	Notification content
status	ENUM('Unread', 'Read')	Read/unread status
created_at	TIMESTAMP	Notification time

---

Conclusion

The Online Service Management System (OSMS) is structured into modules, each handling specific functionalities like user management, service requests, tracking, payments, feedback, and notifications.

Project Structure of Online Service Management System (OSMS)

A well-organized project structure is crucial for maintaining code clarity, scalability, and ease of development. Below is the recommended project directory structure for the Online Service Management System (OSMS), considering a PHP (Laravel) or Node.js (Express) backend with a MySQL database.

---

📂 Project Root (OSMS/)

This is the main directory containing all project files.

---

1️⃣ Backend (server/ - API & Business Logic)

The backend is responsible for handling requests, processing data, and interacting with the database.

📂 server/

 ├── 📂 config/              # Configuration files (Database, JWT, etc.)

 │    ├── database.php       # Database connection settings

 │    ├── auth.php           # Authentication settings

 │    ├── mail.php           # Email configurations

 │    └── app.php            # General app settings

 │

 ├── 📂 controllers/         # Business logic controllers

 │    ├── AuthController.php # Handles login, registration, JWT

 │    ├── UserController.php # Manages user accounts

 │    ├── ServiceController.php # Manages service requests

 │    ├── ProviderController.php # Assigns services to providers

 │    ├── PaymentController.php # Handles payment processing

 │    ├── ReviewController.php # Handles feedback & ratings

 │    ├── AdminController.php # Admin dashboard actions

 │    └── NotificationController.php # Sends notifications

 │

 ├── 📂 models/              # Database models for ORM

 │    ├── User.php           # User model

 │    ├── Service.php        # Service model

 │    ├── Provider.php       # Service provider model

 │    ├── Payment.php        # Payment model

 │    ├── Review.php         # Feedback & rating model

 │    ├── Notification.php   # Notifications model

 │    └── Admin.php          # Admin model

 │

 ├── 📂 middleware/          # Middleware for security & authentication

 │    ├── AuthMiddleware.php # Protects routes

 │    ├── RoleMiddleware.php # Controls role-based access

 │    └── CorsMiddleware.php # Handles CORS requests

 │

 ├── 📂 routes/              # API routes

 │    ├── api.php            # API routes (services, users, etc.)

 │    ├── web.php            # Web routes (if applicable)

 │    ├── admin.php          # Routes for admin panel

 │    ├── auth.php           # Authentication routes

 │    └── provider.php       # Routes for service providers

 │

 ├── 📂 database/            # Database migrations & seeders

 │    ├── migrations/        # Schema for tables

 │    ├── seeders/           # Sample data for testing

 │    └── factories/         # Fake data generation

 │

 ├── .env                    # Environment variables (database, API keys)

 ├── composer.json           # PHP dependencies (for Laravel)

 ├── package.json            # Node.js dependencies (if Express used)

 ├── artisan                 # Laravel CLI (for migrations, seeds, etc.)

 ├── server.js               # Main entry point (if using Node.js)

 ├── index.php               # Entry point (if using Laravel)

 └── README.md               # Documentation

---

2️⃣ Frontend (client/ - User Interface)

The frontend handles user interactions, making API calls to the backend.

📂 client/

 ├── 📂 src/

 │    ├── 📂 assets/         # Static images, icons, logos

 │    ├── 📂 components/     # Reusable UI components

 │    │    ├── Header.js    # Navigation bar

 │    │    ├── Footer.js    # Footer section

 │    │    ├── Sidebar.js   # Sidebar for admin/provider panel

 │    │    └── Notification.js # Notification popup

 │    │

 │    ├── 📂 pages/         # Individual pages for UI

 │    │    ├── Home.js       # Homepage for users

 │    │    ├── Login.js      # Login page

 │    │    ├── Register.js   # User registration page

 │    │    ├── Dashboard.js  # User/Provider/Admin dashboard

 │    │    ├── Services.js   # View available services

 │    │    ├── Request.js    # Submit service requests

 │    │    ├── Payments.js   # Payment processing page

 │    │    ├── Reviews.js    # Feedback and ratings

 │    │    ├── AdminPanel.js # Admin dashboard

 │    │    └── Profile.js    # User profile settings

 │    │

 │    ├── 📂 hooks/         # Custom React Hooks (API calls, etc.)

 │    │    ├── useAuth.js    # Handles authentication

 │    │    ├── useService.js # Fetch service data

 │    │    ├── usePayment.js # Handle payments

 │    │    └── useNotification.js # Manage notifications

 │    │

 │    ├── 📂 styles/        # CSS or Tailwind styling

 │    │    ├── global.css    # Global styles

 │    │    ├── dashboard.css # Dashboard styles

 │    │    └── forms.css     # Form styles

 │    │

 │    ├── App.js             # Main React component

 │    ├── index.js           # React entry point

 │    ├── routes.js          # React Router paths

 │    └── config.js          # Frontend configurations (API URL, etc.)

 │

 ├── public/                 # Static files (favicon, index.html)

 ├── package.json            # Frontend dependencies

 ├── .env                    # Frontend environment variables (API URL)

 ├── vite.config.js          # Vite configuration (if using Vite)

 └── README.md               # Documentation

---

3️⃣ Database (db/ - SQL Scripts & ERD)

This directory contains database-related scripts, ER diagrams, and documentation.

📂 db/

 ├── schema.sql            # SQL script for database schema

 ├── seed.sql              # Sample data for testing

 ├── er_diagram.png        # Entity Relationship Diagram

 ├── db_backup.sql         # Database backup file

 └── README.md             # Database documentation

---

4️⃣ Documentation (docs/ - Guides & API Docs)

All documentation-related files for developers and users.

📂 docs/

 ├── api-docs.md          # API Documentation (Swagger/Postman)

 ├── installation.md      # Installation guide for setup

 ├── user-guide.md        # How to use the system

 ├── admin-guide.md       # Admin panel documentation

 ├── provider-guide.md    # Service provider documentation

 ├── database-design.md   # Explanation of database schema

 └── README.md            # General documentation

---

Folder	Purpose
server/	Handles backend logic, routes, and database interactions
client/	Manages frontend UI (React/Vite)
db/	Contains database schema and backups
docs/	Holds documentation files for setup, API, and database

---

Project Structure for Online Service Management System (OSMS) Without MVC

If you're building the Online Service Management System (OSMS) without using the Model-View-Controller (MVC) pattern, the project structure would be simpler, focusing on separate PHP files for functionalities instead of structured models, views, and controllers.

---

📂 Project Root (OSMS/)

This is the main directory containing all project files.

📂 OSMS/

 ├── 📂 assets/            # Static files (CSS, JS, Images)

 │    ├── 📂 css/         # Stylesheets

 │    │    ├── style.css  # Main CSS file

 │    │    ├── admin.css  # Styles for admin panel

 │    │    └── user.css   # Styles for user panel

 │    │

 │    ├── 📂 js/          # JavaScript files

 │    │    ├── script.js  # Main JS file

 │    │    ├── ajax.js    # Handles AJAX requests

 │    │    └── validation.js # Form validation

 │    │

 │    ├── 📂 images/      # Logo and other images

 │    └── 📂 uploads/     # Stores uploaded files (profile pictures, invoices)

 │

 ├── 📂 includes/         # Reusable common files

 │    ├── header.php      # Header section (Navbar, Logo, etc.)

 │    ├── footer.php      # Footer section

 │    ├── sidebar.php     # Sidebar menu (Admin/User)

 │    ├── db_config.php   # Database connection file

 │    ├── session.php     # User session handling

 │    ├── auth.php        # Authentication (Login, Logout, Access Control)

 │

 ├── 📂 users/            # User-specific pages

 │    ├── dashboard.php   # User dashboard

 │    ├── request_service.php # Service request form

 │    ├── service_history.php # Past service requests

 │    ├── profile.php     # User profile update page

 │    ├── payment.php     # Payment processing

 │    ├── feedback.php    # User feedback and ratings

 │    └── logout.php      # Logout function

 │

 ├── 📂 admin/            # Admin panel

 │    ├── dashboard.php   # Admin dashboard

 │    ├── manage_users.php # View, add, edit, delete users

 │    ├── manage_services.php # Add/edit/delete services

 │    ├── view_requests.php # Manage user service requests

 │    ├── transactions.php # Payment and transaction history

 │    ├── feedbacks.php   # View user feedback and ratings

 │    └── reports.php     # Generate system reports

 │

 ├── 📂 service_providers/ # Service provider panel

 │    ├── dashboard.php   # Service provider dashboard

 │    ├── assigned_requests.php # View assigned service requests

 │    ├── update_status.php # Update service request status

 │    ├── earnings.php    # Payment and earnings report

 │    ├── profile.php     # Update profile details

 │    └── logout.php      # Logout function

 │

 ├── 📂 api/              # API Endpoints for AJAX Requests

 │    ├── login.php       # Handles user login

 │    ├── register.php    # Handles user registration

 │    ├── fetch_services.php # Fetch available services

 │    ├── place_request.php # Handle service request submission

 │    ├── update_status.php # Update request status (admin/provider)

 │    ├── payment_process.php # Handle payment gateway requests

 │    ├── send_feedback.php # Submit feedback

 │    └── notifications.php # Send and fetch notifications

 │

 ├── 📂 database/         # Database scripts & backups

 │    ├── schema.sql      # SQL script for database schema

 │    ├── seed.sql        # Sample data for testing

 │    ├── er_diagram.png  # ER Diagram of the database

 │    └── backup.sql      # Database backup

 │

 ├── index.php            # Homepage (User Login/Register)

 ├── about.php            # About Us page

 ├── contact.php          # Contact Us page

 ├── services.php         # List of all services

 ├── faq.php              # Frequently Asked Questions

 ├── admin_login.php      # Admin Login page

 ├── provider_login.php   # Service Provider Login page

 ├── .htaccess            # URL redirection & security rules

 ├── README.md            # Documentation

---

🔹 Explanation of the Structure

Folder/File	Description
assets/	Stores CSS, JavaScript, images, and uploads
includes/	Contains reusable parts like header, footer, database connection, and session management
users/	User panel: Dashboard, profile, service requests, payments, and feedback
admin/	Admin panel: Manage users, services, transactions, and reports
service_providers/	Service provider panel: Assigned requests, status updates, earnings, and profile
api/	API files handling login, registration, payments, and notifications via AJAX
database/	Contains database schema, ER diagram, and backup files
index.php	Home page of the system
admin_login.php	Login page for admin panel
provider_login.php	Login page for service providers
.htaccess	URL redirection and security settings
README.md	Project documentation

---

🔹 Key Features of This Structure

✅ No MVC (Model-View-Controller) Complexity – Simple PHP files with functional organization
✅ Separation of Concerns – User, Admin, and Provider panels are distinct
✅ Reusable Components – Common files like header, footer, and database connection
✅ API Support – AJAX-based API requests for better user experience
✅ Database Folder – All SQL files for easy setup and migration

---

Database Schema for Online Service Management System (OSMS)

Below is the database schema for the Online Service Management System (OSMS), including all tables, their fields, and relationships.

---

📌 Database Name: osms_db

1️⃣ users Table (Stores user details)

This table contains users (customers), service providers, and admins.

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    email VARCHAR(150) UNIQUE NOT NULL,

    password VARCHAR(255) NOT NULL,

    phone VARCHAR(15) NOT NULL,

    address TEXT NOT NULL,

    role ENUM('admin', 'customer', 'service_provider') NOT NULL,

    profile_picture VARCHAR(255) DEFAULT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

• role: Defines the type of user (Admin, Customer, or Service Provider).
• profile_picture: Stores the image path of the user's profile.

---

2️⃣ services Table (Stores service categories)

Contains a list of services offered by the system.

CREATE TABLE services (

    id INT AUTO_INCREMENT PRIMARY KEY,

    service_name VARCHAR(255) NOT NULL,

    description TEXT NOT NULL,

    price DECIMAL(10,2) NOT NULL,

    duration VARCHAR(50) NOT NULL,  -- e.g., "2 hours", "1 day"

    image VARCHAR(255) DEFAULT NULL, -- Service image path

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

• Stores service names, prices, and durations.

---

3️⃣ service_requests Table (Stores customer service requests)

Keeps track of all service requests made by users.

CREATE TABLE service_requests (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    service_id INT NOT NULL,

    provider_id INT DEFAULT NULL, -- Assigned service provider

    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    status ENUM('pending', 'approved', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',

    notes TEXT DEFAULT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,

    FOREIGN KEY (provider_id) REFERENCES users(id) ON DELETE SET NULL

);

• status: Tracks the service request progress.
• provider_id: Stores the assigned service provider.

---

4️⃣ payments Table (Stores payment details)

Tracks payment transactions made by users for services.

CREATE TABLE payments (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    request_id INT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,

    payment_method ENUM('Credit Card', 'Debit Card', 'PayPal', 'UPI', 'Cash') NOT NULL,

    transaction_id VARCHAR(100) UNIQUE NOT NULL,

    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',

    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

    FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE

);

• transaction_id: Stores the unique ID for tracking payments.

---

5️⃣ feedbacks Table (Stores customer feedback)

This table stores feedback from customers after service completion.

CREATE TABLE feedbacks (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    service_id INT NOT NULL,

    provider_id INT NOT NULL,

    rating INT CHECK (rating BETWEEN 1 AND 5), -- Rating from 1 to 5

    review TEXT DEFAULT NULL,

    feedback_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,

    FOREIGN KEY (provider_id) REFERENCES users(id) ON DELETE CASCADE

);

• rating: Allows customers to give a 1 to 5-star rating.

---

6️⃣ admin_notifications Table (Stores notifications for admins)

Admins receive notifications about new service requests, payments, and feedback.

CREATE TABLE admin_notifications (

    id INT AUTO_INCREMENT PRIMARY KEY,

    message TEXT NOT NULL,

    status ENUM('unread', 'read') DEFAULT 'unread',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

• status: Tracks whether the admin has read the notification.

---

7️⃣ provider_assignments Table (Stores service provider assignments)

Tracks which service provider is assigned to which request.

CREATE TABLE provider_assignments (

    id INT AUTO_INCREMENT PRIMARY KEY,

    request_id INT NOT NULL,

    provider_id INT NOT NULL,

    assigned_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    status ENUM('assigned', 'accepted', 'in_progress', 'completed', 'cancelled') DEFAULT 'assigned',

    FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE,

    FOREIGN KEY (provider_id) REFERENCES users(id) ON DELETE CASCADE

);

• status: Tracks assignment progress.

---

8️⃣ transactions Table (Tracks service transactions)

Stores transaction logs related to service payments.

CREATE TABLE transactions (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    request_id INT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,

    transaction_type ENUM('debit', 'credit') NOT NULL, -- Debit (user pays), Credit (provider earns)

    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

    FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE

);

---

🔹 Relationships Between Tables

Table	Related Table	Relationship Type
users	service_requests	One-to-Many (A user can make multiple requests)
users	feedbacks	One-to-Many (A user can leave multiple reviews)
users	payments	One-to-Many (A user can make multiple payments)
services	service_requests	One-to-Many (Each service can have many requests)
service_requests	provider_assignments	One-to-One (Each request is assigned to a provider)
service_requests	payments	One-to-One (Each request has one payment)
service_requests	transactions	One-to-Many (A request can have multiple transactions)

---