CMMS data model: assets, work orders, spare parts, meters

Key takeaways

• The core is eight objects: asset, meter, reading, PM plan, request, work order, part and part issue. Three more carry the detail: failure record, labour booking and the asset to part link.
• How far you can go is decided by parent_asset_id on the asset and the downtime window on the work order.
• Store location and cost centre as links, never as typed text. Free text kills every roll-up you will want later.
• Worked below: 1,000 run hours, 3 failures, 520 downtime minutes gives MTBF 333.3 h, MTTR 173.3 min and availability 99.1%.
• The CMMS owns the asset, the meter and the work order. The ERP owns the purchase order, the invoice and the book value.

The objects a CMMS needs

This is the reference schema for maintenance in a manufacturing plant: the tables, the fields and the keys that join them.

It covers maintenance work orders. For the orders that make parts, see our production work order data model.

The asset registry

The asset record is the spine. Every reading, plan, work order and part consumption points at it.

Two fields do the work. parent_asset_id builds the hierarchy (site, area, line, machine, component) The levels, the fields that belong at each one and the rules for moving a machine are in our asset hierarchy data model. and asset_class lets you compare every filler you own.

Set the tag scheme before the first import. Our asset hierarchy and naming convention guide covers the levels and the codes.

Criticality drives everything downstream

Criticality decides PM frequency, spare stocking and who gets called at night. Score it on consequence (safety, line stoppage, quality risk, repair cost, redundancy) and keep it to three classes.

Asset status and what triggers each state

Only the six stored states are lifecycle. Down is a condition you derive from open downtime, because storing it overwrites Standby or Commissioning and nothing tells you what to restore on repair.

Never delete an asset. Decommission it, because deleting takes every work order and cost with it.

The maintenance work order

The work order is the unit of work and the unit of cost. One work order, one asset.

Downtime and labour are different quantities. The filler was down 240 minutes, and two technicians each booked 3.5 hours, so labour_hours is 7.0.

Open downtime only when the asset really stopped, and set downtime_type to planned for shutdown and PM work. Without that flag every planned stop inflates your failure figures (see downtime reason code design).

The stop itself belongs to the asset, not to the work order, because a machine can stop with no job on it. Where a monitoring system already records machine states, treat that event as the source and let downtime_start and downtime_end point at it.

Work order status and what triggers each state

Waiting needs a reason and a timestamp. Without them, a job waiting six days for a seal looks like a job nobody started.

For the list behind wo_type, see work order types explained.

Failure coding: problem, cause, remedy

Three code fields answer three questions: what was seen, why it happened, what was done.

Give each list three levels, so a technician picks from a short menu at every step instead of scrolling 200 codes.

Make the codes mandatory only where they pay off: corrective work orders with downtime, on class A and B assets.

Two questions justify the scheme: which component fails most often, and which cause returns after the same remedy.

Keep the codes in a separate failure record with its own key, not as four columns on the work order. One job can find two failed components, and four columns force the technician to pick the more impressive one.

The request that precedes a work order

An operator reporting a leak is not yet a work order. Keep the request separate, so rejected and duplicate reports stay out of your work order history.

reported_at and accepted_at give you reaction time: 12 minutes in the row above.

Reaction time is usually the real complaint behind "maintenance is slow". It stays invisible while requests arrive by phone.

PM plans and schedules

A PM plan is a rule, not a task. It says when work falls due on one asset and which task list to copy into the work order.

At 13,480 h against a due point of 13,500 h, the plan above is 20 run hours away, which is about two days at roughly 11 run hours a day.

The 7 day lead converts to about 77 run hours, so this work order was raised at 13,423 h and is already on the list. A lead expressed in days always needs that conversion, and a plant that skips it schedules meter based PM by accident.

Floating vs fixed: a floating plan counts from the last completion, so a late job pushes the next one, while a fixed plan keeps the original calendar (preventive maintenance schedule).

Meters and readings

A meter belongs to an asset. A reading belongs to a meter, and readings are never overwritten.

The rollover rule

A counter that wraps looks like a huge negative jump, and a plan watching it stops firing silently.

Write the rule once, and guard it: treat a lower value as a wrap only when the last reading sits near rollover_at and the counter was not swapped. Then delta = (rollover_at minus last value) plus new value.

• Last reading 99,942 h, next reading 58 h, rollover_at 100,000.
• Delta = (100,000 minus 99,942) + 58 = 116 h, not minus 99,884.
• Lifetime hours = previous lifetime + delta, so usage always comes from deltas.

Two other cases also produce a lower value and must not be read as a wrap: a replaced counter starting at zero (store an offset) and a wrong entry (post a new reading, never edit the old one). Without the guard above, the rule adds a phantom 100,000 h of life to the asset and every meter based plan goes quiet for years.

Readings from a machine monitoring system remove the typing errors, and a condition trigger needs them anyway (condition based maintenance).

Spare parts, stock and consumption

The part record holds the identity and the stock rule. The issue line holds what was used, and it always belongs to a work order.

