Results 1 to 15 of 15
  1. #1
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22

    Design vs Article (=Design+Materials) problem

    Hello,



    I have a problem where I am not sure how to design the database so it is manageable in future. My problem is as follows.
    I have 4 general groups of items:

    1) Model (or Design)
    2) Operations
    3) Materials
    4) Articles

    The model (1) is basically just a design, a 4-digit unique ID, for this specific purpose it is related to shoes and so for instance model "9017" is different from "9016" by simply having 1 additional stripe, HOWEVER, they are different and so all are unique.

    Operations (2) are basically things that need to be done in order to produce the design. For instance stitching, cutting, gluing and so on.

    Now I have a table linking the model with the operations related to that model.

    However, next comes materials into play and so ModelXOperations (table assigning operations to given model) along with materials would give me "Article" (4).

    Article is basically a model (design) with given materials assigned. For instance 901701 would be model 9017 in a specific colour (material) combination, say blue. 901702 would be blue+white, 901703 would be blue+black and so on.

    Obviously not all operations require a material to be used, for instance only 4 out of 10 operations on a specific model require a material and so the other 6 operations would be "static" - always the same for given model.
    What I basically want is to have a table of articles where I assign them specific material to be used.

    I'm very new to access and databases in general and so would greatly appreciate any insight into this problem.

    If you have any questions please ask.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In the tModel table you have the unique key 9017.
    in tOperations, you have:
    modelKey: 9017
    opKey: autonumber
    opEvent: Gluing

    9017, 1, gluing
    9017,2, stitching
    9017,3, cutting

    ...the same with tArticle, modelKey is 9017 for all items.

  3. #3
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22
    I have probably explained my problem in a wrong way.

    Here is a screenshot of my work so far:



    Please remember that I'm new to databases and access in general, so if you spot something that should be done somehow else, please let me know.

    Basically each Model has a certain set of operations to be performed on it - e.g. gluing, cutting, stitching...

    Each article is a Model with assigned materials to it.

    My aim is to avoid repeatedly assigning operations to each article, when I already assign them to models themselves.

    Here is a direct example. These are the example operations assigned to 9017:
    Click image for larger version. 

Name:	Snímka.PNG 
Views:	20 
Size:	5.8 KB 
ID:	20355

    I would like to open "Articles" table, expand the sub-sheet and see columns:
    Operations (copied from the ModelXOperation table), Material 1, Material 2, Description

    So for example if I expand 901701 it would look something like:
    Click image for larger version. 

