How should I change my tables design on this project?

[HectorGips]

Buenos días to everybody.

I'm preparing a db for the production department of my factory, it's an aggregates and gravel factory.

Its progress it's now over 75%, but I'm beginning to think that I missed the table design and I'm in time yet to change it.

So:
-In the factory there are four working areas: milling, calcination, micronizing and bagging.
-In the db (with forms) you create every day the working order for every shift and area (setting operators, materials, production expected, etc.).
-Once the working day is over, the same db is filled with production results (pallets, bags, incidents, etc.)
-The tracking id (but not necessarily the key id) is the date and shift in this format (20200318M for morning, 20200318N for night and so on).
-Not all the areas work allways. One week might works just milling, or one morning work all the areas but by night just works bagging. They're independents.

So for this I created just one table. In the beginning was enough. A record for every shift, three records a day. I know it's not ideal because several fields remain empty when there isn't work in any of the areas.

But recently the quality guy came with about 100 new fields for the processing quality.
I'm thinking about divide by four the table, one for area. Or one table for production, another for quality records.
But at this point I don't want to miss again, and I'd like to prepare a db that can stand over time.
I don't mind to begin from scratch, or re-arrange my current db, I don't mind the hard work.

So I need your help in order to get the design that fits best this task.

I attach a recent version of the db, yet incomplete, but you can see whats going on.

http://drive.google.com/file/d/1JE2HfZefdsg_YLKkG9DPOcYD1ixgrbzG/view?usp=sharing

Many thanks and regards.

 

[namliam]

Oper1, Oper2 and worse Oper1_mol, Oper1_cal, Oper2_mol, Oper2_cal is a serious design issue
Best course of action is to create an additional 1:n table to store the operators
Consider doing the same for all the numbered and repeating fields in your table.

IdParte is no more than a concatination of 2 other fields.
Date is stored multiple times in duplicate, asking for trouble

Fact that you have 9 forms and only one table is a considerable concern

Dont know the business nor the language, sorry I cant help more than these suggestions.

 

[vba_php]

Hector,

a factory setup is complex, at the very least. If you are a beginner for database design and administration, I would consider hiring an expert to help you set this up. Your ONE table needs to be split up until 5 tables, at the LEAST. you are using access like excel is supposed to be used. many people do this, and it's not right. I did not read through your entire post, but if you are working for a factory, here is what I assume you would have, in terms of *types* of data:

part id/number
worker id/number
production station id
pallet id/number
part storage location (warehouse)
part destination
part carrier / shipper (trucking company)
part buyer (customer)
part type (type of product that *part* is.  example => what kind of thing you are selling)
production hours required
production downtime
production issue support tickets
etc, etc...

now, most of your data can be set up, more than likely, like a CRM (customer relationship management) database. see attached file call example for a small illustration of how this can work very easily. if I were to analyze the data I gave you above, as an example, I would sort it out similar to like what you see in the attached file called hecktor_example. this is not the answer 100%, but it should get you started. don't forget to look at the relationships that are set up. hope this helps you out.

 

[HectorGips]

a factory setup is complex, at the very least. If you are a beginner for database design and administration, I would consider hiring an expert to help you set this up. Your ONE table needs to be split up until 5 tables, at the LEAST. you are using access like excel is supposed to be used. many people do this, and it's not right. I did not read through your entire post, but if you are working for a factory, here is what I assume you would have, in terms of *types* of data:
now, most of your data can be set up, more than likely, like a CRM (customer relationship management) database. see attached file call example for a small illustration of how this can work very easily. if I were to analyze the data I gave you above, as an example, I would sort it out similar to like what you see in the attached file called hecktor_example. this is not the answer 100%, but it should get you started. don't forget to look at the relationships that are set up. hope this helps you out.

Agree with you. I'm the maintenance manager, and my bosses, knowing that I manage myself with I.T. stuff, asked me about preparing something for record the production process. It's a huge task, but luckily the sales, logistics, etc, aren't involved. Just the manufacturing itself. Raw product comes in, pallets ready for selling comes out.


I absolutely get the point, guys.
I handle myself quite comfortable in excel, so I'm very guilty of using access like excel. And it's not. It's hard, because it's a completly new thinking estructure, and, even being aware of it, I keep falling in that.
Guided by your comments, I've been this evening reading about db normalization, and I realize now about my many mistakes.
I'm going to re-build everything, and I'll come back with my advances.

Thanks and regards.

 

[vba_php]

good deal. we will like to hear how it turns out.

 

[moke123]

There are a few sites that have Data Models that may suit your needs or give you some insight.

heres one http://www.databaseanswers.org/data_models/

 

[HiTechCoach]

I handle myself quite comfortable in excel, so I'm very guilty of using access like excel. And it's not. It's hard, because it's a completly new thinking estructure, and, even being aware of it, I keep falling in that.

