Inventory Management: How do I handle sold inventory units in the database?

Question

i sell liquor. so i have an inventory of bottles. so far i have an "InventoryUnit" model which references product and line_item.

1. should every single bottle be stored as an individual InventoryUnit object in my database?

2. what's the best practice to decrease my inventory? if i sell a bottle, do i destroy an InventoryUnit? or should i just add a status-column that can be "sold" or "in-stock"?

3. i'm worried for performance, can Postgres handle hundreds of thousands of InventoryUnit objects?

i'd really appreciate some help on this one. sorry, i'm a frontend-guy so i really suck at database-modelling…

Conceive

What is a "bottle" in your database. How do you tell bottles apart, and what information do you need to track about them? – Episcopate

I would recommend Neil's method. I created a model called "Transactions" and it records the movement from one model to another. Let's say you have 100 bottles and then you sell or lose some. Then you can have a record of all the products you acquire or you sell. The system you are thinking of is very similar to how Quickbooks works. If you understand accounting then this should be pretty easy. – Danedanegeld

Answer 1

One. should every single bottle be stored as an individual InventoryUnit object in my database?

If you can sell them individually, then yes, else track them by the case/box .

Two. what's the best practice to decrease my inventory? if i sell a bottle, do i destroy an InventoryUnit? or should i just add a status-column that can be "sold" or "in-stock"?

Use the concepts of Locations and Movements (a movement should be its own entity). OpenERP for example uses "virtual locations" similar to this.

Bottle smashes? Move it from its inventory location to the "damaged" location
Bottle went missing? Move it from inventory to the "ether" location
Found a random bottle? Move it from "ether" to inventory
Sold a bottle? Move it from inventory to "sold"
Bought a bottle? Move it from purchased to inventory

Three. i'm worried for performance, can Postgres handle hundreds of thousands of InventoryUnit objects?

Postgres can handle hundreds of billions of objects. Normalize properly. Use small data types. Use indexes.

Some other things to keep in mind:

• You could sell something, and it's returned, and you put it back in inventory
• You could buy something, but it ain't right, so you send it back to the seller
• You could sell something that you don't own (on consignment, or not in inventory yet)
• You might have something in inventory that is not currently for sale.

For accounting inventory, you also need to count the goods on inbound and outbound shipments that you're responsible for, based on the free-on-board FOB status.

You need to count raw goods (DIY winemaking stuff?) and works in progress if you make/assemble anything, as well as ordering costs, etc.

Consigned goods are not counted in accounting inventory.

Branks

Answer 2

You should track inventory at the lowest fungible level. In otherwords, when you were going to pick a single unit off a shelf, what's the most specific information that you need to know in order to get the right thing.

In your example, I couldn't just say, "Go get a bottle" or you may bring back wine instead of vodka. I also can't say, "Go get a bottle of vodka" because you might bring back Absolut when I want Ciroc. Finally, I can't say, "go get a bottle of Ciroc" because you might bring back the 1L size when I wanted the 1.5L size.

I could say "Go get the third bottle from the left side in the front row of the bottom case of 1.5L Ciroc," but that would be silly because all 1.5L bottles of Ciroc are the same. (Flavor's aside ;) ).

The sweet spot becomes your stock keeping unit (SKU). Thankfully, almost every company in the world has solved this for you already. Just use the UPC number under the barcode as your SKU.

Based on this, your models would be something like...

InventoryOnHand
- id:int
- product_id:int
- quantity:int

Product
- id:int
- sku:string
- name:string

You would then increase and decrease the InventoryOnHand quantity as things go in and out of stock.

Torrlow

Answer 3

Ah that's a difficult one to answer. There's no right way although different models will have consequences depending on usage.

If you're tracking low volume but highly information intensive stock (say, airplanes and parts) you may want to define an entry for every item; if you're modeling a mass of identical products with close to no identity (as your case seems to be) I would go with focusing on the stock status. It all depends on how deeply you want to track the item's life cycle.

Ask yourself "is it worth to track this instance of a box of crackers, or can I just track how much a pallet has affected my current stock?" Would a gas station create an entry for every liter of fuel?

Gastrostomy