I have a supply delivery database I am working on.
A building requests various quantities of various items.
A delivery slip is generated with the list of items and quantities of each item delivered.
Sometimes the quantity of an item delivered is less than what was requested.
I have a Work Orders table, a Deliveries table and a Transactions table.
Transactions stores requests and deliveries.
A work order can have many requests and many deliveries can be made to a work order.
I want to be able to show a quantity due for each item (request) in the work orders.
Work Order::wid = Deliveries::wid
Transactions::wid = Work Order::wid
(there are other self joins to get portals to work)
My problem is, I can't figure out how to structure things so that the quantity due updates properly when a delivery is made.
Can anyone point me in a direction to make this work? I am sure there is some basic concept here that I am totally not seeing