Good morning,

In an equipment maintenance db I have the following 3 tables. I’ve included the important fields.

TblPMSchedule
UnitID – PK
PMName - PK
PMInterval - PK
Ref_workOrderDetailsID – FK to tblWorkOrderDetails

TblWorkOrders
WorkOrderNumber – PK
Date

TblWorkOrderDetails
DetailsID – PK
Ref_workOrderNumber – FK to tbl tblWorkOrders
PMComplete

A note; the PMSchedule has a composite PK. Briefly, this is because an individual unit and PM name can appear many times using different PM intervals. This table stores PM’s that have been assigned to a unit.

What I’m trying to do is have a work order record the fact that one or more PM’s have been completed on a given unit – thus the details table. However, I’m unsure of the relationship arrangement between the details table and the pm schedule table. It almost seems like this should be a one to one relationship; for every details record there will only be one PM. Am I seeing this correctly? I can provide more info if necessary. Would someone provide another set of eyes and offer some suggestions.

Thank you

I do not recommend or use composite keys. Add an autonumber field and use that as your PK. If you need to create a unique index on a combination of fields you can do that.

Why not just have a completed flag or date in the Schedule table?

Hi Scott,
I thought of that but seemed more appropriate in the details table. Would this be incorrect?

If the whether a PM has been completed or not is an attribute of the specific PM (which it sounds like) then it belongs in the PMSchedule.

Only problem I see is that I want to assign a work order number number to any service performed including PM's.

But that doesn't change the issue of whether its the PM that was completed.

I don't believe you actually need a tblPMSchedule. You should be able to calculate that based on completed work order details. I think I'd go with something like:

tblMaintenanceItems
MaintItemID Autonumber PK
MaintItemName Text

tblUnitTypes
UnitTypeID Autonumber PK
UnitType Text

tblIntervals
IntervalID
IntervalName Text
IntervalUnit Text (use the vb constant here that is used in the DateAdd function for days, weeks, years, months, etc.)

tblUnitTypePMs
UnitTypePM Autonumber PK
UnitTypeID FK to tblUnitTypes
MaintItemID FK to tblPMs
IntervalID FK to tblIntervals
IntervalAmount Number

tblUnits
UnitID Autonumber PK
UnitTypeID FK to tblUnitTypes
UnitSerialNumber Text
any other unit attributes

tblWorkOrders
WorkOrderID Autonumber PK
WorkOrderNumber Text
WorkOrderDate Date/Time
UnitID FK to tblUnits
WorKOrderComments Text

tblWorkOrderDetails
WorkOrderDetailID Autonumber PK
WorkOrderID FK to tblWorkOrders
MaintItemID FK to tblMaintenanceItems

tblTrackingTypes
TrackingTypeID Autonumber PK
TrackingType Text (Assigned, Completed, Closed, Waiting for Parts, On Hold, etc.)

tblWorkOrderDetailTracking
WODTrackingID Autonumber PK
WorkOrderDetailID FK to tblWorkOrderDetails
TrackingTypeID FK to tblTrackingTypes
TrackingDate Date/Time
TrackingComments Text

That should, I believe, let you track all maintenance items, and query the tracking table to determine when any given unit (based on unit type) received a necessary PM.

NoahP,
You put a lot of time into this and I thank you but I've got quite a bit of this ironed out already. Scott...relative to your last response...I still have the issue of a relationship between the pmschedule table and the details table. Or are you suggesting there be no relationship. So if I just add a "completed" field (checkbox), where am I picking up the work order number to refer to? If it's in the details table, it seems straight forward. Could you elaborate. Yes, I agree that composites should be avoided but I did not see a significant consequence in this situation.

Thank you kindly