Manufacturing Execution System (ISA-95) data model
Third-normal-form mirror of an ISA-95 Layer-3 MES. Each ISA-95 object becomes its own entity with a warehouse surrogate key plus the genuine MES business key, child execution/quality/material tables reference their parents via foreign keys, and self-referencing columns capture equipment assembly hierarchy, material BOM links and operation routing predecessors. Operator and inspector identifiers are PII-masked; electronic batch records carry an is_locked flag for 21 CFR Part 11 immutability.
Normalized replica of an ISA-95 Layer-3 Manufacturing Execution System covering Production / Maintenance / Quality / Inventory operations management. Models the ISA-95 Enterprise → Site → Area → WorkCenter → Equipment role-based hierarchy, ERP-issued production orders vs shop-floor production runs (B2MML schedule-vs-response), executed operations with routing predecessors, material consumption / output, quality inspections, equipment state log and downtime events feeding OEE, and 21 CFR Part 11 electronic batch records. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 20
- tables
- 173
- columns
- 31
- relationships
- 4
- 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
ISA-95 hierarchy modeled as nested entities
ISA-95 Part 1 defines a role-based equipment hierarchy of Enterprise → Site → Area → WorkCenter → Equipment; modeling each level as its own entity with a foreign key to its parent keeps the hierarchy normalized and lets reports roll up unambiguously from shop floor to enterprise.
Separate production_order (schedule) and production_run (response)
B2MML defines distinct ProductionSchedule and ProductionResponse / ProductionPerformance messages between ERP (L4) and MES (L3); splitting the order from the run preserves the schedule-vs-actual reconciliation and supports partial runs, rework and split lots without losing the ERP intent.
Downtime events and equipment state log feed OEE
OEE = Availability x Performance x Quality requires per-equipment state intervals (running / idle / setup / down / blocked / starved) and reason-coded downtime events; keeping both at source grain lets analytics compute Availability loss (downtime), Performance loss (slow / idle) and Quality loss (defects) without re-deriving them from aggregates.
Electronic batch record for 21 CFR Part 11 e-records
FDA 21 CFR Part 11 requires electronic records and signatures to be attributable, time-stamped and immutable; a dedicated electronic_batch_record entity with signed_off_by / signed_off_at / is_locked captures the e-signature attestation and lock state without polluting the production_run.
Self-referencing material BOM and equipment assembly hierarchy
Bills of material and equipment sub-assemblies are inherently recursive (material → sub-component material, equipment → sub-equipment); plain self-referencing keys (parent_material_key, parent_equipment_key) keep the recursion in one table without an extra bridge entity, mirroring how ISA-95 Equipment and material masters are represented in source systems.
Tables in the Manufacturing Execution System (ISA-95) 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 (12)
work_center
Grain: one row per ISA-95 work center
ISA-95 WorkCenter — line/cell that groups equipment under an area.
| Column | Type | Keys |
|---|---|---|
| work_center_key | bigint | PK |
| area_key | bigint | |
| work_center_id | varchar | BK |
| work_center_name | varchar | |
| work_center_type | varchar | |
| is_constraint | boolean | |
| capacity_per_hour | decimal |
equipment
Grain: one row per ISA-95 equipment instance
ISA-95 Equipment — physical asset with self-referencing assembly hierarchy.
| Column | Type | Keys |
|---|---|---|
| equipment_key | bigint | PK |
| work_center_key | bigint | |
| equipment_id | varchar | BK |
| equipment_name | varchar | |
| equipment_class | varchar | |
| manufacturer | varchar | |
| model | varchar | |
| parent_equipment_key | bigint | |
| commissioning_date | date | |
| operating_hours | decimal | |
| equipment_status | varchar | |
| is_critical | boolean |
material
Grain: one row per material master record
Material master with self-referencing BOM relationships.
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| material_class_key | bigint | |
| material_id | varchar | BK |
| material_description | varchar | |
| unit_of_measure | varchar | |
| standard_cost | decimal | |
| currency | varchar | |
| parent_material_key | bigint | |
| is_active | boolean | |
| is_batch_managed | boolean |
production_order
Grain: one row per ERP-issued production order
ERP-issued production schedule (B2MML ProductionSchedule).
| Column | Type | Keys |
|---|---|---|
| production_order_key | bigint | PK |
| site_key | bigint | |
| material_key | bigint | |
| work_center_key | bigint | |
| production_order_id | varchar | BK |
| order_type | varchar | |
| planned_quantity | decimal | |
| planned_start | timestamp | |
| planned_end | timestamp | |
| actual_start | timestamp | |
| actual_end | timestamp | |
| order_status | varchar | |
| priority | varchar |
production_run
Grain: one row per shop-floor production run
Shop-floor production response (B2MML ProductionResponse / ProductionPerformance).
| Column | Type | Keys |
|---|---|---|
| production_run_key | bigint | PK |
| production_order_key | bigint | |
| equipment_key | bigint | |
| shift_id_key | bigint | |
| run_start_datetime | timestamp | |
| run_end_datetime | timestamp | |
| produced_good_quantity | decimal | |
| produced_scrap_quantity | decimal | |
| batch_lot_number | varchar | |
| operator_id | varchar | PII |
| run_status | varchar |
operation
Grain: one row per executed operation in a run
Executed operation (ISA-95 OperationsPerformance) with self-referencing routing predecessors.
| Column | Type | Keys |
|---|---|---|
| operation_key | bigint | PK |
| production_run_key | bigint | |
| operation_definition_key | bigint | |
| equipment_key | bigint | |
| operation_id | varchar | BK |
| sequence_number | integer | |
| planned_start | timestamp | |
| actual_start | timestamp | |
| actual_end | timestamp | |
| planned_quantity | decimal | |
| completed_good_quantity | decimal | |
| completed_scrap_quantity | decimal | |
| operation_status | varchar | |
| parent_operation_key | bigint |
downtime_event
Grain: one row per equipment downtime event
Equipment downtime events feeding OEE Availability loss analysis.
| Column | Type | Keys |
|---|---|---|
| downtime_event_key | bigint | PK |
| equipment_key | bigint | |
| production_run_key | bigint | |
| downtime_reason_key | bigint | |
| shift_id_key | bigint | |
| downtime_event_id | varchar | BK |
| event_start_datetime | timestamp | |
| event_end_datetime | timestamp | |
| duration_minutes | decimal | |
| is_planned | boolean | |
| mttr_minutes | decimal | |
| reported_by | varchar | PII |
| root_cause | varchar |
material_consumption
Grain: one row per material consumption transaction
Material consumption on the shop floor (issues, returns, scrap).
| Column | Type | Keys |
|---|---|---|
| material_consumption_key | bigint | PK |
| production_run_key | bigint | |
| material_key | bigint | |
| equipment_key | bigint | |
| consumption_id | varchar | BK |
| consumed_quantity | decimal | |
| unit_of_measure | varchar | |
| lot_or_batch_number | varchar | |
| consumed_at | timestamp | |
| transaction_type | varchar |
material_output
Grain: one row per material output transaction
Material outputs (good / rework / scrap) from a production run.
| Column | Type | Keys |
|---|---|---|
| material_output_key | bigint | PK |
| production_run_key | bigint | |
| material_key | bigint | |
| output_id | varchar | BK |
| output_quantity | decimal | |
| output_quality | varchar | |
| lot_or_batch_number | varchar | |
| produced_at | timestamp |
quality_inspection
Grain: one row per quality inspection record
Quality inspection feeding OEE Quality loss and batch release.
| Column | Type | Keys |
|---|---|---|
| quality_inspection_key | bigint | PK |
| production_run_key | bigint | |
| material_key | bigint | |
| operation_key | bigint | |
| defect_code_key | bigint | |
| inspection_id | varchar | BK |
| inspection_type | varchar | |
| sample_size | integer | |
| defect_count | integer | |
| inspector_id | varchar | PII |
| inspected_at | timestamp | |
| disposition | varchar | |
| is_batch_released | boolean |
equipment_state_log
Grain: one row per equipment state interval
Equipment state history feeding OEE Availability/Performance computation.
| Column | Type | Keys |
|---|---|---|
| equipment_state_log_key | bigint | PK |
| equipment_key | bigint | |
| shift_id_key | bigint | |
| state_log_id | varchar | BK |
| state_start_datetime | timestamp | |
| state_end_datetime | timestamp | |
| equipment_state | varchar | |
| duration_minutes | decimal |
electronic_batch_record
Grain: one row per electronic batch record (21 CFR Part 11)
21 CFR Part 11 electronic batch record with e-signature attestation.
| Column | Type | Keys |
|---|---|---|
| electronic_batch_record_key | bigint | PK |
| production_run_key | bigint | |
| material_key | bigint | |
| ebr_id | varchar | BK |
| batch_number | varchar | |
| manufacture_date | date | |
| expiry_date | date | |
| batch_status | varchar | |
| signed_off_by | varchar | PII |
| signed_off_at | timestamp | |
| is_locked | boolean |
Lookup tables (8)
enterprise
Grain: one row per ISA-95 enterprise
ISA-95 Enterprise — top of the manufacturing role-based equipment hierarchy.
| Column | Type | Keys |
|---|---|---|
| enterprise_key | bigint | PK |
| enterprise_id | varchar | BK |
| enterprise_name | varchar | |
| country | varchar |
site
Grain: one row per ISA-95 site
ISA-95 Site — geographic manufacturing location under an enterprise.
| Column | Type | Keys |
|---|---|---|
| site_key | bigint | PK |
| enterprise_key | bigint | |
| site_id | varchar | BK |
| site_name | varchar | |
| country | varchar | |
| region | varchar |
area
Grain: one row per ISA-95 area within a site
ISA-95 Area — production area inside a site (groups work centers).
| Column | Type | Keys |
|---|---|---|
| area_key | bigint | PK |
| site_key | bigint | |
| area_id | varchar | BK |
| area_name | varchar |
shift
Grain: one row per manufacturing shift definition
Shift calendar dimension used by runs, downtime and state logs.
| Column | Type | Keys |
|---|---|---|
| shift_key | bigint | PK |
| shift_id | varchar | BK |
| shift_name | varchar | |
| start_time | varchar | |
| end_time | varchar | |
| hours | decimal |
material_class
Grain: one row per material classification
Material classification (raw / WIP / finished / by-product / scrap).
| Column | Type | Keys |
|---|---|---|
| material_class_key | bigint | PK |
| material_class_id | varchar | BK |
| description | varchar | |
| is_serialized | boolean | |
| is_lot_controlled | boolean |
operation_definition
Grain: one row per ISA-95 operations definition
ISA-95 OperationsDefinition — recipe/routing step library.
| Column | Type | Keys |
|---|---|---|
| operation_definition_key | bigint | PK |
| operation_definition_id | varchar | BK |
| operation_name | varchar | |
| operation_type | varchar | |
| standard_cycle_time_seconds | decimal | |
| requires_certified_operator | boolean | |
| is_critical | boolean |
downtime_reason
Grain: one row per downtime reason code
Reason code dimension for OEE loss classification.
| Column | Type | Keys |
|---|---|---|
| downtime_reason_key | bigint | PK |
| downtime_reason_id | varchar | BK |
| description | varchar | |
| downtime_category | varchar | |
| oee_loss_class | varchar |
defect_code
Grain: one row per quality defect code
Quality defect taxonomy used by inspections.
| Column | Type | Keys |
|---|---|---|
| defect_code_key | bigint | PK |
| defect_code_id | varchar | BK |
| description | varchar | |
| defect_category | varchar | |
| severity | varchar | |
| is_repairable | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- site.enterprise_key → enterprise (many-to-one)
- area.site_key → site (many-to-one)
- work_center.area_key → area (many-to-one)
- equipment.work_center_key → work_center (many-to-one)
- material.material_class_key → material_class (many-to-one)
- production_order.site_key → site (many-to-one)
- production_order.material_key → material (many-to-one)
- production_order.work_center_key → work_center (many-to-one)
- production_run.production_order_key → production_order (many-to-one)
- production_run.equipment_key → equipment (many-to-one)
- production_run.shift_id_key → shift (many-to-one)
- operation.production_run_key → production_run (many-to-one)
- operation.operation_definition_key → operation_definition (many-to-one)
- operation.equipment_key → equipment (many-to-one)
- downtime_event.equipment_key → equipment (many-to-one)
- downtime_event.production_run_key → production_run (many-to-one)
- downtime_event.downtime_reason_key → downtime_reason (many-to-one)
- downtime_event.shift_id_key → shift (many-to-one)
- material_consumption.production_run_key → production_run (many-to-one)
- material_consumption.material_key → material (many-to-one)
- material_consumption.equipment_key → equipment (many-to-one)
- material_output.production_run_key → production_run (many-to-one)
- material_output.material_key → material (many-to-one)
- quality_inspection.production_run_key → production_run (many-to-one)
- quality_inspection.material_key → material (many-to-one)
- quality_inspection.operation_key → operation (many-to-one)
- quality_inspection.defect_code_key → defect_code (many-to-one)
- equipment_state_log.equipment_key → equipment (many-to-one)
- equipment_state_log.shift_id_key → shift (many-to-one)
- electronic_batch_record.production_run_key → production_run (many-to-one)
- electronic_batch_record.material_key → material (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.