Chemical Batch Traceability

erDiagram
  SUPPLIER ||--o{ RAW_LOT : supplies
  RAW_LOT ||--o{ BATCH_INPUT : "used in"
  BATCH ||--|{ BATCH_INPUT : consumes
  PRODUCT ||--o{ BATCH : "made as"
  BATCH ||--|{ PROCESS_STEP : "goes through"
  BATCH ||--o{ QUALITY_TEST : "tested by"
  BATCH ||--o{ SHIPMENT_LINE : "shipped in"
  SHIPMENT ||--|{ SHIPMENT_LINE : contains
  CUSTOMER ||--o{ SHIPMENT : receives
  SUPPLIER {
    int id PK
    string name
  }
  RAW_LOT {
    int id PK
    int supplier_id FK
    string material
    string lot_no
    date received_on
  }
  PRODUCT {
    int id PK
    string name
    string grade
  }
  BATCH {
    int id PK
    int product_id FK
    string batch_no
    date made_on
    string status
  }
  BATCH_INPUT {
    int id PK
    int batch_id FK
    int raw_lot_id FK
    decimal quantity_kg
  }
  PROCESS_STEP {
    int id PK
    int batch_id FK
    string step
    decimal temperature_c
    decimal pressure_bar
    int minutes
  }
  QUALITY_TEST {
    int id PK
    int batch_id FK
    string test
    decimal result
    boolean passed
  }
  CUSTOMER {
    int id PK
    string name
  }
  SHIPMENT {
    int id PK
    int customer_id FK
    date shipped_on
  }
  SHIPMENT_LINE {
    int id PK
    int shipment_id FK
    int batch_id FK
    decimal quantity_kg
  }