Designing Manufacturing Databases

YAML
---
name: designing-manufacturing-databases
description: Designs and optimizes databases for manufacturing operations, focusing on production tracking, inventory management, and operational efficiency. Use when building database architecture for manufacturing systems or optimizing existing manufacturing data infrastructure.
---

# Manufacturing Database Design

SQL
-- Core manufacturing schema template
CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    version VARCHAR(20) DEFAULT '1.0',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE production_orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID REFERENCES products(product_id),
    quantity_planned INTEGER NOT NULL,
    quantity_completed INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    start_date TIMESTAMP,
    target_completion TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE work_stations (
    station_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    line_id UUID,
    capacity_per_hour DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'active'
);

Database Design Process:

Progress:

• Requirements gathering with operations team
• Map production flow and data touchpoints
• Design core entities (products, orders, inventory, quality)
• Add operational tracking tables
• Implement time-series data for metrics
• Add audit trails and compliance fields
• Performance optimization and indexing
• Create operational dashboards and reports

Key Steps:

1. Map Production Flow

   • Identify all manufacturing stages
   • Document data inputs/outputs at each stage
   • Understand timing and volume requirements

2. Core Schema Design

   • Products and BOMs (Bill of Materials)
   • Production orders and work orders
   • Inventory and material tracking
   • Quality control and testing results

3. Operational Tracking

   • Machine downtime and maintenance
   • Labor hours and efficiency metrics
   • Defect tracking and root cause analysis
   • Real-time production status

4. Performance Optimization

   • Partition large tables by date/production line
   • Index frequently queried columns
   • Implement proper archival strategies

Example 1: Production Tracking Query Input: Need real-time production status for all active orders Output:

SQL
SELECT 
    po.order_id,
    p.sku,
    po.quantity_planned,
    po.quantity_completed,
    ROUND((po.quantity_completed::DECIMAL / po.quantity_planned * 100), 2) as completion_pct,
    po.target_completion,
    CASE 
        WHEN po.target_completion < NOW() AND po.status != 'completed' 
        THEN 'overdue'
        ELSE po.status 
    END as status
FROM production_orders po
JOIN products p ON po.product_id = p.product_id
WHERE po.status IN ('in_progress', 'pending')
ORDER BY po.target_completion;

Example 2: Inventory Optimization Schema Input: Track material usage and predict shortages Output:

SQL
CREATE TABLE inventory_movements (
    movement_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    material_id UUID REFERENCES materials(material_id),
    movement_type VARCHAR(20), -- 'in', 'out', 'adjustment'
    quantity DECIMAL(10,3),
    unit_cost DECIMAL(10,2),
    production_order_id UUID,
    timestamp TIMESTAMP DEFAULT NOW(),
    recorded_by UUID
);

-- Predictive shortage view
CREATE VIEW material_shortage_risk AS
SELECT 
    m.material_id,
    m.name,
    COALESCE(current_stock.quantity, 0) as current_quantity,
    avg_daily_usage.daily_avg,
    ROUND(COALESCE(current_stock.quantity, 0) / NULLIF(avg_daily_usage.daily_avg, 0)) as days_remaining
FROM materials m
LEFT JOIN (
    SELECT material_id, SUM(quantity) as quantity
    FROM inventory_movements 
    WHERE movement_type = 'in'
    GROUP BY material_id
) current_stock ON m.material_id = current_stock.material_id
LEFT JOIN (
    SELECT 
        material_id,
        AVG(daily_usage) as daily_avg
    FROM (
        SELECT 
            material_id,
            DATE(timestamp) as usage_date,
            SUM(ABS(quantity)) as daily_usage
        FROM inventory_movements 
        WHERE movement_type = 'out' 
        AND timestamp >= NOW() - INTERVAL '30 days'
        GROUP BY material_id, DATE(timestamp)
    ) daily_totals
    GROUP BY material_id
) avg_daily_usage ON m.material_id = avg_daily_usage.material_id;

Schema Design:

• Use UUIDs for primary keys to handle distributed systems
• Include created_at/updated_at timestamps on all tables
• Implement soft deletes for audit trails
• Use enum types for status fields
• Design for time-zone awareness in global operations

Performance:

• Partition production data by month or quarter
• Index foreign keys and commonly filtered columns
• Use materialized views for complex operational reports
• Implement connection pooling for high-throughput operations

Data Integrity:

• Foreign key constraints for referential integrity
• Check constraints for business rules (quantities > 0)
• Triggers for automatic audit logging
• Regular backup and point-in-time recovery testing

Operational Monitoring:

• Log slow queries and optimize regularly
• Monitor connection usage and deadlocks
• Set up alerts for failed production data inserts
• Track database growth and plan capacity

Over-normalization - Don't split frequently-joined operational data across too many tables. Manufacturing queries often need real-time performance.

Ignoring Time Zones - Manufacturing operations often span multiple locations. Always use timestamptz and standardize on UTC.

Missing Audit Trails - Manufacturing requires compliance tracking. Every critical data change should be logged with who/when/why.

Inadequate Indexing - Production dashboards need sub-second response times. Index all commonly filtered columns (status, date ranges, product IDs).

Poor Archival Strategy - Manufacturing generates massive datasets. Plan data lifecycle from day one with proper archival and purging strategies.

Assuming Linear Production Flow - Real manufacturing has rework, parallel processes, and exceptions. Design schema to handle non-linear workflows.