Airline MRO Aircraft Maintenance data model
Third-normal-form mirror of an airline / MRO maintenance information system as it lands from the source CAMO/CMMS. Each source table becomes its own entity with a warehouse surrogate key plus the genuine MRO business key(s) (tail number, component serial, work-order id, S1000D DMC, part number). The on-wing rotable assembly tree is preserved via a parent_component_key self-reference; FAA/EASA traceability is preserved on every transactional row (mechanic certificate, inspector RII sign-off, MEL deferral).
Normalized replica of an airline / MRO aircraft-maintenance source system (CAMO/CMMS such as AMOS, TRAX, Maintenix). Anchored to ATA-100 chapter codes (21 AirCond, 27 Flight-Controls, 32 Landing-Gear, 72 Engine), S1000D data module codes for task cards, SPEC 2000 business information exchange for parts and repair orders, and reliability programs driven by flight-hour / flight-cycle / calendar intervals (A/B/C/D checks, CPCP). Fleet, rotables with serial tracking, work orders and task cards, discrepancies with MEL/CDL deferrals, and the off-wing shop-visit cycle are each modeled as their own entity, keyed by the genuine MRO business keys. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 15
- tables
- 147
- columns
- 23
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
ATA-100 chapter as the universal subsystem dimension
ATA-100 (and its iSpec 2200 successor) assigns every aircraft system a two-digit chapter code (21 AirCond, 27 Flight-Controls, 32 Landing-Gear, 72 Engine); reusing it as the single ata_chapter lookup keeps parts, components, work, discrepancies and reliability data joinable across every operator and every type.
Aircraft to installed components via a self-referencing assembly tree
A rotable build-up (engine → module → LRU → sub-LRU) is naturally a tree, and CMMS systems model it that way; a single component entity with a parent_component_key self-reference preserves the on-wing assembly hierarchy without a separate bridge table.
S1000D data module code preserved as the task card business key
S1000D issues every approved maintenance task as a Data Module with a globally unique DMC; keeping the DMC as the task_card business key makes the warehouse reconcilable with the AMM and lets reliability engineers trace findings back to the exact approved procedure.
Discrepancies carry MEL / CDL deferral category and expiry
Operators can dispatch with open defects only under their approved MEL/CDL with a fixed deferral interval (A/B/C/D); modeling deferral category and deferred_until_date on the discrepancy is what lets ops control verify legal dispatch and what auditors reconstruct.
Off-wing shop visit modeled separately from on-wing work orders
A rotable removed from one tail is repaired in a Part-145 shop and may be reinstalled on a different tail; the shop visit has its own SPEC 2000 repair-order business key, costs and outcome, so keeping it distinct from work_order preserves FAA/EASA part traceability.
Tables in the Airline MRO Aircraft Maintenance model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (8)
aircraft
Grain: one row per aircraft (tail number)
Aircraft — one row per tail number, the asset against which all maintenance is tracked.
| Column | Type | Keys |
|---|---|---|
| aircraft_key | bigint | PK |
| airline_operator_key | bigint | |
| aircraft_model_key | bigint | |
| aircraft_id | varchar | BK |
| serial_number | varchar | |
| delivery_date | date | |
| total_flight_hours | decimal | |
| total_cycles | integer | |
| fleet_status | varchar | |
| base_station_code | varchar |
component
Grain: one row per serial-controlled component
Component — serial-controlled rotables, tracked individually with an assembly tree via parent_component_key.
| Column | Type | Keys |
|---|---|---|
| component_key | bigint | PK |
| part_master_key | bigint | |
| aircraft_key | bigint | |
| ata_chapter_key | bigint | |
| parent_component_key | bigint | |
| component_id | varchar | BK |
| install_date | date | |
| removal_date | date | |
| total_time_since_new_hours | decimal | |
| total_time_since_overhaul_hours | decimal | |
| time_since_overhaul_cycles | integer | |
| position_code | varchar | |
| component_status | varchar |
work_order
Grain: one row per maintenance work order / work package
Work order / work package — the top-level container for a maintenance event on a tail.
| Column | Type | Keys |
|---|---|---|
| work_order_key | bigint | PK |
| aircraft_key | bigint | |
| station_key | bigint | |
| maintenance_program_key | bigint | |
| airline_operator_key | bigint | |
| work_order_id | varchar | BK |
| work_order_type | varchar | |
| check_type | varchar | |
| planned_start_date | date | |
| actual_start_date | date | |
| planned_end_date | date | |
| actual_end_date | date | |
| planned_labor_hours | decimal | |
| actual_labor_hours | decimal | |
| work_order_status | varchar | |
| total_cost | decimal | |
| currency | varchar |
task_card
Grain: one row per task card on a work order
Task card — one approved task / inspection step from the AMM / MPD (S1000D DMC).
| Column | Type | Keys |
|---|---|---|
| task_card_key | bigint | PK |
| work_order_key | bigint | |
| ata_chapter_key | bigint | |
| task_card_id | varchar | BK |
| task_description | varchar | |
| task_type | varchar | |
| dmc_data_module_code | varchar | |
| interval_type | varchar | |
| interval_value | decimal | |
| expected_duration_minutes | integer | |
| requires_certified_inspector | boolean | |
| task_status | varchar |
task_signoff
Grain: one row per task card sign-off / certification
Task sign-off — the mechanic / inspector certification that closes a task card.
| Column | Type | Keys |
|---|---|---|
| task_signoff_key | bigint | PK |
| task_card_key | bigint | |
| signoff_id | varchar | BK |
| mechanic_certificate_number | varchar | PII |
| inspector_signoff_number | varchar | PII |
| signoff_date | date | |
| labor_minutes | integer | |
| was_passed | integer |
discrepancy
Grain: one row per discrepancy / finding raised against a tail
Discrepancy — PIREP/MAREP/finding raised against a tail, with MEL/CDL deferral tracking.
| Column | Type | Keys |
|---|---|---|
| discrepancy_key | bigint | PK |
| aircraft_key | bigint | |
| work_order_key | bigint | |
| ata_chapter_key | bigint | |
| discrepancy_id | varchar | BK |
| discrepancy_type | varchar | |
| discovered_date | date | |
| description | varchar | |
| deferral_category | varchar | |
| deferred_until_date | date | |
| resolved_date | date | |
| mel_item_number | varchar |
part_consumption
Grain: one row per part transaction against a task / work order
Part consumption — parts removed, installed, scrapped or returned against a task card.
| Column | Type | Keys |
|---|---|---|
| part_consumption_key | bigint | PK |
| work_order_key | bigint | |
| task_card_key | bigint | |
| part_master_key | bigint | |
| component_removed_key | bigint | |
| vendor_key | bigint | |
| consumption_id | varchar | BK |
| quantity_used | decimal | |
| unit_cost | decimal | |
| total_cost | decimal | |
| currency | varchar | |
| transaction_type | varchar |
shop_visit
Grain: one row per off-wing shop visit on a rotable component
Shop visit — the off-wing rotable repair cycle, distinct from on-wing work orders.
| Column | Type | Keys |
|---|---|---|
| shop_visit_key | bigint | PK |
| component_key | bigint | |
| vendor_key | bigint | |
| shop_visit_id | varchar | BK |
| induction_date | date | |
| completion_date | date | |
| work_scope | varchar | |
| total_cost | decimal | |
| labor_cost | decimal | |
| parts_cost | decimal | |
| currency | varchar | |
| outcome | varchar | |
| replacement_component_id | varchar |
Lookup tables (7)
airline_operator
Grain: one row per airline / operator
Airline / operator master — owner of the aircraft and the maintenance program.
| Column | Type | Keys |
|---|---|---|
| airline_operator_key | bigint | PK |
| operator_id | varchar | BK |
| operator_name | varchar | |
| country | varchar | |
| fleet_size_band | varchar | |
| certificate_type | varchar | |
| is_active | boolean |
ata_chapter
Grain: one row per ATA-100 chapter (aircraft system code)
ATA-100 chapter check table — the universal aircraft subsystem dimension.
| Column | Type | Keys |
|---|---|---|
| ata_chapter_key | bigint | PK |
| ata_chapter | varchar | BK |
| description | varchar | |
| system_category | varchar | |
| is_powerplant | boolean |
aircraft_model
Grain: one row per aircraft type/model
Aircraft type/model master — links to the type certificate and design data.
| Column | Type | Keys |
|---|---|---|
| aircraft_model_key | bigint | PK |
| aircraft_model | varchar | BK |
| manufacturer | varchar | |
| mtow_kg | decimal | |
| max_seats | integer | |
| etops_minutes | integer | |
| type_certificate | varchar |
station
Grain: one row per maintenance station / airport
Station / airport master — where the aircraft is when work is performed.
| Column | Type | Keys |
|---|---|---|
| station_key | bigint | PK |
| station_id | varchar | BK |
| station_name | varchar | |
| country | varchar | |
| station_type | varchar | |
| capabilities | varchar |
vendor
Grain: one row per external vendor / repair station
Vendor master — OEMs, FAR-145 repair stations and parts brokers.
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_id | varchar | BK |
| vendor_name | varchar | |
| country | varchar | |
| vendor_type | varchar | |
| far_145_certified | boolean |
part_master
Grain: one row per part number
Part master — SPEC 2000 part catalog with rotable / LLP attributes.
| Column | Type | Keys |
|---|---|---|
| part_master_key | bigint | PK |
| ata_chapter_key | bigint | |
| part_number | varchar | BK |
| part_name | varchar | |
| unit_of_measure | varchar | |
| is_serialized | boolean | |
| is_consumable | boolean | |
| is_rotable | boolean | |
| is_life_limited | boolean | |
| life_limit_hours | decimal | |
| life_limit_cycles | integer | |
| life_limit_days | integer |
maintenance_program
Grain: one row per maintenance program task / check
Maintenance program — the operator's approved check & inspection schedule.
| Column | Type | Keys |
|---|---|---|
| maintenance_program_key | bigint | PK |
| program_id | varchar | BK |
| program_name | varchar | |
| program_type | varchar | |
| interval_basis | varchar | |
| interval_value | decimal | |
| is_active | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- part_master.ata_chapter_key → ata_chapter (many-to-one)
- aircraft.airline_operator_key → airline_operator (many-to-one)
- aircraft.aircraft_model_key → aircraft_model (many-to-one)
- component.part_master_key → part_master (many-to-one)
- component.aircraft_key → aircraft (many-to-one)
- component.ata_chapter_key → ata_chapter (many-to-one)
- work_order.aircraft_key → aircraft (many-to-one)
- work_order.station_key → station (many-to-one)
- work_order.maintenance_program_key → maintenance_program (many-to-one)
- work_order.airline_operator_key → airline_operator (many-to-one)
- task_card.work_order_key → work_order (many-to-one)
- task_card.ata_chapter_key → ata_chapter (many-to-one)
- task_signoff.task_card_key → task_card (many-to-one)
- discrepancy.aircraft_key → aircraft (many-to-one)
- discrepancy.work_order_key → work_order (many-to-one)
- discrepancy.ata_chapter_key → ata_chapter (many-to-one)
- part_consumption.work_order_key → work_order (many-to-one)
- part_consumption.task_card_key → task_card (many-to-one)
- part_consumption.part_master_key → part_master (many-to-one)
- part_consumption.component_removed_key → component (many-to-one)
- part_consumption.vendor_key → vendor (many-to-one)
- shop_visit.component_key → component (many-to-one)
- shop_visit.vendor_key → vendor (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.