Module Relationship Diagram
erDiagram
COMPANIES ||--|{ ITEMS : "owns"
COUNTRIES ||--|{ REGIONS : "contains"
CATEGORIES ||--|{ SUBCATEGORIES : "has"
ITEM_GROUPS ||--|{ ITEMS : "groups"
SUBCATEGORIES ||--|{ ITEMS : "classifies"
UNITS ||--|{ ITEMS : "measured in"
ITEMS ||--o{ STOCK_TRANSACTIONS : "moved in/out"
STOCK_REQUESTS ||--|{ STOCK_REQUEST_ITEMS : "details"
STOCK_REQUESTS }o--|| STOCK_TRANSACTIONS : "fulfilled by"
ITEMS ||--o{ WAREHOUSE_STOCKS : "current stock per warehouse"
ITEMS ||--o{ WAREHOUSE_STOCKS_ITEMS : "current serialed items per warehouse"
ITEMS ||--o{ ITEM_SELLING_PRICES : "selling history"
WAREHOUSES ||--o{ WAREHOUSE_STOCKS : "stores"
WAREHOUSES ||--o{ WAREHOUSE_STOCKS_ITEMS : "stores"
WAREHOUSES ||--o{ STOCK_TRANSACTIONS : "location"
BRANCHES ||--|{ WAREHOUSES : "has"
STOCK_ISSUE ||--|{ STOCK_ISSUE_ITEMS : "details"
STOCK_ISSUE_ITEMS ||--|{ STOCK_ISSUE_ITEMS_SERIALS : "serials"
TRANSFERS }o--|| STOCK_ISSUE : "will be issued via"
STOCK_RECEIVE ||--|{ STOCK_RECEIVE_ITEMS : "details"
STOCK_RECEIVE_ITEMS ||--|{ STOCK_RECEIVE_ITEMS_SERIALS : "serials"
TRANSFERS }o--|| STOCK_RECEIVE : "will be received via"
COMPANIES {
int id PK
string company_name
string company_code
boolean is_active
}
COUNTRIES {
int id PK
string country_name
string iso_code
boolean is_active
}
REGIONS {
int id PK
string region_name
int country_id FK
boolean is_active
}
CATEGORIES {
int id PK
string category_name
string description
boolean is_active
}
SUBCATEGORIES {
int id PK
string subcategory_name
int category_id FK
boolean is_active
}
ITEM_GROUPS {
int id PK
string group_name
string group_type
boolean is_active
}
UNITS {
int id PK
string unit_name
string symbol
}
ITEMS {
int id PK
string item_code
string item_name
int company_id FK
int subcategory_id FK
int group_id FK
int unit_id FK
boolean is_active
}
STOCK_REQUESTS {
int id PK
datetime request_date
int requested_by_user_id FK
int department_id FK
int request_branch_id FK
string status "Pending, Approved, Fulfilled, Cancelled"
text notes
}
STOCK_REQUEST_ITEMS {
int id PK
int stock_request_id FK
int item_id FK
decimal quantity_requested
decimal quantity_approved
decimal quantity_issued
}
TRANSFERS {
int id PK
string transfer_number
int from_warehouse_id FK
int to_warehouse_id FK
datetime transfer_date
int created_by_user_id FK
string status "Pending, In Transit, Completed, Cancelled"
text remarks
}
STOCK_ISSUE {
int id PK
int stock_request_id FK
int issued_by_user_id FK
int transfer_id FK
}
STOCK_ISSUE_ITEMS {
int id PK
int stock_issue_id FK
int item_id FK
decimal quantity_issued
string item_condition "New, Good, Damaged, Under Repair, etc."
}
STOCK_ISSUE_ITEMS_SERIALS {
int id PK
int stock_issue_item_id FK
string item_serial_number
}
STOCK_RECEIVE {
int id PK
int stock_request_id FK
int received_by_user_id FK
int transfer_id FK
}
STOCK_RECEIVE_ITEMS {
int id PK
int stock_receive_id FK
int item_id FK
decimal quantity_received
string item_condition "New, Good, Damaged, Under Repair, etc."
}
STOCK_RECEIVE_ITEMS_SERIALS {
int id PK
int stock_receive_item_id FK
string item_serial_number
}
STOCK_TRANSACTIONS {
int id PK
int item_id FK
int warehouse_id FK
string transaction_type "IN, OUT, TRANSFER_IN, TRANSFER_OUT, ADJUSTMENT, OPENING"
decimal quantity
decimal unit_cost
decimal total_cost
string reference_type "transfer, request, purchase, sale, production, adjustment, etc."
int reference_id
int performed_by_user_id FK
datetime transaction_date
text remarks
}
WAREHOUSES {
int id PK
int branch_id FK
}
WAREHOUSE_STOCKS {
int id PK
int branch_id FK
int warehouse_id FK
int item_id FK
decimal quantity
datetime updated_at
}
WAREHOUSE_STOCKS_ITEMS {
int id PK
int branch_id FK
int warehouse_id FK
int item_id FK
string item_serial_number
string item_condition "New, Good, Damaged, Under Repair, etc."
}
ITEM_SELLING_PRICES {
int id PK
int item_id FK
string price_type "Retail, Wholesale, Distributor, etc."
decimal selling_price
datetime effective_date
}
Module Process Flows
1. Inter-Warehouse Stock Request, Transfer, & Issue Flow
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
2. Stock Adjustment Flow
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
Table Documentation Links
Companies
Master data for legal entities.
Countries
Geographical country master list.
Regions
Subdivisions within countries (states, provinces).
Categories
High-level classification for items (e.g., Electronics).
Subcategories
Detailed classification (e.g., Laptops, Smartphones).
Item Groups
Grouping for reporting or policy (e.g., Taxable Goods).
Items
The master record for each stock-keeping unit (SKU).
Departments
Organizational units for users and requests.
Stock Requests
Manages requests for items from inventory, including approvals.
Stock Issues
Tracks items issued from the warehouse and their transit status.
Stock Receipts
Confirms the final receipt of issued items by the requester.