I've been beating my head trying to figure this out. I have a widget store. I get orders for many types of widgets. Some of these widgets are made up of still smaller widgets (making sets), which I also sell. What I'm trying to do with limited success is adjust inventory. The widgets that have no sets they are part of are fine. Order comes in, deduct the qty from inventory straight across, no problem. But I can't seem to get my head around how to deduct from the individual smaller widgets when an order for a widget set comes in. To make things worse, there are several types of sets which also contain the same widgets. What I have is:
Products table, full of widgets and sets
Order Details table, same
An Affected products table, containing the various widget sets, widgets which make these up, and the quantity of each individual widgets needed to make the various set.
This is where I'm stuck. I'm not sure how to parse or link the tables which will allow me to change the quantities of the associated widgets. Any help, or a different way of looking at this, would help a lot. My eyes hurt and I've developed just a bit of a headache. Thanks!