It is a huge paradigm shift in thinking to go from designing a spreadsheet to designing tables in a relation database.

The shift from thinking "wide," with all related data in one row with Excel, to thinking, storing data narrow and tall with multiple records in a table is not easy.

I would recommend that you look at the Northwind template that is available here: https://support.office.com/en-us/ar...1de-8278-1afcfc91a9cb?ui=en-US&rs=en-US&ad=US

Northwind

This sample database template demonstrates how Access can manage small business customers, orders, inventory, purchasing, suppliers, shipping, and employees. The database can generate 15 different reports and is a great showcase for learning and customizing Access databases.

. It will teach you a lot about how to normalize your data.

But recently the quality guy came with about 100 new fields for the processing quality.

In a well designed relational database, the 100 new items would be new rows in a table. There should be no need to make any table design changes to add as many quality processing items as needed. Only add new data to tables. When you can do that, you have it correct.

Similar to an Invoice having as many items on as needed. (See Northwind template recommended above)

 

[HectorGips]

There are a few sites that have Data Models that may suit your needs or give you some insight.

heres one http://www.databaseanswers.org/data_models/

Many thanks, so interesting link. It'll be so useful to me.

 

[HectorGips]

In a well designed relational database, the 100 new items would be new rows in a table. There should be no need to make any table design changes to add as many quality processing items as needed. Only add new data to tables. When you can do that, you have it correct.

Similar to an Invoice having as many items on as needed. (See Northwind template recommended above)

Thanks for the advices. That's my goal, but it's not being easy. That northwind template is clarifying things, but for every solved question, 10 more turn out.

I'll come back with my advance asap.

 

[jdraw]

In addition to the advice offered do far, I recommend the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. If you work through one or two of the tutorials you will experience a process to identify tables, fields and relationships that can be used with any database. There are a number of related articles in the link that may be helpful with your specific questions and database concepts generally. You can test your data model with some test data and test scenarios --see the stump the model link in the link mentioned above.
Good luck with your project.

 

[zeroaccess]

Thanks for the advices. That's my goal, but it's not being easy. That northwind template is clarifying things, but for every solved question, 10 more turn out.

I'll come back with my advance asap.

With Northwind you'll find it helping you understand the structure of how it all works together - which is the most important part. I think these examples are one of the best ways to learn. If you stick with it long enough, you'll find yourself replacing some of the actual methods used in some areas, particularly the macros. Also, Northwind was designed before Windows had a screen scaling setting - so if you use 125% or 150%, expect the intended design to get a little skewed (backgrounds not lining up with buttons - this is a problem for all of us).

 

[HectorGips]

Hola de nuevo, guys.

I'm at learning process right now. I have a quick question, I'm stuck at some point.

I'm trying to adapt the lessons I'm reading to my db from zero. First on paper, having in mind normalization, etc. And there is something that I don't understand and I don't want to proceed without having this clear.

The goal of my db is keep record of the factory production. The work orders for the operators are created every week, and at the end of the working day, the operators fill in the db the production results data.

In the current design (it's in the very first steps, just to try what I'm learning at the same time), I've made a table for the operators list, and another for the work order.
I have to include two workers in every w.o., but the aren't always the same two workers. I have to pick them from the list.
The first question is how can I do this. I don't get how to do that. I guess I must do it with an intermediate table, but no clue how to do. I mean, it's easy add one worker, but not two of them or even more.

The second question is where to keep record of the data. I can keep adding tables for every entity, but in the end, an operator must to introduce data, and I'm not seeing yet where are those data going to be recorded.
I suspect that the answer to my first question will answer this as well.

I attach what I have so far, included the work order form translated to english.

Thanks and regards.

 

[The_Doc_Man]

The first question is how can I do this.

If you know you are going to have two workers in the list, you have the problem of a one-to-many event/situation. This is usually handled by having a child table. There is nothing wrong with picking a name via a combo box based on your operator's list, and the idea of having more than one combo on the same form picking the same table isn't a big issue either. Your problem really won't be the picking, but rather the storing and retrieving part.

The EASY way out is to not normalize the data - but it is easy ONLY for the form where it happens. For later reports and other actions, it becomes more of a problem. The right way (I think) is a child table of operators that contains work order number, operator ID, date info, and a number to show from which combo they were picked. When you try to store this, you might have to work a bit because of the layout of your form. It WOULD be possible to make the selection of operators part of a sub-form bound with the parent form, but that might require creating a tabular child form where each row includes the combo box. You have the "Milling" form with three operators in a row. If you make that three operators in a column then a simple parent/child subform could be built that was linked on the milling order info. You have something called tbl_WorkOrder_Mil in your presentation and that may be where you store the info you need with just a little restructuring OR you can create a new child table that resembles tbl_WorkOrder_Mil to hold the list. What you need to capture the day's work is your Lot, Work Date, WorkOrder ID, and the Operator ID. I'll call this new table WO_Mil_Detail just for clarity.

