I would like to create a database to help me track the preventative maintenance work carried out on a variety of different equipment.

In essence, we have 12 different types of equipment which all need different checks carried out at periodic intervals. To simpify lets call them Xpndr1 thru Xpndr12 (Xpndr is an abbreviation for transponder) We carry out weekly checks on all the transponders and then quarterly checks on them all.

I need to record 6 pieces of information for transponder 1's weekly checks and 7 pieces for its quarterly checks. For transponder 2 it is 5 pieces and 9. (Few of the transponders need the same information recorded and the data recorded weekly is not at all simillar to the quarterly data).

The best I can come up with is a seperate table for each set of checks, I.e A table, lets call it Xpndr1_wkly to record the outcome of the weekly checks carried out on Xpndr1. Another table called Xpndr2_qrtly for the quarterly checks on Xpndr 2. And so on and so on.

This however means 24 tables for all the data I want to record. I think this might be inefficient but I cant think of a better way

Any suggestions

It's difficult to give you a good solution without a little more background but here's my attempt:

Why not have a table containing all the types of checks available. Another table holding the types of transponder available. Another table showing the type of each individual transponder. Another table with the schedule of what checks should be applied and when for each type of transponder. Finally a table holding the check results for each individual transponder. I wasn't sure what the results look like so I'll guess they are all numeric.

• CheckTypes( check_id, check_desc )
• TransponderTypes( xpdr_type, xpdr_desc )
• IntervalTypes( check_interval, interval_desc )
• TransponderCheckSchedule( xpdr_type, check_id, check_interval )
• Transponders( xpdr_id, xpdr_type )
• TransponderCheckResults( xpdr_id, check_id, date_taken, result )

The advantages of this type of approach is that you can add new types of transponder or new checks without altering the structure of your database. You could also add new types of interval ie fortnightly or yearly without any change to the structure.

Mike,

First, thanks for the reply.

Despite your comment to the contrary, I reckon you have given me a good solution. Particularly the fact that bringing new transponders in to the system will not necessitate me changing the structure

I would like to ask another couple of questions if I may.

Our transponders are spread over about 50 sites. Some sites have Transponders of type 1, 5,6,7, & 11. Other sites have Transponders 2,7, & 9. You get the idea.

Could I then add a field in your Transponders table so it becomes
Transponders(xpndr_id,Xpndr_type,location)

Also, I would like a quick way of dropping a transponder from the "needing checked" list

Could a boolean field in the Transponders table cover that so the Transponder table now becomes
Transponders(xpndr_id,xpndr_type,location,xpndr_ac tive)

Thanks again for your help.

Yes. It might be worth having a location table to check as a FK. You could then expand this to perhaps have GPS coords so you could automatically work out the transponders in a certain area that an engineer needs to check. You could also perhaps store how long it takes to make each type of check and what equipment might be needed but this might be a future enhancement.

Certainly.

Mike,

Your answer here would seem to give the lie to your claim that you need more detail to fully understand the problem. I would suggest you have a very good idea of what I am trying to achieve and your answers have helped me immeasurably

Thanks very much