Develop inventory database

Question

This question is not about "programming" (does not apply to any language or database), but also about design and architecture. This is also a question like "What is the best way to make X." I hope this does not cause much "religious" controversy.

In the past, I developed systems that somehow preserve some form of inventory of items (it is not appropriate which items). Some use languages ​​/ databases that do not support transactions. In those cases, I decided not to save the number of items at hand in the field in the position record. Instead, a quantity is calculated for the total amount of inventory — the total number of stocks sold. Because of the software, there were practically no discrepancies in the inventory. Tables are properly indexed and performance is good. There is an archiving process if the number of records begins to affect performance.

Now, a few years ago, I started working for this company, and I inherited a system that tracks inventory. But the quantity is stored in the field. When a record is registered, the received quantity is added to the quantity field for the item. When an item is sold, the amount is deducted. This led to discrepancies. In my opinion, this is the wrong approach, but previous programmers swear here.

I would like to know if there is consensus as to what the correct way is to design such a system. Also, what resources are available, printed, or online to get recommendations on this.

thank

Answer 1

I saw both approaches in my current company and definitely leaned towards the first (estimated amounts based on exchange transactions).

If you only store the total quantity in a field somewhere, you have no idea how you arrived at this number. There is no transaction history and you may run into problems.

In the last system, I wrote tracks, saving each transaction as a record with a positive or negative amount. I found that it works very well.

Answer 2

• Data Model Resource Book, vol. 1: Library of universal data models for all enterprises
• Data Model Resource Book, vol. 2: Library of data models for selected industries
• Data Model Resource Book: Generic Data Modeling Templates

I have Vol 1 and Vol 2, and they have been very useful in the past.

Answer 3

It depends, inventory systems are much more than just item counting. For example, for accounting purposes, you may need to know the inventory value of an inventory based on the FIFO (First-in-First-out) model. This cannot be calculated using the simple "total inventory received - the total inventory sold." But their model can easily calculate this because they change the accounting value as it becomes available. I don’t want to go into details because it’s not a programming problem, but if they swear by them, you may not fully understand all your requirements that they need to adapt.

Answer 4

both are valid, depending on the circumstances. The first is best if the following conditions are met:

• the number of elements for the amount is relatively small
• There are several exceptional cases (exceptions, adjustments, etc.) or not.
• the amount of inventory is not required very often

on the other hand, if you have a large number of items, several exceptional cases and frequent access, it will more effectively support the number of items

also note that if your system has discrepancies, then it has errors that should be tracked and deleted.

I made the system in both directions, and both methods can work very well - if you do not ignore the errors!

Answer 5

Take a look at the ARTS (Association for Retail Technology Standards) data model ( http://nrf-arts.org ). It uses a StockLedger table with a record of each item and inventory changes are tracked in InventoryJournalEntries.

Answer 6

It is important to consider the existing system and the cost and risk of changing it. I work with a database that stores inventory types like yours, but it includes audit cycles and store settings, as well as receipts. This seems to work well, but all the participants are well trained and the warehouse staff are not learning the new procedures so quickly.

In your case, if you are looking for a little more tracking without changing the entire db structure, I would suggest adding a tracking table (sort of like from your transaction solution), and then log the changes in the inventory level. It should not be too difficult to update most of the changes at the inventory level so that they also leave a transaction record. You can also add a periodic task to back up the inventory level to the transaction table every couple of hours or so, so that even if you miss a transaction, you can detect when a change or rollback occurred.

If you want to see how a large application views SugarCRM , they also have an inventory management module, although I don’t know how it stores data.

Answer 7

I would choose the first way where

counts the total amount of funds received - total inventory sold

The right way, IMO.

EDIT: I would also like to take into account any losses / losses from stocks in the system, but I'm sure you have it.

Answer 8

I think this is actually a general question of best practices about what you do (relatively) costly tally every time you need a total amount versus having to do it, tallying every time something changes and then remembers the score in the field and reads this field whenever you need everything.

If I could not use transactions, I would go with a live account every time I needed everything. If transactions are available, it would be safe to perform the operations of updating the stocks and storing the recalculated amount within one transaction, which would ensure the accuracy of the calculation (although I am not sure that this will work with several users entering the database).

But if performance is not really a big problem (and modern databases are good enough at counting strings, which I would rarely even bother with about this), I would stick to counting live events every time.

Answer 9

I see some advantages of having two columns, but I don't follow some of the discrepancies - you seem to imply that having two columns (inside and outside) is less prone to discrepancies than a single column (current). Why is this?

Answer 10

I worked on systems that solve this problem before. I think the ideal solution is a pre-computed column that gives you the best of both worlds. Your general information would be a field somewhere, so there would be no costly searches, but it cannot go out of sync with the rest of your data (the database maintains integrity). I don’t remember which RDMSs support precalculated columns, but if you don’t have transactions, this may not be available.

You could potentially fake pre-computed columns (very efficiently ... I don't see any flaws) with triggers. However, you will probably need transactions. IMHO, while maintaining data integrity when you perform such controlled denormalization, is the only legitimate use of a trigger.

Answer 11

Django-inventory is more about fixed assets, but may give you some ideas.

IE: ItemTemplate (class) -> ItemsOnHand (instance)

ItemsOnHand can be associated with a large number of ItemTemplates; Example. Requires a printer and ink cartridges. It also allows you to set reordering points for each ItemOnHand.

Each ItemOnHand item is associated with an InventoryTransactions, making auditing easy. To avoid calculating actual positions manually from thousands of transactions, checkpoints are used, which are simply balance + date. To calculate an incoming query, find the most recent breakpoint and start adding or subtracting items to find the current position balance. Periodically identify new breakpoints.

Answer 12

Does not have one or two columns, what I had in mind with the "total inventory received - total inventory sold" looks something like this:

Select sum(quantity) as inventory_received from Inventory_entry Select sum(quantity) as inventory_sold from Sales_items 

then

 Qunatity_on_hand = inventory_received - inventory_sold 

Please keep in mind that I simplified this and my initial explanation. I know that the inventory is much more than just tracking the quantity, but in this case there was a problem and what we want to fix. At the moment, the reason for its change is undoubtedly the cost of supporting the problems caused by the current design.

I would also like to mention that although this is not a “coding” issue, it is related to algorithms and design, which IMHO are very important topics.

Thank you all for your answers.

Nelson Marmol

Answer 13

We solve various problems, but our approach to some of them may be of interest to you.

We let the system make the “best guess” and give users regular feedback about any of these guesses that look wrong.

To apply this to inventory, you can have 3 fields:

 inventory_received inventory_sold estimated_on_hand 

Then you can start the process (daily?) Line by line:

 SELECT * FROM Inventory WHERE estimated_on_hand != inventory_received - inventory_sold 

Of course, it depends on the users who are looking at this warning and are doing something about it.

In addition, you may have an inventory reset function in some ways, either by updating inventory_sold / received, or perhaps adding another inventory_adjustment field, which can be positive or negative.

... just some thoughts. Hope this is helpful.