I am looking for some advice on how to structure the hour tracking and mainteance tracking portion of my database. So far the database only has specfications like equipment, components, belts, filters, fluids and maintenance tasks.

This is the structure:

Table 1 - Equipment
EquipmentID, EquipmentNumber, Type, Mfr, Mdl, SN

Table 2 - Components
ComponentID, ComponentNumber, Type, Mfr, Mdl, SN, Description, EquipmentID

Table 3 - Belt
BeltID, Mfr, Mdl

Table 4 - Filter
FilterID, Type, Mfr, Mdl

Table 5 - Fluid
FluidID, Type, Mfr, Mdl

Table 6 - Maintenance
MaintenanceID, ComponentType, ServiceType, ServiceItems, ServiceDescription

Table 7 - ComponentBelt
ComponentID, BeltID, Qty

Table 8 - ComponentFilter
ComponentID, FilterID, Qty, Location, SpecIntervalHrs, SpecIntervalMons

Table 9 - ComponentFluid
ComponentID, FluidID, PolarisID, Qty, QtyUnits, SpecFluid, SpecIntervalHrs, SpecIntervalMons

Table 10 - ComponentMainteance
ComponentID, MaintenanceID, IntervalHrs, IntervalMons, Notes

The debate I am having is if the log for hours should be seperate from the log of services. We gather equipment hours every week and services usually occur at 250, 500, 1000, 2000 hour intervals. If they were seperate the tables would look like:

ComponentHrsLog
HrsLogID
ComponentID
LogDate
Hrs

ComponentMaintenanceLog
MaintenanceLogID
ComponentID
LogDate
IntervalHrs (250,500,1000,2000)
Hrs

What do you think? I am looking for advice from people that have done this kind of design before

I should probably also state that I am going to try to use access to calculate hours since last service

I have done a similar project and took the approach that it is all about the equipment.

If you log the equipment hours every week then this should be in one table. ComponentHrsLog. Are the hours measured on the equipment accumulative or are they re-set after each reading?

Your equipment service history should be in a separate table. ComponentMaintenanceLog

You could run queries on ComponentHrsLog to warn if the equipment is due/overdue for servicing.

Whenever I approach a project I keep asking myself. What information do I need from all the data i am collecting? What is the aim? This helps me in focussing on how to approach the structure of the table.

The hours are cumulative. Thank you for your input. I thought it might be best to keep them seperate. Every now and then I have to step back and review what I am doing in the database. I started out with a huge scheme I wanted to do but now I am breaking it down into smaller sections and completing them one at a time.