Skip to main content

System Models

This document provides a straightforward explanation of the database models that power the manufacturing system. Each section describes what a model represents in the business and how it works with other parts of the system.

Common Features

Purpose: Defines standard fields and behavior shared by all models in the system.

Key fields:

  • ID: A unique identifier (UUID) automatically assigned to each record
  • Created: Timestamp showing when the record was first created
  • Updated: Timestamp showing when the record was last modified
  • CreatedBy: Reference to the user who created the record
  • UpdatedBy: Reference to the user who last modified the record
  • History: Track of all changes made to the record over time

Customer

Purpose: Stores and manages information about clients who purchase products from the company.

Key fields:

  • Company name: Official business name of the customer (must be unique)
  • Contact name: Primary person to contact at the company
  • Email: Email address for communications
  • Phone: Contact phone number
  • Address/City/State/Zip/Country: Physical location information
  • Notes: Additional important information about the customer

Vendor

Purpose: Stores and manages information about suppliers who provide materials to the company.

Key fields:

  • Company name: Official business name of the vendor (must be unique)
  • Contact person: Primary contact at the vendor company
  • Email: Email address for communications
  • Phone: Contact phone number
  • Address/City/State/Zip/Country: Physical location information
  • Notes: Additional important information about the vendor

Order

Purpose: Manages customer purchase orders and tracks their status through the fulfillment process.

Key fields:

  • PO number: Unique purchase order identifier
  • Customer: Link to the customer who placed this order
  • Issue date: When the order was created
  • Closed date: When the order was completed
  • Due date: When the order is expected to be completed
  • Total amount: Total price for the order
  • Status: Current state (Open, Closed, Cancelled)
  • Products: Link to what products are being ordered (through OrderItem)
  • Notes: Additional important information about the order

Calculations:

  • formatted_total: Displays the total amount formatted as US currency
  • order_item_total: Calculates the order's total by summing all order items
  • update_total_amt: Updates the database with the calculated total

OrderItem

Purpose: Tracks individual product quantities and details within a purchase order.

Key fields:

  • Purchase order: Link to the parent order
  • Product: Link to the specific product being ordered
  • Units: Number of individual units ordered
  • Cases: Number of cases calculated from units and bill of materials
  • Pallet: Number of pallets calculated from cases and bill of materials
  • Override rate: Optional custom price for this specific order

Calculations:

  • orderitem_price: Calculates total price based on override rate or standard product rate
  • calculate_cases_pallet: Automatically calculates packaging requirements based on the BOM

Material

Purpose: Manages raw materials and components used in manufacturing products.

Key fields:

  • Description: Internal name used to identify the material
  • UOM: Unit of Measure (e.g., lbs, gal, each)
  • Scrap percentage: Expected waste during production (default 5%)
  • Vendor: Link to the supplier of this material
  • Obsolete: Flag indicating the material is no longer viable
  • Archived: Flag to remove from active selection lists
  • Notes: Additional important information about the material

Calculations:

  • quantity_in_stage: Calculates how much of this material is in a specific production stage
  • raw: Shows quantity in raw material inventory
  • wip: Shows quantity in work-in-process
  • finished: Shows quantity in finished goods
  • shipped: Shows quantity that has been shipped
  • get_bom_usages: Identifies all BOMs where this material is used

MaterialInventory

Purpose: Provides real-time tracking of material quantities at different stages of production.

Key fields:

  • Material: Link to the material being tracked
  • beg_bal: Starting balance of the inventory
  • rcvd: Total received from vendors
  • batch: Total assigned to production batches
  • prod: Total assigned to purchase orders
  • scrap: Total wasted during production
  • ship: Total shipped to customers
  • adj: Manual adjustments to inventory

Calculations:

  • qoh: Calculates current Quantity On Hand
  • update_rcvd: Updates received quantity from ReceivedMaterial records
  • update_inventory: Recalculates all inventory values
  • update_qoh: Updates the Quantity On Hand calculation

ReceivedMaterial

Purpose: Records the receipt of materials from vendors and updates inventory automatically.

Key fields:

  • Vendor: Link to the company that supplied the material
  • Material: Link to the specific material received
  • Units: Quantity received in the material's UOM
  • PO number: Purchase order reference
  • Carrier: Shipping company that delivered the material
  • BOL: Bill of Lading number for the shipment
  • Received date: When the material arrived
  • Batch lot: Lot number for tracking and quality control
  • Notes: Additional information about the receipt
  • Duplicate: Flag for identifying duplicate entries
  • Rec paperwork: Flag indicating paperwork was received

Calculations:

  • units_with_uom: Displays quantity with the appropriate unit of measure
  • save: Validates data and updates inventory automatically when saved

MaterialTransaction

Purpose: Tracks movement of materials between different stages of the production process.

Key fields:

  • Material: Link to the material being moved
  • From stage: Which inventory stage the material is moving from
  • To stage: Which inventory stage the material is moving to
  • Quantity: Amount of material being moved
  • Notes: Additional information about the transaction

Product

Purpose: Manages finished goods that can be manufactured and sold to customers.

Key fields:

  • Description: Detailed name/description of the product
  • Vendor: Link to the vendor if applicable
  • Rate: Standard price for the product
  • Units per SKU: Number of units in a stock keeping unit
  • Oz: Volume in ounces if applicable
  • Pack: Packaging information
  • Case units: Number of units in a case
  • Line/Line2: Production line assignments
  • UPH: Units Per Hour production rate
  • Category: Link to product category
  • Obsolete: Flag indicating product is no longer produced
  • Archived: Flag to remove from active selection lists

