Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inventory database design [closed]

This is a question not really about "programming" (is not specific to any language or database), but more of design and architecture. It's also a question of the type "What the best way to do X". I hope does no cause to much "religious" controversy.

In the past I have developed systems that in one way or another, keep some form of inventory of items (not relevant what items). Some using languages/DB's that do not support transactions. In those cases I opted not to save item quantity on hand in a field in the item record. Instead the quantity on hand is calculated totaling inventory received - total of inventory sold. This has resulted in almost no discrepancies in inventory because of software. The tables are properly indexed and the performance is good. There is a archiving process in case the amount of record start to affect performance.

Now, few years ago I started working in this company, and I inherited a system that tracks inventory. But the quantity is saved in a field. When an entry is registered, the quantity received is added to the quantity field for the item. When an item is sold, the quantity is subtracted. This has resulted in discrepancies. In my opinion this is not the right approach, but the previous programmers here swear by it.

I would like to know if there is a consensus on what's the right way is to design such system. Also what resources are available, printed or online, to seek guidance on this.

Thanks

like image 786
nmarmol Avatar asked Nov 13 '08 14:11

nmarmol


People also ask

What is an inventory database?

Inventory database is a centralized repository for all inventory data in an organization. Database for inventory management software allows balancing inventory costs and risks against the desired inventory performance metrics.

How do you design an inventory system?

The following are the key elements to a well organized inventory tracking system. Create well designed location names and clearly label all locations where items may be stored. Use well organized, consistent, and unique descriptions of your items, starting with nouns. Keep item identifiers (part numbers, sku's, etc..)


1 Answers

I have seen both approaches at my current company and would definitely lean towards the first (calculating totals based on stock transactions).

If you are only storing a total quantity in a field somewhere, you have no idea how you arrived at that number. There is no transactional history and you can end up with problems.

The last system I wrote tracks stock by storing each transaction as a record with a positive or negative quantity. I have found it works very well.

like image 131
Neil Aitken Avatar answered Sep 23 '22 21:09

Neil Aitken