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…

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.