Creating a cheese production record from scratch

[Panayiotis]

Hello forum, new user here.

I'm a cheesemaker and having some (very) basic knowledge of programming, I offered to create database for different paperwork that we keep on a daily basis in the cheeseplant. The main goal is for the cheesemaker of the day to input all specific data using a form that is linked to a table.

I'm happy with the tables and forms I created, but I'd love for some advice on how to keep things a bit tidier, and let me explain:

a production record has - among others - the required fields of Date, use by date, volume of milk, etc. Every day we could be using anything from 1 Vat to 6 Vats depending on how much milk we have (see attached). So what I did, was in my table I have the fields V1, v2, v3, v4, v5, v6.... and then for each field I have Supplier1, Supplier2, Supplier3, etc.... and then StarterTimeAdded1, StarterTimeAdded2... and so on. You get where this is going. Is there a way to simplify my database, whilst keeping just one form for the cheesemakers to fill in every day?

I can provide more info, but wanted to keep it basic for now... Please keep in mind I'm only a beginner in all this...any help would be much appreciated, thanks.

 

[Minty]

Welcome to AWF!

Your initial design is unfortunately not correct.
You have taken a spreadsheet design and created a table based on it, and that is a big mistake.

Have a read / look here https://www.niftyaccess.com/excel-in-access/

 

[Panayiotis]

Hello Minty, thank you so much for the prompt response, it's my fault I didn't clarify, the attachment in OP is of the actual sheet of paper we use currently.

 

[Minty]

If you have v1, v2, v3, etc as field names your design is probably wrong.

Can you post up a screenshot of your relationship window with the tables and field names showing?

 

[Panayiotis]

That's my main issue I'm afraid, I haven't created any relationships regarding that table. Showing you two screenshots of the table, in capture2 you can see exactly my problematic...





Attaching you the database if it's any help too, the said table is: tbl_mozz_production_record and it is linked to the frm_production_record_mozz.

 

[plog]

The process of setting up the tables and fields in a database is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

Give that link a read, then find some tutorials to work through, then apply what you have learned to your data. Below are the big issues I see just by glancing at your Relationship Tool:

1. Field and table names should be generic. They should be named after things anyone can grasp. They shouldn't be named after things specific to what the database is for. tbl_mozz_production_record is full of these ([Knife Check x 3], [Curd Sieve Check], etc). My guess is that all those fields are steps in the process. So instead of having each value its own field, in a properly normalized database you would simply have a [StepType] field where you would input "Knife Check x 3" and then another field to store whatever value you are now storing in [Knife Check x3]. You eliminate all those spceifically named steps with one field, [StepType], where you input what are now the names of all those fields.

2. Numerated fields. When you feel the need to suffix a field with a number, its time for a new table. tbl_daily_packing_record has 60 [wt] field in it. This is wrong. Instead all that wt data goes into a new table with just 3 or 4 fields:

tbl_wt
wt_ID, autonumber, primary key
PackingID, number, foreign key to tbl_daily_packing_record
wt_Value, ?, this will hold the value in whatever is currently in all those wt fields
wt_Number, number, this will hold the numeric suffix on all those wt fields if necessary.

wt_Number may or may not be needed. It all depends on if you are using that number after wt as a way to order your data, or just make it unique within tbl_daily_packing_record. If, just a way to uniquely identify field names, then you don't need that field. So, instead of 60 fields in tbl_daily_packing_record you have 60 records in tbl_wt. Or, better still, if you are leaving a ton of those wt fields blank in tbl_daily_packing_record, you only put however many records in tbl_wt you need.

3. Tables without primary keys. tbl_daily_packaging_record doesn't have a primary key, so before you do #2 above you will need to add one. Primary keys and foreign keys (google those terms) are key ingredients in a relational database. Without it you can't properly link your tables together. You need a way from one table to specifically reference just one row of another table and primary keys enable that.

