This document outlines a detailed database design for a car repair shop using MySQL and MySQL Workbench. It covers the entity-relationship (ER) diagrams, entity-attribute-relationship (EAV) models, and detailed explanations of each module, their relationships, and their attributes. Additionally, this document splits the design into modules for a step-by-step development process, covering testing, integration with MongoDB, a notification system, and a billing system using Stripe. Furthermore, it includes a system for employees to upload photos using Multer and Cloudinary.

Overall Structure

The model consists of five subject areas:

Repair Shops & Employees
Customers & Contacts
Vehicles
Services & Offers
Visits

Each subject area contains entities and relationships crucial for managing a car repair shop efficiently.

Module 1: Repair Shops & Employees

Description

This module is essential for managing the core resources of the repair shop – the shops themselves and their employees. It tracks the employees’ details, their positions, and their schedules.

Entities and Attributes

RepairShop: Contains information about the repair shops.
- repair_shop_id: Primary key
- name: Name of the repair shop
- address: Address of the repair shop
- city_id: Foreign key to the City table
Employee: Stores details of the employees.
- employee_id: Primary key
- first_name: First name of the employee
- last_name: Last name of the employee
- employment_start_date: Start date of employment
- employment_end_date: End date of employment (nullable)
- position_id: Foreign key to the Position table
- city_id: Foreign key to the City table
- is_active: Boolean flag indicating if the employee is currently active
Position: Defines various positions within the company.
- position_id: Primary key
- position_name: Name of the position
City: Stores city information.
- city_id: Primary key
- city_name: Name of the city
Schedule: Tracks the schedules of employees.
- schedule_id: Primary key
- repair_shop_id: Foreign key to the RepairShop table
- employee_id: Foreign key to the Employee table
- position_id: Foreign key to the Position table
- schedule_date: Date of the schedule
- time_from: Start time of the schedule
- time_to: End time of the schedule
- plan: Boolean flag indicating if this was a planned entry
- actual: Boolean flag indicating if the entry was realized
- insert_ts: Timestamp of when the record was inserted

Relationships

Each RepairShop has multiple Employees.
Each Employee holds a specific Position.
Each Employee and RepairShop have multiple Schedules.
Each Employee and Schedule are associated with a specific City.

Testing

Create tables and establish relationships in MySQL Workbench.
Insert sample data to verify the integrity and accuracy of the relationships.
Test CRUD operations for each table.

Module 2: Customers & Contacts

Description

This module manages customer information and the contacts made with them. It is crucial for tracking customer interactions and maintaining a comprehensive customer database.

Entities and Attributes

Customer: Stores customer information.
- customer_id: Primary key
- first_name: First name of the customer
- last_name: Last name of the customer
- company_name: Name of the company (nullable)
- address: Address of the customer
- mobile: Mobile phone number
- email: Email address
- details: Additional details
- insert_ts: Timestamp of when the record was inserted
ContactType: Defines different types of contacts.
- contact_type_id: Primary key
- type_name: Name of the contact type
Contact: Stores details of the contacts made with customers.
- contact_id: Primary key
- contact_type_id: Foreign key to the ContactType table
- customer_id: Foreign key to the Customer table
- employee_id: Foreign key to the Employee table
- details: Contact details
- insert_ts: Timestamp of when the record was inserted

Relationships

Each Customer can have multiple Contacts.
Each Contact is associated with a ContactType.
Each Contact involves a specific Employee.

Testing

Create tables and establish relationships in MySQL Workbench.
Insert sample data to verify the integrity and accuracy of the relationships.
Test CRUD operations for each table.

Module 3: Vehicles

Description

This module manages information about vehicles that come into the shop. It tracks vehicle details, makes, models, and types.

Entities and Attributes

Make: Stores information about vehicle manufacturers.
- make_id: Primary key
- make_name: Name of the manufacturer
VehicleType: Defines different types of vehicles.
- vehicle_type_id: Primary key
- type_name: Name of the vehicle type
Model: Stores information about vehicle models.
- model_id: Primary key
- model_name: Name of the model
- make_id: Foreign key to the Make table
- vehicle_type_id: Foreign key to the VehicleType table
Vehicle: Stores information about individual vehicles.
- vehicle_id: Primary key
- vin: Vehicle identification number
- license_plate: License plate number
- customer_id: Foreign key to the Customer table
- model_id: Foreign key to the Model table
- manufactured_year: Year of manufacture
- manufactured_month: Month of manufacture
- details: Additional details
- insert_ts: Timestamp of when the record was inserted

Relationships

Each Make has multiple Models.
Each VehicleType has multiple Models.
Each Model has multiple Vehicles.
Each Customer can have multiple Vehicles.

Testing

Create tables and establish relationships in MySQL Workbench.
Insert sample data to verify the integrity and accuracy of the relationships.
Test CRUD operations for each table.

Module 4: Services & Offers

Description

This module manages the services and tasks offered to customers, including offers made to them. It is crucial for defining what the shop offers and tracking customer engagement with these services.

Entities and Attributes

ServiceCatalog: Stores information about available services.
- service_catalog_id: Primary key
- service_name: Name of the service
- description: Description of the service
- service_discount: Discount on the service
- is_active: Boolean flag indicating if the service is active
TaskCatalog: Defines tasks that are part of services.
- task_catalog_id: Primary key
- task_name: Name of the task
- service_catalog_id: Foreign key to the ServiceCatalog table
- description: Description of the task
- ref_interval: Boolean flag indicating if the task has a reference interval
- ref_interval_min: Minimum reference interval
- ref_interval_max: Maximum reference interval
- describe: Boolean flag indicating if a description is needed for the task
- task_price: Price of the task
- is_active: Boolean flag indicating if the task is active
Offer: Stores offers made to customers.
- offer_id: Primary key
- customer_id: Foreign key to the Customer table
- contact_id: Foreign key to the Contact table
- offer_description: Description of the offer
- service_catalog_id: Foreign key to the ServiceCatalog table (nullable)
- service_discount: Discount on the service
- offer_price: Price of the offer
- insert_ts: Timestamp of when the record was inserted
OfferTask: Links tasks to offers.
- offer_task_id: Primary key
- offer_id: Foreign key to the Offer table
- task_catalog_id: Foreign key to the TaskCatalog table
- task_price: Price of the task
- insert_ts: Timestamp of when the record was inserted

Relationships

Each ServiceCatalog can have multiple TaskCatalog entries.
Each Customer can have multiple Offers.
Each Contact can result in multiple Offers.
Each Offer can include multiple OfferTasks.
Each TaskCatalog can be part of multiple OfferTasks.

Testing

Create tables and establish relationships in MySQL Workbench.
Insert sample data to verify the integrity and accuracy of the relationships.
Test CRUD operations for each table.

Module 5: Visits

Description

This module manages customer visits to the repair shop, including details of the visit and tasks performed during the visit. It is essential for tracking the actual work done on vehicles and billing customers.

Entities and Attributes

Visit: Stores information about customer visits.
- visit_id: Primary key
- repair_shop_id: Foreign key to the RepairShop table
- customer_id: Foreign key to the Customer table
- vehicle_id: Foreign key to the Vehicle table
- visit_start_date: Planned start date of the visit
- visit_start_time: Planned start time of the visit
- visit_end_date: Actual end date of the visit
- visit_end_time: Actual end time of the visit
- license_plate: License plate number at the time of the visit
- offer_id: Foreign key to the Offer table (nullable)
- service_catalog_id: Foreign key to the ServiceCatalog table (nullable)
- service_discount: Discount on the service
- visit_price: Total price for the visit
- invoice_created: Timestamp when the invoice was created
- invoice_due: Timestamp when the invoice is due
- invoice_charged: Timestamp when the invoice was charged
- insert_ts: Timestamp of when the record was inserted
VisitTask: Links tasks to visits.
- visit_task_id: Primary key
- visit_id: Foreign key to the Visit table
- task_catalog_id: Foreign key to the TaskCatalog table
- value_measured: Measured value during the task
- task_description: Description of the task
- pass: Boolean flag indicating if the task was within the expected interval
- task_price: Price of the task
- insert_ts: Timestamp of when the record was inserted