The only wrinkle there is that the combo box has a .RowSource and you need to make it SLIGHTLY more complex in order to prevent the same name from being entered more than once for the same day and same order. Where you might need to do some reading is the topic of SUBQUERIES. The subquery MIGHT look similar (but not identical) to this:

SELECT ID_Operator, OperatorCode, OperatorName, OperatorSurname, OperatorClass
FROM tbl_Operator
WHERE ID_Operator NOT IN 
    (SELECT ID_Operator FROM WO_Mil_Detail WHERE WorkOrder_ID = '<see note 1>' AND WorkDate = '<see note 2>' AND <see note 3> ) ;

First, since you are new to this and still working through things: The combo box I'm suggesting would let you or someone else pick who to put in your boxes for operators 1, 2, and 3. When NO ONE has been picked yet, any operator could be selected. But once an operator has been selected and is in the suggested WO_Mil_Detail table, they cannot be selected a second time for the same work order and work date. If you want to think of the subquery as the "already selected" list, then the outer or main query just says "pick anyone who isn't already in the list." Each record in child table would be a record in WO_Mil_Detail (or whatever else you might want to call it.)

OK, now to notes 1, 2, and 3. They will relate to how you want to control or limit this selection. If the work order ID is on the form that will be the parent to the proposed sub-form and is called WO_ID, then the note 1 segment might read

"... WHERE WorkOrder_ID = " & Me.Parent.WO_ID & "...

Note 2 MIGHT come from the slot that, on your sample form, contains the date. (Next to the three operator drop-downs.) Again, you might use something like this, if that box was called WOWorkDate:

"... AND WorkDate = #" & Me.Parent.WOWorkDate & "# ...

The # signs are of course how you designate that something is a date.

Note 3 is the place where other constraints would go if you had them. For instance, you included a shift designation in one of your exhibits. If the same person could work the same project on a different shift on the same date (say, because they are working overtime), then you would need to have shift information available as well. If that isn't allowed to happen due to your business rules, then maybe you don't need an extra selection criterion. Since I don't know your business rules, I can't answer that so had to leave it open as an option.

 

[HectorGips]

Many thanks for your post. Too much useful info!

I'm focused now in the table design part, I'll save your code help for later. Just knowing that it can be done is enough for me now.

So regarding this, I think I don't see where store the operators data and why I need to duplicate the work order table.

If you know you are going to have two workers in the list, you have the problem of a one-to-many event/situation. This is usually handled by having a child table. There is nothing wrong with picking a name via a combo box based on your operator's list, and the idea of having more than one combo on the same form picking the same table isn't a big issue either. Your problem really won't be the picking, but rather the storing and retrieving part.

The EASY way out is to not normalize the data - but it is easy ONLY for the form where it happens. For later reports and other actions, it becomes more of a problem. The right way (I think) is a child table of operators that contains work order number, operator ID, date info, and a number to show from which combo they were picked. When you try to store this, you might have to work a bit because of the layout of your form. It WOULD be possible to make the selection of operators part of a sub-form bound with the parent form, but that might require creating a tabular child form where each row includes the combo box. You have the "Milling" form with three operators in a row. If you make that three operators in a column then a simple parent/child subform could be built that was linked on the milling order info. You have something called tbl_WorkOrder_Mil in your presentation and that may be where you store the info you need with just a little restructuring OR you can create a new child table that resembles tbl_WorkOrder_Mil to hold the list. What you need to capture the day's work is your Lot, Work Date, WorkOrder ID, and the Operator ID. I'll call this new table WO_Mil_Detail just for clarity.

For a child table you mean something like this? The operator storing might work in this way. With some code in the form I can limit that an operator can be selected twice.

 

[The_Doc_Man]

With some code in the form I can limit that an operator can be selected twice.

If you are using forms to control this process, then putting protective code in the form to limit operator selection is the perfect place for such code.

As to "where to store operator data" I'm not sure how you mean that so I can't respond to it. As to "duplicating the work order table" this is something you almost never would do. But again, I am not sure what you mean so I don't know how to respond to it.

 

[HectorGips]

As to "where to store operator data" I'm not sure how you mean that so I can't respond to it.

Sorry for my english, it sounds better inside my head. For that I mean where to store the work order info regarding the workers. For every work order I have to attach 1, 2 or 3 workers, and that data needs to be stored somewhere, but I'm not sure where. That's what I tried to do in my previous post, in the pictures you can see that. One table for the work order, another for the worker list, and another (is that child table, then?) for the workers that work in a day, relationated by its corresponding ID's.
Is it then a proper way to structurate it? If yes, I can use that schema for another items like this, for example for all those redundant quality data that I need to add.

