Relationship problem

[Graeme at Raptup]

Hi,
I am trying to set up a db (Access 2002) for a food production environment
(bakery).
The relationships I have identified are:
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1, Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date, machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the Batch
#)
There is a Raw Material Inspection sheet that is tested against ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1 Ingredient in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

 

[mscertified]

If you have only spent one day on it, it's early days. I've spent weeks on
some database designs. It might help to model it visually with a tool like
Visio.
I dont have any specific recommendations because I don't know the bakery
business. Just make sure your tables are normalized. I would have thought you
would need to cater for different sizes in your recipes i.e. everything else
stays the same except you are baking 2000 loaves instead of 1000.

-Dorian

 

[Graeme at Raptup]

Thanks,
I don't have Visio but will give it a look. I have drawn up some entity
relationship diagrams but seem to get stuck on the Inspection part.
Yes you are right about the quantity but in their scenario they work with
weight.

 

[Allen Browne]

Graeme, I don't understand the baking process, so I may be way off here.
Part of the issue is identifying all the one-to-many relations correctly,
and determining if any of them are actually many-to-many.

Is it ever the case that you mix up a batch of something, and then use part
of the batch to make different products? Like the base for a scone mix, that
is also used to make tea cakes? Or a bread mix, that is also used to make
pizza bases?

Are there multiple levels of mixing here? Say you mix up a basic ingredient
(a sauce), and then the next mix contains not only raw ingredients, but the
sauce from the previous mix as an ingredient?

It seems that a product is made in stages, where a stage consists of several
components/operations, such as load, mix, deposit. This particular area
needs quite a bit more thought, I suspect, to break it down into a series of
one-to-many relations.

Finally, the inspection process: *what* precisely is inspected?
Do you inspect ingredients periodically?
Are batches inspected?
Are the product stages inspected?
Is the final product inspected?

Does one inspection consist of many inspection aspects (steps, or batches)?
With multiple times? dates? locations? Are there different kinds of
inspections applicable to different batches, or different stages?

Sorry that I am not able to contribute much to actual data structure because
of my ignorance of the process. But hopefully some of those question will
help you to identify how the pieces should fit together, i.e. where all the
one-to-manys lie in modelling your production process.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Graeme at Raptup]

Hi Allen,
you have helped me before, so thanks again.
If you don't mind I"ll add a process flow here and I'll reply to your
questions below;

1. Recipe is developed for a product (e.g. Toffee Pudding)
2. Recipe includes sub-products (e.g. Base, sauce, icing)
3. Each sub-product has ingredients with corresponding weights and brand
(Supplier)
4. Each recipe has a yield (e.g. 1 trolley), bake temp, bake time and scaling
5. Sub products each have a mixing method
6. When a recipe is prepared batches are opened for each sub-product
7. Batches are loaded and ingredients are checked against weight and R/M
batch code (Raw Material Inspection)
8. Each batch follows a process from: Loading – Mixed By – Deposited By –
Ovenor Name – Released to Depanning – Released to Cake Finish – Sauce/Cake
Finish – Released to Wrapping – Cake Wrapping – QC approved (Not all
processes are compulsory)
9. The product is then evaluated. There are two forms for this (Sample and
Finished Product)
10. Some ingredients are inspected as per the RMI (Raw Material Inspection)
sheet. An inspection will include a Lot Number that is to be linked to the
Loading (7 above) process (If that ingredient has been checked). (If
necessary we can set up our own RMI code)

Graeme, I don't understand the baking process, so I may be way off here.
Part of the issue is identifying all the one-to-many relations correctly,
and determining if any of them are actually many-to-many.

Is it ever the case that you mix up a batch of something, and then use part
of the batch to make different products? Like the base for a scone mix, that
is also used to make tea cakes? Or a bread mix, that is also used to make
pizza bases?

Perhaps the terminology is a bit misleading. But in this case a batch is
specific to a mix (or what I have also called a sub product). So no, one
batch only makes one mix.

Are there multiple levels of mixing here? Say you mix up a basic ingredient
(a sauce), and then the next mix contains not only raw ingredients, but the
sauce from the previous mix as an ingredient?

Yes, the way it works is that we have say a Toffee pudding (Recipe). The
recipe is made up of 3 components (Base, thick sauce and a thin sauce). This
could be 1 or it could be as many as 4 (e.g. icing).

It seems that a product is made in stages, where a stage consists of several
components/operations, such as load, mix, deposit. This particular area
needs quite a bit more thought, I suspect, to break it down into a series of
one-to-many relations.

