S

Service Finder — ER Diagram & Database Schema

A complete HTML blog post (Blogger-ready) with ER diagram SVG, relational tables, and SQL snippets for an app that stores electricians, plumbers, and other workers. Roles: Admin, Secretary, Worker, Consumer.

Quick summary

This post provides a ready-to-use database schema and a simple ER diagram for a marketplace-style service app. The schema includes additional tables for payments, notifications, and locations. Use these SQL snippets to create the tables or adapt them for your preferred RDBMS (MySQL / MariaDB / PostgreSQL).

Tip: Copy the SQL blocks below into your DB client. Use strong foreign-key constraints in production.

ER Diagram

A simplified ER diagram showing relationships between the core entities.

How to use

1. Create tables in your database using the SQL below.
2. Seed ServiceCategory with categories like Electrician, Plumber.
3. Register users with appropriate role_id.
4. Use WorkerService to attach categories, rates, and availability to workers.

Search tips

To allow consumers to search by area and category: join Users → Location and filter by category_id in WorkerService. Add geo-coordinates if you want distance-based search later.

Relational table definitions (short)

Table	Important columns	Notes
roles	role_id (PK), role_name	Store role names
users	user_id (PK), role_id (FK), location_id (FK), full_name, email	All app users: admin, secretary, worker, consumer
location	location_id (PK), address_line, city, state, pincode	Normalized addresses; optional geo columns
service_category	category_id (PK), category_name, description	Electrician, Plumber, Carpenter, etc.
worker_service	worker_service_id (PK), worker_id (FK), category_id (FK), rate_per_hour	Map worker to categories/skills
request_status	status_id (PK), status_name	Pending, Approved, Completed, Cancelled
request	request_id (PK), consumer_id (FK), worker_id (FK), category_id (FK), status_id (FK), scheduled_date	Service requests between consumer and worker
payments	payment_id (PK), request_id (FK), amount, payment_method, payment_status	Link payments to requests
review	review_id (PK), request_id (FK), consumer_id (FK), rating, comment	Post-work reviews
notifications	notification_id (PK), user_id (FK), title, message, is_read	In-app notifications

SQL — Create tables (MySQL-style)

Use the button to toggle each CREATE statement.

Create roles, location, users

-- roles
CREATE TABLE roles (
  role_id INT AUTO_INCREMENT PRIMARY KEY,
  role_name VARCHAR(50) NOT NULL
);

-- location
CREATE TABLE location (
  location_id INT AUTO_INCREMENT PRIMARY KEY,
  address_line VARCHAR(255),
  city VARCHAR(80),
  state VARCHAR(80),
  pincode VARCHAR(20)
);

-- users
CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL,
  location_id INT,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(150) UNIQUE,
  phone VARCHAR(30),
  password_hash VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(role_id),
  FOREIGN KEY (location_id) REFERENCES location(location_id)
);

ServiceCategory & WorkerService

-- service categories
CREATE TABLE service_category (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(80) NOT NULL,
  description TEXT
);

-- worker service
CREATE TABLE worker_service (
  worker_service_id INT AUTO_INCREMENT PRIMARY KEY,
  worker_id INT NOT NULL,
  category_id INT NOT NULL,
  experience_years INT DEFAULT 0,
  rate_per_hour DECIMAL(10,2),
  availability BOOLEAN DEFAULT TRUE,
  FOREIGN KEY (worker_id) REFERENCES users(user_id),
  FOREIGN KEY (category_id) REFERENCES service_category(category_id)
);

Request, RequestStatus, Review

-- request status
CREATE TABLE request_status (
  status_id INT AUTO_INCREMENT PRIMARY KEY,
  status_name VARCHAR(60) NOT NULL
);

-- request
CREATE TABLE request (
  request_id INT AUTO_INCREMENT PRIMARY KEY,
  consumer_id INT NOT NULL,
  worker_id INT,
  category_id INT,
  status_id INT,
  request_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  scheduled_date DATETIME,
  completion_date DATETIME,
  description TEXT,
  FOREIGN KEY (consumer_id) REFERENCES users(user_id),
  FOREIGN KEY (worker_id) REFERENCES users(user_id),
  FOREIGN KEY (category_id) REFERENCES service_category(category_id),
  FOREIGN KEY (status_id) REFERENCES request_status(status_id)
);

-- review
CREATE TABLE review (
  review_id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  consumer_id INT NOT NULL,
  rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES request(request_id),
  FOREIGN KEY (consumer_id) REFERENCES users(user_id)
);

Payments & Notifications

-- payments
CREATE TABLE payments (
  payment_id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  consumer_id INT NOT NULL,
  worker_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  payment_method VARCHAR(50),
  payment_status VARCHAR(50) DEFAULT 'Paid',
  FOREIGN KEY (request_id) REFERENCES request(request_id),
  FOREIGN KEY (consumer_id) REFERENCES users(user_id),
  FOREIGN KEY (worker_id) REFERENCES users(user_id)
);

-- notifications
CREATE TABLE notifications (
  notification_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(120),
  message TEXT,
  is_read BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Extras & next steps

• Add geo coordinates (latitude/longitude) to location for distance-based search and sorting.
• Add an images table if workers need portfolio pictures.
• Add verification flags for worker documents and admin-approved badges.
• Consider a wallet table if you want to keep in-app balances and payouts.

If you want, I can also produce:

• A downloadable PNG of the ER diagram.
• An ER diagram in draw.io / Lucidchart format.
• API endpoints (example routes) for user registration, search, request creation, payment webhook, and reviews.

API Endpoints for Service Finder

1. User Management

• POST /users
  Parameters: full_name, email, phone, password, role_id
  Returns: JSON (user_id, message)
• GET /users
  Parameters: (optional) role_id, location_id
  Returns: JSON array of users
• PUT /users/{id}
  Parameters: user_id (path), full_name, phone, location_id
  Returns: JSON (message, updated_user)
• DELETE /users/{id}
  Parameters: user_id (path)
  Returns: JSON (message)

2. Service Requests

• POST /requests
  Parameters: consumer_id, worker_id, category_id, scheduled_date, description
  Returns: JSON (request_id, message)
• GET /requests
  Parameters: (optional) status_id, consumer_id, worker_id
  Returns: JSON array of requests
• PUT /requests/{id}
  Parameters: request_id (path), status_id, completion_date
  Returns: JSON (message, updated_request)

3. Payments

• POST /payments
  Parameters: request_id, consumer_id, worker_id, amount, payment_method
  Returns: JSON (payment_id, message)
• GET /payments
  Parameters: (optional) consumer_id, worker_id, request_id
  Returns: JSON array of payments

4. Reviews

• POST /reviews
  Parameters: request_id, consumer_id, rating, comment
  Returns: JSON (review_id, message)
• GET /reviews
  Parameters: (optional) worker_id, consumer_id
  Returns: JSON array of reviews

5. Notifications

• GET /notifications
  Parameters: user_id
  Returns: JSON array of notifications
• PUT /notifications/{id}
  Parameters: notification_id (path), is_read
  Returns: JSON (message, updated_notification)

Happy Coding .