Comprehensive Database Schema for Fleet Management in ERP

Optimizing Fleet Operations for Construction Supply Logistics

Key Takeaways

• Centralized Management: Unified tables for trucks, tires, batteries, and maintenance ensure efficient data handling.
• Real-Time Tracking: Integrated GPS tracking and route management enable dynamic fleet monitoring and optimization.
• Comprehensive Maintenance: Detailed logging of maintenance events and fuel management support proactive fleet maintenance.

---

Introduction

Designing a robust database schema for a fleet management module within an ERP system is pivotal for construction supply businesses that rely heavily on logistics and fleet operations. This comprehensive schema facilitates efficient tracking, maintenance, and management of a diverse fleet, ensuring seamless operations and optimal resource utilization.

Core Components of the Schema

1. Trucks Management

The Trucks table serves as the central repository for all truck-related information. It captures essential details such as identification numbers, types, fuel capacities, and operational statuses.

Table Structure

CREATE TABLE Trucks (
    truck_id SERIAL PRIMARY KEY,
    plate_number VARCHAR(20) UNIQUE NOT NULL,
    truck_type VARCHAR(20) CHECK (truck_type IN ('ELF', 'FORWARD', 'OTHER')) NOT NULL,
    fuel_tank_size DECIMAL(10, 2) NOT NULL,
    current_fuel_level DECIMAL(10, 2) DEFAULT 0,
    status VARCHAR(20) CHECK (status IN ('ACTIVE', 'INACTIVE', 'UNDER_MAINTENANCE')) NOT NULL,
    date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  

Key Attributes

• truck_id: Unique identifier for each truck.
• plate_number: Registration number ensuring each truck is uniquely identifiable.
• truck_type: Categorizes trucks as ELF, FORWARD, or OTHER.
• fuel_tank_size: Capacity of the fuel tank in liters.
• current_fuel_level: Real-time fuel level tracking.
• status: Operational status of the truck.
• date_added: Timestamp of when the truck was added to the fleet.

2. Maintenance Management

The MaintenanceEvents table logs all maintenance activities for each truck, capturing descriptions, costs, and dates. This facilitates proactive maintenance scheduling and cost tracking.

Table Structure

CREATE TABLE MaintenanceEvents (
    maintenance_id SERIAL PRIMARY KEY,
    truck_id INT REFERENCES Trucks(truck_id) ON DELETE CASCADE,
    description TEXT NOT NULL,
    cost DECIMAL(10, 2) NOT NULL,
    date_of_maintenance DATE NOT NULL,
    next_maintenance_date DATE
);
  

Key Attributes

• maintenance_id: Unique identifier for each maintenance record.
• truck_id: Foreign key linking to the Trucks table.
• description: Detailed description of the maintenance performed.
• cost: Financial expenditure on the maintenance event.
• date_of_maintenance: Date when the maintenance was performed.
• next_maintenance_date: Scheduled date for the next maintenance.

3. Tires Management

The schema includes dedicated tables for managing tires, ensuring each tire's history and assignment are meticulously tracked.

Tires Inventory

CREATE TABLE Tires (
    tire_id SERIAL PRIMARY KEY,
    serial_number VARCHAR(50) UNIQUE NOT NULL,
    size VARCHAR(20) NOT NULL,
    ply_rating INT NOT NULL,
    brand VARCHAR(50) NOT NULL,
    status VARCHAR(20) CHECK (status IN ('IN_USE', 'STOCK', 'DISPOSED')) NOT NULL,
    date_installed DATE,
    truck_id INT REFERENCES Trucks(truck_id) ON DELETE SET NULL
);
  

Key Attributes

• tire_id: Unique identifier for each tire.
• serial_number: Unique serial number for traceability.
• size: Tire size specification.
• ply_rating: Indicates the tire's load-carrying capacity.
• brand: Manufacturer or brand of the tire.
• status: Current status of the tire.
• date_installed: Date when the tire was installed on a truck.
• truck_id: Foreign key linking to the Trucks table, nullable if not assigned.

4. Batteries Management

The Batteries table manages the batteries assigned to each truck, tracking purchase dates, warranty periods, and current statuses.

Table Structure

CREATE TABLE Batteries (
    battery_id SERIAL PRIMARY KEY,
    serial_number VARCHAR(50) UNIQUE NOT NULL,
    truck_id INT REFERENCES Trucks(truck_id) ON DELETE SET NULL,
    date_of_purchase DATE NOT NULL,
    warranty_years INT NOT NULL,
    warranty_expiry_date DATE NOT NULL,
    status VARCHAR(20) CHECK (status IN ('IN_USE', 'STOCK', 'DISPOSED')) NOT NULL,
    date_installed DATE
);
  

Key Attributes

• battery_id: Unique identifier for each battery.
• serial_number: Unique serial number for each battery.
• truck_id: Foreign key linking to the Trucks table.
• date_of_purchase: Purchase date of the battery.
• warranty_years: Duration of the battery's warranty in years.
• warranty_expiry_date: Calculated expiry date based on purchase date and warranty period.
• status: Current status of the battery.
• date_installed: Date when the battery was installed in a truck.

5. Route Management

Efficient route management is crucial for optimizing logistics. The schema accommodates route planning, multiple destinations, and real-time tracking.

Routes Table

CREATE TABLE Routes (
    route_id SERIAL PRIMARY KEY,
    truck_id INT REFERENCES Trucks(truck_id) ON DELETE SET NULL,
    starting_point GEOGRAPHY(POINT, 4326) NOT NULL,
    destination_points JSONB NOT NULL,
    saved_route_name VARCHAR(100),
    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    route_status VARCHAR(20) CHECK (route_status IN ('PLANNED', 'ACTIVE', 'COMPLETED')) NOT NULL
);
  

Route Segments

CREATE TABLE RouteSegments (
    segment_id SERIAL PRIMARY KEY,
    route_id INT REFERENCES Routes(route_id) ON DELETE CASCADE,
    segment_order INT NOT NULL,
    start_point GEOGRAPHY(POINT, 4326) NOT NULL,
    end_point GEOGRAPHY(POINT, 4326) NOT NULL,
    distance_km DECIMAL(10, 2) NOT NULL
);
  

Key Attributes

• route_id: Unique identifier for each route.
• truck_id: Assigned truck for the route.
• starting_point: GPS coordinates for the route's origin.
• destination_points: JSON array storing multiple destination GPS coordinates.
• saved_route_name: Optional name for saved routes.
• date_created: Timestamp when the route was created.
• route_status: Current status of the route.
• segment_id: Unique identifier for each route segment.
• segment_order: Order of the segment within the route.
• start_point & end_point: GPS coordinates defining each segment.
• distance_km: Distance covered in each segment.

6. GPS Tracking

Real-time tracking of trucks is facilitated through the GPSTracking table, capturing live location data.

Table Structure

CREATE TABLE GPSTracking (
    tracking_id SERIAL PRIMARY KEY,
    truck_id INT REFERENCES Trucks(truck_id) ON DELETE CASCADE,
    latitude DECIMAL(10, 6) NOT NULL,
    longitude DECIMAL(10, 6) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
  

Key Attributes

• tracking_id: Unique identifier for each GPS record.
• truck_id: Foreign key linking to the Trucks table.
• latitude & longitude: Current GPS coordinates of the truck.
• timestamp: Time when the GPS data was recorded.

7. Fuel Management

Managing fuel consumption and purchases is streamlined through the FuelPurchases table.

Table Structure

CREATE TABLE FuelPurchases (
    fuel_purchase_id SERIAL PRIMARY KEY,
    truck_id INT REFERENCES Trucks(truck_id) ON DELETE CASCADE,
    fuel_quantity DECIMAL(10, 2) NOT NULL,
    cost DECIMAL(10, 2) NOT NULL,
    date_of_purchase DATE NOT NULL
);
  

Key Attributes

• fuel_purchase_id: Unique identifier for each fuel purchase.
• truck_id: Foreign key linking to the Trucks table.
• fuel_quantity: Amount of fuel purchased in liters.
• cost: Total cost of the fuel purchase.
• date_of_purchase: Date when the fuel was purchased.

Relationships and Data Integrity

Ensuring data integrity through well-defined relationships is crucial. Foreign keys establish connections between tables, enabling cascading updates and deletions to maintain consistency.

Entity-Relationship Diagram

The following table provides an overview of the primary tables and their relationships:

Table	Related Tables	Relationship
Trucks	MaintenanceEvents, Tires, Batteries, Routes, GPSTracking, FuelPurchases	One-to-Many
MaintenanceEvents	Trucks	Many-to-One
Tires	Trucks	Many-to-One
Batteries	Trucks	Many-to-One
Routes	Trucks, RouteSegments	One-to-Many
RouteSegments	Routes	Many-to-One
GPSTracking	Trucks	Many-to-One
FuelPurchases	Trucks	Many-to-One

---

Implementation Considerations

Indexing for Performance

To optimize query performance, especially for large datasets, indexing is essential. Primary keys are inherently indexed, but additional indexes should be created on foreign keys and frequently queried columns.

• Index truck_id in all related tables to speed up join operations.
• Index timestamp in the GPSTracking table for efficient retrieval of recent locations.
• Index serial_number in Tires and Batteries for quick lookup.

Data Validation and Constraints

Implementing constraints ensures data integrity. Enums restrict the values to predefined options, and foreign keys maintain referential integrity.

• Use CHECK constraints on enum fields like truck_type, status, and route_status.
• Enforce unique constraints on fields like plate_number, serial_number to prevent duplicates.
• Implement NOT NULL constraints where necessary to ensure essential data is always captured.

Scalability and Flexibility

The schema is designed to scale with the business, accommodating additional trucks, maintenance events, and expanding route networks without significant redesign.

• Utilize JSONB fields for storing variable data like destination_points, allowing flexibility in the number of destinations per route.
• Allow nullable foreign keys in certain tables (e.g., truck_id in Tires and Batteries) to handle unassigned assets.
• Design route segments to support complex multi-stop routes, enhancing route planning capabilities.

Sample Queries and Workflows

1. Viewing All Trucks on the Map

To display the latest GPS positions of all trucks, the following query retrieves the most recent location for each truck:

SELECT t.truck_id, t.plate_number, gt.latitude, gt.longitude, gt.timestamp
FROM Trucks t
JOIN GPSTracking gt ON t.truck_id = gt.truck_id
WHERE gt.timestamp = (
    SELECT MAX(timestamp) 
    FROM GPSTracking 
    WHERE truck_id = t.truck_id
);
  

2. Calculating Fuel Purchase Requirements

To determine the amount of fuel needed to refill a truck's tank:

SELECT 
    t.truck_id, 
    t.plate_number, 
    t.fuel_tank_size - t.current_fuel_level AS fuel_to_purchase
FROM Trucks t
WHERE t.truck_id = 123;
  

3. Assigning Tires to a Truck

To assign six tires to a forward or ELF truck:

UPDATE Tires
SET truck_id = 123, date_installed = CURRENT_DATE, status = 'IN_USE'
WHERE tire_id IN (456, 457, 458, 459, 460, 461);
  

4. Saving a Planned Route

To save a new planned route with multiple destinations:

BEGIN;

INSERT INTO Routes (truck_id, starting_point, destination_points, saved_route_name, route_status)
VALUES (
    123, 
    ST_GeogFromText('POINT(-73.935242 40.730610)'), 
    '[{"latitude":40.730610,"longitude":-73.935242}, {"latitude":40.750610,"longitude":-73.925242}]',
    'Morning Delivery Route',
    'PLANNED'
) RETURNING route_id;

-- Assuming the returned route_id is 1
INSERT INTO RouteSegments (route_id, segment_order, start_point, end_point, distance_km)
VALUES 
    (1, 1, ST_GeogFromText('POINT(-73.935242 40.730610)'), ST_GeogFromText('POINT(-73.925242 40.750610)'), 5.0),
    (1, 2, ST_GeogFromText('POINT(-73.925242 40.750610)'), ST_GeogFromText('POINT(-73.915242 40.760610)'), 3.5);

COMMIT;
  

5. Tracking Maintenance Costs

To aggregate total maintenance costs for a specific truck over a period:

SELECT 
    t.truck_id, 
    t.plate_number, 
    SUM(me.cost) AS total_maintenance_cost
FROM Trucks t
JOIN MaintenanceEvents me ON t.truck_id = me.truck_id
WHERE t.truck_id = 123 
  AND me.date_of_maintenance BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY t.truck_id, t.plate_number;
  

Advanced Features and Extensions

GIS Integration

Integrating Geographic Information Systems (GIS) enhances route planning and real-time tracking. Utilizing spatial data types like GEOGRAPHY(POINT, 4326) allows for precise location handling and spatial queries.

Automated Maintenance Scheduling

By tracking usage metrics and maintenance histories, the system can automatically schedule future maintenance events, reducing downtime and preventing unexpected failures.

Inventory Management for Tires and Batteries

Managing stocks of tires and batteries ensures that replacements are readily available, minimizing delays in maintenance and operations.

Conclusion

This comprehensive database schema is meticulously designed to support the multifaceted requirements of a fleet management module within an ERP system tailored for a construction supplies business. By centralizing truck data, enabling real-time tracking, and ensuring detailed maintenance and fuel management, the schema empowers businesses to optimize their logistics operations, enhance efficiency, and maintain high operational standards.

---

References