How to design the table structure of the warehouse management system in MySQL to manage inventory price information?

How to design the table structure of the warehouse management system in MySQL to manage inventory price information?

In the warehouse management system, the management of inventory prices is very important for the operation of the enterprise. In order to effectively manage inventory price information, when designing the table structure of the MySQL database, the following aspects need to be considered: warehouse management, product management, inventory management, and price management.

First, we need to create tables related to warehouse management. These tables need to record the basic information of the warehouse, such as warehouse name, address, contact information, etc. The following is the table creation statement for the warehouse table:

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

Next, we need to create tables related to product management. These tables need to record the basic information of the product, such as product name, specifications, units, etc. The following is the table creation statement for the product table:

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

Then, we need to create tables related to inventory management. These tables need to record information about the goods in inventory, including inventory quantity, warehouse to which they belong, etc. The following is the table creation statement for the inventory table:

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

Finally, we need to create tables related to price management. These tables need to record the price information of the goods, including purchase price, sales price, etc. The following is the table creation statement of the price list:

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

In the above table structure design, the product table and warehouse table record the basic information of the product and warehouse respectively, the inventory table records the product inventory information, and the price list records Product price information. Through these tables, we can easily manage inventory price information.

The following is some sample code to show how to insert and query data into the above table:

Insert product data:

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

Insert warehouse data:

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

Insert inventory data:

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

Insert price data:

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

Query products and corresponding inventory and price information:

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;

Through the above table structure design and sample code , we can achieve effective management of inventory price information in the warehouse management system in MySQL.

The above is the detailed content of How to design the table structure of the warehouse management system in MySQL to manage inventory price information?. For more information, please follow other related articles on the PHP Chinese website!