One Number, Many Units: How We Model Inventory for POS Systems That Sell By the Carton and the Piece

A POS system that tracks inventory in a single unit works fine right up until a business buys in cartons and sells in pieces. At that point, a stock count that only understands one "quantity" field starts lying to the owner, and a lying inventory system is worse than no inventory system at all.

The failure mode is subtle at first. A shop owner receives a carton of 24 tins of tomato paste and records "1" in stock, because the system only understands whole units of whatever was typed in. A cashier sells a single tin, and the system either can't process a partial sale, or decrements the carton count by one, silently deleting 23 tins from the books. Multiply that across a few hundred SKUs and the stock report becomes fiction within a week, and the owner quietly goes back to a paper ledger.

The fix is to stop treating stock quantity and transaction unit as the same thing. Store stock in exactly one base unit, the smallest unit the business could ever sell, and model every purchase or selling unit as a conversion factor against that base unit, not as its own independent quantity.

In practice this is three tables: products, units, and a join table linking them with a conversion factor. A carton of Coke maps to 24 pieces. A 5kg bag of rice maps to 5,000 grams if you're tracking loose sales by the gram. Stock quantity lives on the product record, always in base units, full stop.

Every transaction, sale, purchase, return, adjustment, runs through the same conversion step before it touches stock. Sell 2 cartons at a 24-to-1 ratio, and the system deducts 48 base units. Sell 3 loose pieces, and it deducts 3. There is never a second number to keep in sync, because there was never a second number to begin with.

Use decimal types for both the stock quantity and the conversion factor, not floats, floating-point rounding compounds across enough small transactions to eventually put your stock count off by fractions of a unit, and that's the kind of bug that takes months to notice and a full audit to fix.

The most common mistake is storing a separate quantity per unit instead of one base-unit quantity with conversions. It looks simpler on day one, "5 cartons" stored as literally 5, but it means the application has to manually keep every unit's quantity synchronized on every transaction, and any missed sync point produces stock numbers nobody can trust.

This costs a bit more schema upfront: three tables instead of one, a conversion step on every write. For a system handling real inventory and real money, that trade is worth making from day one, retrofitting it later means migrating live transaction history, which is a far worse afternoon than designing it correctly the first time.