Jan 30 2007, 06:18 AM
In an equipment maintenance db I have the following 3 tables. I’ve included the important fields.
UnitID – PK
PMName - PK
PMInterval - PK
Ref_workOrderDetailsID – FK to tblWorkOrderDetails
WorkOrderNumber – PK
DetailsID – PK
Ref_workOrderNumber – FK to tbl tblWorkOrders
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.
Jan 30 2007, 10:18 AM
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?
Jan 30 2007, 10:51 AM
I thought of that but seemed more appropriate in the details table. Would this be incorrect?
Jan 30 2007, 10:54 AM
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.
Jan 30 2007, 11:26 AM
Only problem I see is that I want to assign a work order number number to any service performed including PM's.
Jan 30 2007, 11:30 AM
But that doesn't change the issue of whether its the PM that was completed.
Jan 30 2007, 11:51 AM
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:
MaintItemID Autonumber PK
UnitTypeID Autonumber PK
IntervalUnit Text (use the vb constant here that is used in the DateAdd function for days, weeks, years, months, etc.)
UnitTypePM Autonumber PK
UnitTypeID FK to tblUnitTypes
MaintItemID FK to tblPMs
IntervalID FK to tblIntervals
UnitID Autonumber PK
UnitTypeID FK to tblUnitTypes
any other unit attributes
WorkOrderID Autonumber PK
UnitID FK to tblUnits
WorkOrderDetailID Autonumber PK
WorkOrderID FK to tblWorkOrders
MaintItemID FK to tblMaintenanceItems
TrackingTypeID Autonumber PK
TrackingType Text (Assigned, Completed, Closed, Waiting for Parts, On Hold, etc.)
WODTrackingID Autonumber PK
WorkOrderDetailID FK to tblWorkOrderDetails
TrackingTypeID FK to tblTrackingTypes
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.
Jan 30 2007, 01:45 PM
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here