Hi everyone,
I am having trouble figuring out how to design a database which will allow me to track a raw material lot number through a number of productions until it reaches the final product. I then need to be able to look at a final product and identify the original LOT of the raw materials, and to look at the original raw material LOT to identify all finished products produced with that particular raw material.
We have production software right now that doesn't do this very well. It uses an access database but a closed source front-end and the designer isn't into working on it anymore. I have no trouble making a connection from access to our production software's DB and import data into my own database. I designed a database that works really well to track the first and second level. The way we work right now is that the production number generated by our production software is used as the tracking number. My database will import the production order, and then a user fills in the raw material quantities used and the LOT number. This works well when I go from raw material to recipe, but I can't figure out how to keep tracking that recipe into another recipe or a finished product. What I have created right now is driven by the production order.
Here's an example of a raw material and some of the different permutations:
- Raw almonds LOT #12345
- ALMONDS - Dry roasted almonds with sea salt (recipe component) production #5000
- TRAILMIX1 - Trail mix (recipe component) production #5001
- 06811292021 - Packaged trail mix 300g x 12 (finished product) production #5003
- 06811292022 - Packaged dry roasted almonds with sea salt (finished product) 800g x 12 production # 5002
- 06811292023 - Raw almonds packaged 500g x 8 (finished product) production #5004
So the LOT number on the final product 06811292021 would be 5003. From that, I should be able to work backwards and see that in 5003, I used the recipe TRAILMIX1 from production 5001, which had dry roasted almonds from production 5000 which used almonds from LOT #12345.
Here's a sample of my DB.