I run a small company that makes products using parts from an inventory and sells them to vendors. I'm trying to achieve the following - I receive an order, I enter the order as an invoice which includes all the information about which products are ordered, the database checks the amount of raw parts I have in stock and tells me whether I have enough to make the products in the order.
Currently I have few tables with junctions between them - a table with a list of parts, a table with the details of products and what parts are needed to make them, a table with a list of products with each product as a one line entry. Then I have invoices and a junction with invoice detail which use the product information.
Currently I am using a one line entry to indicate whether an invoice is yet to be made or completed, I want a system that will tell me which parts I need to complete an invoice and then whether I have enough, which parts of the order can be made ok and which can't. I don't want to deduct anything from the inventory until I indicate that that invoice is to be made.
hopefully that makes sense! Can anyone offer some suggestions about how I could do this?