As to "duplicating the work order table" this is something you almost never would do. But again, I am not sure what you mean so I don't know how to respond to it.

About that, I refer to this:
"You have something called tbl_WorkOrder_Mil in your presentation and that may be where you store the info you need with just a little restructuring OR you can create a new child table that resembles tbl_WorkOrder_Mil to hold the list. What you need to capture the day's work is your Lot, Work Date, WorkOrder ID, and the Operator ID. I'll call this new table WO_Mil_Detail just for clarity."

Isn't that almost like duplicate tbl_WorkOrder_Mil? It's just that I don't understand the use of that second table.

Thanks and regards.

 

[The_Doc_Man]

To your second part first, (duplicating the work order table) I was thinking that you would directly USE the tbl_WorkOrder_Mil, but the problem is as always one of intended usage. My comments confused you, and that was NOT my goal. The question to ask is this, and I'll try to be more precise in my language.

If you do NOT consider my comments, what is the current or intended use of tbl_WorkOrder_Mil? More to the point, does it represent work TO BE done or work that HAS BEEN done? Given your comment, I do not understand what you are storing when it APPEARS that it is already in that table.

When normalizing data, you not only consider what you are storing but how you are using it. While it may indeed be rare, if you happen to have two totally different uses for what looks like the same data, there IS an argument for duplication. Usually only if the same data can be used for two different purposes at the same time and have two different meanings in the two contexts. Therefore my confusion is because it appears that you have two different usages - or at least you THINK you do.

The first part (where to store operator data) is basically that you need to look at what tables you have and what each one represents. (This is part of building a database to be a virtual model of your actual business.) If you need to store a type of data not currently represented by any existing table then the solution is to add another table. From your description, this could well be a good place for a child table. But here is the rule that I usually give to people: If you can't do it on paper, you can't do it in Access.

So this is the question related to my rule: Can you make this work using only paper record-keeping? The point being that if you say "NO" then you are not ready to use Access on it yet. The process here is to decide what it looks like when imagining (or remembering) how it works with little paper forms and tickets and such. THEN you can convert each separate form, ticket, or other piece of paper to a table. In those tables, each record holds exactly the data that would have been on one such piece of paper. And once you can do it on paper, you can do it in Access.

So here is the refined question about where to store operator data: Where would you have stored it without a computer?

 

[HectorGips]

Many thanks for the explanation, it's more clear now.

So here is the refined question about where to store operator data: Where would you have stored it without a computer?

About that, this how we do now: this spreadsheet is filled by the production manager with the production parameters, printed, and given to the operator. According to the production parameters (blue cells), the worker make the expected production. After that, he fills the orange cells with the production results.
After that, the production manager store the data from the paper on a spreadsheet copy in the computer.
There are four areas, one spreadsheet each area.

Maybe an image is better than words. I attach two of them as an example.

 

[The_Doc_Man]

So far, so good. You are looking at the process with an eye towards identifying distinct steps at the data level. I know you already understand the process from the idea of what work the people perform. You know the machines and the physical processes. It is now a matter of translating the DATA FLOW just as you have previously used worksheets to track the WORK FLOW. You are using Access to "model" your business. You have to go through this process of "making a map of the territory" once or twice to see how it works, but once you do, it becomes easier.

Based on the two paper-form presentations you just made, you have identified two potential tables OR two potential tables with potential child tables. (And you have either "report" or "form" prototype designs for later use.)

Regarding the question of duplicating data by duplicating tables, there is another (far more common) approach: Store things in one table - but you don't have to completely fill it right away. You can create your work order tables - but then leave space in the record for entering the aftermath of the work. Does that make sense as an approach? Then you have no duplicate tables.

I mentioned "child tables" above since I could imagine wanting to keep records either of two ways: Per person performing the work, or per individual parts ("steps"?) of the work. In which case your work order form could have either a "work order actions" child table, or a "persons on work order" child table. This is in line with the idea of normalizing your data.

I don't recall whether you have had a chance to look at normalization, but it is a VERY EXTREMELY handy tool to have in your mental tool kit. If you are having trouble with repetitious data, you are in a prime situation for normalization to help you. I know other folks have suggested things for you to look at, and I don't want to overwhelm you. However, I see it this way: Normalization concepts are one of those up-front types of mental investment. It's a "pay me now or pay me later" situation, but for which the "pay me later" component is more expensive because of retrofitting requirements.

What it would give you is that once you are comfortable about normalization concepts, you can more easily spot - and implement - those cases where a parent/child data relationship is appropriate. If you HAVE studied normalization, great. If you haven't, I suggest it NOW before you create a set of tables that is more complex than needed for the job.