Table design

[directorz]

I'm having a tough time with what seems to be rather simple, I just can't see it and need a second set of eyes. I have the following arrangement in a vehicle maintenance db:

TblVehicles
ID - pk
VehID

TblServiceInfo
WorkOrderNo - pk
ID - fk to TblVehicles/ID
DateofService
Odometer

TblPMSchedule
Unit ID - pk
PMName - PK
PMInterval - PK
WorkOrderNo - fk to TblServiceInfo/WorkOrderNo

Here's the thought process.
1.TblVehicles is populated with info about vehicles...year, make, model etc.

2. Work completed on a vehicle is then entered in TblServiceInfo...oil change, replace wipers and the date completed.

3. I have broken down PM's into PM Names and intervals. What I need to show is the date that PM's (preventive maintenance items) were completed and associate a work order number with it. Seems that I can't do this until a work order number is first established, at which time I can then look it up, but that's after the fact. Does anyone have any enlightenment?

Thank you

Directorz

 

[LittleSmudge]

tblPMSchedule does not want tobe linked in the way you have it - but the correct solution will depend on a few other factors.

So here is a few guess.

If the records in tblPMSchedure are repetative
Ie One record can apply to lots of Service records/ lots of vehicles then you need a linking table tblServicePM

tblServicePM
WorkOrderNoRef FK to tblWorkOrderNo and joint PK
PMScheduleRef FK to tblPMSchedule and joint PK


and delete tblPMSchedule.WorkOrderNo

You then populate the tblPMSchedule at your leisure and link the service event to the PMSchedule when it happens.



'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[directorz]

Hi LittleSmudge,
May I clarify. One work order can have several PM's associated with it.

"tblServicePM
WorkOrderNoRef FK to tblWorkOrderNo and joint PK
PMScheduleRef FK to tblPMSchedule and joint PK"

Do you mean ?
"tblServicePM
WorkOrderNoRef FK to tblServiceInfo/WorkOrderNo and joint PK
PMScheduleRef FK to tblPMSchedule/UnitID and joint PK"

 

[directorz]

If that's the case, I still don't see the process. I'm entering data in Service info. PMSchedule (PMName and PMInterval) is pre-populated with data, say PM-1, Interval 1. I want to use these values and say car 1 had these PM's done on August 1. It still seems that this is after the fact.....

 

[willir]

directorz

Hmmm

I have broken down PM's into PM Names and intervals. What I need to show is the date that PM's (preventive maintenance items) were completed and associate a work order number with it.

By PM's into PM Names, do you mean that you have created profiles, and then want to assign a profile to a work order - 10,000 mile / 16,000 km oil change, 2 year tune up, winter special, summer special??

If this is correct, consider the following (change names to match your needs)

tbl_PMMaster
PMMasterID - primary key
PMName - descriptive text
IntervalByDate
IntervalByOdemeter
MaterialCost
LabourCost
...etc

tbl_PMDetail
PMDetailID - primary key
PMMasterID - foreign key to tbl_PMMaster
TaskName
ItemQuantity
ItemCode - foreign key to Item table
ItemLabour
...etc

Discussion:
Oil change = 4 qt / L of oil + 0.15 hr


tblItem
ItemCode - primary key
ItemName
ItemCost
Taxable - yes / no

Discussion:
ItemCode
- materials: oil, belts, break liners, etc


TblVehicles
ID - pk
VehID
OwnerName
Phone

TblServiceInfo
WorkOrderNo - pk
ID - fk to TblVehicles.ID
DateofService
NextServiceByDate
Odometer
NextServiceByOdometer
Mechanic

Now comes the decision on assiging the PM. You have three choices...
1) Using tbl_PMMaster, assign details from tbl_PMDetail to the ServiceDetail table/ The issue is how do you handle service discount specials? You could create tbl_PMDetail record that allows you to enter the discount.

2) As in 1, assign the PM detail records to the ServiceDetail, and capture the discount PM on the Service master record. What do you do when you have to PM specials to apply.

3) Allow the ServiceDetail record to capture both the PM Master record and Detail records.

