Thank you, once again, for the response - I really do appreciate your help.
My only concern is the possibility of inputting wrong data in tblArticleOperation.
The only reason I wanted to create a tblModelOperation is because then something like:
Code:
tblArticleOperation
AO_ID MA_ID Op_ID AO_Description
1 1 1 My Company's Only Model - Article X - Operation Gluing
2 1 2 My Company's Only Model - Article X - Operation Sewing
3 1 3 My Company's Only Model - Article X - Operation Stitching
4 2 1 My Company's Only Model - Article X - Operation Gluing
5 2 2 My Company's Only Model - Article X - Operation Sewing
6 3 4 My Company's Only Model - Article X - Operation Stitching
cannot happen, because the ModelOperation would have specified that only operations 1, 2 or 3 exists there. How can I overcome this situation? Is it possible to make a combobox in the form where the Op_ID is taken from a ModelOperation table?
Let's assume that there are these unique items:
Code:
tblModel
Mod_ID Mod_Num Mod_Desc
1 0001 Model01
2 0002 Model02
tblArticle
Art_ID Art_Num Art_Desc
1 001 Article X
2 002 Article Y
3 003 Article Z
4 004 Article K
tblOperation
Op_ID Op_Num Op_Desc
1 01 Gluing
2 02 Sewing
3 03 Stitching
tblMaterial
Mat_ID Mat_Num Mat_Desc
1 B001 Blue
2 R001 Red
3 B001 Black
I added 1 additional model and 2 additional articles and 1 additional colour compared to your suggested data.
Now let's take the structure:
Code:
tblModelArticle
MA_ID Mod_ID Art_ID MA_Description
1 1 1 Model01 - Article X
2 1 2 Model01 - Article Y
3 2 3 Model02 - Article Z
4 2 4 Model02 - Article K
tblModelOperation
MO_ID Mod_ID Op_ID MO_Description
1 1 1 Model01 - Op Gluing
2 1 2 Model01 - Op Sewing
3 1 3 Model01 - Op Stitching
4 2 1 Model02 - Op Gluing
5 2 2 Model02 - Op Sewing
6 2 3 Model02 - Op Stitching
Now tblaArticleOperation would not be needed and a table tblArticleOperationMaterial could be used directly instead such that:
tblArticleOperationMaterial
AOM_ID MA_ID MO_ID Mat_ID AOM_Description
1 1 3 1 Model01 - Article X - Operation Stitching - Material Blue
2 2 3 2 Model01 - Article Y - Operation Stitching - Material Red
3 3 6 2 Model02 - Article Z - Operation Stitching - Material Red
4 4 6 3 Model02 - Article K - Operation Stitching - Material Black
If additional article is created, simply adding 1 row into tblModelArticle and 1 into tblArticleOperationMaterial would be sufficient, instead of adding 3 rows into tblArticleOperation while also having to add 1 to tblModelArticle in this particular case.
Does this make sense? Can you please explain how I would go about adding a comboBox on Operation field in tblArticleOperation in your design? I mean so that the operations assigned to different articles, but same model, would be the same, that is, the example I gave above (tblArticleOperation AO_ID 6 where I assigned operation 4 instead of 3 in the specified AO).
The foremost worry for me is that the operations need to be very consistent, that is, if Model01 has Stitching01 assinged to it, then all Articles (01, 02,...) under Model01 must have Stitching01 assinged to it and no other stitching. There would be for example 10 different types of Stitching(01...10) and assigning wrong one would mean a disaster in my case.
Furthermore, if we assume that we have 1000 models and 5000 articles and 100 operations (let's say each model has in average 10 operations assigned to it). Then your design would have 5000*10 = 50 000 rows in tblArticleOperation and if we assume 5 of the operations's materials keep changing in every article, 5000*5 = 25 000 rows in tblOperationMaterial. My design (assuming if it works) should have 1000*10 = 10 000 rows in tblModelOperation and 5000*5 = 25 000 rows in tblArticleOperationMaterial. How big of an impact would having 50 000 vs 10 000 rows have on the performance?
P.S.: Please let me know if there is something like "Reddit Gold" on this website so I can give you one as your help was really immense for me.