Database & table structure -- Bakery question

[andreohnona]

Hi,

Context
I am a database novice currently in the process of building an access database to manage production in a manufacturing company.

Simplified Version of Problem
Suppose I wanted to build a database to manage the baking process at a bakery.

Goal is to eventually generate a report from this database that tells the baker i) which raw ingredients to use and ii) gives instructions on baking his cake from the raw ingredients.

I believe I would need the following tables:

tblRawIngredients --> contains all raw ingredients that the bakery purchases (as well as respective properties of those ingredients)

tblCake --> all cakes sold at the bakery (and their respective properties)

tblCakeIngredients --> For each cake, this table stores which Raw Ingredients are needed and in what quantities. This table links tblCake with tblIngredients in some sort of many-to-many relationship

tblCakeInstructions --> For each cake must give me instructions as to how to bake

Specific Question

How do you recommend setting-up tblCakeIngredients?
Given my inexperience, I am not sure which structure would be easiest to work with.
I could think of two options:

Option 1) Each record couples 1 ingredient with 1 cake.
The field list would be :

​

Cake (links to tblCake)

​

Ingredient (links to tblRawIngredients)

​

Quantity

In order to know the complete ingredient list for "Brownies" we'd have to pull all records where Cake = Brownies

Option 2) Each record gives the complete list of ingredients for a cake. The field list would therefore be

​

Cake(links to tblCake)

​

Ingredient_1 (links to tblRawIngredients)

​

Quantity_1

​

Ingredient_2 (links to tblRawIngredients)

​

Quantity_2

​

Ingredient_3 (links to tblRawIngredients)

​

Quantity_3

​

Ingredient_4 (links to tblRawIngredients)

​

Quantity_4

​

ETC....

Note that Ingredient_1, Ingredient_2 etc. all link to the same field (primary key) of tblRawIngredients. In this structure, cake could be the primary key.

In order to know the complete ingredient list for "Brownies" we'd pull up the 1 record where Cake = Brownies.

Which structure do you recommend? Why?

I am a little bit stuck on this problem and want to choose the best structure so that I can easily build on this structure and expand/ refine the database.

Thanks a lot!

Andre

 

[The_Doc_Man]

It looks like you are heading in more or less the right direction.

Each ingredient is an entity of one type. The things you make with ingredients are entities of another type. And you have apparently noted that while you can mix ingredients, it isn't so good to mix data types.

A junction table is how you link ingredients to finished products. It is your many-to-many table that you called TblCakeIngredients. Suppose that you have this schema...

tblCake
CakeID, code number and prime key
CakeName, what you call it
etc.

tblIngred
IngredID, code number and prime key
IngredName, what you call it
CostPerUnit, currency
UnitMeasured, text name? or code?
etc.

tblCakIng
CakeID, code number and foreign key to tblCake
IngredID, code number and foreign key to tblIngred
IngAmt, how much of the ingredient goes into this particular cake?

tblCakeRecipe
CakeID, code number and foreign key to tblCake
StepID, code number unique per recipe.
Step, text statement of what to do next.
LaborTime, number of minutes estimated for the step

You have as many elements in tblCakIng as you need to exhaustively list the ingredients. NOTE: If you use the same ingredient twice but in two different ways, it is up to you to add a sequence number or other code to show the different uses.

Now, to figure the cost of a cake, you can add the time to make it (from the labor minutes and a cost-per-minute constant or table) and the ingredient cost (from the amount for that cake from tblCakIng and the units & cost per unit from the tblIngred).

From there, the sky's the limit.

Some questions to decide: If a person wants a cake with lemon icing or chocolate icing or almond icing, are those three different cakes or do you want to allow options on a single cake. You don't have to tell me. You have to decide for yourself how you want that. Either way works. It just multiplies the number of entities you have to load to the tblCakIng table.

If a person has options, you need a table of options and a second table of options allowed for a given cake - like

tblOption
OptID, number/prime ky
OptName, name of option - like white cake, chocolate cake, almond cake, yellow cake, etc.

tblCakOpt
CakeID, FK to cake table
OptID, FK to option table
Cost, dollar value of option, currency

Then you can choose any option appearing in tblCakOpt for the selected cake.

The whole thing is finding the entities of your business and putting them in their own separate tables. Then putting together junction tables to show the ways those entities get together.

 

[andreohnona]

Thanks a lot!

I will re-work my manufacturing DB design, based on the info from this example.

Just for my personal education: why is it a bad idea to list the all ingredients in one record? (i.e. "option 2" in my original posting ) does it make it more complicated to work with the data afterwards?

best regards,

Andre

 

[The_Doc_Man]

The best reason is something called "normalization" - read up on that before you design too much.

A purely practical reason is this. Think of this for a BRIEF moment as rows and columns. Is there a practical difference in CONCEPT between having a few wide rows or a lot of narrow columns?

There is not a big difference at the mental level - but a REALLY REALLY big difference at the implementation level. Because by listing the ingredients as many records of one ingredient each, you GUARANTEE the structure of the ingredient table. You can change the amount of any single ingredient in any single recipe in a heartbeat. You can add a new ingredient trivially.

BUT if you do it the other way - one record with lots of ingredients - you can run smack-dab into the problem of having a product with 11 ingredients when your product ingredient table has 10 slots. So you have to redesign your DB. BUT doing so changes the size of every record in the table.

Doing it with lots of narrow records, you have as many ingredients as the number of records you add. Not a design change. Just a content change. Helps stability of the DB tremendously. Saves lots of space, too, because if you needed 11 slots for your complicated products but most of them only need 6 or 7 ingredients, you are wasting the space implied by those extra 4 or 5 slots.

Not only that, ...

Suppose down the road you wanted to know all of the products that use a particular ingredient? Let's say,... cinnamon powder. With the ingredients several fields to a row, you have to search EACH FIELD. With the ingredients in a list, all entries are the same format, one ingredient per entry. You can build a report that searches the ingredient list to get the product number and do the reverse lookup to get the name of the product. So let's say that someone is allergic to cinnamon and wants to know which products to avoid?

How about trying to do a global search for all products that use some ingredient because you want to make a substitution? If the ingredients are not all in the same field, you have to search as many fields as could hold ingredients. Doing it with many narrow records, you have a simpler search AND a simpler update query to make the changes.

Finally, all the Access reports and forms are oriented towards efficiency for the paradigm of having many narrow records rather than fewer but wider records.

 

[andreohnona]

Thanks a lot Doc Man!

This really helps my understanding. I have now read up on Normalization and can see things a little more clearly.

best regards.

Andre