warehouse-management-BE/docs/db/WareHouseDB.md

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)

-- 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

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

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

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)

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