Relationships

Each Visit can include multiple VisitTasks.
Each TaskCatalog can be part of multiple VisitTasks.
Each RepairShop can have multiple Visits.
Each Customer can have multiple Visits.
Each Vehicle can have multiple Visits.
Each Offer can result in multiple Visits.
Each ServiceCatalog can include multiple Visits.

Testing

Create tables and establish relationships in MySQL Workbench.
Insert sample data to verify the integrity and accuracy of the relationships.
Test CRUD operations for each table.

Additional Functionalities

Notification System

A notification system is essential for keeping customers updated about the status of their repairs. This system will notify customers when their repair starts and when it finishes.

Notification Entity:

notification_id: Primary key
visit_id: Foreign key to the Visit table
message: Notification message
notification_type: Type of notification (e.g., ‘start’, ‘finish’)
sent_at: Timestamp of when the notification was sent

Billing System (using Stripe)

Integrating with Stripe for payments ensures a smooth and secure billing process.

Payment Entity:

payment_id: Primary key
visit_id: Foreign key to the Visit table
amount: Payment amount
currency: Payment currency
payment_status: Status of the payment
stripe_charge_id: Stripe charge ID
created_at: Timestamp of when the payment was created
updated_at: Timestamp of when the payment was last updated

Media Upload System (using Multer and Cloudinary)

Employees can upload photos of vehicle parts or the entire vehicle for customer reference. Using Multer and Cloudinary ensures efficient media management.

Media Entity:

media_id: Primary key
visit_id: Foreign key to the Visit table
employee_id: Foreign key to the Employee table
url: URL of the uploaded media
public_id: Public ID in Cloudinary
uploaded_at: Timestamp of when the media was uploaded

Admin and Employee Access

Admin: Manages overall operations and user access.
- admin_id: Primary key
- username: Admin username
- password_hash: Password hash for security
- email: Admin email
Employee Access: Manages employee authentication and operations.
- employee_id: Primary key, foreign key to the Employee table
- username: Employee username
- password_hash: Password hash for security
- email: Employee email

This comprehensive database design document provides a detailed roadmap for building a car repair shop management system. By following the outlined steps and testing procedures, developers can ensure a robust and efficient system that meets the needs of both the business and its customers.

Module 1: Repair Shops & Employees

The first module in the Car Repair Shop Management System focuses on managing the repair shops and their employees. This module includes entities and attributes necessary to capture the details of repair shops, employees, and their schedules. By implementing this module, the repair shop can ensure efficient workforce management and optimize resource allocation.

Entities and Attributes

RepairShop

The RepairShop entity represents a repair shop location. It captures essential details about each shop.

repair_shop_id (PK): Unique identifier for the repair shop.
shop_name: Name of the repair shop.
address: Physical address of the repair shop.
city_id (FK): Reference to the city where the repair shop is located.
phone: Contact phone number for the repair shop.
email: Contact email address for the repair shop.
insert_ts: Timestamp when the record was inserted.

Employee

The Employee entity stores details about the employees working in the repair shop.

employee_id (PK): Unique identifier for the employee.
first_name: First name of the employee.
last_name: Last name of the employee.
employment_start_date: Date when the employee started working at the repair shop.
employment_end_date: Date when the employee stopped working at the repair shop (nullable).
position_id (FK): Reference to the position held by the employee.
city_id (FK): Reference to the city where the employee resides.
is_active: Flag indicating if the employee is currently active.
insert_ts: Timestamp when the record was inserted.

Schedule

The Schedule entity manages the work schedules of employees. It captures planned and actual work intervals.

schedule_id (PK): Unique identifier for the schedule entry.
repair_shop_id (FK): Reference to the related repair shop.
employee_id (FK): Reference to the related employee.
position_id (FK): Reference to the position held by the employee during the scheduled time.
schedule_date: Date for the schedule entry.
time_from: Start time for the schedule entry.
time_to: End time for the schedule entry.
plan: Flag indicating if this entry was planned.
actual: Flag indicating if this entry was realized.
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A RepairShop can have multiple Employee records.
An Employee can have multiple Schedule entries.
A Schedule entry is linked to one RepairShop, one Employee, and one Position.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE City (
    city_id INT PRIMARY KEY AUTO_INCREMENT,
    city_name VARCHAR(100) NOT NULL
);

CREATE TABLE Position (
    position_id INT PRIMARY KEY AUTO_INCREMENT,
    position_name VARCHAR(100) NOT NULL
);

CREATE TABLE RepairShop (
    repair_shop_id INT PRIMARY KEY AUTO_INCREMENT,
    shop_name VARCHAR(100) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city_id INT,
    phone VARCHAR(15),
    email VARCHAR(100),
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES City(city_id)
);

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    employment_start_date DATE NOT NULL,
    employment_end_date DATE,
    position_id INT,
    city_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (position_id) REFERENCES Position(position_id),
    FOREIGN KEY (city_id) REFERENCES City(city_id)
);

CREATE TABLE Schedule (
    schedule_id INT PRIMARY KEY AUTO_INCREMENT,
    repair_shop_id INT,
    employee_id INT,
    position_id INT,
    schedule_date DATE NOT NULL,
    time_from TIME NOT NULL,
    time_to TIME NOT NULL,
    plan BOOLEAN DEFAULT TRUE,
    actual BOOLEAN DEFAULT TRUE,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (repair_shop_id) REFERENCES RepairShop(repair_shop_id),
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id),
    FOREIGN KEY (position_id) REFERENCES Position(position_id),
    UNIQUE(repair_shop_id, employee_id, schedule_date, time_from)
);

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the City, Position, RepairShop, Employee, and Schedule tables.

-- Insert sample cities
INSERT INTO City (city_name) VALUES ('New York'), ('Los Angeles'), ('Chicago');

-- Insert sample positions
INSERT INTO Position (position_name) VALUES ('Mechanic'), ('Manager'), ('Receptionist');

-- Insert sample repair shops
INSERT INTO RepairShop (shop_name, address, city_id, phone, email) 
VALUES 
('Downtown Auto Repair', '123 Main St', 1, '555-1234', 'info@downtownrepair.com'),
('Westside Repair Shop', '456 Elm St', 2, '555-5678', 'contact@westsiderepair.com');

-- Insert sample employees
INSERT INTO Employee (first_name, last_name, employment_start_date, position_id, city_id) 
VALUES 
('John', 'Doe', '2020-01-15', 1, 1),
('Jane', 'Smith', '2019-03-22', 2, 2);

-- Insert sample schedules
INSERT INTO Schedule (repair_shop_id, employee_id, position_id, schedule_date, time_from, time_to) 
VALUES 
(1, 1, 1, '2024-07-01', '08:00:00', '17:00:00'),
(1, 2, 2, '2024-07-01', '09:00:00', '18:00:00');

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all employees in a specific repair shop
SELECT e.first_name, e.last_name, r.shop_name
FROM Employee e
JOIN RepairShop r ON e.city_id = r.city_id
WHERE r.repair_shop_id = 1;

-- Retrieve schedule for a specific employee
SELECT s.schedule_date, s.time_from, s.time_to, r.shop_name
FROM Schedule s
JOIN RepairShop r ON s.repair_shop_id = r.repair_shop_id
WHERE s.employee_id = 1;

-- Retrieve all active employees
SELECT first_name, last_name
FROM Employee
WHERE is_active = TRUE;

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that schedules do not overlap for the same employee on the same date.

The first module, Repair Shops & Employees, establishes the foundation for managing the workforce and facilities in a car repair shop. By implementing this module, the repair shop can efficiently manage employee schedules and ensure optimal resource allocation. This module serves as the cornerstone for subsequent modules, enabling a structured and incremental development approach for the entire Car Repair Shop Management System.