Yes, I had in fact set up 1 to-M relationships for each of those (as in '8'
above). That part seemed to work, but based on the assumption that they all
linked to an entity called Batch. (I used BatchID as my key which is an
autonumber)

Finally, the inspection process: *what* precisely is inspected?
Do you inspect ingredients periodically?
Are batches inspected?
Are the product stages inspected?
Is the final product inspected?

Yes, this is the sticky part (Poor pun, I know).
Certain products such as liquified egg are tested upon receipt from the
supplier (each and every delivery). Water, for example is not tested in this
manner.
The supplier provides a Lot Number which is their unique code for each
delivery. That Lot Number (or I suppose we can assign our own code if need
be) must be assigned to each ingredient (where there is an inspection code)
in the Loading stage.
Each Loading is linked to a specific batch.
As per (7) above a Batch starts by being Loaded. Each Load is loaded by a
person, date, and all the ingredients of the sub-product/mix, each with an
inspection code, weight (pulled in from the mix table I presume) and a
Checked field.

Final product is Evaluated, but that is a different form/process to this
inspection.

Does one inspection consist of many inspection aspects (steps, or batches)?
With multiple times? dates? locations? Are there different kinds of
inspections applicable to different batches, or different stages?

Each (Raw Material) Inspection includes Date, Lot Number and various testing
fields (e.g. temperature). One Inspection is likely to be applicable to many
batches

Sorry that I am not able to contribute much to actual data structure because
of my ignorance of the process. But hopefully some of those question will
help you to identify how the pieces should fit together, i.e. where all the
one-to-manys lie in modelling your production process.

Thanks, I can't expect you to do the work for me! Hopefully you can see that
I have the basic relationships in place. My issue seems to be between the
mix/sub-product and the ingredients and the batch and the RM code.

By the way, big cricket game Friday?

 

[Allen Browne]

Okay a key sticking point is the relation between an inspection and the
other tables.

It seems you inspect at several points, so one of the things you might want
to consider is a bill-of-materials type structure:
http://www.mvps.org/access/modules/mdl0027.htm

With this structure, an ingredient is a "product".
The ingrediant goes into the mix, and the resultant batch is also a
"product."
The next mix is made from products: some raw ingredients, some mixes, but
since both are in the Product table, that's as very easy relationship.

So, ultimately you inspect products. With the BOM structure, the product
being inspected can be a raw ingredient, end product, or any level of
intermediate mix.

BOM is not overly easy to work with, but sometime it is the best structure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Graeme at Raptup]

Ok Allen,
I downloaded and went through that BOM sample.
I recognise that it works on a Boolean loop but I am a bit thrown by the VB
detail.
I don't understand how the Mix (Assembly) becomes my batch. The batch needs
to link to various other tables.
Neither do I understand where the raw material inspection comes in.
Note that the raw material inspection is a different process to the finished
product evaluation.
I can't help but think I have not got the normalisation right!

 

[Allen Browne]

Ultimately, it's up to you as to whether the BOM approach suits your data
best, but the core idea is that everything you inspect is in the one table:
raw ingredients, mixes, mixes that use other mixes, and end products.

The advantage of the BOM structure is the flexibility it provides. If you
don't do it this way, how will you model mixes that use other mixes? On in
your Inspection table, how will you have a foreign key field that can relate
to end products, raw ingredients, and all levels of mixes in between? Even
in your Recipe table, you need a foreign key field that could be a raw
ingredient, or could be a mix; do you see that if you have separate tables
for Ingredient and Mix, your key field cannot connect to both?

Another core concept (separate from the BOM idea) is the difference between
a mix (a list of ingredients and quantities, like a sub-recipe) and a batch
(a specific instance of a mix). A recipe must be able to refer to a mix. A
batch is made from the list in the mix, on a particular date, and goes bad
if not used. The mix is just the concoction you use to make your batches.

Hope that's helping you work through the way your data connects.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Graeme at Raptup]

Hi Allen,
I'm coming round to the BOM concept, but don't know how to link to batches
or Raw Material Inspection.
I was going to have a table 'Recipe' that has many 'Mix'. The 'RM
Inspection' (many) relates to (one) 'Ingredient'. My thinking was that the
'Ingredient' is linked to the 'Mix' rather than the 'Recipe'. However I then
get stuck assigning quantities to ingredients. Which brings me back to the
BOM..!
Would it be ok to build my other tables around the BOM? I'm thinking of
taking the BOM and changing the names (e.g. Components to Ingredients).
Clearly I'd have to go into the VB and change these.
Any suggestions on how to apply the BOM to my db?

 

[Allen Browne]

