案例设计建立仓储订货数据库

数据库需求分析

在建立仓储订货数据库时,需涵盖以下核心业务需求：

1. 供应商管理：记录供应商基本信息及合作状态
2. 商品管理：维护商品基础信息及规格参数
3. 仓库管理：管理多仓库基础信息及负责人
4. 库存监控：实时跟踪各仓库库存量及警戒值
5. 订货流程：记录订货单信息及供应商关联
6. 人员权限：区分不同操作人员的系统权限

数据库逻辑设计

表结构设计

示例数据结构

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    contact VARCHAR(30),
    phone VARCHAR(20),
    address VARCHAR(100),
    status ENUM('active', 'inactive') DEFAULT 'active'
);
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(20),
    spec VARCHAR(50),
    unit VARCHAR(10),
    barcode VARCHAR(20)
);
CREATE TABLE warehouses (
    warehouse_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    address VARCHAR(100),
    manager VARCHAR(30)
);
CREATE TABLE inventory (
    inventory_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    quantity DECIMAL(18,2) NOT NULL,
    alert_threshold DECIMAL(18,2) DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    delivery_date DATE,
    supplier_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(18,2) NOT NULL,
    status ENUM('pending', 'shipped', 'received') DEFAULT 'pending',
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

关键业务逻辑实现

库存预警机制

SELECT p.name AS product_name, w.name AS warehouse_name, i.quantity, i.alert_threshold 
FROM inventory i
JOIN products p ON i.product_id = p.product_id
JOIN warehouses w ON i.warehouse_id = w.warehouse_id
WHERE i.quantity <= i.alert_threshold;

订货统计报表

SELECT 
    s.name AS supplier_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.quantity) AS total_quantity,
    MIN(o.delivery_date) AS first_delivery,
    MAX(o.delivery_date) AS last_delivery
FROM orders o
JOIN suppliers s ON o.supplier_id = s.supplier_id
WHERE o.status = 'received'
GROUP BY s.supplier_id;

问题与解答

Q1：如何实现跨仓库调拨功能？

A1：需新增调拨表transfers,包含以下字段：

• transfer_id（主键）
• from_warehouse_id（外键）
• to_warehouse_id（外键）
• product_id（外键）
• transfer_date
• quantity
• status（如：pending, completed）

调拨逻辑：

1. 扣减源仓库库存
2. 增加目标仓库库存
3. 更新调拨状态为completed

Q2：如何保证订货数据与库存数据的一致性？

A2：可通过以下机制保障：

1. 事务控制：在订货入库时使用事务，确保库存更新与订货状态修改原子性
2. 触发器校验：创建BEFORE INSERT触发器，校验订货数量不超过安全库存上限
3. 定时对账：每日执行对账脚本，比对ERP系统与WMS系统的库存差异
4. 状态机机制：订货单需经过”待收货->部分收货->完成”的状态流转，每个状态对应库存增量

示例触发器代码：

CREATE TRIGGER before_insert_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE current_stock DECIMAL(18,2);
SELECT quantity INTO current_stock 
FROM inventory 
WHERE product_id = NEW.product_id 
AND warehouse_id = (SELECT warehouse_id FROM warehouses WHERE name = '主仓');

IF (NEW.quantity > current_stock) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订货量超过可用库存';
END IF;