How to use MySQL to design the table structure of a warehouse management system to handle inventory scrapping?

How to use MySQL to design the table structure of a warehouse management system to handle inventory scrapping?

In a warehouse management system, handling inventory scrapping is an important and necessary task. When certain inventory items expire, are damaged, or are no longer needed, we need to mark them as scrapped in a timely manner and process them accordingly. In order to effectively handle inventory scrapping, we need to design a suitable table structure in MySQL to store relevant information and write corresponding code to implement the function.

First, we need to create an "inventory" table to store inventory information. The table will contain the following fields:

1. id: The unique identifier of the inventory item, which is an auto-increasing integer type.
2. name: The name of the inventory item, which is a string type.
3. quantity: The quantity of the inventory item, which is an integer type.
4. expire_date: The expiration date of the inventory item, which is a date type.
5. status: The status of the inventory item, including normal and scrapped status, is an integer type.

You can use the following code to create the "inventory" table:

CREATE TABLE inventory (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
quantity INT NOT NULL,
expire_date DATE,
status INT
);

Next, we need to create a "scrap_inventory" table to store scrap inventory related information. The table will contain the following fields:

1. id: The unique identifier of the scrapped inventory item, which is a self-increasing integer type.
2. inventory_id: The inventory item id corresponding to the scrapped inventory item, which is an integer type.
3. reason: The reason for scrapping, which is a string type.
4. scrap_date: Scrap date, which is date type.

You can use the following code to create the "scrap_inventory" table:

CREATE TABLE scrap_inventory (
id INT AUTO_INCREMENT PRIMARY KEY,
inventory_id INT NOT NULL,
reason VARCHAR(100) NOT NULL,
scrap_date DATE,
FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE
);

In the above code, we define a foreign The key "inventory_id" is associated with the "id" field of the "inventory" table. In this way, when we delete an inventory item in the "inventory" table, the scrap information related to the inventory item will also be automatically deleted.

Next, we need to write code to implement the inventory scrapping function. First, we need to mark an inventory item as scrapped and insert the corresponding scrap information into the "scrap_inventory" table. The following is a simple PHP code example:

$inventoryId = 1; // Define the id of the inventory item
$reason = "Expired"; // Define the reason for scrapping
$scrapDate = date("Y-m-d"); // Get the current date

// Update the status of the inventory item to scrap
$sql = "UPDATE inventory SET status = 1 WHERE id = $inventoryId ";
// Insert scrap information into scrap_inventory table
$sql .= "INSERT INTO scrap_inventory (inventory_id, reason, scrap_date) VALUES ($inventoryId, '$reason', '$scrapDate')";

//Execute SQL statement
if (mysqli_multi_query($conn, $sql)) {

echo "库存报废成功！";

} else {

echo "库存报废失败：" . mysqli_error($conn);

}

/ / Close the database connection
mysqli_close($conn);
?>

The above code will mark the specified inventory item as scrapped by executing the SQL statement and insert the scrap information into "scrap_inventory" "In the table.

Through the table structure and corresponding code designed above, we can handle the inventory scrapping problem in the warehouse management system in MySQL. By associating scrapped inventory items with original inventory items, we can easily track and manage scrapped inventory information and ensure the operating efficiency and accuracy of the warehouse management system.

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