The basic idea is that a product is made from other products, so you have
tables like this:

Product table:
ProductID primary key
ProductName Text

ProductInProduct table:
TargetProductID foreign key
SourceProductID foreign key
Quantity number
UnitID foreign key

So the records in the ProductInProduct table look like this:
TargetProduct SourceProduct
========== ============
1 (Toffee pudding) 2 (toffee pudding base)
1 (Toffee pudding) 3 (toffee pudding sauce)
1 (Toffee pudding) 4 (toffee pudding icing)
2 (Toffee pud. base) 5 brown sugar
2 (Toffee pud. base) 6 butter
3 (Toffee pud. sauce) 7 water
3 (Toffee pud. sauce) 8 thickening
4 ...

A raw ingredient has an entry in the Product table only (not in
ProductInProduct.)

An intermediate product (such as a sauce) has an entry in the Product table,
and multiple entries in the ProductInProduct table (one for each of its
ingredients.)

An end product has an entry in the Product table, and multiple entries in
the ProductInProduct table (one for each ingredient, and these ingredients
may themselves be interemediate products.)

The recipes are generic instructions of how to make each product.
The batches are specific instances of the recipe being used (with a
created-date and a use-by date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Graeme at Raptup]

Allen,
Thanks a lot for your help.
I'm pretty sure I should be able to get around from here.
Cheers,
Graeme

 

[Graeme at Raptup]

Hi Allen,
please bear with me - I'm sure this is frustrating for you.
I get the concept and can see how the ingredient can be a raw ingredient or
an intermediate product.

I am a bit thrown by the lack of relationship links between the tables, so I
assume the (BOM) VB script handles this. I also don't know what the 'Form1'
function is, is it necessary?

Your tables look the same as those in BOM, Product (Component) and
ProductinProduct (Assembly). Fine. Is the UnitID a replacement of
'AssemblyBoolean'. Does it indicate a final product?

Your tables read;
Product (ProductID, ProductName)
ProductinProduct (TargetProductID, SourceProductID, Quantity, UnitID)

What happens to the Output table?
Does the recipe form a table at all? If so does it link with ingredients?
Would the batch look something like this?
Batch (BatchID, RecipeID, CreateDate, UseByDate)

Sorry about my confusion, I'm just having difficulty finding how to link the
BOM to the rest of the database.

Cheers,

Graeme

 

[Allen Browne]

Replies in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hi Allen,
please bear with me - I'm sure this is frustrating for you.
I get the concept and can see how the ingredient can be a raw ingredient
or
an intermediate product.

I am a bit thrown by the lack of relationship links between the tables,

You do create relationships between the tables I suggested.

Add a 2nd copy of the Product table to the Relationships window.
Access will alias it as Product_1.
You then create a relation from:
Product.ProductID => ProductInProduct.TargetProductID
and another relation from
Product_1.ProductID => ProductInProduct.SourceProductID

so I assume the (BOM) VB script handles this. I also don't know what
the 'Form1' function is, is it necessary?

Presumably this if from the example, which I haven't looked at for some
years.

Your tables look the same as those in BOM, Product (Component) and
ProductinProduct (Assembly). Fine. Is the UnitID a replacement of
'AssemblyBoolean'. Does it indicate a final product?

I'm guessing that in a recipe, some ingredients are measured in grams, some
in mililitres, some in cups, etc. If so, you will have a UnitOfMeasurement
table containing all the valid measurement types. The UnitID field is meant
to be a foreign key to that table. The Quantity field is just a Number (size
Double.) So the combination of Quantity + UnitID can say things like:
2 cups
500 grams

Your tables read;
Product (ProductID, ProductName)
ProductinProduct (TargetProductID, SourceProductID, Quantity, UnitID)

What happens to the Output table?
Does the recipe form a table at all? If so does it link with ingredients?

Assuming your factory has only one recipe to make any product, the
ProductInProduct table gives you the ingredients for the recipe. You might
want another related table that contains a memo field for spelling out the
steps of the recipe, but you already have the ingredients listed.

Would the batch look something like this?
Batch (BatchID, RecipeID, CreateDate, UseByDate)

Yes, that's the idea. If there is only one recipe for any product you make,
then you might be able to use ProductID instead of RecipeID. If a batch
might be double-quantity, you might want another Factor field (type Number,
size Double.)

Sorry about my confusion, I'm just having difficulty finding how to link
the
BOM to the rest of the database.

This structure is likely to be quite different from whatever you already
have in mind for the rest of the database.

 

[Graeme at Raptup]

Allen,
it's working like a charm.
THANK YOU.

