A retail catalog can look simple: one product, one price, and one stock quantity. The model becomes harder when the same item is sold in several packages, stocked across branches, and identified by different variants or barcodes.
I encountered those differences while working on POS systems for retailers with different operating models. A bottled-water seller needed to sell bottles, packs, and cases from the same inventory. A clothing store needed separate stock for each color and size. Another retailer needed stock to remain independent across five branches.
They all sounded like “one product has many things”, but they did not describe the same inventory relationship.
The Bottled-Water Problem
The first concrete request involved bottled water:
- one bottle sold for Rp 5,000;
- one pack contained 6 bottles;
- one case contained 48 bottles;
- each package could have a different selling price;
- every package consumed the same physical inventory.
My initial suggestion was to create three products. That made each package easy to sell, but it also created three unrelated stock quantities. Selling a pack would not reduce the available bottle count.
The more useful question was:
What is the smallest inventory quantity that every sale can be converted into?
For this product, the answer was one bottle. That base quantity became the foundation for the wider model.
Separate Units, Variants, and Locations
I separated three concepts that had different stock behavior:
- A unit says how a package converts to the base quantity.
- A variant identifies a separately stocked sellable item.
- A location says where that stock physically exists.
Product
-> Unit (package and conversion)
-> Variant (separately stocked item)
-> Inventory balance per locationA product without visible variants can use a default variant internally. At checkout, the selected unit and variant must still belong to the same product.
This separation also explained why the simpler alternatives did not fit:
- one product per package duplicated stock;
products.stockcould not identify a variant or location;- one generic option model treated a pack that shares stock like a color-size combination that owns separate stock.
Barcodes can point to the unit or variant they identify. Supplier relationships and payment methods remain outside the inventory balance because they describe purchasing and settlement, not physical stock ownership.
A Small Schema for the Boundaries
The following MySQL tables illustrate the model rather than the complete
catalog. The examples use MySQL 8.4 CHECK constraint
behavior;
an older MySQL version or a different database engine needs its enforcement verified
instead of assumed.
Units and variants
CREATE TABLE product_units (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(50) NOT NULL,
conversion_value BIGINT UNSIGNED NOT NULL,
price DECIMAL(15,2) NOT NULL,
CONSTRAINT fk_product_units_product
FOREIGN KEY (product_id) REFERENCES products(id),
CONSTRAINT chk_product_units_conversion
CHECK (conversion_value > 0),
CONSTRAINT chk_product_units_price
CHECK (price >= 0),
UNIQUE KEY uq_product_units_name (product_id, name)
);
CREATE TABLE product_variants (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT UNSIGNED NOT NULL,
sku VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
CONSTRAINT fk_product_variants_product
FOREIGN KEY (product_id) REFERENCES products(id),
UNIQUE KEY uq_product_variants_name (product_id, name)
);The conversion values normalize package sales:
Bottle -> 1 base unit
Pack -> 6 base units
Case -> 48 base unitsSelling two packs deducts 2 × 6 = 12 bottles. If each variant-unit
combination has a different price, that price belongs in a table keyed by both
IDs rather than in the stock balance.
Balances and movements
CREATE TABLE inventory_balances (
variant_id BIGINT UNSIGNED NOT NULL,
location_id BIGINT UNSIGNED NOT NULL,
quantity BIGINT NOT NULL DEFAULT 0,
min_quantity BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (variant_id, location_id),
CONSTRAINT fk_inventory_balances_variant
FOREIGN KEY (variant_id) REFERENCES product_variants(id),
CONSTRAINT fk_inventory_balances_location
FOREIGN KEY (location_id) REFERENCES locations(id),
CONSTRAINT chk_inventory_balances_quantity
CHECK (quantity >= 0),
CONSTRAINT chk_inventory_balances_min_quantity
CHECK (min_quantity >= 0)
);The composite key answers “which variant, at which location?” A branch can be out of stock while another branch still has the same variant.
I keep a movement ledger beside the balance so a quantity also has a reason:
CREATE TABLE inventory_movements (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
variant_id BIGINT UNSIGNED NOT NULL,
location_id BIGINT UNSIGNED NOT NULL,
movement_type VARCHAR(30) NOT NULL,
quantity_delta BIGINT NOT NULL,
reference_type VARCHAR(30) NOT NULL,
reference_id BIGINT UNSIGNED NOT NULL,
reference_line INT UNSIGNED NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_inventory_movements_variant
FOREIGN KEY (variant_id) REFERENCES product_variants(id),
CONSTRAINT fk_inventory_movements_location
FOREIGN KEY (location_id) REFERENCES locations(id),
CONSTRAINT chk_inventory_movements_type CHECK (
movement_type IN (
'sale',
'receipt',
'transfer_in',
'transfer_out',
'adjustment'
)
),
CONSTRAINT chk_inventory_movements_delta
CHECK (quantity_delta <> 0),
UNIQUE KEY uq_inventory_movement_reference (
reference_type,
reference_id,
reference_line,
movement_type,
variant_id,
location_id
)
);This integer model assumes the base unit is the smallest countable item. Products sold by weight or volume need a documented decimal precision instead.
Deduct Stock Atomically
Two checkouts can read the same balance before either one saves its change. For a simple deduction, I use a conditional update inside the same transaction as the movement record:
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
if ($requiredBaseQuantity < 1) {
throw ValidationException::withMessages([
'quantity' => 'The quantity must be at least one base unit.',
]);
}
DB::transaction(function () use (
$variantId,
$locationId,
$requiredBaseQuantity,
$saleId,
$saleLine
) {
$updated = DB::table('inventory_balances')
->where('variant_id', $variantId)
->where('location_id', $locationId)
->where('quantity', '>=', $requiredBaseQuantity)
->decrement('quantity', $requiredBaseQuantity);
if ($updated !== 1) {
throw ValidationException::withMessages([
'stock' => 'The requested stock is no longer available.',
]);
}
DB::table('inventory_movements')->insert([
'variant_id' => $variantId,
'location_id' => $locationId,
'movement_type' => 'sale',
'quantity_delta' => -$requiredBaseQuantity,
'reference_type' => 'sale',
'reference_id' => $saleId,
'reference_line' => $saleLine,
]);
});Zero affected rows means the balance did not exist or no longer had enough stock. The unique movement reference prevents the same sale line from recording the same movement twice. Checkout still needs a sale-level idempotency key so a retry cannot create another sale with a new ID.
A transfer uses the same boundary: the outbound balance, inbound balance, and both movement records commit together. Updating only one location would change the retailer’s total inventory.
Preserve Historical Meaning
Changing a pack conversion from six to eight must not rewrite an earlier sale. The transaction line keeps the selected unit, conversion value, selling price, and deducted base quantity as they were at checkout.
The same principle applies to operations around the balance:
- a barcode must resolve to one intended unit or variant;
- a manual adjustment records its reason, actor, timestamp, and reference;
- a transfer keeps one business reference across both locations;
- balance and movement records change in the same transaction.
The balance remains useful for fast reads, while the movement history explains how it reached that value.
What I Verify
I test the rules that define the model:
- selling two packs of six reduces base inventory by twelve;
- selling one variant does not change another variant;
- one variant can have different balances at two locations;
- a transfer preserves total quantity across locations;
- a failed transfer leaves both balances unchanged;
- concurrent deductions cannot make the balance negative;
- a retried checkout does not deduct the same sale twice;
- historical lines retain their original conversion and price.
The movement history also supports a reconciliation check:
opening stock
+ receipts
+ transfer in
- sales
- transfer out
+ adjustments
= expected closing stockThe expected value should match the stored balance for each variant and location. A mismatch points to a missing, duplicated, or incorrectly applied movement.
The Trade-off Was Worth It for These Retailers
This design has more tables and joins than products.stock. Checkout must
resolve the product, unit, variant, and location before changing a balance.
Reporting must understand both base quantities and package-level sales.
That complexity only makes sense when the retailer actually has these workflows. A single-location café may not need variants, transfers, or multiple units.
For the retailers I worked with, separating the concepts made each rule easier to explain:
- units answer how much base inventory a package consumes;
- variants answer which sellable item owns the stock;
- locations answer where the stock exists;
- movements answer why the stock changed.
The result was not a model for every possible retail feature. It was a model where new requirements had a clear boundary instead of being added to one generic product-options table.