How to Design a Manufacturing Database with PostgreSQL
Build the data model behind a Smart Factory before writing complex SQL.
In the previous article, we introduced Industrial SQL through a fictional manufacturing environment. We saw that production orders, machines, production events, downtime, quality, maintenance, and energy data are connected. In this article, we take the next step: designing the relational database that makes those connections possible.
1. Why database design matters in a Smart Factory
A manufacturing system can generate thousands of records every day. But storing data is not enough. We need a structure that allows us to answer industrial questions reliably.
- Which production line produced a given order?
- Which machines belong to that line?
- Which machine generated a production event?
- How much downtime did the machine experience?
- Which quality inspection belongs to a production order?
- How much energy did a machine consume?
These questions require relationships between tables. That is the fundamental idea behind a relational manufacturing database.
2. Start from the manufacturing process
Before creating tables, start with the physical process.
Production Line → Machine → Production Event
Machines also generate downtime events, maintenance events, and energy measurements.
Production orders form another branch:
Product → Production Order → Production Event / Quality Inspection
This process-oriented view helps us identify the entities that should become database tables.
3. The nine core entities
production_linesmachinesproductsproduction_ordersproduction_eventsdowntime_eventsmaintenance_eventsquality_inspectionsenergy_consumption
Each table represents a specific business entity or event rather than mixing everything into one giant table.
4. Production lines and machines
A production line contains industrial machines. This creates a one-to-many relationship:
One production line → many machines
production_lines
-----------------
line_id PRIMARY KEY
line_code
line_name
location
is_active
machines
--------
machine_id PRIMARY KEY
line_id FOREIGN KEY
machine_code
machine_name
machine_type
install_date
is_activeThe line_id in machines points back to production_lines. We therefore do not need to repeat the complete production-line information for every machine.
5. Products and production orders
products
--------
product_id PRIMARY KEY
product_code
product_name
standard_cycle_time_sec
is_active
production_orders
-----------------
order_id PRIMARY KEY
line_id FOREIGN KEY
product_id FOREIGN KEY
order_date
planned_quantity
actual_quantity
rejected_quantity
statusA production order references both a production line and a product. This lets us analyze planned quantity, actual quantity, rejected quantity, product, and line.
6. Production events
Manufacturing systems are event-driven. Machines continuously generate observations.
production_events
-----------------
event_id
order_id
machine_id
event_timestamp
quantity_produced
quantity_rejected
cycle_time_secondsAn event connects several dimensions: when did it happen, which machine, which production order, and what was produced?
7. Downtime and maintenance
Machine availability is a critical manufacturing dimension.
downtime_events
---------------
downtime_id
machine_id
start_time
end_time
downtime_type
reasonMaintenance is modeled as a separate event table:
maintenance_events
------------------
maintenance_id
machine_id
maintenance_type
start_time
end_time
description
parts_cost
labor_cost
total_costA machine can therefore experience many downtime events and many maintenance interventions during its lifetime.
8. Quality inspections
quality_inspections
-------------------
inspection_id
order_id
machine_id
inspection_date
inspected_quantity
passed_quantity
failed_quantity
defect_categoryQuality is connected to production rather than stored as unrelated information. We can investigate reject rates by machine, product, production order, or line.
9. Energy consumption
energy_consumption
------------------
energy_id
machine_id
measurement_time
energy_kwh
avg_power_kwThis structure allows analysis of energy consumption over time and by machine or production activity.
10. The complete relational model
.jpg "How to Design a Manufacturing Database with PostgreSQL")
Production Lines
│
└── Machines
├── Production Events
├── Downtime Events
├── Maintenance Events
└── Energy Consumption
Products
│
└── Production Orders
├── Production Events
└── Quality InspectionsThe key idea is simple: the manufacturing domains are connected instead of being treated as independent datasets.
11. Primary keys and foreign keys
A primary key uniquely identifies a row:
machine_id PRIMARY KEYA foreign key creates a relationship with another table:
line_id REFERENCES production_lines(line_id)Together, primary keys and foreign keys allow PostgreSQL to represent the structure of the manufacturing system.
12. Why not put everything into one table?
A single table containing machine, production, downtime, maintenance, quality, and energy data would quickly create duplicated data, difficult updates, sparse columns, and unreliable analytics.
A relational model separates entities and connects them through keys. The result is easier to maintain and much more powerful for SQL analysis.
13. Your database design challenge
- What is the primary key of each table?
- Which tables reference
machines? - Which tables reference
production_orders? - How would you calculate total production by machine?
- How would you connect downtime with production performance?
- How would you connect quality results with a production order?
If you can explain these relationships, you are ready for more advanced Industrial SQL.
14. Explore the Smart Factory SQL project
The free learning repository contains the project structure, schema resources, sample data, starter queries, and investigation challenges.
→ Explore the Smart Factory SQL repository on GitHub
💰 Get the Complete Smart Factory Dataset
PostgreSQL 17 database dump, complete CSV datasets, generator SQL, data dictionary, database schema, starter queries and industrial investigation challenges.
Smart Factory Manufacturing Dataset v0.1 — $9
What's next?
Now that the relational model is understood, we can start using SQL to investigate production performance.
In the next article, we will analyze planned versus actual production and build our first manufacturing KPI queries.
The goal: use SQL to turn manufacturing data into operational insight.
No comments:
Post a Comment