Blue Cursor.
A vehicle management system web application
Role
Freelance Full-stack Developer.
Client
Shfte Tracking Systems Company.
Date
Jan.2023
Tech & Tools
Overview
Blue cursor is a web-based vehicle management system designed and built for Khartoum state police departments. The system allows departments to manage and keep track of all vehicle information by offering a range of features that include analytics, charts, drill-down filterable reports, and configurable settings. Moreover, it enables users to manage vehicles, departments, and user accounts effortlessly.
The Dashboard
The dashboard offers a comprehensive overview of essential vehicle information through various metrics, analytics, and charts, allowing system administrators to view critical insights and information about department vehicles, which facilitates data-driven decision making.
Reports
The system provides useres with detailed reports that cover a wide range of metrics and data points related to vehicle operations, including but not limited to vehicle type, model, GPS tracking device status, condition, assigned department, depreciation value, current value and so forth , allowing administrators to make informed decisions.
To cater to diverse needs , the reports are downloadable and highly filterable. Users can apply filters based on specific criteria such as vehicle type, model, department, location, and time period, in addition to general search functionality. This allows for customized views of the data, enabling users to focus on the information most relevant to their requirements.
Users Management
Blue cursor enables administrators to easily create and manage user accounts, in addition to granting users different roles and policies, and assigning them to specific departments or sub-departments. The system also enables admins to dynamically search for user accounts to manage and edit them as needed.
Vehicles management
Through the data entry portal, users with the required policy can add new vehicles to the system via a dedicated form that includes all necessary vehicle information. The system allows users to easily edit and manage stored vehicles information.
Maintenance scheduling
Users have the option to schedule routine vehicle maintenance through dedicated settings. When the scheduled maintenance date/time for a particular vehicle approaches, the system will automatically notify users, ensuring they stay informed and prepared for upcoming maintenance tasks. Additionally, users have the ability to search, filter, and manage stored maintenance schedules with ease.
Architecture
I have always loved JavaScript, that’s why I built Blue Cursor leveraging the MERN stack. At the frontend, the user interface was built using React utilizing a number of third-party libraries. On the backend, the Node.js server built with Express.js handles incoming requests from the frontend. JWT authentication ensures secure communication between the frontend and backend, validating user access to protected routes and resources. I used MySQL database to store and manage data efficiently. Below is a simplified diagram of the systems architecture.
Challenges
During the requirements gathering phase the client mentioned to me that the state police departments have a hierarchical structure, meaning that the top state department manages a number of sub-departments where each can have further sub-departments. They requested me to reflect this hierarchical structure to the VMS system where top department users can monitor vehicles within their department and all assigned sub-departments. However, Sub-department users shouldn’t be able to access any vehicle/user information linked to top departments, only their department and assigned sub-departments, you get the gist.
I didn’t think this would be much of an issue until I started planning and designing the system’s database scheme, I couldn’t figure out how to approach implementing this hierarchical structure to the database scheme. The problem might not be very obvious in the beginning. One could say we can simply make a table for each department level, where managing departments on the same hierarchical level go to one table and sub-departments go to another table and so on. That was the approach I initially took.
But this can be problematic for a number of reasons:
- Introducing new department levels to the hierarchy would require creating additional tables. This could mean we have 4, 5, 6 or more tables to record the hierarchy.
- All the tables would be similar, storing the same kind of information. This is a kind of duplication, which is not very efficient.
- One would have to assign vehicles and useres to different department tables. Which will make the implementation quite challenging and mistake-prone.
- Overall, managing the department’s structure (creating, editing, removing, and moving) will be very difficult in such a scheme.
Solution
During the process of searching for a viable solution to the departments hierarchy problem, I encountered a beautiful concept called the adjacency list relational pattern that came in very handy for our little problem. The adjacency list model is a technique used to represent hierarchical relationships by storing parent-child associations in a single table. That allows us to represent the whole hierarchical structure of departments in a single table, where each department contains a reference (foreign key) to its parent department (primary key) in the same table.
There are special ways to query data while using the adjacency list, to learn more about it and the concept of hierarchical data in SQL I encourage you to read this amazing article by Ben Brumm.
After successfully implementing the solution, the system enabled users to monitor/manage (vehicles, users, reports, and maintenance) information related only to their department or sub-department.