Ten seals at 18.40 EUR is the 184 EUR parts cost on that work order. Keep unit_cost_at_issue on the line, because a price change must not rewrite last year's repairs.

qty_on_hand is the result of movements, never a typed number. The reorder point arithmetic is worked through in our tool crib data model and our spare parts management guide.

Which part fits which asset

Most plants skip this table, and then a technician spends 20 minutes in the store guessing.

Allow the link on asset_class too. One entry then covers all six fillers instead of six.

Keys and foreign keys

Three joins earn their keep: asset to work order (history and cost), work order to part issue (what a repair cost) and meter to plan (usage based PM).

Six supporting lists sit behind these keys and each needs its own table: location, cost centre, bin, vendor, task list and employee. Point at them, because a code list kept as typed text rebuilds the free text problem one column further down.

Worked example: MTBF and MTTR for one filler

Asset FIL-01, period 1 June to 31 August 2026. The run hour meter read 12,480 h at the start and 13,480 h at the end.

Three corrective work orders carry is_failure = yes. The two preventive jobs are excluded, because planned work is not a failure.

1. The definitions used

• Operating time = meter delta = 13,480 minus 12,480 = 1,000.0 h. The counter runs only while the machine runs, so downtime is already excluded.
• Downtime per failure = downtime_end minus downtime_start, not labour hours.
• Failure = a work order on this asset with is_failure = yes.

2. MTTR

Mean time to repair = total downtime ÷ number of failures.

• 520 ÷ 3 = 173.3 minutes, or 2.89 h.
• Wrench time instead of downtime gives a smaller number, so state which one you mean (mean time to repair).

3. MTBF

Mean time between failures = operating time ÷ number of failures.

• 1,000.0 ÷ 3 = 333.3 operating hours.
• Strictly, "between" failures includes the repair: (1,000.0 + 8.667) ÷ 3 = 336.2 h.
• The gap is 0.9% here, and it grows on assets with long repairs, so put the definition in the report header (mean time between failures).

4. Availability, and which one it is

• Use the MTBF that excludes repair: 333.3 ÷ (333.3 + 2.89) = 99.1%. Feeding the 336.2 h figure into the same formula counts the repair twice.
• It is the same arithmetic as 1,000.0 ÷ 1,008.667, so treat that as a restatement, not an independent check.
• This is inherent availability: uptime against uptime plus repair time, with no idle time and no planned maintenance in it.

The availability factor in OEE is a different number: run time divided by planned production time, which does include planned stops. The same filler can honestly show 99.1% on the reliability report and 90% on the OEE dashboard.

None of it works without is_failure, the downtime window and a meter reading at each end of the period. Drop one and you are counting jobs, not reliability.

What belongs in the CMMS and what in the ERP

The split is clean if you ask one question per field: is this about the physical machine, or about money and procurement?

Two fields must be identical on both sides: part_no and cost_centre. If they drift, no maintenance cost report will reconcile.

Costs cross the line in one direction only. The CMMS holds a unit cost and a labour rate for reporting, copied from the ERP, while the ERP keeps the valuation and the payroll figure of record.

The rule of thumb: the CMMS raises the demand for a part, the ERP turns it into a purchase order. Our CMMS vs ERP comparison goes further, and what is a CMMS covers the basics.

Five modelling mistakes that cost you later

1. Location as free text

"Hall B, near the palletiser" cannot be grouped or counted. Make location a record with a parent and put the wording in a note.

2. No parent asset

A flat list of 900 machines cannot answer "what did line 2 cost last year". You will not retro-fit it later, because nobody re-tags 900 rows.

3. Meter readings without a rollover rule

The first wrap turns usage negative and the meter based plans stop firing. Nobody notices until a bearing does.

4. Parts booked outside the work order

Issue a seal to a cost centre instead of WO-26-0412 and stock stays right while history stays empty. You know the plant spent 30,000 EUR on spares, not which asset ate it.

5. Deleting instead of closing

Deleting a cancelled work order or a retired asset erases the evidence behind your MTBF, your backlog and your budget. Use a status and keep the row.

How Fabrico helps

Fabrico is an OEE platform with a full CMMS built in, and the working core of this model is in it out of the box. The deeper reference pieces here, such as meter rollover handling, three level failure code trees and an asset to part compatibility table, are a specification to compare against, not a feature list.

Assets carry a hierarchy, files, history and QR codes, and your team scans a machine or a part from the iOS, Android or web app to report a problem or open a work order.

Work orders run through a status workflow with approvals, shifts, assignment and time tracking, and preventive plans use recurring templates and conditional tasks. Inventory holds min and max levels, deliveries and stock-takes, with consumption booked against the work order.

Machine data from PLC connections, IoT sensors and AI cameras puts real run time and downtime beside the maintenance history, and the analytics include MTTR and MTBF by machine.

What it does not do: your team creates every work order, failure forecasting is not a standard feature, and there is no formal root cause investigation and no production planning. Problem, cause and remedy codes are never forced on a technician.

Want your asset register and work order history in one place? Book a 30 minute demo with a Fabrico consultant, no commitment, or contact us with your questions.