Regardless, you will have the following basics...

tblServiceDetail
ServiceDetailID - primary key
WorkOrderNo
ItemCode
ItemCost
Labour
LabourCost

I guess the things here are...
- The tbl_PMMaster has a set of tasks and costs associated with it
- The tbl_PMMaster is used to generate the tblServiceDetail records
- The costs / cacluations need to be worked out. In general, the 3rd rule of normalization means the calculated values are not stored, but you will change your prices so the service record needs to store these numbers. Handling discounts needs to be worked out. Storing totals on the service master is iffy.

Richard

 

[LittleSmudge]

Yes - i'm getting confused with your table names and PK names becuase they sometimes don't match.

If the table name is TblServiceInfo then why isn'tthe PK called ServiceInfoId

OR
If the PK is WorkOrderNo Then why isn't the table tblWorkOrder ?


Std Naming convention - Foreign Keys carry a similar name to the PK they point to except the Id is replaced by Ref ( because they Refer to that record.



tblVehicle contains data about the cars

tblPMSchedule contains info about service intervals, planned maintenance events etc.

There two are pre-populated


Then, at the time that the vehicle is 'on the ramp' you add new records to the tblServiceInfo to record which vehicle has had which PM actions applied to it.

Using this approach means any vehicle can have many PMs applied to it and any PM can BE applied to many vehicles.
Is that what you're looking for ?



'ope-that-'elps.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[directorz]

Willir,
I going thru your response.

"By PM's into PM Names, do you mean that you have created profiles, and then want to assign a profile to a work order - 10,000 mile / 16,000 km oil change, 2 year tune up, winter special, summer special??"

"PM" values are, for example, "PM-1, PM-2, PM-3" etc. PM Interval values, for example are 3000, 6000, 9000 etc, representing mileage. Thus, in TblPMSchedule could read Car1, PM-1, 3000. Here, I also have a pre-defined list of items to be completed relative to this specific PM/Interval. So I believe we're on the same page. I will read on...

 

[directorz]

LittleSmudge,
I can modify the names to match.

<<"Using this approach means any vehicle can have many PMs applied to it and any PM can BE applied to many vehicles.
Is that what you're looking for ?>>"

This is correct. There could be an infinite number of PM's, any of which, or combination of, could apply to a vehicle. Conversely, the same PM could apply to many vehicles.

 

[directorz]

LittleSmudge,
Appreciate your observations. I ended up with:

TblVehicles
ID - PK
VehID

TblPMSchedule
PMID - PK (joint)
UnitID - PK (joint)/FK to TblVehicles/ID
PMName - PK (joint)
PMInterval - PK (joint)

TblWorkOrders
WorkOrderNo - PK
UnitID - FK to TblVehicles/ID
PMID - FK to TblPMSchedule / PMID

 

[BSman]

It looks to me (based on the tables you mention in your original question) that you had no reference to a PMID in your tblServiceInfo.

You say that multiple PMs can be done on one work order. I work in a transportation company and PM schedules normally are a variety of work done at specific mileage intervals. So a PM would be defined as a specific set of work items (replace certain filters, true wheels (for rail vehicles), replace valves, etc.). In order to capture that you need at least one more table and possibly two.

First, a PM table that identifies each scheduled PM. (By the way, you might want to include a vehicle type in your vehicle table and in your PM table so if you end up with a mixed fleet you can handle different PM schedules.)

Second, a PM details table that lists all of the specific work items to be done for the associated PM.

If you want/need to keep detailed information about each of the items for a specific PM done on a work order, then you also need a second table for the work orders that will be automatically loaded with all of the details (reference IDs) of the work required for the selected PM and column(s) to record additional information (even if just a check box identifying that the item was done).

While designing a vehicle maintenance system I would suggest that you also need to handle non-PM work within the same work orders, since that is still maintenance work done on the vehicles and will provide a complete vehicle history (unless your vehicles never break down between PMs!).

 

[directorz]

BSman,
Your suggestions are most helpful. You are corret about the PMID. It's been changed and works great. As you indicate, PM's are pre-defined and I have allowed for additional entries by the user. Each PM is associated with specific sets of operations that need to be performed and, although I cannot discuss the method, the associations and deinitions are infinite. Each work order handles single, multiple or no PM's. We're on the same track

Thank you!

 

[RDWilson2]

Although it was intended for hospital equipment maintenance, I designed a system a while back that was very similar to what yours sounds like.

My approach was to defined the following:

PM_Master:
PM_ID AutoIncrement
PM_Name Text (Char(50) of Human conumable name)
PM_Descript Memo (Short paragraph description)
PM_Interval ???? (Used to define the interval for a
particular PM)

Maintenance_Task:
Maint_Tsk_ID AutoIncrement
Maint_Tsk_Name Text (Char(50) of Human conumable
name)
Maint_Tsk_Descript Memo (short paragraph description or
instructions for doing this task)
Maint_Tsk_Dur ???? (Expected time required to
complete task)

Maint_Task_Parts:
Mnt_Tsk_ID Long (Foreign Key link to
Maintenance_Tasks)
Part_ID Long (Foreign Key link to Parts Table)

PM_Detail
PM_ID Long (Foreign Key link to PM_Master)
Mnt_Tsk_ID Long (Foreign Key link to
Maintenance_Tasks)

Equipment:
Equip_ID AutoIncrement
Equip_Name Text (Char(50) of Human conumable
name)
Equip_Descript Memo (Short paragraph description)
Equip_Loc ???? (Where the equipment is located)
In_Service_Date Date (Date unit was put in service)
Status ???? (Used to indicate
"In-Service", "Out-of-Service",
or whatever)
Last_PM_Date Date (Date of Last PM WO)
Last_PM_WO Long (Foreign Key to a Work Order)
Last_Serv_Date Date (Date of Last Service WO)
Last_Serv_WO Long (Foreign Key to a Work Order)
SinceLastPM ???? (this relates to possible PM
interval information such as
miles, hours, days, months, etc.)

Work_Orders:
WO_ID AutoIncrement
WO_Date Date (Date WO Issued)
WO_PM_YN Yes/No (Does this include PM WO?)
PM_ID Long (Foreign Key link to PM Master
if WO_PM_YN = Yes)
WO_Start_Date Date (Date Work started on WO)
WO_Comp_Date Date (Date WO Completed)
Duration ???? (Time required to complete all work
for the Work Order)

WO_Detail:
WO_ID
Mnt_Tsk_ID
Tsk_Start_Date Date (DateTime Work started on Task)
Tsk_Comp_Date Date (DateTime Task Completed)
Duration ???? (Time required to complete Task)
Assigned_To Long (Foreign Key link to Personell)
Labor_Cost Currency
Parts_Cost Currency

WO_Det_Parts:
WO_ID
Mnt_Tsk_ID
Part_ID Long (Foreign Key link to Parts Table)

The Task table is used to define not only PM tasks but also routine non-PM maintenance tasks (e.g. changing the oil in a car). I also found that many Tasks were performed for various periodic Preventive Maintenances (e.g. you change the oil as part of several x-number-of-miles check-ups for a car). (I also had a "Misc" Task which would trigger a requirement for a description of what was done.)

By having the expected duration and parts information associated with a Task, the work schedule can be better estimated and, in the case of a PM WO, you can be sure that the parts are available.

By having one Work_Order file, all work done on equipment is referenced via the same table whether it is PM or unscheduled. When work order is created, it can be flagged as including a PM. (This was done because there are occassions where an unshceduled maintenance activity is so close in time or in effective work requirements that the PM may as well be done.)

As you might expect, flagging an unscheduled maintenance activity as including a PM would trigger the inclusion of the various tasks and their associated parts. If the WO does NOT include a PM, then the various tasks would need to be selected individually with the result that the various parts required for the Tasks would be automatically included, as would the estimated time requirements.

In my case, where the PM was associated with medical equipment, there was an additional table of step by step instructions for each PM task. That table provided the means for a scheduled (or unscheduled by included)PM to be assigned to a technician and the instructions printed out for everything that had to be done, which elliminated the tech having to lug around some hefty tech manuals.

The scheduling of PM for a given piece of equipment was a bit trickier. Essentially, the "scheduling" consisted of a report of PM due within the next x number of days based upon analyzing the WO's that had been performed for each machine since the date of the last PM. In the case of having a variety of PM's for a given piece of equipment, it would be necessary to include business rules to filter/analyze based upon the intervals for the various PM's. By having that interval in the PM_Master, the PM_Master's PM_Interval could be used to check for the last performance of/need to perform a given PM.

 

[directorz]

RDWilson2,
I am going to be getting into the parts thing here shortly. Thank you for making your setup available to me and others who may be doing smilar projects!

Directorz

 

[BSman]

If you are going include parts with the work order data, consider the following:

You obviously need a list of parts with a PartID, description, type of unit quantity (each, gallon, etc.), and perhaps the cost per part (if that isn't tool much hassle to keep updated).

The simplest way to go from there is to have a WOParts table with the WOID, PartID, quantity. (Multiple records per WOID).

If you want to get fancier, you can also set up bills of materials for "units". A unit might be an engine, air compressor, transmission, motor, etc., or even a PM (or a work item associated with a PM). Then you need a UnitID, description, etc.

You also would then need a standard Bill of Materials (BOM) table that would include an ID, UnitID, PartID, Quantity per unit, replacement percentage (or the quantity typically replaced). You could even have two replacement columns, one for overhaul and the other for scheduled PM.

The BOM table could then be used to control the parts that could be used on a work order where units (or a PM) are identified. It could also be used as a way of posting the standard parts used to a work order so that only differences in quantities would need to be entered.

If you end up setting up a parts purchasing system within this VMIS you could use the BOM to help analyze parts usage, stock, and requirements.

 

[LittleSmudge]

directorz
I think the table structure posted recently by RDWilson2 goes a log way towards what you want and it's along the line of what I was suggesting - and it goes further too.
So I feel I've nothing more to add here.


RDWilson2
I can understand what the
xxxx_Name
field is actually used for in many of your table but what does "conumable" mean ?

It's not a word I've come across.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[directorz]

RDWilson2
I would like to include "OK" or "PASTDUE" in a status field. Did you do this? Based on this sample, what coding did you use to arrive at the result. What changes need to be made

TblPMSchedule
PMID - PK
PMName - PK
PMInterval - PK

TblWorkOrders
WorkOrderNo - PK
PMID - FK to TblPMSchedule / PMID
Hours
Status

 

[directorz]

Please disregard...

 

[directorz]

RDWilson2,

Disregard the 'please disregard'. Did you incorporate a status field indicating whether or not a pm was past due. If I have a row unitid, workorder, pmid, hours, interval and status, what is the method of pulling the previous record with a matching pmid for that unit in order to make the calculation?

 

[BSman]

For the VMIS system which I worked with, PM requirements were determined by a combination of (by vehicle) the type of the last PM, the scheduled interval (time or mileage as appropriate) to the next PM, and the difference between the (date or mileage) at the previous PM versus the present (date or mileage). The comparison of the scheduled interval versus the actual interval, which is performed from existing data, enabled us to report on which vehicles would require PM in, for example, the next two weeks (or x miles), and which were overdue for PM.

There is no need for a status field for PM status.

Status in a work order would be useful to indicate special situations such as "waiting for parts" or "returned to service without repair". The second one is so you can indicate that a vehicle is available for service even though there is an oustanding work order on it. It's when the work order indicates that some work in needed on the vehicle, but the work is not something (like a safety problem) that would make the vehicle unsuitable for service.

 

[directorz]

BSMan,
Status could go in the PMSchedule table. The problem is that I can show when the next one is due by something like DataAdd("m",6,[tblpmworkorders]![dateofservice], but I need to pull the previous record to be able to show a status of the pm. The status is simply used as a quick visual reference where a user can see all units at a glance...