FYI I have added two fields to the Product table (Yes/No) for Recipe and Mix.
That way I have created a query to list only Recipes & Mixes for user forms.
Make sense?

Also, I have created a form (Product query as above) with a subform
(ProductInProduct). I have inserted a lookup field for ProductID &
ProductName that inserts data into SourceProductID. It seems to work but is
it correct?

One more question. The memo field spelling out the steps (mix method). Why
can't I just add a field to Product.ProductID? This field is only relevant to
Mixes.

Finally, the Raw Material Inspection. As each ingredient (product) can be
inspected many times I assume that Product.ProductID is linked to a table
RawMaterialInspection (with ProductID as a foreign key)?
Thanks again,
Graeme

 

[Allen Browne]

Okay, Graeme. You are much closer to the data than my very limited
understanding of your situation permits me to be, so you are probably on the
right track here.

A single memo field to spell out the mixing steps is fine. (A related table
with a record for each step is probably overkill.)

Regarding inspections, yes: I imagine you will have a table of inspections,
indicated who (foreign key to employee table) inspected what (foreign key to
product table) when (date/time.) It will just be one table if each
inspection is considered independent of the others.

Hopefully this structure will serve you well. It's certainly very flexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Graeme at Raptup]

Damn,
I can't make the Batch/RMInspection/Product link.
On our Batch Sheet (Lets say for Toffee Pudding Base) there are many
subsections (Loaded, Mixed, Deposited, etc).
For the Loaded section there is a LoadedBy field, and then all the
ingredients (Sugar, Egg, Jam) Each ingredient has a weight (as per
ProductInProduct table) but also has two more fields: RMCode and Checked (Y/N)

I had created two more tables: Batch and RMCode
Batch => BatchID, ProductID, CreateDate, UseByDate
RMCode => RMCodeID, ProductID, Supplier, Date

Just when you think it's sorted...

 

[Graeme at Raptup]

Hi Allen,
I thought I had this figured out, but.....

I'm having trouble either (a) with my relationships or (b) setting up forms.

I need to set up a form or capture data from a table that looks like this;

Batch# Mix (ProductName)
LoadedBy
Date

RM Code Ingredient (ProductName) Weight Checked (Y/N)
:::::::::::::: ::::::::::::::: ::::::::::: ::::::::::::::::::::::
a1 Sugar 5.23 Y
ve234 Egg 2.34 Y
Water 1 N

In the relationships window I have Batch.BatchID and ProductID as a foreign
key to the Product.ProductID table. I also have a table RMCode with productID
also as a foreign key to the Product table.

When it comes to designing the forms I just can't seem to create a form +
subform that works. (I've tried a number of permutations)

I have figured out how to create a recipe form and a form to create new
batches. I have tried different queries and then adding them in the form
wizard but with no joy.
My logic tells me that my tables are right, maybe I'm linking the wrong
product table?
Or is my form design poor?

Thanks again,
Graeme

 

[Allen Browne]

Access will probably get confused when you create the subform, because you
have 2 foreign keys on this table.

Create the subform and drag it onto your main form. Still in design view,
right-click the edge of the subform control, and choose Properties. On the
Data tab of the Properties box, set the LinkMasterFields to ProductID (the
name of the field in the main form), and LinkChildFields to SourceProductID
(the name of the matching field in the subform.)

The subform should then show the ingredients for the product in the main
form.

Hope I've understood: I'm not really retaining this whole thread in my
thinking at present.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Graeme at Raptup]

I'm afraid that did not work.
What you had suggested is similar to my recipe form, where Product creates
the main form and ProductInProduct creates the subform.

However the introduction of Batch as the main form does not seem to work.

I have created two new tables:
Batch => BatchID, ProductID, CreateDate, UseByDate
RMCode => RMCodeID, ProductID, Supplier, Date

And the other tables are:
Product => ProductID, ProductName,Recipe (Y/N), Mix (Y/N)
ProductInProduct => TargetProductID, SourceProductID, Weight
and in the relationship window we have Product1 as the Product table copy.

I have linked the RM Code.ProductID (many) to Product.ProductID (one) and
the Batch.ProductID to Product.ProductID.

Am I linking the right tables?
Should I be creating some type of query before creating forms?

This is doing my nut in, I'm sure it's the same for you.

Cheers,

Graeme

 

[Allen Browne]

Graeme, I may need to let this thread go.

The BOM structure is very handy where you have things that do into other
things that go into still other things. Its strength is its flexibility. Its
weakness is that it is less obvious to set up (especially the first time you
do one.) It seems ideal for what you are doing though.

All the best

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message