Relational database design

Hello all!

I was tasked to design a database for the manufacturing company I work and so would appreciate all the feedback I can get whether the current design structure could work (I'm very new to databases).

So far I have identified 4 main datas - "Articles", "Models", "Operations", "Materials".

The relations are as follows:
MODELS can have many ARTICLES.
MODELS can have many OPERATIONS.
OPERATIONS can have many MATERIALS.
MATERIALS.

Here is some example data:

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

---

Now having the basic data as example, here are the relationships, please bear in mind that this is just an initial design and I have no idea if it can work like this, so please if you have suggestions... go ahead and educate me ;).

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      3      Model02 - Op Stitching

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      5      2      Model02 - Article Z - Operation Stitching - Material Red
4      4      5      3      Model02 - Article K - Operation Stitching - Material Black

---

Let me give you some background now.

• MODEL is a theoretical concept, a design. Imagine it in a way that it is simply a sketch on paper.
  
• ARTICLE is a produced model. This basically means that you take a MODEL (design) and produce it using specific materials.
  
• OPERATION is a thing, a procedure that is done in order to produce something. For example Gluing, Sewing, Stitching (as above).
  
• MATERIAL is material, no need to explain.

Overall we can have, for example, 1000 unique models and if each has in average 5 colour combinations we produce them in, it would result in 5000 unique articles.

If we now have 20 operations in average per model (realistically), that would result in having 20 000 rows in tblModelOperation. Furthermore, if out of those 20 operations only 5 keep chaging (the colour variations), then we need 5000*5 = 25 000 rows in tblArticleOperationMaterial table.

So to sum it up let me give a direct example from above:
Model01 has 2 articles - Article X and Article Y. This basically means that the specific design is being produced in 2 colour variations. Furthermore, there is a list of operations that need to be done in order to produce anything. For the specific Model01, it is Gluing, Sewing and Stitching.

However, only Stitching is an operation where something "changes" from article to article. Take a "Red" variation of product (article) and compare it to the "Blue" one. The only difference in our example would be the material used to stitch - ie. red vs blue. The glue and sewing would be the same.

Now my questions: Do you see any obvious flaws in this design :confused:? Can it, first of all, work at all if it is designed this way? Is it possible to later on expand it in a sense that we make salaries for the employees based on the operations they have done. Make logs of production. Plan production and so on? Is there an obvious thing that should be changed in order to make all of this possible in future?

Thank you for the response in advance, I really do appreciate it ^_^.
Oh and of course, if you have any questions, please don't hesitate to ask.

P.S.: I am from EU and have to go off for today so realistically the soonest I can reply is in about 30 hours (tomorrow morning) :banghead:.

Relational database design