Maintenance Database Design, from First Principles

In other articles on this site, we’ve discussed identifying the required outputs for a maintenance computer system as an early step toward database design. “Outputs” include all those documents or screens used by Maintenance people to manage plant maintenance, everything from work orders to preventive maintenance route sheets to work schedules and so on.

In this article we’ll go back one step further and identify some of the information that key maintenance people need to effectively do their jobs, then look at how the design and configuration of the maintenance computer system can provide that information in the most sensible form. This article does not cover all, or even most of the information needed by Maintenance. What it does is provide some guidelines on how to produce a list of requirements that you can use to select a maintenance computer system or to improve the configuration of an existing system.

Let’s look at some key positions and their information needs:


  1. A Backlog Manager and/or Scheduler, i.e. the person or people who maintain the backlog and create weekly maintenance work schedules.

What’s needed here is a list of work orders that exist in the backlog, in a format that makes each job easily identifiable to someone who is familiar with the work and can be either viewed on a screen without scrolling left and right or can fit the width of a standard page as a printed report. The backlog management and scheduling functions involve putting these jobs in order of priority, then constructing a weekly (or other) schedule for those work orders for which all material is on site. The list of work orders should contain the bare minimum of information required to put the work orders in the correct order of priority and include:

– the work order number

– the equipment location number

– the short equipment location description

– the initial work priority

– a secondary priority to refine the order in which the jobs will be done

– the status (e.g. is material available yet?)

– the short work order description

– the estimated hours for each trade

– and perhaps other information, such as the date originated, shutdown requirements, PM frequency and so on if this information is used for scheduling.

The Backlog Manager/Scheduler needs to see enough information for each work order for it to be easily identified without expanding so it can be compared to all the other work orders to allow good priority decisions to be made. The more work orders that can be viewed on a printed page or a computer screen at one time the better. To achieve this, the data displayed must be brief, to the point and recognizable. Ideally, the database and software should be designed to do as much of the following as possible:

– avoid displaying leading zeroes and other repeating leading digits on work order numbers – just show enough to clearly identify the job

– show an abbreviated equipment location description. If the full name (e.g. “No. 4 Kraft Machine Layboy Finger Bars”) is shown, there won’t be much room to show other key information. An abbreviation such as “KM4 Lboy Fgr Brs” might not mean much to an outsider, but the area Maintenance and Operating Supervisors and the Planner, who are the people who will read the list will recognize it instantly. Of course, whenever an equipment location number is displayed it MUST ALWAYS be accompanied with a description, usually the abbreviated description.

– limit the number of characters that can be entered in the work order “Short Description” field so that it is never truncated in any report or on any screen. This avoids displaying descriptions such as (real example) “Please inspect and make repairs to..” that require the work order to be expanded to be understood.

– most importantly, allow all fields to be edited in the table (list) view without expanding or opening individual work orders. It should also be possible to sort and filter the list by most fields, with at least two sorting “levels”.

For more details on information required by schedulers and backlog managers, see note 1.

  1. An Operator

If an Operator or Operations Supervisor needs to request maintenance work one of the first and most important pieces of information to be entered is the equipment location number. And the easiest way to find the right number in the maintenance computer is to search for key words (see Note 2), or better still, several key words if the system supports flexible searching. If the job is on the No. 4 Kraft Machine Layboy Finger Bars, the key word that will probably result in the lowest number of hits is “Finger”. Obviously, if key words are to be used in searching, the database must contain the full, unabbreviated description. No-one can be expected to remember that “finger” is entered as “fgr” in the database. In addition to the unabbreviated description, this “search” field should contain other search terms that are likely to be used. For example, if “KM4” is the standard abbreviation for “No.4 Kraft Machine”, then the search description should be something like “No. 4 Kraft Machine KM4 Layboy Finger Bars” so that it includes all likely search words (see note 3).

So while an abbreviated equipment location description is needed for reporting and the unabbreviated description is needed for searching, it can be concluded that there must be two description fields for each equipment location.

  1. A Preventive Maintenance inspector