I see other nitpicky errors (Category table serves no purpose, poor field names, etc.). But those 3 above are the biggies. Again, read up on normalization, work througha few tutorials, apply what you learn to your database then repost your updated database with completed Relationship Tool and we can get you there.

 

[Panayiotis]

Thank you so much, really appreciate the time you devoted for this response, I will study and then come back for more questions!

 

[gemma-the-husky]

Blessed are the cheesemakers.
I am sure we will all try to help you.

 

[Panayiotis]

You are all so kind, I feel stupid for not asking questions before attempting all that, I've been focusing on learning VBA and skipped the most important part, which is to get the tables right to begin with.

Trying to reorganise everything now...

I want to focus on two particular issues, which will help me change all the rest (like the 60 different fields for weights as spotted by plog).

I now know that in my production record, which is the main record that is filled every day, I need to have a separate table that will include the vat details.

So....the main table will have fields like...

RecordID
ProdDate
UseByDate
Startupcheck_signature

Now after this, it gets tricky because the person who will be filling in these fields could have anything between 1 vat or 8 vats with the fields

StarterTimeAdded
StarterVolumeAdded
RennetTimeAdded
RennetVolumeAdded
etc....


So focusing on just these fields as an example, how could I create that important relationship between the two tables? Bear in mind that all these should end up in a user-friendly form(/subform?) for the cheesemaker to input every day, and a report should also be available).

 

[plog]

First, let's remove database jargon from your example. This process allows us to identify entities and characteristics and from there we can convert those entity and characteristics into objects in the database. Here's what I heard:

A production can involve multiple vats and each vat has a series of steps to run

If that's right, I see 3 entities (production, vats and steps). Because you don't really have more data that goes just with a vat you don't need a whole table for that entity. You do however need a table for production and steps because those entities have multiple characteristics that you need to capture. You already have a production table so your new VatSteps table swould be structured like so:

tbl_VatSteps
vs_ID, autonumber, primary key
RecordID, number, foreign key back to production table
vat_Number, number, will hold what vat number this is for
vat_StepType, text, will hold the name of the step e.g. Starter, Rennet, etc
vat_StepTime, date/time, will hold the value of time added
vat_StepVolume, numeric?, will hold whatever value is currently going into VolumeAdded field

This is similar to my #1 issue in my first post--you put industry specific data in a field, not in a field name. So 'Starter' and 'Rennet' aren't in a field name, but in the Type field.

 

[Panayiotis]

First, let's remove database jargon from your example. This process allows us to identify entities and characteristics and from there we can convert those entity and characteristics into objects in the database. Here's what I heard:

A production can involve multiple vats and each vat has a series of steps to run

If that's right, I see 3 entities (production, vats and steps). Because you don't really have more data that goes just with a vat you don't need a whole table for that entity. You do however need a table for production and steps because those entities have multiple characteristics that you need to capture. You already have a production table so your new VatSteps table swould be structured like so:

tbl_VatSteps
vs_ID, autonumber, primary key
RecordID, number, foreign key back to production table
vat_Number, number, will hold what vat number this is for
vat_StepType, text, will hold the name of the step e.g. Starter, Rennet, etc
vat_StepTime, date/time, will hold the value of time added
vat_StepVolume, numeric?, will hold whatever value is currently going into VolumeAdded field

This is similar to my #1 issue in my first post--you put industry specific data in a field, not in a field name. So 'Starter' and 'Rennet' aren't in a field name, but in the Type field.

I'm so grateful! Will work on it for a few days and get back to you. Even though i was close to finishing with this record, I prefer to do things right, I know I was late to seek for help. Thank you

 

[Pat Hartman]

Each vat is a separate entity. Your production process could be producing the same type of cheese in all vats or different types of cheese in different vats.

Let me offer a suggestion to help with the normalization.

Make a spreadsheet

List on separate rows all the data fields you have identified.
Try to separate the fields into attributes - descriptions of something, and actions - the process of making the cheese.

You might have different types of actions. You might end up separating the process of making the cheese from the validation steps along the way. Or, they could be inter mixed in a step sequence.