Module 2: Customers & Contacts

The second module in the Car Repair Shop Management System focuses on managing customer information and their interactions with the repair shop. This module includes entities and attributes necessary to capture customer details, contact types, and the interactions between customers and employees. Efficient management of this data ensures personalized service and helps build strong customer relationships.

Entities and Attributes

Customer

The Customer entity represents an individual or a company that has engaged with the repair shop.

customer_id (PK): Unique identifier for the customer.
first_name: First name of the customer (nullable if the customer is a company).
last_name: Last name of the customer (nullable if the customer is a company).
company_name: Name of the company (nullable if the customer is an individual).
address: Physical address of the customer.
mobile: Mobile phone number of the customer.
email: Email address of the customer.
details: Additional details about the customer.
insert_ts: Timestamp when the record was inserted.

ContactType

The ContactType entity is a dictionary table that represents different types of contacts made with customers.

contact_type_id (PK): Unique identifier for the contact type.
type_name: Name of the contact type.

Contact

The Contact entity stores details of interactions between employees and customers.

contact_id (PK): Unique identifier for the contact.
contact_type_id (FK): Reference to the type of contact.
customer_id (FK): Reference to the customer involved in the contact.
employee_id (FK): Reference to the employee who made the contact.
contact_details: Details of the contact.
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A Customer can have multiple Contact records.
An Employee can have multiple Contact records.
A Contact is linked to one ContactType, one Customer, and one Employee.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    company_name VARCHAR(100),
    address VARCHAR(255),
    mobile VARCHAR(15),
    email VARCHAR(100),
    details TEXT,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ContactType (
    contact_type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(100) NOT NULL
);

CREATE TABLE Contact (
    contact_id INT PRIMARY KEY AUTO_INCREMENT,
    contact_type_id INT,
    customer_id INT,
    employee_id INT,
    contact_details TEXT,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_type_id) REFERENCES ContactType(contact_type_id),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the Customer, ContactType, and Contact tables.

-- Insert sample customers
INSERT INTO Customer (first_name, last_name, company_name, address, mobile, email, details)
VALUES 
('John', 'Doe', NULL, '123 Main St', '555-1234', 'john.doe@example.com', 'Regular customer'),
(NULL, NULL, 'ABC Corp', '456 Elm St', '555-5678', 'contact@abccorp.com', 'Corporate client');

-- Insert sample contact types
INSERT INTO ContactType (type_name) VALUES ('Phone Call'), ('Email'), ('In-Person');

-- Insert sample contacts
INSERT INTO Contact (contact_type_id, customer_id, employee_id, contact_details)
VALUES 
(1, 1, 1, 'Discussed repair options over the phone'),
(2, 2, 2, 'Sent invoice via email');

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all contacts for a specific customer
SELECT c.contact_id, ct.type_name, e.first_name AS employee_first_name, e.last_name AS employee_last_name, c.contact_details
FROM Contact c
JOIN ContactType ct ON c.contact_type_id = ct.contact_type_id
JOIN Employee e ON c.employee_id = e.employee_id
WHERE c.customer_id = 1;

-- Retrieve all contacts made by a specific employee
SELECT c.contact_id, ct.type_name, cu.first_name AS customer_first_name, cu.last_name AS customer_last_name, cu.company_name, c.contact_details
FROM Contact c
JOIN ContactType ct ON c.contact_type_id = ct.contact_type_id
JOIN Customer cu ON c.customer_id = cu.customer_id
WHERE c.employee_id = 1;

-- Retrieve all contact types
SELECT * FROM ContactType;

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that contacts are correctly linked to customers, employees, and contact types.

The second module, Customers & Contacts, is essential for managing customer information and their interactions with the repair shop. By implementing this module, the repair shop can provide personalized service, build strong customer relationships, and track interactions effectively. This module integrates seamlessly with the first module, creating a comprehensive system for managing repair shop operations.

Module 3: Vehicles

The third module in the Car Repair Shop Management System focuses on managing vehicle information. This module includes entities and attributes necessary to capture details about vehicle makes, models, types, and individual vehicles. Efficient management of this data ensures accurate tracking of vehicle histories and facilitates reporting for regulatory agencies and insurance companies.

Entities and Attributes

Make

The Make entity represents a vehicle manufacturer.

make_id (PK): Unique identifier for the vehicle make.
make_name: Name of the vehicle make.

VehicleType

The VehicleType entity is a dictionary table that represents different types of vehicles.

vehicle_type_id (PK): Unique identifier for the vehicle type.
type_name: Name of the vehicle type.

Model

The Model entity represents a specific model of a vehicle.

model_id (PK): Unique identifier for the vehicle model.
model_name: Name of the vehicle model.
make_id (FK): Reference to the vehicle make.
vehicle_type_id (FK): Reference to the vehicle type.

Vehicle

The Vehicle entity stores details about individual vehicles.

vehicle_id (PK): Unique identifier for the vehicle.
vin: Vehicle Identification Number.
license_plate: Current license plate number.
customer_id (FK): Reference to the customer who owns the vehicle.
model_id (FK): Reference to the vehicle model.
manufactured_year: Year the vehicle was manufactured.
manufactured_month: Month the vehicle was manufactured.
details: Additional details about the vehicle.
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A Make can have multiple Model records.
A VehicleType can have multiple Model records.
A Model is linked to one Make and one VehicleType.
A Customer can have multiple Vehicle records.
A Vehicle is linked to one Model and one Customer.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE Make (
    make_id INT PRIMARY KEY AUTO_INCREMENT,
    make_name VARCHAR(100) NOT NULL
);

CREATE TABLE VehicleType (
    vehicle_type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(100) NOT NULL
);

CREATE TABLE Model (
    model_id INT PRIMARY KEY AUTO_INCREMENT,
    model_name VARCHAR(100) NOT NULL,
    make_id INT,
    vehicle_type_id INT,
    FOREIGN KEY (make_id) REFERENCES Make(make_id),
    FOREIGN KEY (vehicle_type_id) REFERENCES VehicleType(vehicle_type_id)
);

CREATE TABLE Vehicle (
    vehicle_id INT PRIMARY KEY AUTO_INCREMENT,
    vin VARCHAR(17) NOT NULL,
    license_plate VARCHAR(10) NOT NULL,
    customer_id INT,
    model_id INT,
    manufactured_year YEAR,
    manufactured_month INT,
    details TEXT,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (model_id) REFERENCES Model(model_id)
);

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the Make, VehicleType, Model, and Vehicle tables.

-- Insert sample makes
INSERT INTO Make (make_name) VALUES ('Toyota'), ('Ford'), ('BMW');

-- Insert sample vehicle types
INSERT INTO VehicleType (type_name) VALUES ('Sedan'), ('SUV'), ('Truck');

-- Insert sample models
INSERT INTO Model (model_name, make_id, vehicle_type_id)
VALUES 
('Camry', 1, 1),
('RAV4', 1, 2),
('F-150', 2, 3),
('X5', 3, 2);

-- Insert sample vehicles
INSERT INTO Vehicle (vin, license_plate, customer_id, model_id, manufactured_year, manufactured_month, details)
VALUES 
('1HGCM82633A123456', 'ABC123', 1, 1, 2018, 5, 'Black sedan in good condition'),
('1FAFP404X2F123456', 'XYZ789', 2, 3, 2020, 8, 'Blue truck used for deliveries');

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all vehicles for a specific customer
SELECT v.vin, v.license_plate, m.model_name, mk.make_name, vt.type_name, v.manufactured_year, v.manufactured_month, v.details
FROM Vehicle v
JOIN Model m ON v.model_id = m.model_id
JOIN Make mk ON m.make_id = mk.make_id
JOIN VehicleType vt ON m.vehicle_type_id = vt.vehicle_type_id
WHERE v.customer_id = 1;

-- Retrieve all models for a specific make
SELECT m.model_name, vt.type_name
FROM Model m
JOIN VehicleType vt ON m.vehicle_type_id = vt.vehicle_type_id
WHERE m.make_id = 1;