Let’s assume that a good PM inspection system is in place, with equipment locations listed in the order of shortest walking distance, and good, clear descriptions of the inspections required at each location (and there may be a lot of other information, such as high/low limits for measurements and readings, inspection standards, etc).

The first thing that the inspector needs to know if he (or she) finds a problem that needs attention is if the problem needs to be reported at all. If someone else has already submitted a work request, then the inspector does not need to, and should not, report it again. Duplicate work requests complicate backlog management. For this reason inspectors should be able to see the open work orders for all equipment locations on their inspection route. There are a number of ways the maintenance system could do this, including:

– print a list of open work orders for the equipment items on the inspection route and attach it to the inspection route sheet.

– if the route sheets are on paper, design these sheets so that the short description of open work orders appear against each location to be inspected.

– if hand-held devices are being used for inspections and the wireless system is reliable, provide easy on-line access to show open work orders at each location.

– if the wireless system is unreliable and hand-held devices are being used, load the open work orders for equipment locations on the route on to the device prior to starting the inspection.

These are features that may not be available on your system but downloading information from the database to a flexible tool such as MS Access can make these things possible.

Some of the better systems can also interface to the operating control system and are able to show PM inspectors useful “live” data such as motor loads, tank levels, etc.

There are, of course, many other aspects of system functionality required to support a good PM inspection programme.


  1. A Planner

A Planner’s primary responsibility is to prepare detailed work plans for maintenance jobs. A detailed work plan consists of a list of work steps, with instructions and a list of all the resources and information required for each step and their locations, plus estimated times for each step (See note 4). The information for a detailed work plan may include drawings and specifications, photographs, manufacturer’s repair instructions, component and repair standards, isolation and lockout procedures and so on.

A few of the important system functions for a Planner include:

– the ability to quickly insert or delete job steps

– the ability to assign materials and other resources to each step (e.g. drag-and-drop from the list of stock and direct purchase materials [and tools] for the work order to each job step).

– text wrapping and other standard formatting functions for fields that contain text, such as job instructions,

– the ability to link to other documents, such as standard job procedures, engineering data, isolation procedures, etc.

– clear instructions on the fields to use for both stock and purchased materials and services, so that the system can flag work orders as “ready to schedule” as soon as all materials are on site.

This is just a very short list of the system functions that will assist a Planner.


There are, of course, many other positions in Maintenance and Maintenance Materials management that have their own special information needs.

Maintenance computer systems should help everybody in Maintenance, Purchasing and Stores and many people in Operations. As an early part of a maintenance system project, its a good idea to talk to the people whose jobs will be affected and find out what is important to them, as in the above examples. This information can then be used to prepare a list of requirements for evaluating the available systems.

If you’ve had a system in use for a while, there’s a good probability that the people using it will now have a much better idea of what they would like it to do. The better systems have considerable flexibility in the way that they are configured and many features such as adding custom fields and limiting the number of characters allowed in each field are probably available. Some minor changes in the system may have large benefits.

For more on developing a comprehensive list of requirements for a maintenance computer system, please contact us.



Note 1. For more on backlogs and schedules, see the article “Backlogs – their vital importance to Maintenance” and the video “Scheduling Maintenance Work – Part 1“.

Note 2. In many systems the recommended equipment location number search process is to “drill down” through the equipment hierarchy. In my experience the structure of these hierarchies is often poorly planned and searching is time-consuming and difficult. A keyword search is usually much faster and more reliable. For an example of a flexible search tool, check out “FindIt“. FindIt includes both a Stores search and an equipment location number search and makes both as easy as “Google”.

Note 3. For more information on naming standards see the articles “Asset ID and numbering” and “Naming parts“.

Note 4. For more on planning see the article “Detailed Planning“.


To return to the articles index, click here.

Don Armstrong, P. Eng


Veleda Services Ltd

250-655-8267 (Pacific Time)

©Veleda Services Ltd