# Warehouse Management Database Schema ## ER Diagram Overview ``` Warehouse 1──N Room 1──N Cabinet 1──N Shelf 1──N Container │ M──N Component (qua ComponentItem) │ ComponentType 1──N Component 1──N ComponentCode │ ├── ComponentItem (instance tại từng Container) │ └── ComponentStatusHistory (lịch sử thay đổi tình trạng) InvoiceConfig 1──N InvoiceConfigItem 1──N AlternativeComponent │ Invoice 1──N InvoiceItem │ └── InvoiceStatusHistory (lịch sử trạng thái hóa đơn) ``` --- ## Tables ### 1. warehouses (Kho) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | ------------------ | | id | BIGINT | PK, AUTO_INCREMENT | | | name | VARCHAR(255) | NOT NULL | Tên kho | | description | TEXT | | Mô tả | | address | VARCHAR(500) | | Địa chỉ | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 2. rooms (Phòng) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | ------------------ | | id | BIGINT | PK, AUTO_INCREMENT | | | warehouse_id | BIGINT | FK → warehouses(id) | Thuộc kho nào | | name | VARCHAR(255) | NOT NULL | Tên phòng | | description | TEXT | | Mô tả | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 3. cabinets (Tủ) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | ------------------ | | id | BIGINT | PK, AUTO_INCREMENT | | | room_id | BIGINT | FK → rooms(id) | Thuộc phòng nào | | name | VARCHAR(255) | NOT NULL | Tên tủ | | description | TEXT | | Mô tả | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 4. shelves (Tầng / Kệ) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | -------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | cabinet_id | BIGINT | FK → cabinets(id) | Thuộc tủ nào | | name | VARCHAR(255) | NOT NULL | Tên tầng (VD: Tầng 1)| | level_index | INT | NOT NULL | Thứ tự tầng | | description | TEXT | | Mô tả | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 5. containers (Vật chứa: thùng rỗng, khay, thùng giấy, ...) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | ----------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | shelf_id | BIGINT | FK → shelves(id) | Nằm trên tầng nào | | name | VARCHAR(255) | NOT NULL | Tên vật chứa | | container_type | ENUM('empty_box', 'tray', 'paper_box', 'plastic_box', 'bag', 'other') | NOT NULL | Loại vật chứa | | description | TEXT | | Mô tả | | max_capacity | INT | | Sức chứa tối đa (số linh kiện)| | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 6. component_types (Loại linh kiện) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | ---------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | name | VARCHAR(255) | NOT NULL, UNIQUE | Tên loại (VD: Resistor, IC) | | description | TEXT | | Mô tả | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 7. components (Linh kiện) | Column | Type | Constraints | Description | | ----------------- | ------------ | ------------------------- | ----------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | component_type_id | BIGINT | FK → component_types(id) | Thuộc loại nào | | name | VARCHAR(255) | NOT NULL | Tên linh kiện | | description | TEXT | | Mô tả chi tiết | | unit | VARCHAR(50) | DEFAULT 'cái' | Đơn vị tính (cái, m, kg, ...) | | total_quantity | INT | DEFAULT 0 | Tổng số lượng (tính tự động) | | min_quantity | INT | DEFAULT 0 | Số lượng tối thiểu (cảnh báo) | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | > **Note**: `total_quantity` được tính tổng từ `component_items.quantity` và nên được cache/update qua trigger hoặc application logic. ### 8. component_codes (Mã linh kiện - 1 linh kiện có thể có nhiều mã) | Column | Type | Constraints | Description | | ------------ | ------------ | ----------------------- | -------------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | component_id | BIGINT | FK → components(id) | Thuộc linh kiện nào | | code | VARCHAR(255) | NOT NULL | Mã (VD: ESP32-WROOM-32D) | | code_type | VARCHAR(100) | | Loại mã (VD: SKU, Part Number) | | is_primary | BOOLEAN | DEFAULT FALSE | Mã chính | | created_at | DATETIME | DEFAULT NOW() | | ``` UNIQUE (code, code_type) ``` ### 9. component_items (Linh kiện tại từng vị trí - nằm trong container) | Column | Type | Constraints | Description | | ------------ | ------- | ------------------------------------ | ------------------------------ | | id | BIGINT | PK, AUTO_INCREMENT | | | component_id | BIGINT | FK → components(id) | Linh kiện nào | | container_id | BIGINT | FK → containers(id) | Nằm trong vật chứa nào | | quantity | INT | NOT NULL, DEFAULT 0 | Số lượng tại vị trí này | | status | ENUM('normal', 'damaged', 'long_unused', 'expired', 'pending_inspection') | NOT NULL DEFAULT 'normal' | Tình trạng | | created_at | DATETIME| DEFAULT NOW() | | | updated_at | DATETIME| DEFAULT NOW() | | ``` UNIQUE (component_id, container_id, status) ``` > **Note**: Cùng 1 linh kiện ở cùng 1 container nhưng khác status sẽ là các record khác nhau. Điều này cho phép phân biệt linh kiện hỏng và bình thường trong cùng vị trí. ### 10. component_status_history (Lịch sử thay đổi tình trạng linh kiện) | Column | Type | Constraints | Description | | ---------------- | ---------- | --------------------------------- | ----------------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | component_item_id| BIGINT | FK → component_items(id) | Linh kiện item nào | | old_status | ENUM (như component_items.status) | | Tình trạng cũ | | new_status | ENUM (như component_items.status) | | Tình trạng mới | | changed_quantity | INT | | Số lượng bị thay đổi tình trạng | | note | TEXT | | Ghi chú lý do | | changed_by | VARCHAR(255)| | Người thay đổi | | changed_at | DATETIME | DEFAULT NOW() | | ### 11. invoice_configs (Cấu hình hóa đơn mẫu) | Column | Type | Constraints | Description | | ------------ | ------------ | -------------------- | -------------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | name | VARCHAR(255) | NOT NULL | Tên cấu hình (VD: Kit xuất kho A)| | type | ENUM('import', 'export') | NOT NULL | Loại hóa đơn | | description | TEXT | | Mô tả | | is_active | BOOLEAN | DEFAULT TRUE | Đang sử dụng | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 12. invoice_config_items (Chi tiết cấu hình hóa đơn - linh kiện cần xuất/nhập) | Column | Type | Constraints | Description | | ----------------- | ------------ | ---------------------------------- | ----------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_config_id | BIGINT | FK → invoice_configs(id) | Thuộc cấu hình nào | | component_id | BIGINT | FK → components(id) | Linh kiện cần | | required_quantity | INT | NOT NULL | Số lượng yêu cầu | | allow_alternative | BOOLEAN | DEFAULT FALSE | Cho phép dùng linh kiện thay thế | | priority_order | INT | DEFAULT 0 | Thứ tự ưu tiên | | note | TEXT | | Ghi chú | ``` UNIQUE (invoice_config_id, component_id) ``` ### 13. alternative_components (Linh kiện thay thế - dùng khi thiếu) | Column | Type | Constraints | Description | | ---------------------- | ------- | ---------------------------------------- | ---------------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_config_item_id | BIGINT | FK → invoice_config_items(id) | Item cấu hình nào | | alternative_component_id | BIGINT | FK → components(id) | Linh kiện thay thế | | conversion_ratio | DECIMAL(10,2) | DEFAULT 1.0 | Tỷ lệ quy đổi (VD: 2 cái nhỏ = 1 cái lớn) | | priority | INT | DEFAULT 0 | Thứ tự ưu tiên khi thay thế | | note | TEXT | | Ghi chú | ``` UNIQUE (invoice_config_item_id, alternative_component_id) ``` > **Ví dụ**: Nếu cấu hình yêu cầu "IC WiFi ESP32" nhưng hết, có thể dùng "IC WiFi ESP32-C3" thay thế với `conversion_ratio = 1.0`. ### 14. invoices (Hóa đơn nhập / xuất) | Column | Type | Constraints | Description | | ----------------- | ------------ | ---------------------------- | ---------------------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_code | VARCHAR(100) | NOT NULL, UNIQUE | Mã hóa đơn (tự generate) | | type | ENUM('import', 'export') | NOT NULL | Loại hóa đơn | | status | ENUM('draft', 'pending', 'approved', 'completed', 'cancelled') | NOT NULL DEFAULT 'draft' | Trạng thái | | invoice_config_id | BIGINT | FK → invoice_configs(id), NULL | Cấu hình áp dụng (có thể null) | | total_items | INT | DEFAULT 0 | Tổng số loại linh kiện | | note | TEXT | | Ghi chú | | created_by | VARCHAR(255) | | Người tạo | | approved_by | VARCHAR(255) | | Người duyệt | | completed_at | DATETIME | | Thời gian hoàn thành | | created_at | DATETIME | DEFAULT NOW() | | | updated_at | DATETIME | DEFAULT NOW() | | ### 15. invoice_items (Chi tiết hóa đơn - từng linh kiện) | Column | Type | Constraints | Description | | ----------------- | ------------ | ---------------------------------- | -------------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_id | BIGINT | FK → invoices(id) | Thuộc hóa đơn nào | | component_id | BIGINT | FK → components(id) | Linh kiện | | original_component_id | BIGINT | FK → components(id), NULL | Linh kiện gốc (nếu dùng thay thế)| | required_quantity | INT | NOT NULL | Số lượng yêu cầu | | actual_quantity | INT | DEFAULT 0 | Số lượng thực tế | | is_substituted | BOOLEAN | DEFAULT FALSE | Đã dùng linh kiện thay thế | | is_short | BOOLEAN | DEFAULT FALSE | Có thiếu hay không | | shortage_quantity | INT | DEFAULT 0 | Số lượng thiếu | | note | TEXT | | Ghi chú | ``` UNIQUE (invoice_id, component_id) ``` ### 16. invoice_item_locations (Chi tiết vị trí xuất/nhập cho từng item) | Column | Type | Constraints | Description | | --------------- | ------- | --------------------------------- | ------------------------------------ | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_item_id | BIGINT | FK → invoice_items(id) | Item hóa đơn nào | | container_id | BIGINT | FK → containers(id) | Xuất/nhập từ container nào | | quantity | INT | NOT NULL | Số lượng xuất/nhập tại vị trí này | > **Note**: Bảng này cho phép xuất 1 linh kiện từ nhiều vị trí khác nhau, và ghi lại chính xác vị trí. ### 17. invoice_status_history (Lịch sử trạng thái hóa đơn) | Column | Type | Constraints | Description | | ---------- | ------------ | ---------------------------- | -------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_id | BIGINT | FK → invoices(id) | Hóa đơn nào | | old_status | VARCHAR(50) | | Trạng thái cũ | | new_status | VARCHAR(50) | NOT NULL | Trạng thái mới | | changed_by | VARCHAR(255) | | Người thay đổi | | note | TEXT | | Ghi chú | | changed_at | DATETIME | DEFAULT NOW() | | ### 18. stock_transactions (Lịch sử nhập xuất kho - phục vụ thống kê) | Column | Type | Constraints | Description | | ----------------- | ------------ | ---------------------------------- | ----------------------------- | | id | BIGINT | PK, AUTO_INCREMENT | | | invoice_id | BIGINT | FK → invoices(id) | Hóa đơn liên quan | | component_id | BIGINT | FK → components(id) | Linh kiện | | container_id | BIGINT | FK → containers(id) | Vị trí | | transaction_type | ENUM('import', 'export', 'adjustment', 'transfer') | NOT NULL | Loại giao dịch | | quantity | INT | NOT NULL | Số lượng (âm = xuất, dương = nhập) | | balance_after | INT | | Số dư sau giao dịch | | note | TEXT | | Ghi chú | | created_by | VARCHAR(255) | | Người thực hiện | | created_at | DATETIME | DEFAULT NOW() | | > **Note**: Bảng này là bảng tổng hợp để phục vụ thống kê nhanh, tránh phải tính toán từ invoice_items mỗi lần. --- ## Indexes (Quan trọng) ```sql -- Tìm linh kiện theo vị trí CREATE INDEX idx_component_items_component ON component_items(component_id); CREATE INDEX idx_component_items_container ON component_items(container_id); CREATE INDEX idx_component_items_status ON component_items(status); -- Tìm mã linh kiện CREATE INDEX idx_component_codes_code ON component_codes(code); CREATE INDEX idx_component_codes_component ON component_codes(component_id); -- Thống kê hóa đơn theo thời gian CREATE INDEX idx_invoices_type_status ON invoices(type, status); CREATE INDEX idx_invoices_created_at ON invoices(created_at); CREATE INDEX idx_invoices_type_created ON invoices(type, created_at); -- Thống kê giao dịch kho CREATE INDEX idx_stock_transactions_component_date ON stock_transactions(component_id, created_at); CREATE INDEX idx_stock_transactions_type_date ON stock_transactions(transaction_type, created_at); CREATE INDEX idx_stock_transactions_invoice ON stock_transactions(invoice_id); -- Tìm vị trí container CREATE INDEX idx_containers_shelf ON containers(shelf_id); CREATE INDEX idx_shelves_cabinet ON shelves(cabinet_id); CREATE INDEX idx_cabinets_room ON cabinets(room_id); CREATE INDEX idx_rooms_warehouse ON rooms(warehouse_id); ``` --- ## Ví dụ truy vấn phổ biến ### 1. Tìm vị trí của 1 linh kiện ```sql SELECT c.name AS component_name, ct.name AS type_name, ci.quantity, ci.status, cn.name AS container_name, cn.container_type, s.name AS shelf_name, cb.name AS cabinet_name, r.name AS room_name, w.name AS warehouse_name FROM component_items ci JOIN components c ON ci.component_id = c.id JOIN component_types ct ON c.component_type_id = ct.id JOIN containers cn ON ci.container_id = cn.id JOIN shelves s ON cn.shelf_id = s.id JOIN cabinets cb ON s.cabinet_id = cb.id JOIN rooms r ON cb.room_id = r.id JOIN warehouses w ON r.warehouse_id = w.id WHERE ci.component_id = :componentId AND ci.quantity > 0; ``` ### 2. Kiểm tra thiếu hụt khi xuất hóa đơn ```sql SELECT ici.component_id, c.name AS component_name, ici.required_quantity, COALESCE(SUM(ci.quantity), 0) AS available_quantity, CASE WHEN COALESCE(SUM(ci.quantity), 0) < ici.required_quantity THEN TRUE ELSE FALSE END AS is_short, ici.allow_alternative FROM invoice_config_items ici JOIN components c ON ici.component_id = c.id LEFT JOIN component_items ci ON ci.component_id = ici.component_id AND ci.status = 'normal' WHERE ici.invoice_config_id = :configId GROUP BY ici.component_id, c.name, ici.required_quantity, ici.allow_alternative; ``` ### 3. Thống kê nhập xuất theo thời gian ```sql SELECT st.transaction_type, c.name AS component_name, SUM(ABS(st.quantity)) AS total_quantity, DATE(st.created_at) AS transaction_date FROM stock_transactions st JOIN components c ON st.component_id = c.id WHERE st.created_at BETWEEN :startDate AND :endDate GROUP BY st.transaction_type, c.id, DATE(st.created_at) ORDER BY transaction_date DESC; ``` ### 4. Linh kiện sắp hết (dưới mức tối thiểu) ```sql SELECT c.id, c.name, c.total_quantity, c.min_quantity, ct.name AS type_name FROM components c JOIN component_types ct ON c.component_type_id = ct.id WHERE c.total_quantity <= c.min_quantity AND c.min_quantity > 0; ``` --- ## Flow xử lý hóa đơn xuất ``` 1. Tạo Invoice (status: draft) ↓ 2. Chọn InvoiceConfig hoặc thêm items thủ công ↓ 3. Kiểm tra tồn kho: - Query component_items WHERE status = 'normal' - So sánh available vs required - Nếu thiếu: a. Cảnh báo item thiếu b. Kiểm tra allow_alternative c. Query alternative_components d. Đề xuất linh kiện thay thế ↓ 4. Chấp nhận / Điều chỉnh (status: approved) ↓ 5. Thực hiện xuất kho: - Trừ quantity trong component_items - Tạo stock_transactions - Cập nhật total_quantity trong components - Tạo invoice_item_locations ↓ 6. Hoàn thành (status: completed) ``` --- ## Enum Values Reference | Enum Field | Values | | ---------------- | --------------------------------------------------- | | container_type | `empty_box`, `tray`, `paper_box`, `plastic_box`, `bag`, `other` | | status (item) | `normal`, `damaged`, `long_unused`, `expired`, `pending_inspection` | | invoice type | `import`, `export` | | invoice status | `draft`, `pending`, `approved`, `completed`, `cancelled` | | transaction_type | `import`, `export`, `adjustment`, `transfer` |