need help conceptualizing manufacturing LOT tracking database design

**angloman** · 05-25-2015, 07:43 PM

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.

**ItsMe** · 05-25-2015, 08:43 PM

I did not look at your DB and I do not know much about your business. But, it may be as easy as having a few tables...

tblLots (Record New LOT#s here)

tblProductions (Record New Productions Here)

tblHasLots (Add a tblProductions PK and the associated tblLots PK) many to many

tblHasProductions (Add a tblProductions and the associated tblProductions PK) many to many

**angloman** · 05-25-2015, 09:10 PM

You might be onto something, but before I try to reinvent my DB, I tried to use the data in my DB to explain where I'm at right now.

Here's a pdf with the table view from my current DB and some notes.

**ItsMe** · 05-26-2015, 05:40 AM

Originally Posted by angloman

...I tried to use the data in my DB to explain where I'm at right now.
...

I took a look at the screenshot. It may be easier for members here if you use this forum to communicate images and samples of your DB. You can use the "Go Advanced" option when replying to take advantage of the upload features.

The screenshot does not say much. However, it does seem to indicate you are doing something that is commonly referred to as "committing to spreadsheet". Where you are now does not seem to be a very good place. You will not be able to query your data where there is a many to many relationship.

In order to manage these, "many to many relationships", you will need to create tables that can store, "duplicate PK values", for each of two tables. It would not be a large exercise to attempt to do this in a new sample database. You could use your business model and my explanation in post #2 to get started.

Perhaps, you can post your Entity Relationship Diagram here if you get stuck. Your ERD does not have to be complex and include symbols that represent one to one or one to many relationships. A simple line should suffice.
https://www.youtube.com/watch?v=-fQ-bRllhXc

**angloman** · 05-26-2015, 09:09 AM

Originally Posted by ItsMe

In order to manage these, "many to many relationships", you will need to create tables that can store, "duplicate PK values", for each of two tables. It would not be a large exercise to attempt to do this in a new sample database. You could use your business model and my explanation in post #2 to get started.

Perhaps, you can post your Entity Relationship Diagram here if you get stuck. Your ERD does not have to be complex and include symbols that represent one to one or one to many relationships. A simple line should suffice.
https://www.youtube.com/watch?v=-fQ-bRllhXc

Thanks for the tips about uploading attachments. I was trying to reduce my footprint on this site by hosting the attachments myself.

I think I'm a little bit out of my league here. I tried building an ERD and ran into the exact same confusion I have with my current database. I think that you are right about my tendency to spreadsheet. I was looking at how some BOM systems work, and I guess I was trying to avoid actually "assembling" products and keeping inventory. I just wanted a simple system to track lot numbers and quantities used of those lots from raw materials to finished products.

I don't entirely understand how to work with your suggestion. How would this work with my business model of raw materials, sub-products and finished products? Sorry for asking you to spoon feed me but the few Access courses I took in university weren't enough and this is the only place I can turn to for help.

tblLots (Record New LOT#s here)

tblProductions (Record New Productions Here)

tblHasLots (Add a tblProductions PK and the associated tblLots PK) many to many

tblHasProductions (Add a tblProductions and the associated tblProductions PK) many to many

**ItsMe** · 05-26-2015, 10:04 AM

I just wanted a simple system to track lot numbers and quantities used of those lots from raw materials to finished products.

The first step is to identify your Business Rules. I think I understand the need is to track your supply chain for audit purposes. Because of all the different possible recipes, several many to many relationships exist. What I suggested may not manage all of the possible combinations. But, this ERD (there are a couple things not perfect about it) will get you started with a couple many to many relationships I recognized.

As a side note, some people may argue you do not need a Primary Key field in the table that acts as a Lookup Table (Conjunction Table, Junction Table, or whatever you want to name it). I like to include a unique PK in these tables because if you need it later it will be difficult to add.

The idea with this ERD is you have your basic tables to store info regarding entities. Here we have two tables for that, tblLots and tblProductions. Then, when a production has many lots, you will create many records in tblHasLots. Similarly, when a single production has many productions, you create many records in tblHasProductions. In the example of tblHasProductions, the ProductionsPK_Parent value will be duplicated for a few to several records, depending on how many other Productions are contained within. Each of these records mentioned will have a distinct ProductionsPK_Child to represent the Parent's composition.

.