I want to build a simple system for tracking inventory of parts. For this I have 4 key things:
A Part is just that - a screw, an electronic component, whatever. Something that I need to build a Product I need to keep track of how many of each Part I have at any given time and how many are committed to an order.
A Product is a collection of parts. Product 1 might take 1x Part 1, 5x part 2, and 10x Part 3 to build. I have a set of products that I sell, each of which requires a different combination of Parts.
A Part Order is an order that I place to replenish the stock of some parts. A Part Order might include 100x Part 1 and 50x Part 2. Part Orders add to the total number of the associated Parts.
A Product Order is an order placed by a customer that requires me to build the products in the order, and decrease the total number of the associated Parts that I have in stock.
I am having a hard time structuring this set of databases to interact effectively, and I am hoping for some guidance.
Right now I have 4 databases:
Parts: Each row is a different part number. Columns should include total count for each, which is (the sum of all associated Parts in Part Orders that have been marked as Arrived minus the sum of all associated Parts in Product Orders that have been marked as shipped), the total count incoming (the sum of all associated parts in Parts Orders that have not been marked as Arrived) the total count committed (the sum of all associated parts in Product Orders that have not been marked as shipped)
Products: Each row is a different Product. I need this to define which Parts, and how many of each, make up the total. I could do this by having a Number column for every Part, but this becomes unwieldy fast and doesn't scale well. I could have a relation column that lets me call out all of the Parts that make it up, but I do not see how I can keep track of the count of each part in the list. How would one do this effectively without needing a column for every part number?
Parts Orders: Each row should be a new order, and should include how many of each part were ordered. This needs to be structured in such a way that my Parts database can extract the information and add it all up. I can do this pretty easily if I only order one Part per row, but this is inefficient. How can I structure this to be able to include multiple different Parts per Part Order and still be able to extract the information about totals in the Parts database?
Product Orders: each row should be a new order that should include all of the Products orders. Again, I could have a number column for every Product and a calculated column for every part, but this scales very poorly. I could have a relation column that just lets me list out the Products in the order, but then I don't see how to keep track of counts of each, or how to break that out into Parts so that I can add them up for the Parts table.
How would you structure this? Any suggestions are most appreciated!
