Huffman Trucking Fleet Maintenance Database Solution

Company

Huffman Trucking is a privately owned transportation company that has been in business for 60 years. The company has grown into a multi million-dollar company with over 1,400 employees and hundreds of vehicles. Huffman Trucking needs a better way to track the maintenance on their fleet of tractors, trailer and the roll-on/roll-offs. The fleet has to undergo periodic maintenance.

Plan

Putting the equipment's maintenance history, the parts used and vendor information into one database will save Huffman Trucking time and money. The database can include parts inventory information, parts catalogue information, the vendors used and their associated information, and parts purchasing history. The database will include all the maintenance history on each piece of equipment, such as the maintenance of the equipment itself, tire maintenance, the type of maintenance and the maintenance work orders.

Conclusion

Building a database saves money on maintenance by reducing redundant information and improving record keeping. Both the service and accounting departments will be able to utilize this database to keep track of parts purchases and costs. Having all the information in one central database would allow the diverse facilities to use the database. This type of centralization would bring more accuracy in Huffman Trucking's record keeping and would save money, time and manpower.

Huffman Trucking Fleet Maintenance Database Solution

Introduction

Huffman Trucking is a privately held national transportation company with just under 1,400 employees and facilities across the United States. Huffman Trucking has benefited from a relationship with US Government that started in the late 1930's. During the Second World War, shipping of materiel between manufacturers and port cities increased business to the point that Huffman was eventually able to purchase and absorb several other trucking companies (http://aapd.phoenix.edu/CIST/VIP).

Huffman Trucking has recently hired a consultant company, Smith Systems Consulting, to design a database that will enable Huffman Trucking to better organize and track the truck maintenance for their fleet. Smith Systems Consulting has done an analysis of Huffman Trucking to develop a basic plan for integrating information into a database. Entities and attributes have been defined by Smith Systems, but the relationships and tables of the database still need to be defined.

Smith Systems Consulting has identified eleven entities which will be included in the database. These are: Tire Maintenance, Maintenance Descriptions, Vehicle Types, Vehicles, Maintenance Work Order, Parts Inventory Add To Stores, Parts Inventory Issues, Parts Catalogue, Vendors, Parts Purchasing History and Vehicle Maintenance. We will describe the entities, their attributes and the relationships among them in relation to the business needs of Huffman Trucking.

Business Requirements

Huffman Trucking needs a database to help maintain their fleet of trucks and trailers, control inventory and reduce costs. The database will need to include all service items, inventory, suppliers and costs associated with these entities. The maintenance hub in Cleveland, OH is the major beneficiary of the database, as they will be able to predict the upcoming truck maintenance schedules and costs with greater accuracy. In addition, supplies can be ordered only when needed, minimizing the amount of company assets which must be tied up in spare parts. The database will also be used by the company's other facilities in New Jersey, California and Missouri, for tracking inventory and fleet movements.

Smith Systems Consulting has done a complete analysis of the different areas involved in the fleet maintenance area of Huffman Trucking. The resulting database will consist of the following eleven tables: Vehicles, Vehicle Types, Vehicle Maintenance, Maintenance Work Order, Maintenance Descriptions, Tire Maintenance, Parts Catalogue, Parts Purchasing History, Parts Inventory Issues, and Vendors.

Vehicle Types

Type ID

Descriptions

Vehicles

VIN

Type ID

Class Code

Put into Service Date

Gross Weight

Mileage

Purchase Price

Accumulated Depreciation

Taken out of Service Date

Capacity

These are laid out, along with the attributes, in Table 1 - Fleet Maintenance Entities. This table shows the different areas where data tracking is needed to reduce costs and speed up maintenance times for the aging truck fleet. The eleven tables can be divided into three main categories Vehicles, Maintenance, and Parts. Below is a description of each table and how it will be used.

Vehicles

The vehicles portion of the database consists of two tables: Vehicles and Vehicle Types. The Vehicle Type table will be used to describe each vehicle. It contains the two fields TypeID and Descriptions. TypeID is an assigned number that is a unique identifier and Description is a description of the type of vehicle, whether tractor, trailer or roll-on/roll-off. The Vehicles table will be used to identify all the vehicles in the fleet. The Vehicles table will contain the fields VIN, TypeID, Class Code, Put Into Service Date, Gross Weight, Mileage, Purchase Price, Accumulated Deprecation, Purchase Price, Take out of Service Date, Capacity.

The VIN number will be used to identify each vehicle as it is unique to each one. The TypeID will be used to link to the Vehicle table to get a description of the vehicle. The Class Code will contain the assigned vehicle class. Put into Service Date and Take out of Service date will contain the dates the vehicle was added to or removed from the fleet. The Mileage filed will contain the vehicle mileage. The Purchase Price field will contain the price the vehicle was purchased for. Accumulated Depreciation will contain the current deprecation amount of the vehicle. The final field Capacity will contain the vehicle load capacity.

Maintenance

The maintenance portion of the database consists of four tables: Maintenance Work Order, Maintenance Descriptions, Tire Maintenance, and Vehicle Maintenance. The Vehicle