如何使用MySQL设计仓库管理系统的表结构来处理库存转移？

CREATE TABLE Product (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    specification VARCHAR(255),
    unit VARCHAR(50),
    supplier VARCHAR(100),
    PRIMARY KEY (id)
);

CREATE TABLE Warehouse (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    location VARCHAR(255),
    PRIMARY KEY (id)
);

CREATE TABLE Inventory (
    product_id INT(11) NOT NULL,
    warehouse_id INT(11) NOT NULL,
    quantity INT(11) DEFAULT 0,
    PRIMARY KEY (product_id, warehouse_id),
    FOREIGN KEY (product_id) REFERENCES Product (id),
    FOREIGN KEY (warehouse_id) REFERENCES Warehouse (id)
);

CREATE TABLE InventoryTransfer (
    id INT(11) NOT NULL AUTO_INCREMENT,
    product_id INT(11) NOT NULL,
    source_warehouse_id INT(11) NOT NULL,
    target_warehouse_id INT(11) NOT NULL,
    quantity INT(11) NOT NULL,
    transfer_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (product_id) REFERENCES Product (id),
    FOREIGN KEY (source_warehouse_id) REFERENCES Warehouse (id),
    FOREIGN KEY (target_warehouse_id) REFERENCES Warehouse (id)
);

<?php

// 假设以下参数由用户输入或其他方式获取
$product_id = 1;
$source_warehouse_id = 1;
$target_warehouse_id = 2;
$quantity = 10;

// 进行库存转移操作
// 1. 检查源仓库中的库存是否足够
$query = "SELECT quantity FROM Inventory WHERE product_id = $product_id AND warehouse_id = $source_warehouse_id";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_assoc($result);
$source_quantity = $row['quantity'];
if ($source_quantity < $quantity) {
    echo "源仓库库存不足";
    exit;
}

// 2. 更新源仓库的库存数量
$new_source_quantity = $source_quantity - $quantity;
$query = "UPDATE Inventory SET quantity = $new_source_quantity WHERE product_id = $product_id AND warehouse_id = $source_warehouse_id";
mysqli_query($connection, $query);

// 3. 更新目标仓库的库存数量
$query = "SELECT quantity FROM Inventory WHERE product_id = $product_id AND warehouse_id = $target_warehouse_id";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_assoc($result);
$target_quantity = $row['quantity'];
$new_target_quantity = $target_quantity + $quantity;
$query = "UPDATE Inventory SET quantity = $new_target_quantity WHERE product_id = $product_id AND warehouse_id = $target_warehouse_id";
mysqli_query($connection, $query);

// 4. 插入库存转移记录
$query = "INSERT INTO InventoryTransfer (product_id, source_warehouse_id, target_warehouse_id, quantity)
          VALUES ($product_id, $source_warehouse_id, $target_warehouse_id, $quantity)";
mysqli_query($connection, $query);

echo "库存转移成功";

?>