Database for vehicle fleet management

Hello people. This article gives you information about Database for vehicle fleet management.

Let us discuss about a simple SQLite database wrapper with a singleton pattern. Here's a breakdown of the key components:

Database Initialization

• The DB class takes a dbFilePath parameter in its constructor, which is the path to the SQLite database file.
• The constructor initializes the SQLite database using the sqlite3 module.
• The database instance is stored in the class property db.

Singleton Pattern

• The class maintains a static property dbInstance to hold the single instance of the DB class.
• The instance() method is implemented as a static method to retrieve the single instance of the class. If an instance already exists, it returns that instance.

Initialization Method

• The init() method is used to create an SQLite table named "inputs" if it doesn't already exist. It also has commented-out code for inserting data into the "inputs" table.

Query Method

• The query() method is asynchronous and wraps SQLite queries in a Promise. It takes a SQL query string and an array of parameters, executes the query using db.all(), and resolves or rejects the Promise based on the result.

Error Handling

• The constructor logs an error message if there is an error initializing the database.
• The query() method rejects the Promise if there is an error in executing the query.

Export

• The class is exported as the default export, making it easy to import and use in other files.

To create a database for vehicle fleet management, you'll need to define the tables and relationships that store relevant information about vehicles, drivers, maintenance records, trips, and any other pertinent data.

CREATE TABLE IF NOT EXISTS vehicles (
id INTEGER PRIMARY KEY,
make TEXT,
model TEXT,
year INTEGER,
license_plate TEXT,
status TEXT -- e.g., "Active", "Inactive", etc.
);

CREATE TABLE IF NOT EXISTS drivers (
id INTEGER PRIMARY KEY,
name TEXT,
license_number TEXT,
contact_number TEXT,
email TEXT
);

CREATE TABLE IF NOT EXISTS trips (
id INTEGER PRIMARY KEY,
vehicle_id INTEGER,
driver_id INTEGER,
start_location TEXT,
end_location TEXT,
start_time DATETIME,
end_time DATETIME,
distance_km REAL,
cost REAL,
FOREIGN KEY(vehicle_id) REFERENCES vehicles(id),
FOREIGN KEY(driver_id) REFERENCES drivers(id)
);

CREATE TABLE IF NOT EXISTS maintenance_records (
id INTEGER PRIMARY KEY,
vehicle_id INTEGER,
service_date DATETIME,
description TEXT,
cost REAL,
FOREIGN KEY(vehicle_id) REFERENCES vehicles(id)
);

This schema includes three main tables: vehicles, drivers, and trips, along with a maintenance_records table to track maintenance activities. Each table has its primary key, and relationships are established using foreign keys.

After defining the schema, you can use your DB class to perform operations such as inserting, updating, querying, and deleting records. For example:

// Assuming dbInstance is an instance of your DB class

// Insert a new vehicle
dbInstance.query("INSERT INTO vehicles (make, model, year, license_plate, status) VALUES (?, ?, ?, ?, ?)", ['Toyota', 'Camry', 2022, 'ABC123', 'Active']);

// Insert a new driver
dbInstance.query("INSERT INTO drivers (name, license_number, contact_number, email) VALUES (?, ?, ?, ?)", ['John Doe', 'XYZ789', '1234567890', '[email protected]']);

// Insert a new trip
dbInstance.query("INSERT INTO trips (vehicle_id, driver_id, start_location, end_location, start_time, end_time, distance_km, cost) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
[1, 1, 'Location A', 'Location B', '2024-02-05 08:00:00', '2024-02-05 10:00:00', 50.5, 100.0]);

// Insert a maintenance record
dbInstance.query("INSERT INTO maintenance_records (vehicle_id, service_date, description, cost) VALUES (?, ?, ?, ?)",
[1, '2024-02-05 12:00:00', 'Oil Change', 50.0]);

// Retrieve all vehicles
const vehicles = await dbInstance.query("SELECT * FROM vehicles", []);

console.log(vehicles);

This is just a basic example, and depending on your specific requirements, you may need to modify the schema and queries. Ensure that you handle errors, input validation, and any additional business logic in your application.

Hope this article on Database for vehicle fleet management is useful to you. Please read Form validations in vehicle fleet management