Name:	11.PNG 
Views:	20 
Size:	8.4 KB 
ID:	20356
    where the operations are simply copied from the operations assigned to the Model 9017.

    Thanks in advance!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you supply a sample database, you have some relationships that I don't really understand from your diagram. Just make sure it has enough data in it so that viewing the links/actual data that your links will make sense.

  5. #5
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22
    Unfortunately I have very limited data at the moment, as all the data the company uses now are all over the place in excel sheets (and so I was given a task to try and make a sense out of it). I have some, very limited, data available which I give you here:

    https://www.dropbox.com/s/1vbf19436s...se2.accdb?dl=0

    I didn't know how to upload to the forum itself here (or if there is some convenient agreed way to upload databases) so I just put it to my dropbox, hope that works for you.

    However, should you have any questions so you understand the problematic better, I'm very happy to help. Please remember that the database design is in such an early stage that it can all be made again from scratch within few minutes, so if you have any suggestions, please go ahead and share them ^_^.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Sorry I do not have a dropbox account, nor am I going to create one. You can add attachments here (zip up the file first) by using the GO ADVANCED button on the bottom right of your posting area then using the attachment button (look for the paperclip icon).

  7. #7
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22
    You can still download it without having to create a dropbox account as far as I know - simply click "No thanks, continue to download." when it asks you to login.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I have the file, I am not going to try and decipher your relationship diagram but here are few things you should fix.

    1. Do not use a primary key that is not an autonumber if you are new to databases, in your case you are using text based primary keys. If those keys change (or get overwritten by accident) you will have orphaned a lot of your data. Autonumbers are not changeable by users so even if your codes (for instance ModelXCuttingKnife) change over time your data will maintain it's integrity.
    2. Do not use spaces in your object names (field names, table names, query names, report names, etc) they will cause you problems as you progress with your database design.
    3. Do not store your costs as text fields, it will be a pain to parse the string to perform mathematical functions, instead store the numerical value and the currency type separately within your tables.

    Now the meat of this, I assume the problem is how to set up your data structure and this is how I see it

    you have four levels of information

    Model is the top layer

    For each Model you may have any number of Operations (one to many)
    For each Operation may you have any number of Articles (one to many)
    For each Article you may have any number of Materials (one to many)

    So to accommodate just the 'construction' information you would need 7 tables (I'm not sure I have the table names here correct)

    ModelList (the list of models your company makes)
    OperationList (the list of ALL operations regardless of model)
    Article (the list of ALL articles regardless of operation)
    FinishedMaterials (the list of ALL materials regardless of article)

    I would tend to name these so it's a little clearer like
    tblModel
    tblOperation
    tblArticle
    tblMaterial

    Next you would need 3 junction tables
    tblModelOperation (the list of operations related to specific models)
    tblOperationArticle (the list of articles related to specific operations)
    tblArticleMaterial (the list of materials related to specific articles)

    Now each combination of Article and Material should have a single entry on your tblArticleMaterial
    so when you get to your table tblOperationArticle you should be able to enter the PK (through a combo box for example) of the tblArticleMaterial table and be able to produce a list of the materials needed.

    Similarly if you go up another level to your models, you would store the operation PK, the operation PK in turn will relate to several articles, each article of which will link to a specific material.

    Is this what you're after?

  9. #9
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22
    Thanks a lot for the reply!

    Regarding the 3 initial points you made - I took note of them and will make changes accordingly - I'd just like to point out that I haven't even gotten to the price field yet so I just put some random values there for now (and the data type) so I have something there, once I come to calculations, I would fix it.

    Although you were not exactly correct in the way you grasped articles & operations & models, it was immense help regardless because you helped me realise that Models are actually higher in terms of structure level than articles (I thought it would be other way around). I'm sure this is due to my bad explanatory consideing I don't know the technical terms when it comes down to databases.

    Basically:
    For each Model there are a number of operations (one to many)
    For each Model there are a number of articles (one to many) - for instance a Model 9017 can have articles 901701, 901702, 901703 and so on, but they are just colour combinations of given model.
    For each Article there are a number of materials (one to many) used within the operations on given model.

    ModelList (the list of models your company makes)
    OperationList (the list of ALL operations regardless of model)
    Article (the list of ALL articles regardless of operation)
    FinishedMaterials (the list of ALL materials regardless of article)
    Mostly correct, however, it should look something like:
    ModelList (list of ALL models the company produces)
    OperationList (list of ALL operations regardless of model)
    ArticleList (list of ALL articles regardless of model)
    FinishedMaterials (list of aLL materials regardless of how/where it is used)

    tblModelOperation (list of operations related to specific model) - for instance Model: 9017, operations related to that model: STITCHING01, SEWING01, GLUING02...
    tblModelArticle (list of articles related to specific model) - for instance Model: 9017, articles related to that model: 901701, 901702, 901703...
    tblArticleMaterial - now this is where it all comes down to. I don't know how to link the specific materials of the specific article to given operations in given model.

    Let me explain it once more in a greater detail:

    - Model is a certain design of a product. Each model is so unique it might as well be completely different product, say shoe lances vs finished shoes.
    - Operation is a certain type of work required in order to produce the product (model). For instance it can be gluing, stitching, sewing, cutting... any action. For shoe lances there would be, say, 3 operations required, for whole shoes maybe 20.
    - Article is a model, product, in a certain material (colour) combination. For example shoe lances (model) can have 3 different articles - blue, yellow, red. All 3 colours (articles) of shoe lances require same operations in order to produce them, the only difference is that the person who colours them will use specific colour.

    And so I'd like to first assign operations to a specific model and then when I'm creating an article - colour combination (assigning materials to specific operations, simply use the already pre-defined operations for the model and add material to it.

    So for instance model "Shoe Lances 101" would have 3 following operations:
    1) Cutting fabris
    2) Gluing the ends
    3) Colouring

    However, article "Shoes Lances 101 BLUE" would have 3 following operations:
    1) Cutting fabris <nothing to add here>
    2) Gluing the ends <maybe different type of glue than standard>
    3) Colouring <use Blue colour here (specific material ID)>

    The reasoning I have behind this logic is that if I have, say, 5 different articles per model (5 colour combinations) and in future I want to change an operation in model - say an innovation in the production occurs and 2 operations can be done in 1, I would simply go to the model itself and delete 2 old operations while adding 1 new operation which incorporates both of them.

    If the old ones used any materials I would just go to each specific article and assign the material used to the new one.

    Imagine the following scenario: a specific model, product, has 30 different articles. The model itself has 20 operations but only 5 operations use different materials and so the other 15 operations are always the same, no matter article.

    Now if I would have it separated - operations from models from articles and would simple create a table where I link operations to given articles, I would have 20*30 = 600 rows of data. Theoretically, if I know the 15 operations are always the same, that means a model table should have 15 rows of data with same operations and then 30articles*5different operations = 150 rows of data in the article&operation table.

    I'm not exactly sure how to do this in a good way so it is easy to manage in future. Please keep in mind that there could be as many as 100 articles and as many as 5000 total models, that is why I'm thinking on how to make it as efficient as possible.

    Thanks a lot in advance!

    Edit: I forgot to mention one of the most important things - Model is not produced, it is just a theoretical concept. When it comes down to production it is always Article which is produced. You always need certain colour combination to produce and so when orders come in etc it will always be specific Articles they want. Say model is a sketch on paper, the theory of what should be done and how it should be done, but article is an actual thing where you combine this theoretical concept (which requires operations to produce) along with certain materials to make.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I have misunderstood your business, if the finished product is the ARTICLE then your 'waterfall' would be

    ARTICLE (Can have many MODELS, finished product)
    MODEL (Can have many OPERATIONS)
    OPERATION (Can have many MATERIALS)
    MATERIALS

    What you have to examine is whether it's possible for your 'waterfall' to skip steps, for instance:

    Is it possible for a MATERIAL to be related to a MODEL with no OPERATION in between
    Is it possible for an OPERATION to be related to an ARTICLE and not have a MODEL between
    Is it possible for an ARTICLE to be related to a MATERIAL and not have either a MODEL or OPERATION in between

  11. #11
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22
    Sorry, I was busy over the weekend and didn't have time to reply! ^_^

    It is still not quite right, unfortunately. ARTICLE is the finished product, and there are way more articles than MODELS.

    For every MODEL there are several ARTICLES which are just colour variations of a given model. A specific model can be produced in 5 different colour/material variations and so it has 5 articles.

    The rest, however, is correct.
    MODELS can have many OPERATIONS.
    OPERATION can have many MATERIALS.
    MATERIALS are on their own.

    So overall there are, let's say, 5000 articles, 1000 models, 10 000 operations, 15 000 materials (all being unique).

    Ideally I'd like to have it look like:

    Model:
    ModelID

    Operations:
    OperationID

    tblModelOperation:
    ModelID
    OperationID

    tblArticle:
    -take the ModelID and OperationID from tblModelOperation
    MaterialID - assign a specific material to that specific operation.

    Not sure if that makes sense. In this way the operation would have assigned material when Article is specified, but the operation itself would be assigned to model (ie. article takes model and the associated operations to the model).

    Sorry for being such a headache! =).

    Edit:
    Basically my question is how do I take more than 1 column from 1 table as an input to another table. Say I have table:

    tbl1:
    tbl1ID
    inf1

    where inf1 is associated with the specific ID, now I want to make a table:

    tbl2:
    tbl2ID
    tbl1ID
    inf1
    inf2

    Where I basically not only take tbl1ID but also inf1 column from the tbl1.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ...
    Look at it this way

    Just for the sake of argument let's say you only produce *1* MODEL of shoe (MODEL 0001)
    That MODEL of shoe has 2 different ARTICLES. The ARTICLES are identical in every way, in other words they have the same OPERATIONS
    Each ARTICLE has three OPERATIONS (OPERATION 01, Gluing, OPERATION 02, Sewing, OPERATION 03, Stitching)
    The difference between the two original ARTICLES is the color of the thread in OPERATION 03, Stitching, one is blue, one is red.

    In terms of a data structure you would have:

    Code:
    tblModel
    Mod_ID  Mod_Num  Mod_Desc
    1       0001     My Company's Only Model
    
    tblArticle
    Art_ID  Art_Num  Art_Desc
    1       001      Article X
    2       002      Article Y
    
    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 Description 1
    2       R001     Red Description 1
    These would be the tables listing your unique items.

    In terms of how you store the 'build' (how you put the shoe together) you would have a similar setup of junction tables

    Code:
    tblModelArticle
    MA_ID  Mod_ID  Art_ID  MA_Description
    1      1       1       My Company's Only Model - Article X
    2      1       2       My Company's Only Model - Article Y
    
    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      2      3      My Company's Only Model - Article X - Operation Stitching
    
    tblOperationMaterial
    OM_ID  AO_ID  Mat_ID  Mat_Description
    1      3      1       My Company's Only Model - Article X - Operation Stitching - Material Blue
    2      6      2       My Company's Only Model - Article X - Operation Stitching - Material Red
    I would venture to say there are more elegant ways of handling the 'build' portion of your data structure but this is a solid structure. There is a lot of up front setup in terms of getting the builds right but once they're in you only have to deal with 'new' items (models, articles, operations or materials)

  13. #13
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22

    Post

    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.
    Last edited by emihir0; 04-20-2015 at 02:50 PM.

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What I've been talking about has been setting up your models so when you go to do data entry your users can't enter an article/operation/material that is not valid for a particular model. It would be a one time set up for each new model.

    The data entry for recording what happened on a particular run/manufacturing process (i.e. recording scrap) would then be limited to the particular point in the process, for instance if you were recording a stitching error for model 0001, you would limit what your users see in terms of articles/operations/materials as entered in your setup, so they can't possibly enter an operation that does not relate to the particular model they are working on. There should be two 'streams' of data

    1. Setting up the initial model/article/operation/materials. This is a one time setup per model.
    2. Data entry. This is the actual data recorded against articles/operations/materials in the manufacturing process (recording scrap, rejection reasons etc)

    I think we're splitting hairs over setting up the model in the first place (the step you need to do to block your users from entering bad data, and what I've been talking about) and data entry (what I think you're worrying about).

    I do not know if I'm interpreting your last post correctly

  15. #15
    emihir0 is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    22
    for instance if you were recording a stitching error for model 0001
    Model is a theoretical concept and Article is model with materials assigned. That means there cannot be a stitching error on model itself, only on article. Article is the thing that goes to the production, model is just the sketch on paper basically.

    1. Setting up the initial model/article/operation/materials. This is a one time setup per model.
    Kind of, but not exactly. Same model from 3 years ago can have a new article created for the following season (new colour combinations) and so articles are being added constantly. Models too, but in a much lesser extent; let's say 30 new models are created per year but each of them has in average 5 colour variations/combinations and so 150 new articles are added.

    2. Data entry. This is the actual data recorded against articles/operations/materials in the manufacturing process (recording scrap, rejection reasons etc)
    I haven't gotten to the part where I would be putting the database into manufacturing process. What I mean to say is that I want to categorise everything first, so everything can be accessed systematically. That is the main purpose of the database that I am working on (or pretending to work on, as the process is more about learning than actually doing anything so far ).

    Once everything is categorised (or at least the design for this is created), the stages of production design/salaries and so on will come into play, however, I think these cannot be even thought about without having a proper design for the structure of the data first.

    Please ask me anything about the last post, as it is possible the most important of our discussion so far (I want to make sure you understand it).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Design Problem
    By hardleydirt in forum Queries
    Replies: 5
    Last Post: 11-16-2012, 01:58 PM
  2. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  3. design problem
    By masoud_sedighy in forum Database Design
    Replies: 1
    Last Post: 12-15-2011, 11:22 AM
  4. design problem
    By marianne in forum Database Design
    Replies: 7
    Last Post: 05-26-2009, 07:25 PM
  5. Table design problem
    By wasim_sono in forum Database Design
    Replies: 0
    Last Post: 04-08-2009, 06:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums