database design for inventory from transactions I have a database design question. 

We are working on an existing inventory system where stock is transferred from head-office to branch office, from branch to distributor and from distributor to dealer. 

The system is used to generate inventory from transaction tables. For example for a distributor, we can find rows for that distributor from transactions( it marks items further transferred to dealers too). 

This was easy as there was simple hierarchy. 

Now there is a change. A stockist is being introduced. Stock can be transferred to distributors from branch or from stockist to distributors, any of these two. 

Previously, we could get distributor inventory from b2d transaction tables( challan, challanrows, rowdetails).  Now, we have two channels of inward for same distributor.   

Should we need change the database design? Do we need some double entry system? Or we should try to get the inventory table by merging data from 2 tables?
Ultimately, the answer will depend on what you can spend the time to do.

In the ideal world, I would recommend a DB design like the following:


stock_holder - identifies an entity that can provide or receive stock. This can actually be your existing table(s) for HO, branches, etc.; a new "bridge" table that allows you to treat each of the current entities as basically equals; or, a new table that actually holds all the data you currently have for these various entities. I assume that there may be different columns needed for different entities.
stock_exchange - at the core, holds: entity sending stock; entity receiving stock; and amount of stock. This would probably be a mutation of your current transaction table. An entry in this table would mean the sender's stock inventory is decremented by the amount, and the receiver's is incremented.


Of course, you have an entire existing working program that is probably not designed like this. And completely changing the DB design of a working system to handle what (at least seems) like a minor change is probably not practical.

So, another alternative is to set up a new table structure that still works with the existing application.

Take the data from the current tables, and figure out how to combine it into a stock_exchange table. Then, based on the stock_exchange table, create views that would replicate the existing tables. This would require that your version of the SQL language to allow INSTEAD OF triggers on views: you would create such triggers on each of your views, to make sure that inserts, updates, and deletes actually happen to stock_exchange. Then, the application keeps using the views for now; as opportunities to update the application arise, you can phase out the views.

This would allow for other oddities - emergency shipments direct from HO to a distributor, or something like that.