超市产品库存数据库设计：复杂库存数量追踪表需求咨询

Hey there! I get where you're coming from—your original simple Product table worked great for a basic shop, but once you need to track expiration dates, varying purchase/sell prices, and granular stock levels, we need to split things into dedicated tables to keep your data accurate and scalable. Let's walk through a solid design for your new supermarket app:

We'll split core entities into three main tables to separate static product info, batch-specific inventory details, and transaction history.

1. Products (Core Product Master Table) #

Store static, unchanging product details here—no stock quantities, since those will be tracked per batch:

CREATE TABLE Products (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    IDCategory INT NOT NULL, -- Links to your category table
    Size VARCHAR(50), -- e.g., "500ml", "1kg"
    Brand VARCHAR(100) -- Optional, add other universal product attributes here
);

Why this works: Basic product info (like name, category) doesn't change between purchase batches, so storing it separately avoids redundant data.

2. InventoryBatches (Batch-Specific Inventory Tracking) #

This is the heart of your new system—each row represents a single purchase batch, so you can track unique details per shipment:

CREATE TABLE InventoryBatches (
    BatchID INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    PurchasePrice DECIMAL(10,2) NOT NULL, -- Cost for this specific batch
    SellPrice DECIMAL(10,2) NOT NULL, -- Price you'll sell this batch for (can be updated)
    ExpirationDate DATE, -- Nullable for products without expiration
    InitialQty INT NOT NULL, -- Total units received in this batch
    CurrentQty INT NOT NULL, -- Remaining stock for this batch
    PurchaseDate DATE NOT NULL, -- When the batch was received
    FOREIGN KEY (ProductID) REFERENCES Products(ID)
);

Key benefits:

• Track different purchase/sell prices for every batch
• Manage expiration dates per shipment (critical for supermarkets)
• Implement stock rotation strategies like FIFO (First-In-First-Out) easily by sorting batches by PurchaseDate or ExpirationDate

3. TransactionRecords (Audit & History Log) #

Log every stock change (incoming or outgoing) to maintain a full audit trail:

CREATE TABLE TransactionRecords (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    BatchID INT NOT NULL,
    TransactionType ENUM('IN', 'OUT') NOT NULL, -- "IN" = restock, "OUT" = sale
    Qty INT NOT NULL,
    TransactionDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Notes VARCHAR(255) -- e.g., "Sale #123", "Supplier X shipment"
    FOREIGN KEY (BatchID) REFERENCES InventoryBatches(BatchID)
);

How to use it:

• When you receive a new batch: Add a row to InventoryBatches, then log an IN transaction with the full batch quantity
• When you sell items: Find the appropriate batch (e.g., oldest expiring), decrement its CurrentQty, then log an OUT transaction with the sold quantity

Quick Business Flow Example #

1. You receive 20 cartons of milk with a $1.50 purchase price, $3.00 sell price, and expiration date 2024-12-31:
   • Add a row to InventoryBatches with these details, InitialQty and CurrentQty set to 20
   • Log an IN transaction in TransactionRecords for 20 units
2. A customer buys 3 cartons:
   • Find the milk batch with the earliest expiration date
   • Update its CurrentQty to 17
   • Log an OUT transaction in TransactionRecords for 3 units

This setup gives you full visibility into every batch's stock, cost, and expiration status—way more flexible than your original single-table approach!

内容的提问来源于stack exchange，提问作者Goofy_Phie