Relational database design

[emihir0]

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:

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 .

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 ? 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:.

 

[CJ_London]

Do you see any obvious flaws in this design

? Can it, first of all, work at all if it is designed this way?

if models can have many articles, then tblarticles needs to have a field (ModFK) to link back to tblmodels (ModID)- the same can be said for your other relationships.

However if articles can be used in many models you need an extra linking table (ModFK,ArtFK). Almost certainly you will need this for operations and materials

Also, not clear why you need mod_num and Mod_ID, both appear to fulfil the same requirement

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?

no reason why not. Depends on the basis of remuneration/bonus but the usual way is to include a 'standard time for a standard unit' in the operations table - this can then be multiplied up by the number of units to determine a standard time for that job/batch which can then be compared with the actual time - rates might come from an employee table or maybe an employee role table or similar, depends what you want. Standard times X quantity plus a start date gets you towards production planning/logs etc, although you will need a lot more -cross checking material is available, If a job overruns/is quicker adjusting future production etc, wastage and more

 

[emihir0]

However if articles can be used in many models you need an extra linking table (ModFK,ArtFK). Almost certainly you will need this for operations and materials

I thought this was all covered in:

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

 

[CJ_London]

sorry - misunderstood. You would not include the description, just the FK's. Depending on how users are inputting data, I suspect you don't need the first two tables, only tblArticleOperationMaterial

 

[emihir0]

I could see how I would go around without having tblModelArticle (simply add a FK in tblArticle and assign models there straight away), but how would you go about not having tblModelOperation?

 

[CJ_London]

but how would you go about not having tblModelOperation?

because you are already defining the relationship in tblArticleOperationMaterial

 

[emihir0]

Yes but I define the relationship for operation which change depending upon material. However, there are other operations which do not change depending upon article produced (ie. gluing will always use same material).

 

[CJ_London]

Well, you asked if there were any flaws in the design, I've raised some questions and you've answered them. You understand your business, I only have a vague idea so can only comment about possible issues with the structure based on normal db constructs.

However, there are other operations which do not change depending upon article produced (ie. gluing will always use same material).

My answer to that would be to just leave the Mat_ID null or 0 for the gluing operation.