재고 가격 정보를 관리하기 위해 MySQL에서 창고 관리 시스템의 테이블 구조를 어떻게 설계합니까?

CREATE TABLE warehouse (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    contact VARCHAR(50) NOT NULL
);

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    specification VARCHAR(100),
    unit VARCHAR(20)
);

CREATE TABLE inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    warehouse_id INT,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES product(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouse(id)
);

CREATE TABLE price (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    purchase_price DECIMAL(10, 2),
    sale_price DECIMAL(10, 2),
    FOREIGN KEY (product_id) REFERENCES product(id)
);

INSERT INTO product (name, specification, unit) VALUES ('苹果', '红富士', '个');

INSERT INTO warehouse (name, address, contact) VALUES ('仓库A', '北京市朝阳区', '1234567890');

INSERT INTO inventory (product_id, warehouse_id, quantity) VALUES (1, 1, 100);

INSERT INTO price (product_id, purchase_price, sale_price) VALUES (1, 5.00, 8.00);

SELECT p.id, p.name, p.specification, p.unit, i.quantity, pr.purchase_price, pr.sale_price
FROM product p
LEFT JOIN inventory i ON p.id = i.product_id
LEFT JOIN price pr ON p.id = pr.product_id;