I once did a project for Clairol that provided step by step instructions for each batch. One station might be making shampoo, another might be making conditioner, another might be making hair dye. I would say that your shop floor probably smelled a lot better but I am the one in a million persons who hates cheese

The tables I used would actually be similar to what you need. Our formulas did everything in pounds rather than dealing with different measurements for dry and wet ingredients. The fragrance and dye packs were handled separately since their quantities were in fractions of an ounce and so rather than have tiny fractions of pounds, they were scaled differently and compounded separately, and all components were added as a "pack" rather than individually.

tblBatch - what you are making now. It includes the name of the product, the size of the batch in pounds, RunDT, EmployeeID of who was running the machine, the vat where it will be mixed, etc.
tblIngredients - a list of all possible ingredients for all the products that were made by this system
tblVats - a list of the work stations and their attributes such as size
tblFormulas - a list of the products that could be produced
tblFormulaByStep - the steps to make the product. Each row was an instruction and some included an ingredient. The ingredients included a percent rather than actual weight. That allowed the size of a batch to be variable. If the batch was 500 pounds, then the percent for an ingredient was multiplied by the size of the batch to determine how much to add.

So a Formula might be:
1. Rinse Vat
2. Add 20 pounds water
3. Heat to 100 degrees
4. Add 30 pounds ingredient1
5. Mix at 30 rpm for 5 minutes
6. Let cool to 90 degrees
7. Add 25 pounds ingredient2
8. Mix at 30 rpm for 5 minutes
9. Mix at 60 rpm for 5 minutes
10 Check pH. must be between x and y
11. Add dye pack
12. tare scale
13. Add 5 pounds Ingredient3

The instructions were shown on a form and all steps except the current one were locked. The current one was also highlighted and all the completed and future steps were greyed out to reduce the potential for error. The operator initialed each step as he completed it and the program logged the time.

 

[Panayiotis]

Pat, thank you for your valuable feedback. I did as you suggested and also having in mind what plog is recommending, i'm starting to have a better picture of what I'm doing (see attached screenshot of my spreadsheet items, any comments welcome). So i clearly see that I have some "checks" and some "actions". I'll update further soon, need to get some sleep as I'm combining cheesemaking and database creation - i enjoy both now! - it's been a 12hour shift today!

 

[Pat Hartman]

You're welcome Did the analogy to my project for Clairol make sense to you?

 

[Panayiotis]

Yes definitely! The formula and formula steps are what we call here SoP's (standard operating procedures), well compared to Clairol we are tiny (only talking about 6 full-time employees!)

I think what doesn't help is that I've been thinking in an "excel/spss" manner, partly because I have a researcher background, designing questionnaires etc, and also the records are already created by our food and safety control person, so I have to transfer that design in a database format.

 

[Panayiotis]

1. Field and table names should be generic. They should be named after things anyone can grasp. They shouldn't be named after things specific to what the database is for. tbl_mozz_production_record is full of these ([Knife Check x 3], [Curd Sieve Check], etc). My guess is that all those fields are steps in the process. So instead of having each value its own field, in a properly normalized database you would simply have a [StepType] field where you would input "Knife Check x 3" and then another field to store whatever value you are now storing in [Knife Check x3]. You eliminate all those spceifically named steps with one field, [StepType], where you input what are now the names of all those fields.

