Inventory System calculating stock from materials used
Hello, I've been researching the subject for some time now and have struggled to find a answer. I hope that the expert database designers on this forum can help!
I'm an American running a non-profit water well drilling organization in South Sudan, Africa. We want to use the iPad to track different aspects of our work instead of using paper and pen. For example, I want the crew to fill out the drilling log on a iPad running FM. My main goal is that I can add inventory/new stock in the Inventory table and then a inventory report will show the total stock minus deductions made during drilling (taken from drilling logs) with a summary of the items left in stock.
The inventory system will not only accept deductions from drilling logs, but also from well repairs, equipment repairs, and materials sold. This way we can in a glance see which materials and spare parts are available and when/how others were used.
I was thinking I needed to have a new recorded created automatically in my inventory table via relationships between drilling logs, repairs, and sales and the items used subtracted from those items added. From reading other threads, it seems that people are saying that this is unnessary and defeats the purpose of relational databases. I've also read that the best way to do this is similar to a bank balance with credits and debits but I would need the debits to be transfered or related over from a another table.
Any help you can provide would be greatly appreciated!