-- Retrieve all makes and their corresponding models
SELECT mk.make_name, m.model_name
FROM Make mk
JOIN Model m ON mk.make_id = m.make_id;

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that vehicles are correctly linked to models, makes, and customers.

The third module, Vehicles, is essential for managing information about the vehicles serviced by the repair shop. By implementing this module, the repair shop can accurately track vehicle details, maintain repair histories, and generate necessary reports. This module integrates seamlessly with the previous modules, creating a comprehensive system for managing repair shop operations.

Module 4: Services & Offers

The fourth module in the Car Repair Shop Management System focuses on managing the services offered by the repair shop and recording offers made to customers. This module includes entities and attributes necessary to capture details about services, tasks, and offers. Efficient management of this data ensures transparency, helps optimize service offerings, and facilitates accurate pricing.

Entities and Attributes

ServiceCatalog

The ServiceCatalog entity represents a list of services offered by the repair shop.

service_catalog_id (PK): Unique identifier for the service.
service_name: Name of the service.
description: Description of the service.
service_discount: Discount percentage applicable to the service.
is_active: Flag indicating if the service is currently active.

TaskCatalog

The TaskCatalog entity represents individual tasks that are part of services.

task_catalog_id (PK): Unique identifier for the task.
task_name: Name of the task.
service_catalog_id (FK): Reference to the service the task belongs to.
description: Description of the task.
ref_interval: Flag indicating if the task has a reference interval.
ref_interval_min: Minimum reference interval for the task.
ref_interval_max: Maximum reference interval for the task.
task_price: Price of the task.
is_active: Flag indicating if the task is currently active.

Offer

The Offer entity represents offers made to customers.

offer_id (PK): Unique identifier for the offer.
customer_id (FK): Reference to the customer the offer is made to.
contact_id (FK): Reference to the contact through which the offer was made.
offer_description: Description of the offer.
service_catalog_id (FK): Reference to the service offered (nullable).
service_discount: Discount percentage applicable to the service at the time of the offer.
offer_price: Total price of the offer.
insert_ts: Timestamp when the record was inserted.

OfferTask

The OfferTask entity links tasks to offers.