Hey plog, I'm still struggling, first tried to focus on the weights table, then the vats table you suggested, but got overwhelmed so I thought i'd start with this very basic tip you gave me. I feel like an idiot for still not fully understanding it (see attached database that i've tried to create). My problem is everywhere I look for the relational database examples I see are about customer/orders or teacher/students, i feel what i'm trying to understand here is a bit more complicated because it has to do with a procedure.

Would you be so kind to give me your thoughts on those two tables i've created so far? Everyone else is welcome to add their comments of course. Also attaching the current paperwork in full that we sign off everyday, that's the daily record that the user would be expected to fill in when I start creating the form(s?).

 

[mike60smart]

Hey plog, I'm still struggling, first tried to focus on the weights table, then the vats table you suggested, but got overwhelmed so I thought i'd start with this very basic tip you gave me. I feel like an idiot for still not fully understanding it (see attached database that i've tried to create). My problem is everywhere I look for the relational database examples I see are about customer/orders or teacher/students, i feel what i'm trying to understand here is a bit more complicated because it has to do with a procedure.

Would you be so kind to give me your thoughts on those two tables i've created so far? Everyone else is welcome to add their comments of course. Also attaching the current paperwork in full that we sign off everyday, that's the daily record that the user would be expected to fill in when I start creating the form(s?).

Hi

Your tables are slightly wrong.

The tables should be as shown in the Reliationship Screenshot attached.

tblProduction has a Primary Key ProductionID

The Production Process has a number of steps so the Production Table is liunked to a related table
to record all of the Steps (tblPreProductionSteps)

In tbl_PreProductionSteps which contains a related Foreign Key - ProductionID.
It also contains a Field named StepID which is linked to the PK from tblSteps
tblSteps just contains your list of required Steps for the Process.

From these 3 Tables I created :-

1. A Main Form based on tblProduction
2. A Subform based on tbl_PreProductionSteps
3. On the Subform based on tbl_PreProductionSteps I created a Combobox which allows you to select a Process Step.

Come back with any questions you may have.

 

[Pat Hartman]

My problem is everywhere I look for the relational database examples I see are about customer/orders or teacher/students

That's why I gave you the specific example of a manufacturing process similar to what you have.

One of the problems with your attempt was the pk/fk situation. Relationships only go in ONE direction so you can't use the crossing PK-FK and FK-PK that you have. That would be a pathological relationship. I.e. you have to have a record in tblA to add a record in tblB but you have to have a record in tblB to add a record in tblA. So, the rules prevent adding a row to either table.

 

[plog]

By themselves your 2 tables look good. The issue is linking them together. All relationships are 1 to many. A record in one table (Parent) can have many related records in another table (child). To establish that you make a field in the child table to hold the value of the primary key from the parent table. But not vice versa which you have done--only one table holds a foreign key.

My guess is that tbl_production_record is the parent and TBL_preproduction_checks is the child: A production can have many preproduction checks--right? If that's the case tbl_production_record doesn't need the ppchecksID field.

 

[Pat Hartman]

It looks like you are currently only making two kinds of cheese. You could limit the application so it looks like your hard copy form but I would think bigger and make it support any kind of cheese.

I created a starting layout. Please note that there are two paths. The production path is on top and the definition path is below it. The idea is that one set of tables is used to describe the process and the other set is used to record batches as they are produced. I didn't go into depth on inventory and I didn't create any forms to show you how to use the data. It is way too early for that. I did put data in some tables so you can see the relationships and I also put notes in certain places.

The group table is intended to be used to make subforms if you want to try to lay out the production instructions to be similar to your current form. I also attached a sample I created to show you how to make sequence numbers. I incremented the sequence number by 10 so you could insert a step if one got omitted accidentally or if you change the process later. The sample includes code to renumber the sequences for neetness The sample shows you that you can rearrange the details by just changing the sequence number. If you want to move step 30 to be after 40, change it to 45 and then renumber.

The Inventory table allows you to log batches and later when you get better at this, you can add another table that records the quantity used and so you know when you've depleted the quantity. The schema only allows for one batch per row, if you commonly use ingredients from multiple batches, ie finish up batch 123 and add 124 to complete the amount, I need to add a table to handle that. It would go between tblProductionSteps and tblIngredientInventory

Once you understand what I've done, we can help you move on.

PS, I changed your table names because I could I really hate names that include underscores so I always use CamelCase rather than the_underscore. I reserve the use of the underscore where I want emphasis. But, feel free to change them back if you really like the underscores and I'll live with it since the underscores are valid when making object names.