Calculations:

  • bill_of_materials: Returns list of all materials required to produce this product

ProductCategory

Purpose: Organizes products into logical groups for easier management.

Key fields:

  • Name: Category name (must be unique)

BillofMaterial

Purpose: Defines the exact recipe and packaging specifications for manufacturing a product.

Key fields:

  • Item description: Name of the BOM (must be unique)
  • Vendor/Product/Customer: Links to related entities
  • Batched material: Raw material for the product formula
  • Batched material weight: Quantity of raw material needed
  • Bottle material: Container component
  • Bottle units: Number of bottles per unit
  • Cap material: Closure component
  • Cap units: Number of caps per unit
  • Label materials: Primary and secondary labels
  • Label units: Number of each label type per unit
  • Accessory materials: Additional components
  • Accessory quantities: Number of each accessory per unit
  • Units per case: How many units fit in one case
  • Cases per pallet: How many cases fit on one pallet
  • Fill specifications: Target fill weights/volumes
  • Shipping information: Weights and classifications

Calculations:

  • calculate_units_cases: Automatically calculates packaging requirements
  • save: Updates related OrderItems when BOM changes

Line

Purpose: Represents a physical production line in the manufacturing facility.

Key fields:

  • Name: Identifier for the production line (must be unique)

Shift

Purpose: Defines working periods for scheduling production.

Key fields:

  • Name: Identifier for the shift (must be unique)
  • Start time: When the shift begins
  • End time: When the shift ends

Calculations:

  • duration_hours: Calculates the length of the shift in hours

MPSRecord

Purpose: Schedules production of specific order items on specific lines during specific shifts.

Key fields:

  • Order item: Link to the product being produced
  • Production line: Link to where it will be produced
  • Shift: Link to when it will be produced
  • Start date: When production should begin
  • End date: Calculated completion date
  • Status: Current state (Scheduled, Running, Delayed, Completed)

Calculations:

  • percent_complete: Shows what percentage of the planned production is complete
  • total_produced_quantity: Counts total units already produced
  • remaining_quantity: Calculates how many more units need to be produced
  • update_end_date: Estimates when production will be completed
  • daily_production_target: Calculates how many units should be produced each day
  • is_behind_schedule: Determines if production is falling behind plan

ProductionRecord

Purpose: Records actual production quantities completed against the production schedule.

Key fields:

  • MPS record: Link to the scheduled production
  • Quantity produced: Number of units completed
  • Timestamp: When the production was recorded

Calculations:

  • line: Returns the production line being used
  • order_item: Returns the product being produced

Holiday

Purpose: Defines non-working days to exclude from production scheduling calculations.

Key fields:

  • Date: Calendar date of the holiday (must be unique)
  • Description: Name or explanation of the holiday

BatchTicket

Purpose: Manages a specific manufacturing batch with its unique identifiers and specifications.

Key fields:

  • Order item: Link to which order this batch fulfills
  • Customer FM/EPA numbers: Reference numbers for regulatory compliance
  • Gallons/Pounds/Units: Quantity measurements for the batch
  • Water/Air temperatures: Environmental conditions during production
  • Start/End times: When production of the batch began and ended
  • Batch letter: Automatically assigned identifier (A, B, C, etc.)
  • Lot number: Automatically generated tracking number

BatchMaterial

Purpose: Tracks specific materials used in a production batch for traceability.

Key fields:

  • Batch ticket: Link to the parent batch
  • Order item: Link to the order being fulfilled
  • Lot number: Material lot number for traceability
  • Manufacturer: Company that made the material
  • Material description: Name of the material
  • Material number: Identifier code
  • Step: Production process step number
  • Weight planned vs actual: Expected and actual material quantities used

MixingInstruction

Purpose: Provides step-by-step manufacturing procedure for producing a product.

Key fields:

  • Product: Link to which product these instructions apply
  • Step number: Order of the instruction in the sequence
  • Instruction: Detailed explanation of what to do

CleaningChecklist

Purpose: Ensures proper cleaning and sanitization between production runs.

Key fields:

  • Product: Link to which product these procedures apply
  • Step number: Order of the procedure in the sequence
  • Description: What cleaning action to perform
  • Equipment information: Tank, pump, and hose identifiers
  • Lot number: Production batch reference
  • QC approved by: Who verified the cleaning was properly done
  • Notes: Additional information about the cleaning process

QCTest

Purpose: Records quality control test results to ensure products meet specifications.

Key fields:

  • Product: Link to the product being tested
  • Test type: Category of test (Color, Appearance, Odor, etc.)
  • Result: Outcome of the test

Understanding the Flow of Information

  1. Customer orders are entered into the system, creating Order and OrderItem records.

  2. Bill of Materials defines what raw materials are needed for each product.

  3. Production is scheduled using MPSRecords, assigning order items to production lines and shifts.

  4. Materials are received from vendors and tracked in inventory.

  5. BatchTickets are created to manage the production process.

  6. Production Records track actual production against the schedule.

  7. Material Transactions track the movement of materials through the production process.

  8. QC Tests ensure the quality of finished products.

This integrated system helps manage the entire manufacturing process from order entry to product delivery, ensuring efficient use of materials and production capacity.