Inventory management: how to process sold inventory units in a database?

Question

I sell liquor. so I have an inventory of bottles. I still have an InventoryUnit model that references the product and line_item.

• should each individual bottle be stored as a separate InventoryUnit object in my database?

• What is the best practice for reducing my inventory? if I sell a bottle, will I destroy InventoryUnit? or should I just add a status column that can be “sold” or “in stock”?

• I'm worried about performance, can Postgres handle hundreds of thousands of InventoryUnit objects?

I would really appreciate help on this. Sorry, I'm a supporter, so I really suck in database modeling ...

Answer 1

One. should each individual bottle be stored as a separate InventoryUnit object in my database?

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

Two. What is the best practice for reducing my inventory? if I sell a bottle, will I destroy InventoryUnit? or should I just add a status column that can be “sold” or “patented”?

Use the concepts of locations and movements (movement must be its own essence). OpenERP, for example, uses "virtual locations" like this.

Is the bottle breaking? Move it from your inventory to the “damaged” location.
Is the bottle missing? Move it from inventory to ether
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 about performance, can Postgres handle hundreds of thousands of InventoryUnit objects?

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

Some other things to keep in mind:

• You can sell something, and he returned, and you returned it to inventory.
• You can buy something, but it’s wrong, so you send it to the seller.
• You can sell what you do not have (on consignment or not in inventory)
• You may have something in your inventory that is currently not for sale.

To account for inventory, you also need to count goods for incoming and outgoing shipments for which you are responsible, depending on the status of free FOB on board.

You need to count raw goods (DIY winemaking stuff?) And continue to work if you produce / collect something, as well as order expenses, etc.

Consignment goods are not accounted for.

Answer 2

You must track inventory at the lowest level. In other words, when you were going to select one block from a shelf, what is the most specific information 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 can return the wine instead of vodka. I also can’t say, “Go get a bottle of vodka,” because you can return the Absolute when I want the Cirque. Finally, I can’t say, “Go get a bottle of Ciroc,” because you can return a size of 1L when I need a size of 1.5L.

I could say, “Go get the third bottle on the left side in the front row of the lower 1.5L Ciroc case,” but that would be stupid because all 1.5L Ciroc bottles are the same. (Taste aside;)).

The sweet spot becomes your warehouse (SKU). Fortunately, almost every company in the world has decided this for you already. Just use the UPC number under the barcode as your SKU.

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

InventoryOnHand - id:int - product_id:int - quantity:int Product - id:int - sku:string - name:string 

Then you increased and decreased the amount of InventoryOnHand as everything happened and was lost.

Answer 3

And that is hard to answer. There is no right way, although different models will have consequences depending on use.

If you track a small volume, but enough informational background (for example, airplanes and spare parts), you can define a record for each element; if you are modeling a ton of identical products close to identity (in your opinion), I would focus on the stock status. It all depends on how deeply you want to track the product life cycle.

Ask yourself: "Is it worth keeping track of this instance of the box with crackers, or can I just track how much the pallet has affected my current stock?" Will a gas station create an entrance for every liter of fuel?