Creating a maintenance database (1 Viewer)

[shariq1989]

I am trying to put together a database for the maintenance tracking and planning of components on various vessels. I am having issues coming up with a schema that works and a good way to track maintenance intervals. Here is the low down: Vessels have many components that will be tracked like propellers, shafts and engines. Many of these components will have sub-components like pistons (engines), shaft seals (shafts), etc. Each component will have a service interval and an overhaul interval which is not based on time but on operating hours. Engine component intervals depend upon the running hours of that respective engine and other components depend upon the operating hours of the vessel. The database will be sending out emails when the overhaul/service intervals are close to being due. I'm also gonna need a reset function of some sort to reset the count for a certain component if it was replaced before the interval was coming up. For example, if the bearings were to be serviced in 500 hours but they broke down and had to be replaced prematurely, the would now be needing service in 35000 hours since they are serviced every 35000 hours. I also would like the database to track the running hours of each component. This means not having the hours count of each component going to zero hours at each overhaul, but the overhaul hours adjusting accordingly. For example, pistons are serviced at 17500 hours and then overhauled at 35000 hours. Now the next service should automatically be at 35000+17500 and the next overhaul should be at 35000*2 hours. So there would be a planning part of it, where the database forecasts upcoming maintenance and an executing part of it, where the engineer can enter in what maintenances have been completed. The engineer will update operating hours for all engines and vessels at the end of each month. This seems like it could be very complex or very simple at the same time and I just don't know how to go about it.

This is the schema I have right now:

TblVessels
VslID (1), VslName (Wanderer), VslTypeID (1)

TblVslTypes
VslTypeID, VslType (Barge)

TblEngines
EngineID, ManufacturerID, EngineModelNumber, VesselID, LocationID, EngineTypeID

TblManufacturers
ManufacturerID, ManufacturerName

TblLocations
LocationID, LocationName (Starboard)

TblEngineTypes
EngineTypeID, EngineTypeName (Aux)

TblEngineComponents
EngineComponentID, EngineID, EngineComponentName, Service Interval, Overhaul Interval

TblVesselComponents
VslCompID, VslCompName, VesselID, Service Interval, Overhaul Interval

TblCompletedMaintenance
OverhaulID, ComponentID, DateOverhauled

 

[jamesmor]

Honestly, if you can spend the money there's a application called MP2 from Infor that will do all that pretty much automatically after you get it set up correctly.