Type of relationship?

[hoofit]

Good afternoon folks,

I have an equipment maintenance db. Within it there are 3 tables causing me some confusion and I am requesting some assistance. Here’s what I’ve got;

Tblworkorders
Workordernumber – PK
Date
Other work order info

Tblworkorderdetails
detailsID – PK
ref_workOrderNumber – FK to tblworkorders

tblPMschedule
unitid
pmname
pminterval
ref_detailsID – FK to detailsID
PMcomplete

Some notes on the design of the pmschedule table. The PK is a composite consisting of the first 3 fields. I’m aware that this is not recommended and that there may be consequences under certain conditions. Here’s a few possible entries in the table;

Unitid – 1
Pmname – A
PMinterval – 50
Complete? – yes

Unitid – 1
Pmname – A
PMinterval - 500
Complete? - no

Unitid – 1
Pmname – A
PMinterval – 1000
Complete? – no

So, in this example, a unit can have an assigned PM-a with different intervals like 50 hours, 500 hours or 1000 hours. Anyway, I need to get to the point. I have a one to many relationship between the details table and the schedule table. A details record can include many PM’s. But here is where I become confused.

Question: Is this really a one to many relationship?

A single detail record can only have one PM making this a one to one relationship.

1.There can be an unlimited number of PM's - these will be defined by the
user and is entirely dependent on the type and number of pieces of equipment.
2. A work order can include documenting the fact that one or more PM's have been completed. However, the detail record of the work order is where this will occur. (This is part of my confusion - a SINGLE detail record can only have 1 PM).
3. Can more than one detail record have the same PM? Sure. On 10/1/06 I can complete PM-1 and again on 12/5/07 I can do it again.
4. Yes, a single PM can have many detail records associated with it over time.
5. I think I just confused myself even more trying to explain this.

Thank you

 

[Darrylles]

Hi,

What does UnitID and PMName represent? Are they ALWAYS the same (1 & A) in all records? i.e. could this also be 1 and B?

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[hoofit]

Darrylle,
There are several tables not shown here, one of which is the units table. UnitID is simply the unit number being tracked. For example if I had a truck I may wish to name it truck 1. Thus, its unitID would be 1.

PMName is the name given to a particular Preventive Maintenance operation. Let's say that every 3000 miles you want to change your oil. You may decide to call this PM-A.You may include other things as well that you want to check every 3000 miles. Then you come up with another service - maybe rotate your tires every 5000 miles - you need to come up with a different name becuase it is done at a different time frame - so you may call it PM-B.

So, to answer your question, no it's not always the same.

 

[Darrylles]

Hi,

Ok so you have a 'tblUnit'.

PMName is preventive maintenance for 'units' is it not? Should it not therefore reside in the 'Units' table?

(This could be a long thread mate).

Regards

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[hoofit]

I have a units table to store info about a unit ie; make, model, engine size, vin#, etc. The PM table stores PM's assigned to a specific unit. Thus, a unit can have one or more PM's assigned to it.

 

[PHV]

You may have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnherman]

I think I would change the PM schedule a bit:

This is your required maintenance table:
tblPMrequirements
unitid
pmname
pminterval
ref_detailsID – FK to detailsID

This is your completed maintenance table and it allows you to calculate the next due date from the performed date and the interval.
tblPMschedule
unitid
pmname
pminterval
pmdateperformed
pmcomments

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[hoofit]

johnherman,
Sorry about the long delay, I will take a gander at this. Thank you also PHV. Did not want to not respond to the replies.

Thank you