database design for inventory from transactions
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.