offer_task_id (PK): Unique identifier for the offer task.
offer_id (FK): Reference to the related offer.
task_catalog_id (FK): Reference to the related task.
task_price: Price of the task at the time of the offer.
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A ServiceCatalog can have multiple TaskCatalog records.
A Customer can have multiple Offer records.
An Offer can have multiple OfferTask records.
A TaskCatalog can belong to one ServiceCatalog.
An OfferTask links one Offer with one TaskCatalog.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE ServiceCatalog (
    service_catalog_id INT PRIMARY KEY AUTO_INCREMENT,
    service_name VARCHAR(100) NOT NULL,
    description TEXT,
    service_discount DECIMAL(5,2),
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE TaskCatalog (
    task_catalog_id INT PRIMARY KEY AUTO_INCREMENT,
    task_name VARCHAR(100) NOT NULL,
    service_catalog_id INT,
    description TEXT,
    ref_interval BOOLEAN DEFAULT FALSE,
    ref_interval_min INT,
    ref_interval_max INT,
    task_price DECIMAL(10,2) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (service_catalog_id) REFERENCES ServiceCatalog(service_catalog_id)
);

CREATE TABLE Offer (
    offer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    contact_id INT,
    offer_description TEXT,
    service_catalog_id INT,
    service_discount DECIMAL(5,2),
    offer_price DECIMAL(10,2) NOT NULL,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (contact_id) REFERENCES Contact(contact_id),
    FOREIGN KEY (service_catalog_id) REFERENCES ServiceCatalog(service_catalog_id)
);

CREATE TABLE OfferTask (
    offer_task_id INT PRIMARY KEY AUTO_INCREMENT,
    offer_id INT,
    task_catalog_id INT,
    task_price DECIMAL(10,2) NOT NULL,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (offer_id) REFERENCES Offer(offer_id),
    FOREIGN KEY (task_catalog_id) REFERENCES TaskCatalog(task_catalog_id)
);

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the ServiceCatalog, TaskCatalog, Offer, and OfferTask tables.

-- Insert sample services
INSERT INTO ServiceCatalog (service_name, description, service_discount)
VALUES 
('Oil Change', 'Complete oil change service', 10.00),
('Brake Replacement', 'Replacement of brake pads and rotors', 15.00);

-- Insert sample tasks
INSERT INTO TaskCatalog (task_name, service_catalog_id, description, task_price)
VALUES 
('Oil Filter Replacement', 1, 'Replace the oil filter', 25.00),
('Engine Oil Replacement', 1, 'Replace the engine oil', 50.00),
('Brake Pad Replacement', 2, 'Replace the brake pads', 80.00),
('Rotor Resurfacing', 2, 'Resurface the brake rotors', 40.00);

-- Insert sample offers
INSERT INTO Offer (customer_id, contact_id, offer_description, service_catalog_id, service_discount, offer_price)
VALUES 
(1, 1, 'Oil change offer for John Doe', 1, 10.00, 67.50),
(2, 2, 'Brake replacement offer for ABC Corp', 2, 15.00, 102.00);

-- Insert sample offer tasks
INSERT INTO OfferTask (offer_id, task_catalog_id, task_price)
VALUES 
(1, 1, 25.00),
(1, 2, 50.00),
(2, 3, 80.00),
(2, 4, 40.00);

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all tasks for a specific service
SELECT t.task_name, t.description, t.task_price
FROM TaskCatalog t
JOIN ServiceCatalog s ON t.service_catalog_id = s.service_catalog_id
WHERE s.service_catalog_id = 1;

-- Retrieve all offers for a specific customer
SELECT o.offer_id, o.offer_description, s.service_name, o.service_discount, o.offer_price
FROM Offer o
JOIN ServiceCatalog s ON o.service_catalog_id = s.service_catalog_id
WHERE o.customer_id = 1;

-- Retrieve all tasks included in a specific offer
SELECT ot.offer_task_id, t.task_name, ot.task_price
FROM OfferTask ot
JOIN TaskCatalog t ON ot.task_catalog_id = t.task_catalog_id
WHERE ot.offer_id = 1;

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that offers and tasks are correctly linked to services and customers.

The fourth module, Services & Offers, is essential for managing the services offered by the repair shop and recording offers made to customers. By implementing this module, the repair shop can provide detailed and accurate service offerings, track offers, and ensure transparency in pricing. This module integrates seamlessly with the previous modules, creating a comprehensive system for managing repair shop operations.

Module 5: Visits

The fifth module in the Car Repair Shop Management System focuses on managing customer visits to the repair shop. This module includes entities and attributes necessary to capture details about visits, the services performed, and the tasks completed. Efficient management of this data ensures accurate tracking of repair work, billing, and customer interactions.

Entities and Attributes

Visit

The Visit entity represents a customer visit to the repair shop.

visit_id (PK): Unique identifier for the visit.
repair_shop_id (FK): Reference to the repair shop where the visit occurred.
customer_id (FK): Reference to the customer who made the visit.
vehicle_id (FK): Reference to the vehicle brought in for the visit.
visit_start_date: Planned start date of the visit.
visit_start_time: Planned start time of the visit.
visit_end_date: Actual end date of the visit.
visit_end_time: Actual end time of the visit.
license_plate: License plate number at the time of the visit.
offer_id (FK): Reference to the offer related to the visit.
service_catalog_id (FK): Reference to the service performed (nullable).
service_discount: Discount percentage applicable to the service at the time of the visit.
visit_price: Total price of the visit.
invoice_created: Timestamp when the invoice was generated.
invoice_due: Timestamp when the invoice is due.
invoice_charged: Timestamp when the invoice was paid.
insert_ts: Timestamp when the record was inserted.

VisitTask

The VisitTask entity records tasks completed during a visit.

visit_task_id (PK): Unique identifier for the visit task.
visit_id (FK): Reference to the related visit.
task_catalog_id (FK): Reference to the related task.
value_measured: Value measured during the task (if applicable).
task_description: Description related to the task (if applicable).
pass: Flag indicating if the task was completed within the expected interval.
task_price: Price of the task at the time of the visit.
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A RepairShop can have multiple Visit records.
A Customer can have multiple Visit records.
A Vehicle can have multiple Visit records.
An Offer can be linked to multiple Visit records.
A ServiceCatalog can be linked to multiple Visit records.
A Visit can have multiple VisitTask records.
A VisitTask is linked to one Visit and one TaskCatalog.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE Visit (
    visit_id INT PRIMARY KEY AUTO_INCREMENT,
    repair_shop_id INT,
    customer_id INT,
    vehicle_id INT,
    visit_start_date DATE,
    visit_start_time TIME,
    visit_end_date DATE,
    visit_end_time TIME,
    license_plate VARCHAR(10),
    offer_id INT,
    service_catalog_id INT,
    service_discount DECIMAL(5,2),
    visit_price DECIMAL(10,2),
    invoice_created TIMESTAMP,
    invoice_due TIMESTAMP,
    invoice_charged TIMESTAMP,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (repair_shop_id) REFERENCES RepairShop(repair_shop_id),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (vehicle_id) REFERENCES Vehicle(vehicle_id),
    FOREIGN KEY (offer_id) REFERENCES Offer(offer_id),
    FOREIGN

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the Visit and VisitTask tables.

-- Insert sample visits
INSERT INTO Visit (repair_shop_id, customer_id, vehicle_id, visit_start_date, visit_start_time, license_plate, offer_id, service_catalog_id, service_discount, visit_price)
VALUES 
(1, 1, 1, '2024-07-01', '09:00:00', 'ABC123', 1, 1, 10.00, 67.50),
(2, 2, 2, '2024-07-02', '10:00:00', 'XYZ789', 2, 2, 15.00, 102.00);

-- Insert sample visit tasks
INSERT INTO VisitTask (visit_id, task_catalog_id, value_measured, task_description, pass, task_price)
VALUES 
(1, 1, NULL, 'Oil filter replaced', TRUE, 25.00),
(1, 2, NULL, 'Engine oil replaced', TRUE, 50.00),
(2, 3, NULL, 'Brake pads replaced', TRUE, 80.00),
(2, 4, NULL, 'Rotors resurfaced', TRUE, 40.00);

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all visits for a specific customer
SELECT v.visit_id, v.visit_start_date, v.visit_start_time, v.visit_end_date, v.visit_end_time, s.shop_name, v.visit_price
FROM Visit v
JOIN RepairShop s ON v.repair_shop_id = s.repair_shop_id
WHERE v.customer_id = 1;

-- Retrieve all tasks for a specific visit
SELECT vt.visit_task_id, t.task_name, vt.task_description, vt.task_price
FROM VisitTask vt
JOIN TaskCatalog t ON vt.task_catalog_id = t.task_catalog_id
WHERE vt.visit_id = 1;

-- Retrieve all visits within a specific date range
SELECT v.visit_id, v.visit_start_date, v.visit_start_time, c.first_name, c.last_name, v.visit_price
FROM Visit v
JOIN Customer c ON v.customer_id = c.customer_id
WHERE v.visit_start_date BETWEEN '2024-07-01' AND '2024-07-31';

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that visits and tasks are correctly linked to repair shops, customers, vehicles, and services.

The fifth module, Visits, is essential for managing customer visits to the repair shop, tracking the services performed, and recording the tasks completed. By implementing this module, the repair shop can accurately track repair work, manage billing, and ensure a comprehensive record of customer interactions. This module integrates seamlessly with the previous modules, creating a complete system for managing repair shop operations.

Module 6: Notification System

The sixth module in the Car Repair Shop Management System focuses on managing notifications sent to customers regarding the status of their vehicle repairs. This module includes entities and attributes necessary to capture notification details and ensure customers are informed about the progress of their service. Efficient management of this data enhances customer satisfaction and improves communication.

Entities and Attributes

NotificationType

The NotificationType entity is a dictionary table that represents different types of notifications.

notification_type_id (PK): Unique identifier for the notification type.
type_name: Name of the notification type (e.g., “Repair Started”, “Repair Finished”).

Notification

The Notification entity stores details about notifications sent to customers.

notification_id (PK): Unique identifier for the notification.
notification_type_id (FK): Reference to the type of notification.
visit_id (FK): Reference to the related visit.
customer_id (FK): Reference to the customer receiving the notification.
employee_id (FK): Reference to the employee who sent the notification.
message: Content of the notification message.
sent_at: Timestamp when the notification was sent.
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A NotificationType can have multiple Notification records.
A Visit can have multiple Notification records.
A Customer can receive multiple Notification records.
An Employee can send multiple Notification records.
A Notification is linked to one NotificationType, one Visit, one Customer, and one Employee.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE NotificationType (
    notification_type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(100) NOT NULL
);

CREATE TABLE Notification (
    notification_id INT PRIMARY KEY AUTO_INCREMENT,
    notification_type_id INT,
    visit_id INT,
    customer_id INT,
    employee_id INT,
    message TEXT,
    sent_at TIMESTAMP,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (notification_type_id) REFERENCES NotificationType(notification_type_id),
    FOREIGN KEY (visit_id) REFERENCES Visit(visit_id),
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the NotificationType and Notification tables.

-- Insert sample notification types
INSERT INTO NotificationType (type_name) VALUES ('Repair Started'), ('Repair Finished');

-- Insert sample notifications
INSERT INTO Notification (notification_type_id, visit_id, customer_id, employee_id, message, sent_at)
VALUES 
(1, 1, 1, 1, 'Your vehicle repair has started.', '2024-07-01 09:00:00'),
(2, 1, 1, 1, 'Your vehicle repair is complete.', '2024-07-01 17:00:00'),
(1, 2, 2, 2, 'Repair work on your vehicle has begun.', '2024-07-02 10:00:00'),
(2, 2, 2, 2, 'Your vehicle is ready for pickup.', '2024-07-02 18:00:00');

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all notifications for a specific customer
SELECT n.notification_id, nt.type_name, n.message, n.sent_at
FROM Notification n
JOIN NotificationType nt ON n.notification_type_id = nt.notification_type_id
WHERE n.customer_id = 1;

-- Retrieve all notifications related to a specific visit
SELECT n.notification_id, nt.type_name, n.message, n.sent_at
FROM Notification n
JOIN NotificationType nt ON n.notification_type_id = nt.notification_type_id
WHERE n.visit_id = 1;

-- Retrieve all notifications sent by a specific employee
SELECT n.notification_id, nt.type_name, n.message, n.sent_at
FROM Notification n
JOIN NotificationType nt ON n.notification_type_id = nt.notification_type_id
WHERE n.employee_id = 1;

-- Retrieve all notifications of a specific type
SELECT n.notification_id, v.visit_id, c.first_name, c.last_name, n.message, n.sent_at
FROM Notification n
JOIN NotificationType nt ON n.notification_type_id = nt.notification_type_id
JOIN Visit v ON n.visit_id = v.visit_id
JOIN Customer c ON n.customer_id = c.customer_id
WHERE nt.type_name = 'Repair Started';

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that notifications are correctly linked to notification types, visits, customers, and employees.

The sixth module, Notification System, is essential for managing notifications sent to customers regarding the status of their vehicle repairs. By implementing this module, the repair shop can ensure timely communication with customers, enhancing customer satisfaction and improving overall service quality. This module integrates seamlessly with the previous modules, creating a comprehensive system for managing repair shop operations.

Module 7: Billing System

The seventh module in the Car Repair Shop Management System focuses on managing invoicing and payments. This module includes entities and attributes necessary to capture billing details, generate invoices, and track payment statuses. Efficient management of this data ensures accurate billing, timely payments, and proper financial management.

Entities and Attributes

Invoice

The Invoice entity represents an invoice issued to a customer for services rendered.

invoice_id (PK): Unique identifier for the invoice.
visit_id (FK): Reference to the related visit.
amount: Total amount of the invoice.
due_date: Date by which the invoice is due.
paid_date: Date when the invoice was paid (nullable).
status: Current status of the invoice (e.g., “Pending”, “Paid”).
insert_ts: Timestamp when the record was inserted.

Relationships

The relationships between the entities in this module are as follows:

A Visit can generate multiple Invoice records (in case of split billing).
An Invoice is linked to one Visit.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE Invoice (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    visit_id INT,
    amount DECIMAL(10,2) NOT NULL,
    due_date DATE NOT NULL,
    paid_date DATE,
    status VARCHAR(20) NOT NULL,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES Visit(visit_id)
);

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the Invoice table.

-- Insert sample invoices
INSERT INTO Invoice (visit_id, amount, due_date, status)
VALUES 
(1, 67.50, '2024-07-15', 'Pending'),
(2, 102.00, '2024-07-20', 'Pending');

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all invoices for a specific customer
SELECT i.invoice_id, v.visit_start_date, i.amount, i.due_date, i.paid_date, i.status
FROM Invoice i
JOIN Visit v ON i.visit_id = v.visit_id
WHERE v.customer_id = 1;

-- Retrieve all pending invoices
SELECT i.invoice_id, v.visit_start_date, c.first_name, c.last_name, i.amount, i.due_date
FROM Invoice i
JOIN Visit v ON i.visit_id = v.visit_id
JOIN Customer c ON v.customer_id = c.customer_id
WHERE i.status = 'Pending';

-- Retrieve invoice details for a specific visit
SELECT i.invoice_id, i.amount, i.due_date, i.paid_date, i.status
FROM Invoice i
WHERE i.visit_id = 1;

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that invoices are correctly linked to visits and contain accurate billing information.

The seventh module, Billing System, is essential for managing invoicing and payments in the repair shop. By implementing this module, the repair shop can ensure accurate billing, track payment statuses, and maintain proper financial records. This module integrates seamlessly with the previous modules, creating a comprehensive system for managing repair shop operations.

Module 8: Admin Area

The eighth module in the Car Repair Shop Management System focuses on the administrative functions necessary for managing the repair shop’s operations. This module includes entities and attributes necessary to provide administrators with access to detailed records of customers, employees, work done, and the status of vehicles. Efficient management of this data ensures that administrators can oversee operations effectively and make informed decisions.

Entities and Attributes

Admin

The Admin entity represents an administrative user in the system.

admin_id (PK): Unique identifier for the admin.
username: Username of the admin.
password: Password for the admin (stored securely).
first_name: First name of the admin.
last_name: Last name of the admin.
email: Email address of the admin.
phone: Phone number of the admin.
insert_ts: Timestamp when the record was inserted.

AdminLog

The AdminLog entity records actions performed by administrators.

log_id (PK): Unique identifier for the log entry.
admin_id (FK): Reference to the admin who performed the action.
action: Description of the action performed.
timestamp: Timestamp when the action was performed.

Relationships

The relationships between the entities in this module are as follows:

An Admin can have multiple AdminLog records.
An AdminLog is linked to one Admin.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE Admin (
    admin_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,  -- Note: In a real system, passwords should be hashed
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE AdminLog (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT,
    action TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES Admin(admin_id)
);

Features for the Admin Area

1. View Customer Records

Admins can view detailed records of all customers, including their contact information, vehicles, and repair history.

2. View Employee Records

Admins can access information about employees, including their schedules, positions, and work history.

3. View Work Done

Admins can see records of all repair work done, including details of services and tasks performed.

4. Vehicle Status

Admins can check the status of any vehicle currently being serviced, including the progress of repairs and estimated completion times.

5. Generate Reports

Admins can generate various reports, such as total sales, employee performance, and customer feedback.

6. Manage Notifications

Admins can view and manage notifications sent to customers.

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the Admin and AdminLog tables.

-- Insert sample admins
INSERT INTO Admin (username, password, first_name, last_name, email, phone)
VALUES 
('admin1', 'password1', 'Alice', 'Johnson', 'alice.johnson@example.com', '555-1234'),
('admin2', 'password2', 'Bob', 'Smith', 'bob.smith@example.com', '555-5678');

-- Insert sample admin logs
INSERT INTO AdminLog (admin_id, action)
VALUES 
(1, 'Added new customer record'),
(1, 'Updated employee schedule'),
(2, 'Generated sales report');

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all admin users
SELECT admin_id, username, first_name, last_name, email, phone
FROM Admin;

-- Retrieve all actions performed by a specific admin
SELECT log_id, action, timestamp
FROM AdminLog
WHERE admin_id = 1;

-- Retrieve all logs within a specific date range
SELECT log_id, admin_id, action, timestamp
FROM AdminLog
WHERE timestamp BETWEEN '2024-07-01' AND '2024-07-31';

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that admin logs are correctly linked to admins and accurately record actions performed.

The eighth module, Admin Area, is essential for providing administrative oversight and management capabilities in the repair shop. By implementing this module, administrators can effectively manage customer and employee records, monitor repair work, and generate reports. This module integrates seamlessly with the previous modules, creating a comprehensive system for managing repair shop operations.

Implementation of Admin Area Features

Feature 1: View Customer Records

SQL Query to Retrieve Customer Records

Admins can view detailed records of all customers, including their contact information, vehicles, and repair history.

-- Retrieve customer details along with their vehicles and repair history
SELECT 
    c.customer_id, 
    c.first_name, 
    c.last_name, 
    c.company_name, 
    c.address, 
    c.mobile, 
    c.email, 
    v.vin, 
    v.license_plate, 
    v.manufactured_year, 
    v.manufactured_month, 
    s.service_name, 
    vt.visit_start_date, 
    vt.visit_end_date
FROM 
    Customer c
LEFT JOIN 
    Vehicle v ON c.customer_id = v.customer_id
LEFT JOIN 
    Visit vt ON c.customer_id = vt.customer_id
LEFT JOIN 
    ServiceCatalog s ON vt.service_catalog_id = s.service_catalog_id
ORDER BY 
    c.customer_id, v.vehicle_id, vt.visit_id;

Web Interface Mockup

Admins can use a web interface to view customer records. This would include search and filter options to find specific customers and view their detailed records.

<!-- Customer Records Page -->
<!DOCTYPE html>
<html>
<head>
    <title>Customer Records</title>
</head>
<body>
    <h1>Customer Records</h1>
    <input type="text" id="searchCustomer" placeholder="Search Customers..." onkeyup="searchCustomer()">
    <table id="customerTable">
        <thead>
            <tr>
                <th>Customer ID</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Company Name</th>
                <th>Address</th>
                <th>Mobile</th>
                <th>Email</th>
                <th>VIN</th>
                <th>License Plate</th>
                <th>Manufactured Year</th>
                <th>Manufactured Month</th>
                <th>Service Name</th>
                <th>Visit Start Date</th>
                <th>Visit End Date</th>
            </tr>
        </thead>
        <tbody>
            <!-- Data fetched from database will be populated here -->
        </tbody>
    </table>
    <script>
        function searchCustomer() {
            // JavaScript function to filter customer records
        }
    </script>
</body>
</html>

Feature 2: View Employee Records

SQL Query to Retrieve Employee Records

Admins can access information about employees, including their schedules, positions, and work history.

-- Retrieve employee details along with their schedules and positions
SELECT 
    e.employee_id, 
    e.first_name, 
    e.last_name, 
    e.employment_start_date, 
    e.employment_end_date, 
    p.position_name, 
    s.schedule_date, 
    s.time_from, 
    s.time_to, 
    s.plan, 
    s.actual
FROM 
    Employee e
LEFT JOIN 
    Position p ON e.position_id = p.position_id
LEFT JOIN 
    Schedule s ON e.employee_id = s.employee_id
ORDER BY 
    e.employee_id, s.schedule_date;

Web Interface Mockup

Admins can use a web interface to view employee records, schedules, and work history.

<!-- Employee Records Page -->
<!DOCTYPE html>
<html>
<head>
    <title>Employee Records</title>
</head>
<body>
    <h1>Employee Records</h1>
    <input type="text" id="searchEmployee" placeholder="Search Employees..." onkeyup="searchEmployee()">
    <table id="employeeTable">
        <thead>
            <tr>
                <th>Employee ID</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Employment Start Date</th>
                <th>Employment End Date</th>
                <th>Position</th>
                <th>Schedule Date</th>
                <th>Time From</th>
                <th>Time To</th>
                <th>Planned</th>
                <th>Actual</th>
            </tr>
        </thead>
        <tbody>
            <!-- Data fetched from database will be populated here -->
        </tbody>
    </table>
    <script>
        function searchEmployee() {
            // JavaScript function to filter employee records
        }
    </script>
</body>
</html>

Feature 3: View Work Done

SQL Query to Retrieve Work Done

Admins can see records of all repair work done, including details of services and tasks performed.

-- Retrieve details of work done along with services and tasks
SELECT 
    v.visit_id, 
    v.visit_start_date, 
    v.visit_end_date, 
    s.shop_name, 
    sc.service_name, 
    vt.task_name, 
    vt.task_description, 
    vt.task_price
FROM 
    Visit v
LEFT JOIN 
    RepairShop s ON v.repair_shop_id = s.repair_shop_id
LEFT JOIN 
    ServiceCatalog sc ON v.service_catalog_id = sc.service_catalog_id
LEFT JOIN 
    VisitTask vt ON v.visit_id = vt.visit_id
ORDER BY 
    v.visit_id, vt.visit_task_id;

Web Interface Mockup

Admins can use a web interface to view details of work done on vehicles.

<!-- Work Done Page -->
<!DOCTYPE html>
<html>
<head>
    <title>Work Done Records</title>
</head>
<body>
    <h1>Work Done Records</h1>
    <input type="text" id="searchWorkDone" placeholder="Search Work Done..." onkeyup="searchWorkDone()">
    <table id="workDoneTable">
        <thead>
            <tr>
                <th>Visit ID</th>
                <th>Visit Start Date</th>
                <th>Visit End Date</th>
                <th>Repair Shop</th>
                <th>Service Name</th>
                <th>Task Name</th>
                <th>Task Description</th>
                <th>Task Price</th>
            </tr>
        </thead>
        <tbody>
            <!-- Data fetched from database will be populated here -->
        </tbody>
    </table>
    <script>
        function searchWorkDone() {
            // JavaScript function to filter work done records
        }
    </script>
</body>
</html>

Feature 4: Vehicle Status

SQL Query to Retrieve Vehicle Status

Admins can check the status of any vehicle currently being serviced, including the progress of repairs and estimated completion times.

-- Retrieve vehicle status including ongoing repairs and progress
SELECT 
    v.vehicle_id, 
    v.vin, 
    v.license_plate, 
    vs.visit_start_date, 
    vs.visit_end_date, 
    sc.service_name, 
    vt.task_name, 
    vt.task_description, 
    vt.pass
FROM 
    Vehicle v
LEFT JOIN 
    Visit vs ON v.vehicle_id = vs.vehicle_id
LEFT JOIN 
    ServiceCatalog sc ON vs.service_catalog_id = sc.service_catalog_id
LEFT JOIN 
    VisitTask vt ON vs.visit_id = vt.visit_id
WHERE 
    vs.visit_end_date IS NULL
ORDER BY 
    v.vehicle_id, vs.visit_id;

Web Interface Mockup

Admins can use a web interface to view the status of vehicles currently being serviced.

<!-- Vehicle Status Page -->
<!DOCTYPE html>
<html>
<head>
    <title>Vehicle Status</title>
</head>
<body>
    <h1>Vehicle Status</h1>
    <input type="text" id="searchVehicleStatus" placeholder="Search Vehicles..." onkeyup="searchVehicleStatus()">
    <table id="vehicleStatusTable">
        <thead>
            <tr>
                <th>Vehicle ID</th>
                <th>VIN</th>
                <th>License Plate</th>
                <th>Visit Start Date</th>
                <th>Service Name</th>
                <th>Task Name</th>
                <th>Task Description</th>
                <th>Task Status</th>
            </tr>
        </thead>
        <tbody>
            <!-- Data fetched from database will be populated here -->
        </tbody>
    </table>
    <script>
        function searchVehicleStatus() {
            // JavaScript function to filter vehicle status records
        }
    </script>
</body>
</html>

Feature 5: Generate Reports

SQL Queries for Generating Reports

Total Sales Report

-- Generate a report of total sales within a specific date range
SELECT 
    SUM(i.amount) AS total_sales, 
    COUNT(i.invoice_id) AS total_invoices
FROM 
    Invoice i
WHERE 
    i.due_date BETWEEN '2024-07-01' AND '2024-07-31';

Employee Performance Report

-- Generate a report of employee performance based on completed tasks
SELECT 
    e.employee_id, 
    e.first_name, 
    e.last_name, 
    COUNT(vt.visit_task_id) AS tasks_completed, 
    SUM(vt.task_price) AS total_earnings
FROM 
    Employee e
LEFT JOIN 
    Visit v ON e.employee_id = v.employee_id
LEFT JOIN 
    VisitTask vt ON v.visit_id = vt.visit_id
GROUP BY 
    e.employee_id, e.first_name, e.last_name
ORDER BY 
    tasks_completed DESC;

Customer Feedback Report

-- Generate a report of customer feedback (assuming feedback is recorded)
SELECT 
    c.customer_id, 
    c.first_name, 
    c.last_name, 
    COUNT(f.feedback_id) AS feedback_count, 
    AVG(f.rating) AS average_rating
FROM 
    Customer c
LEFT JOIN 
    Feedback f ON c.customer_id = f.customer_id
GROUP BY 
    c.customer_id, c.first_name, c.last_name
ORDER BY 
    average_rating DESC;

Web Interface Mockup

Admins can use a web interface to generate and view various reports.

<!-- Reports Page -->
<!DOCTYPE html>
<html>
<head>
    <title>Reports</title>
</head>
<body>
    <h1>Reports</h1>
    <div>
        <h2>Total Sales Report</h2>
        <input type="text" id="salesStartDate" placeholder="Start Date">
        <input type="text" id="salesEndDate" placeholder="End Date">
        <button onclick="generateSalesReport()">Generate Report</button>
        <div id="salesReport">
            <!-- Sales report data will be displayed here -->
        </div>
    </div>
    <div>
        <h2>Employee Performance Report</h2>
        <button onclick="generateEmployeePerformanceReport()">Generate Report</button>
        <div id="employeePerformanceReport">
            <!-- Employee performance report data will be displayed here -->
        </div>
    </div>
    <div>
        <h2>Customer Feedback Report</h2>
        <button onclick="generateCustomerFeedbackReport()">Generate Report</button>
        <div id="customerFeedbackReport">
            <!-- Customer feedback report data will be displayed here -->
        </div>
    </div>
    <script>
        function generateSalesReport() {
            // JavaScript function to generate sales report
        }

        function generateEmployeePerformanceReport() {
            // JavaScript function to generate employee performance report
        }

        function generateCustomerFeedbackReport() {
            // JavaScript function to generate customer feedback report
        }
    </script>
</body>
</html>

Feature 6: Manage Notifications

SQL Query to Manage Notifications

Admins can view and manage notifications sent to customers.

-- Retrieve all notifications
SELECT 
    n.notification_id, 
    nt.type_name, 
    c.first_name, 
    c.last_name, 
    n.message, 
    n.sent_at, 
    e.first_name AS employee_first_name, 
    e.last_name AS employee_last_name
FROM 
    Notification n
JOIN 
    NotificationType nt ON n.notification_type_id = nt.notification_type_id
JOIN 
    Customer c ON n.customer_id = c.customer_id
JOIN 
    Employee e ON n.employee_id = e.employee_id
ORDER BY 
    n.sent_at DESC;

Web Interface Mockup

Admins can use a web interface to view and manage notifications.

<!-- Manage Notifications Page -->
<!DOCTYPE html>
<html>
<head>
    <title>Manage Notifications</title>
</head>
<body>
    <h1>Manage Notifications</h1>
    <table id="notificationsTable">
        <thead>
            <tr>
                <th>Notification ID</th>
                <th>Type</th>
                <th>Customer</th>
                <th>Message</th>
                <th>Sent At</th>
                <th>Employee</th>
            </tr>
        </thead>
        <tbody>
            <!-- Data fetched from database will be populated here -->
        </tbody>
    </table>
</body>
</html>

The implementation of these features enhances the administrative capabilities of the Car Repair Shop Management System, allowing administrators to view detailed records, manage operations, generate reports, and oversee customer communications effectively. By integrating these features, the system becomes a comprehensive tool for managing repair shop operations, ensuring efficiency, transparency, and customer satisfaction.

Module 9: User Authentication and Authorization

The ninth module in the Car Repair Shop Management System focuses on implementing user authentication and authorization. This module includes entities and attributes necessary to manage user accounts, roles, and permissions. Efficient management of this data ensures that the system is secure and that users have access only to the functionalities relevant to their roles.

Entities and Attributes

User

The User entity represents a user of the system.

user_id (PK): Unique identifier for the user.
username: Username of the user.
password: Password for the user (stored securely).
first_name: First name of the user.
last_name: Last name of the user.
email: Email address of the user.
phone: Phone number of the user.
role_id (FK): Reference to the role of the user.
insert_ts: Timestamp when the record was inserted.

Role

The Role entity represents a role assigned to users in the system.

role_id (PK): Unique identifier for the role.
role_name: Name of the role (e.g., “Admin”, “Employee”, “Customer”).

Permission

The Permission entity represents a specific permission that can be assigned to roles.

permission_id (PK): Unique identifier for the permission.
permission_name: Name of the permission (e.g., “View Records”, “Edit Records”, “Generate Reports”).

RolePermission

The RolePermission entity assigns permissions to roles.

role_permission_id (PK): Unique identifier for the role permission.
role_id (FK): Reference to the role.
permission_id (FK): Reference to the permission.

Relationships

The relationships between the entities in this module are as follows:

A User can have one Role.
A Role can have multiple User records.
A Role can have multiple RolePermission records.
A Permission can have multiple RolePermission records.

SQL Schema

Below is the SQL schema to create the tables for this module.

CREATE TABLE Role (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(100) NOT NULL
);

CREATE TABLE Permission (
    permission_id INT PRIMARY KEY AUTO_INCREMENT,
    permission_name VARCHAR(100) NOT NULL
);

CREATE TABLE RolePermission (
    role_permission_id INT PRIMARY KEY AUTO_INCREMENT,
    role_id INT,
    permission_id INT,
    FOREIGN KEY (role_id) REFERENCES Role(role_id),
    FOREIGN KEY (permission_id) REFERENCES Permission(permission_id)
);

CREATE TABLE User (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,  -- Note: In a real system, passwords should be hashed
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    role_id INT,
    insert_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES Role(role_id)
);

User Authentication and Authorization Flow

User Registration

A new user registers through a registration form.
The system stores the user’s details securely, with passwords hashed for security.

User Login

The user logs in using their username and password.
The system verifies the credentials and grants access based on the user’s role.

Role-Based Access Control

The system checks the user’s role and permissions before granting access to specific functionalities.
Admins have full access, employees have access to operational functionalities, and customers have limited access to their records.

Testing the Module

Step 1: Create Tables

Execute the SQL schema provided above to create the tables in your MySQL database.

Step 2: Insert Data

Insert sample data into the Role, Permission, RolePermission, and User tables.

-- Insert sample roles
INSERT INTO Role (role_name) VALUES ('Admin'), ('Employee'), ('Customer');

-- Insert sample permissions
INSERT INTO Permission (permission_name) VALUES ('View Records'), ('Edit Records'), ('Generate Reports');

-- Assign permissions to roles
INSERT INTO RolePermission (role_id, permission_id) VALUES 
(1, 1),  -- Admin: View Records
(1, 2),  -- Admin: Edit Records
(1, 3),  -- Admin: Generate Reports
(2, 1),  -- Employee: View Records
(2, 2);  -- Employee: Edit Records

-- Insert sample users
INSERT INTO User (username, password, first_name, last_name, email, phone, role_id)
VALUES 
('admin', 'adminpassword', 'Alice', 'Admin', 'alice.admin@example.com', '555-1234', 1),
('employee', 'employeepassword', 'Bob', 'Employee', 'bob.employee@example.com', '555-5678', 2),
('customer', 'customerpassword', 'Charlie', 'Customer', 'charlie.customer@example.com', '555-9012', 3);

Step 3: Queries

Write and execute queries to validate the data and relationships.

-- Retrieve all users and their roles
SELECT u.user_id, u.username, u.first_name, u.last_name, r.role_name
FROM User u
JOIN Role r ON u.role_id = r.role_id;

-- Retrieve all permissions for a specific role
SELECT rp.role_permission_id, r.role_name, p.permission_name
FROM RolePermission rp
JOIN Role r ON rp.role_id = r.role_id
JOIN Permission p ON rp.permission_id = p.permission_id
WHERE rp.role_id = 1;  -- Admin role

-- Verify user credentials (example for login validation)
SELECT user_id, username, first_name, last_name
FROM User
WHERE username = 'admin' AND password = 'adminpassword';

Step 4: Testing

Verify that the tables are created correctly.
Ensure that sample data is inserted without errors.
Run queries to check data integrity and relationships.
Validate that roles and permissions are correctly assigned and enforced.

The ninth module, User Authentication and Authorization, is essential for ensuring that the Car Repair Shop Management System is secure and that users have appropriate access based on their roles. By implementing this module, the repair shop can control access to sensitive data and functionalities, enhancing the overall security and efficiency of the system. This module integrates seamlessly with the previous modules, creating a comprehensive and secure system for managing repair shop operations.

What's Hot

+385 99 8302 482

What are you looking for?

Database Design and Documentation for a Car Repair Shop in SQL

Overall Structure

Module 1: Repair Shops & Employees

Description

Entities and Attributes

Relationships

Testing

Module 2: Customers & Contacts

Description

Entities and Attributes

Relationships

Testing

Module 3: Vehicles

Description

Entities and Attributes

Relationships

Testing

Module 4: Services & Offers

Description

Entities and Attributes

Relationships

Testing

Module 5: Visits

Description

Entities and Attributes

Relationships

Testing

Additional Functionalities

Notification System

Billing System (using Stripe)

Media Upload System (using Multer and Cloudinary)

Admin and Employee Access

Module 1: Repair Shops & Employees

Entities and Attributes

RepairShop

Employee

Schedule

Relationships

SQL Schema

Testing the Module

Step 1: Create Tables

Step 2: Insert Data

Step 3: Queries

Step 4: Testing

Module 2: Customers & Contacts

Entities and Attributes

Customer

ContactType

Contact

Relationships

SQL Schema

Testing the Module

Step 1: Create Tables

Step 2: Insert Data

Step 3: Queries

Step 4: Testing

Module 3: Vehicles

Entities and Attributes

Make

VehicleType

Model

Vehicle

Relationships

SQL Schema

Testing the Module

Step 1: Create Tables

Step 2: Insert Data

Step 3: Queries

Step 4: Testing

Module 4: Services & Offers

Entities and Attributes

ServiceCatalog

TaskCatalog

Offer

OfferTask

Relationships

SQL Schema