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

CREATE TABLE warehouse (
  warehouse_id INT PRIMARY KEY,
  warehouse_name VARCHAR(255)
);

CREATE TABLE product (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255)
);

CREATE TABLE inventory (
  inventory_id INT PRIMARY KEY,
  warehouse_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id)
);

CREATE TABLE transfer (
  transfer_id INT PRIMARY KEY,
  transfer_time DATETIME,
  transfer_quantity INT,
  source_warehouse_id INT,
  target_warehouse_id INT,
  product_id INT,
  FOREIGN KEY (source_warehouse_id) REFERENCES warehouse(warehouse_id),
  FOREIGN KEY (target_warehouse_id) REFERENCES warehouse(warehouse_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id)
);

SELECT p.product_name, i.quantity
FROM inventory i
JOIN product p ON i.product_id = p.product_id
WHERE i.warehouse_id = <仓库ID>;

SELECT t.transfer_time, t.transfer_quantity, w1.warehouse_name AS source_warehouse, w2.warehouse_name AS target_warehouse
FROM transfer t
JOIN warehouse w1 ON t.source_warehouse_id = w1.warehouse_id
JOIN warehouse w2 ON t.target_warehouse_id = w2.warehouse_id
WHERE t.product_id = <商品ID>;

INSERT INTO transfer (transfer_id, transfer_time, transfer_quantity, source_warehouse_id, target_warehouse_id, product_id)
VALUES (<调拨ID>, <调拨时间>, <调拨数量>, <源仓库ID>, <目标仓库ID>, <商品ID>);