For an instructor lead, in-depth look at learning SQL click below.
Whether you’re managing a small fleet of cars or a large assortment of various vehicles, maintenance is crucial. The ability to track maintenance activities in your fleet can save your company time and money. This blog post introduces a basic structure of creating a vehcicle fleet maintenance tracking system using SQL (Structured Query Language) for data management.
1. Database Design
Before we start with the SQL code itself, we must first design the foundation for our tracking system: the database. A fundamental database in a vehicle maintenance system would include tables for Vehicles, Maintenance Tasks, and a bridging table, Maintenance History.
Vehicles Table
This table holds information about each vehicle in your fleet. We need columns for vehicleID (primary key), make, model, year, and mileage.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Vehicles ( vehicleID INT PRIMARY KEY, make VARCHAR(50), model VARCHAR(50), year INT, mileage INT ); |
Maintenance Tasks Table
This table contains possible maintenance tasks. We need columns for taskID (primary key) and task description.
|
1 2 3 4 5 6 |
CREATE TABLE MaintenanceTasks ( taskID INT PRIMARY KEY, description VARCHAR(255) ); |
Maintenance History Table
This bridges our other two tables, linking a vehicle with the maintenance tasks performed on it. Columns necessary here are historyID (primary key), vehicleID (foreign key), taskID (foreign key), and date of maintenance.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE MaintenanceHistory ( historyID INT PRIMARY KEY, vehicleID INT, taskID INT, maintenanceDate DATE, FOREIGN KEY (vehicleID) REFERENCES Vehicles(vehicleID), FOREIGN KEY (taskID) REFERENCES MaintenanceTasks(taskID) ); |
2. Querying the Database
With our database design finished, we can now fill it with data, and perform queries to track and analyse our vehicle maintenance.
Adding Data
Here’s an example of how you can add data to the Vehicles and MaintenanceTasks tables.
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO Vehicles (vehicleID, make, model, year, mileage) VALUES (1, 'Ford', 'Focus', 2015, 50000), (2, 'Toyota', 'Corolla', 2018, 30000); INSERT INTO MaintenanceTasks (taskID, description) VALUES (1, 'Engine oil change'), (2, 'Tire rotation'); |
Performing Queries
Here’s how you might check which tasks have been done on a particular vehicle:
|
1 2 3 4 5 6 7 |
SELECT t.description, h.maintenanceDate FROM MaintenanceTasks t INNER JOIN MaintenanceHistory h ON t.taskID = h.taskID WHERE h.vehicleID = 1 ORDER BY h.maintenanceDate DESC; |
This SQL query uses a JOIN clause to combine rows from two or more tables, based on a related column. The result shows the description of tasks performed on vehicleID 1, sorted in descending order by the date of maintenance.
Wrapping Up
And there you have it – the basics of a vehicle fleet maintenance tracking system using SQL. With appropriate application of this powerful language, you can build a valuable tool to help manage your company’s vehicles.