Stock Management & Warehouse Operations
erDiagram
CATEGORIES ||--|{ SUBCATEGORIES : "has"
CATEGORIES ||--o{ ITEMS : "categorizes"
SUBCATEGORIES ||--o{ ITEMS : "classifies"
ITEM_GROUPS ||--o{ ITEMS : "groups"
UNITS ||--o{ UNIT_CONVERSIONS : "from/to"
UNITS ||--o{ ITEM_UNITS : "used in"
ITEMS ||--|{ ITEM_VARIANTS : "has"
ITEMS ||--|{ ITEM_UNITS : "measured in"
VARIANT_ATTRIBUTES ||--|{ VARIANT_ATTRIBUTE_VALUES : "has"
VARIANT_ATTRIBUTE_VALUES ||--|{ ITEM_VARIANT_ATTRIBUTE_MAP : "maps to"
ITEM_VARIANTS ||--|{ ITEM_VARIANT_ATTRIBUTE_MAP : "has"
STOCK_OPENINGS ||--|{ STOCK_OPENING_ITEMS : "details"
ITEM_VARIANTS ||--o{ STOCK_OPENING_ITEMS : "included in"
ITEM_VARIANTS ||--o{ STOCK_TRANSACTIONS : "moved in"
WAREHOUSE_STOCK_ITEMS ||--|{ WAREHOUSE_STOCK_ITEM_SERIALS : "individual serials"
ITEM_VARIANTS ||--o{ WAREHOUSE_STOCK_ITEMS : "current stock"
STOCK_ADJUSTMENTS ||--|{ STOCK_ADJUSTMENT_ITEMS : "details"
ITEM_VARIANTS ||--o{ STOCK_ADJUSTMENT_ITEMS : "adjusted"
STOCK_REQUESTS ||--|{ STOCK_REQUEST_ITEMS : "details"
ITEM_VARIANTS ||--o{ STOCK_REQUEST_ITEMS : "requested"
TRANSFERS ||--|{ TRANSFER_ITEMS : "details"
ITEM_VARIANTS ||--o{ TRANSFER_ITEMS : "transferred"
RECEIVING_VOUCHERS ||--|{ RECEIVING_VOUCHER_ITEMS : "details"
ITEM_VARIANTS ||--o{ RECEIVING_VOUCHER_ITEMS : "received"
DELIVERY_VOUCHERS ||--|{ DELIVERY_VOUCHER_ITEMS : "details"
ITEM_VARIANTS ||--o{ DELIVERY_VOUCHER_ITEMS : "delivered"
CATEGORIES {
bigint id PK "e.g., 1"
string category_name "e.g., Electronics"
text description "e.g., All electronic items and accessories"
boolean is_active "e.g., true"
}
SUBCATEGORIES {
bigint id PK "e.g., 5"
string subcategory_name "e.g., Mobile Phones"
bigint category_id FK "e.g., 1 (Electronics)"
boolean is_active "e.g., true"
}
UNITS {
bigint id PK "e.g., 1"
string unit_code "e.g., PCS"
string unit_name "e.g., Pieces"
string unit_type "e.g., Count"
boolean is_base "e.g., true"
boolean is_active "e.g., true"
}
UNIT_CONVERSIONS {
bigint id PK "e.g., 1"
bigint from_unit_id FK "e.g., 2 (DOZ)"
bigint to_unit_id FK "e.g., 1 (PCS)"
enum operation_type "e.g., MULTIPLY"
decimal factor "e.g., 12.0"
decimal offset "e.g., 0.0"
text formula "e.g., null"
boolean is_bidirectional "e.g., true"
}
ITEM_GROUPS {
bigint id PK "e.g., 1"
string group_name "e.g., Taxable Items"
string group_type "e.g., Tax"
boolean is_active "e.g., true"
}
ITEMS {
bigint id PK "e.g., 101"
string item_code "e.g., IPHONE15"
string item_name "e.g., iPhone 15 Pro Max"
enum item_type "e.g., PRODUCT"
enum serial_type "e.g., SERIAL"
bigint category_id FK "e.g., 1 (Electronics)"
bigint subcategory_id FK "e.g., 5 (Mobile Phones)"
bigint group_id FK "e.g., 1 (Taxable Items)"
boolean is_active "e.g., true"
}
ITEM_UNITS {
bigint id PK "e.g., 501"
bigint item_id FK "e.g., 101 (iPhone)"
bigint unit_id FK "e.g., 1 (PCS)"
boolean is_base_unit "e.g., true"
decimal conversion_factor "e.g., 1.0"
boolean is_active "e.g., true"
}
VARIANT_ATTRIBUTES {
bigint id PK "e.g., 1"
string attribute_name "e.g., Color"
}
VARIANT_ATTRIBUTE_VALUES {
bigint id PK "e.g., 10"
bigint attribute_id FK "e.g., 1 (Color)"
string value_name "e.g., Blue"
}
ITEM_VARIANTS {
bigint id PK "e.g., 1001"
bigint item_id FK "e.g., 101 (iPhone)"
string variant_sku "e.g., IP15-BL-256"
string variant_name "e.g., iPhone 15 Pro Max Blue 256GB"
boolean is_active "e.g., true"
}
ITEM_VARIANT_ATTRIBUTE_MAP {
bigint id PK "e.g., 5001"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
bigint attribute_value_id FK "e.g., 10 (Blue)"
}
STOCK_OPENINGS {
bigint id PK "e.g., 1001"
bigint warehouse_id FK "e.g., 1 (Main Warehouse)"
date opening_date "e.g., 2023-01-01"
bigint created_by FK "e.g., 5 (Admin)"
text remarks "e.g., Initial stock for new year"
}
STOCK_OPENING_ITEMS {
bigint id PK "e.g., 5001"
bigint stock_opening_id FK "e.g., 1001"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
decimal entered_qty "e.g., 10.0"
bigint entered_unit_id FK "e.g., 1 (PCS)"
decimal base_qty "e.g., 10.0"
bigint base_unit_id FK "e.g., 1 (PCS)"
decimal unit_cost "e.g., 999.99"
json item_serial_lots "e.g., [{serial: 'SN001'}, {serial: 'SN002'}]"
string lot_number "e.g., LOT2023-001"
date manufacture_date "e.g., 2023-01-15"
date expiration_date "e.g., 2025-01-15"
}
STOCK_TRANSACTIONS {
bigint id PK "e.g., 10001"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
bigint warehouse_id FK "e.g., 1 (Main Warehouse)"
enum transaction_type "e.g., IN"
decimal quantity "e.g., 5.0"
bigint base_unit_id FK "e.g., 1 (PCS)"
decimal unit_cost "e.g., 999.99"
decimal total_cost "e.g., 4999.95"
string item_serial_numbers "e.g., SN001,SN002,SN003,SN004,SN005"
json item_serial_lots "e.g., [{lot_number: 'LOT2023-001', serial_numbers: ['SN001','SN002']}]"
string reference_type "e.g., receiving_voucher"
bigint reference_id "e.g., 1001"
bigint performed_by FK "e.g., 5 (Admin)"
datetime transaction_date "e.g., 2023-06-15 10:30:00"
text remarks "e.g., New stock from supplier"
}
WAREHOUSE_STOCK_ITEMS {
bigint id PK "e.g., 2001"
bigint warehouse_id FK "e.g., 1 (Main Warehouse)"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
integer quantity "e.g., 15"
date expiration_date "e.g., 2025-01-15"
date manufacture_date "e.g., 2023-01-15"
string lot_number "e.g., LOT2023-001"
enum status "e.g., Available"
}
WAREHOUSE_STOCK_ITEM_SERIALS {
bigint id PK "e.g., 30001"
bigint warehouse_stock_items_id FK "e.g., 2001"
string item_serial_number "e.g., SN001"
string bundle_number "e.g., BUNDLE-001"
enum status "e.g., Available"
}
STOCK_ADJUSTMENTS {
bigint id PK "e.g., 101"
bigint warehouse_id FK "e.g., 1 (Main Warehouse)"
string adjustment_number "e.g., ADJ-2023-001"
date adjustment_date "e.g., 2023-06-20"
enum adjustment_type "e.g., LOSS"
text reason "e.g., Damaged items found during audit"
bigint created_by FK "e.g., 5 (Admin)"
enum status "e.g., approved"
}
STOCK_ADJUSTMENT_ITEMS {
bigint id PK "e.g., 1001"
bigint stock_adjustment_id FK "e.g., 101"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
decimal system_qty "e.g., 15.0"
decimal adjusted_qty "e.g., 13.0"
decimal difference_qty "e.g., -2.0"
decimal entered_qty "e.g., -2.0"
bigint entered_unit_id FK "e.g., 1 (PCS)"
decimal base_qty "e.g., -2.0"
bigint base_unit_id FK "e.g., 1 (PCS)"
decimal unit_cost "e.g., 999.99"
decimal line_value "e.g., -1999.98"
json item_serial_lots "e.g., [{serial: 'SN005'}, {serial: 'SN006'}]"
string lot_number "e.g., LOT2023-001"
date manufacture_date "e.g., 2023-01-15"
date expiration_date "e.g., 2025-01-15"
}
STOCK_REQUESTS {
bigint id PK "e.g., 1001"
string request_number "e.g., SR-2023-001"
datetime request_date "e.g., 2023-06-15 09:00:00"
bigint requested_by_user_id FK "e.g., 10 (John Smith)"
bigint request_branch_id FK "e.g., 2 (Branch Office)"
string status "e.g., Approved"
text notes "e.g., Request for new display items"
bigint created_by FK "e.g., 10 (John Smith)"
}
STOCK_REQUEST_ITEMS {
bigint id PK "e.g., 5001"
bigint stock_request_id FK "e.g., 1001"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
decimal quantity_requested "e.g., 5.0"
decimal quantity_approved "e.g., 5.0"
decimal quantity_issued "e.g., 5.0"
}
TRANSFERS {
bigint id PK "e.g., 201"
string transfer_number "e.g., TRF-2023-001"
bigint from_warehouse_id FK "e.g., 1 (Main Warehouse)"
bigint to_warehouse_id FK "e.g., 2 (Branch Warehouse)"
datetime issue_date "e.g., 2023-06-16 10:00:00"
datetime expected_receipt_date "e.g., 2023-06-17 17:00:00"
datetime actual_receipt_date "e.g., 2023-06-17 15:30:00"
bigint created_by FK "e.g., 5 (Admin)"
bigint received_by FK "e.g., 12 (Jane Doe)"
enum status "e.g., Completed"
text remarks "e.g., Transfer for new store opening"
}
TRANSFER_ITEMS {
bigint id PK "e.g., 1001"
bigint transfer_id FK "e.g., 201"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
decimal entered_qty "e.g., 5.0"
bigint entered_unit_id FK "e.g., 1 (PCS)"
decimal base_qty "e.g., 5.0"
bigint base_unit_id FK "e.g., 1 (PCS)"
json item_serial_lots "e.g., [{lot_number: 'LOT2023-001', serial_numbers: ['SN001','SN002']}]"
}
RECEIVING_VOUCHERS {
bigint id PK "e.g., 301"
string voucher_number "e.g., RV-2023-001"
bigint warehouse_id FK "e.g., 1 (Main Warehouse)"
bigint created_by FK "e.g., 5 (Admin)"
datetime voucher_date "e.g., 2023-06-15 10:30:00"
enum reference_type "e.g., purchase_order"
bigint reference_id "e.g., 101"
bigint supplier_id FK "e.g., 10 (Tech Supplier)"
decimal subtotal "e.g., 4500.00"
decimal tax_amount "e.g., 450.00"
decimal discount_amount "e.g., 100.00"
decimal downpayment_amount "e.g., 1000.00"
decimal total_amount "e.g., 3850.00"
enum status "e.g., Completed"
text remarks "e.g., Received items from PO-2023-001"
}
RECEIVING_VOUCHER_ITEMS {
bigint id PK "e.g., 2001"
bigint receiving_voucher_id FK "e.g., 301"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
decimal entered_qty "e.g., 5.0"
bigint entered_unit_id FK "e.g., 1 (PCS)"
decimal base_qty "e.g., 5.0"
bigint base_unit_id FK "e.g., 1 (PCS)"
decimal unit_cost "e.g., 900.00"
json item_serial_lots "e.g., [{lot_number: 'LOT2023-001', serial_numbers: ['SN001','SN002']}]"
enum item_condition "e.g., New"
}
DELIVERY_VOUCHERS {
bigint id PK "e.g., 401"
string voucher_number "e.g., DV-2023-001"
bigint warehouse_id FK "e.g., 1 (Main Warehouse)"
bigint created_by FK "e.g., 5 (Admin)"
datetime voucher_date "e.g., 2023-06-18 14:30:00"
enum reference_type "e.g., sales_order"
bigint reference_id "e.g., 501"
bigint customer_id "e.g., 20 (Retail Store)"
decimal subtotal "e.g., 1800.00"
decimal tax_amount "e.g., 180.00"
decimal discount_amount "e.g., 50.00"
decimal total_amount "e.g., 1930.00"
enum status "e.g., Completed"
text remarks "e.g., Items delivered to customer"
}
DELIVERY_VOUCHER_ITEMS {
bigint id PK "e.g., 3001"
bigint delivery_voucher_id FK "e.g., 401"
bigint item_variant_id FK "e.g., 1001 (iPhone Blue)"
decimal entered_qty "e.g., 2.0"
bigint entered_unit_id FK "e.g., 1 (PCS)"
decimal base_qty "e.g., 2.0"
bigint base_unit_id FK "e.g., 1 (PCS)"
decimal unit_cost "e.g., 900.00"
json item_serial_lots "e.g., [{lot_number: 'LOT2023-001', serial_numbers: ['SN001','SN002']}]"
enum item_condition "e.g., Good"
}
flowchart TD
subgraph "Request & Approval"
A1[User creates STOCK_REQUEST
for items at a warehouse in 'Branch B'] --> A2{Manager Approves?}
A2 -- Yes --> A3[STOCK_REQUEST status: 'Approved']
A2 -- No --> A_REJECT[Status -> 'Rejected'
Process Ends]
end
subgraph "Transfer & Issue from Source Warehouse"
A3 --> B1[System creates a TRANSFERS doc
From: 'Warehouse A', To: 'Warehouse B'
Status: 'Pending Issue']
B1 --> B2{Warehouse A has sufficient stock?}
B2 -- Yes --> B3[Warehouse user creates STOCK_ISSUE doc]
B2 -- No --> B_HOLD[TRANSFER status: 'On Hold'
Awaits stock] --> B_END((End))
B3 --> B4[Create 'TRANSFER_OUT' STOCK_TRANSACTION
Decrements stock at Warehouse A]
B4 --> B5[Update TRANSFERS status to 'In Transit']
end
subgraph "Receipt at Destination Warehouse"
B5 --> C1{Shipment arrives at 'Warehouse B'}
C1 --> C2{Receiving Staff Action}
C2 -- Receives All Items OK --> C3[Creates 'TRANSFER_IN' STOCK_TRANSACTION
Increments stock at Warehouse B]
C2 -- Receives with Discrepancy --> C4[Receives partial qty via 'TRANSFER_IN'
Logs discrepancy notes]
C2 -- Rejects Shipment --> C5[TRANSFER Status -> 'Rejected'
Requires reversal at Warehouse A] --> C_END((End))
end
subgraph "Completion"
C3 --> D1[Update TRANSFERS status to 'Completed']
C4 --> D1
D1 --> D2[Update original STOCK_REQUEST
status to 'Fulfilled'] --> D_COMPLETE(( Process Complete))
end
flowchart TD
subgraph "Stock Adjustment Process"
A1[Auditor/Manager identifies discrepancy
at a specific warehouse] --> A2{Is the item instance-tracked?
e.g., has a serial number}
subgraph "Bulk Item Adjustment"
direction LR
A2 -- No --> A3{Type?}
A3 -- Increase --> A4[Create '+' ADJUSTMENT
STOCK_TRANSACTION] --> A5[Increment quantity
in BRANCH_STOCKS] --> A_END(( Logged))
A3 -- Decrease --> A6[Create '-' ADJUSTMENT
STOCK_TRANSACTION] --> A7[Decrement quantity
in BRANCH_STOCKS] --> A_END
end
subgraph "Serialized Item Adjustment"
direction LR
A2 -- Yes --> A8{Type?}
A8 -- Add Instance --> A9[Create '+' ADJUSTMENT
STOCK_TRANSACTION] --> A10[Add record to
BRANCH_STOCKS_ITEMS] --> A11[Increment quantity
in BRANCH_STOCKS] --> A_END2(( Logged))
A8 -- Remove Instance --> A12[Create '-' ADJUSTMENT
STOCK_TRANSACTION] --> A13[Remove record from
BRANCH_STOCKS_ITEMS] --> A14[Decrement quantity
in BRANCH_STOCKS] --> A_END2
end
end
Master data for legal entities.
Geographical country master list.
Subdivisions within countries (states, provinces).
High-level classification for items (e.g., Electronics).
Detailed classification (e.g., Laptops, Smartphones).
Grouping for reporting or policy (e.g., Taxable Goods).
The master record for each stock-keeping unit (SKU).
Organizational units for users and requests.
Manages requests for items from inventory, including approvals.
Tracks items issued from the warehouse and their transit status.
Confirms the final receipt of issued items by the requester.