9b. ERD — Order Entities

FK references to products, suppliers, locations are defined in 9a.

Sales Orders

erDiagram
    sales_order_statuses {
        int id PK
        string name UK "open | completed | cancelled"
    }

    sales_order_update_types {
        int id PK
        string name UK "cancellation | amendment | return | price_adjustment"
    }

    sales_orders {
        int id PK
        string external_id UK
        int location_id FK
        date order_date
        int sales_order_status_id FK
    }

    sales_order_lines {
        int id PK
        string external_id UK
        int sales_order_id FK
        int product_id FK
        float quantity
        float standard_unit_price "MSRP/RRP — catalog list price"
        float unit_price "actual selling price (may include ERP markdown)"
        float line_discount_pct "transaction-level discount applied at POS"
        float revenue "unit_price * qty * (1 - line_discount_pct)"
        float unit_cost "cost from ST receipt or PO invoice"
        float line_cost "unit_cost * quantity"
        int currency_id FK "cw_global"
        int customer_id FK "nullable"
        int location_pos_terminal_id FK
    }

    sales_order_line_updates {
        int id PK
        int sales_order_line_id FK
        int sales_order_update_type_id FK
        date update_date
        float quantity
        float revenue
        float unit_cost
        float line_discount_pct
        int sales_order_status_id FK
        string reason
    }

    sales_order_statuses ||--o{ sales_orders : ""
    sales_order_statuses ||--o{ sales_order_line_updates : ""
    sales_orders ||--o{ sales_order_lines : ""
    customers o|--o{ sales_order_lines : ""
    sales_order_update_types ||--o{ sales_order_line_updates : ""
    sales_order_lines ||--o{ sales_order_line_updates : "history"

Purchase Orders

erDiagram
    purchase_order_statuses {
        int id PK
        string name UK
    }

    purchase_order_update_types {
        int id PK
        string name UK "confirmation | partial_delivery | cancellation | amendment | price_adjustment"
    }

    purchase_orders {
        int id PK
        string external_id UK
        int supplier_id FK
        int location_id FK
        date order_date
        int purchase_order_status_id FK
    }

    purchase_order_lines {
        int id PK
        string external_id UK
        int purchase_order_id FK
        int product_id FK
        float qty_requested
        float qty_confirmed
        float qty_delivered
        float unit_cost
        date expected_delivery
        float qty_cancelled
        float qty_pending
        int currency_id FK "cw_global"
    }

    purchase_order_line_updates {
        int id PK
        int purchase_order_line_id FK
        int purchase_order_update_type_id FK
        date update_date
        float qty_requested
        float qty_confirmed
        float qty_delivered
        float qty_cancelled
        float qty_pending
        int purchase_order_status_id FK
        string reason
    }

    purchase_order_statuses ||--o{ purchase_orders : ""
    purchase_order_statuses ||--o{ purchase_order_line_updates : ""
    purchase_orders ||--o{ purchase_order_lines : ""
    purchase_order_update_types ||--o{ purchase_order_line_updates : ""
    purchase_order_lines ||--o{ purchase_order_line_updates : "history"

Receiving Orders

erDiagram
    receiving_orders {
        int id PK
        string external_id UK
        int supplier_id FK
        int location_id FK
        date document_date
        int receiving_order_status_id FK
    }

    receiving_order_statuses {
        int id PK
        string name UK "pending | partial | complete | cancelled"
    }

    receiving_order_lines {
        int id PK
        string external_id UK
        int receiving_order_id FK
        int product_id FK
        int purchase_order_line_id FK
        float quantity
        float unit_cost
        int currency_id FK "cw_global"
    }

    purchase_order_lines ||--o{ receiving_order_lines : "PO-RO link"
    receiving_order_statuses ||--o{ receiving_orders : ""
    receiving_orders ||--o{ receiving_order_lines : ""

Store & Transfer Orders

erDiagram
    store_order_statuses {
        int id PK
        string name UK
    }

    store_orders {
        int id PK
        string external_id UK
        int from_location_id FK
        int to_location_id FK
        int purchase_order_id FK "nullable"
        date order_date
        int store_order_status_id FK
    }

    store_order_lines {
        int id PK
        string external_id UK
        int store_order_id FK
        int product_id FK
        float quantity
    }

    transfer_types {
        int id PK
        string name UK
    }

    transfer_orders {
        int id PK
        string external_id UK
        int from_location_id FK
        int to_location_id FK
        date document_date
        int transfer_type_id FK
        int transfer_order_status_id FK
    }

    transfer_order_statuses {
        int id PK
        string name UK "pending | in_transit | received | cancelled"
    }

    transfer_order_lines {
        int id PK
        string external_id UK
        int transfer_order_id FK
        int product_id FK
        float quantity
    }

    store_order_statuses ||--o{ store_orders : ""
    purchase_orders o|--o{ store_orders : "optional"
    store_orders ||--o{ store_order_lines : ""
    transfer_order_statuses ||--o{ transfer_orders : ""
    transfer_types ||--o{ transfer_orders : ""
    transfer_orders ||--o{ transfer_order_lines : ""

Convention — child table naming: In parent-child relationships, the child table name includes the parent table name as prefix (e.g. purchase_order_line_updates for updates to purchase_order_lines). This is relaxed when the combined name becomes unwieldy.