Home >Database >Mysql Tutorial >How to design the table structure of a warehouse management system in MySQL to manage inventory entry and exit?
How to design the table structure of the warehouse management system in MySQL to manage inventory entry and exit?
With the rapid development of e-commerce, warehouse management systems have become an indispensable part of many enterprises. In the warehouse management system, the entry and exit of inventory are two very important links. Therefore, it is crucial to design a suitable table structure to manage the entry and exit of inventory. This article will introduce in detail how to design the table structure of the warehouse management system in MySQL to manage the entry and exit of inventory, and provide corresponding code examples.
The following is the corresponding MySQL code example:
CREATE TABLE goods ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE inbound ( id INT(11) NOT NULL AUTO_INCREMENT, goods_id INT(11) NOT NULL, quantity INT(11) NOT NULL, inbound_time DATETIME NOT NULL, PRIMARY KEY (id), FOREIGN KEY (goods_id) REFERENCES goods(id) ); CREATE TABLE outbound ( id INT(11) NOT NULL AUTO_INCREMENT, goods_id INT(11) NOT NULL, quantity INT(11) NOT NULL, outbound_time DATETIME NOT NULL, PRIMARY KEY (id), FOREIGN KEY (goods_id) REFERENCES goods(id) );
Query inventory
In order to achieve inventory management, we need to frequently query the inventory quantity. To do this, we can use the following SQL query statement:
SELECT g.name, SUM(i.quantity) - SUM(o.quantity) AS stock FROM goods g LEFT JOIN inbound i ON g.id = i.goods_id LEFT JOIN outbound o ON g.id = o.goods_id GROUP BY g.id;
This SQL statement will return the name of each product and the corresponding inventory quantity.
Warehousing operation
When goods are put into the warehouse, we need to insert a record into the warehousing table. The following is an example SQL statement for inserting warehousing records:
INSERT INTO inbound (goods_id, quantity, inbound_time) VALUES (1, 10, NOW());
The above SQL statement will put 10 pieces of goods with goods number 1 into the warehouse, and record the current time as the warehousing time.
Outbound operation
When goods are shipped out of the warehouse, we need to insert a record into the outbound table and update the inventory quantity at the same time. The following is an example SQL statement for inserting outbound records and updating inventory quantity:
INSERT INTO outbound (goods_id, quantity, outbound_time) VALUES (1, 5, NOW()); UPDATE goods SET quantity = quantity - 5 WHERE id = 1;
The above SQL statement will outbound 5 pieces of goods with goods number 1, and record the current time as the outbound time. At the same time, the inventory quantity will be reduced by 5 pieces accordingly.
The above is the detailed content of How to design the table structure of a warehouse management system in MySQL to manage inventory entry and exit?. For more information, please follow other related articles on the PHP Chinese website!