Table design questions

[Damon Heron]

I am creating tables for a food mfg. Various products are created in
batches that have ingredients. Sometimes the batches are mixed to create
new batches. Here is my problem: what is the most efficient way to design
my tables to track this?

As an example, here is a partial batch, I'll call Batch 1:

10% dark chocolate
90% milk chocolate

34% corn syrup
66% malt syrup

100% Vanilla extract
0% Vanillin

.............other ingredients, etc.

Notice that they all add up to 100%.

Batch Table has BatchID, BatchName
Chocolate table has ChocolateID and chocName: dark, milk, semi-sweet, bitter
Syrup table has SyrupID, SyrupName: corn, malt, molasses, maple
Vanilla table has VanillaID, VName: Extract, pure vanillin, whole beans

If I want to keep the composition of a batch, should I have a batch
composition table that has all of data in the one table, or should I have a
separate table for each ingredient? For instance, a chocComposition table
that has BatchNumber, ChocolateID, Percent?

Thanks for any input

Damon

 

[Allen Browne]

I think you are saying you have a hierarchical structure, where you make a
product from ingredients, but one of the ingredients might actually be a
previously mixed batch of ingredients?

To build that in Access, you would need to treat products and ingredients as
the same thing, i.e. put them all into one Product table.

Product table
ProductID primay key
ProductName text

ProductIngredient table
ProductID which Product this is.
IngredientID which product is an ingredient
Ratio Number.

This would enable you to enter a product, and then define which other
products make it up, and in what ratios.

 

[tina]

Chocolate table has ChocolateID and chocName: dark, milk, semi-sweet,
bitter

Syrup table has SyrupID, SyrupName: corn, malt, molasses, maple
Vanilla table has VanillaID, VName: Extract, pure vanillin, whole beans

putting data into table names - i.e. Chocolate table, Syrup table, etc - is
poor design. what you're doing is dividing the ingredients into *types*.
ingredient types should be entered as data in a field, not in table names.
suggest the following two tables to handle listing ingredients and
designating each one as a specific "type":

tblIngredientTypes
IngredTypeID (primary key)
IngredTypeName
(chocolate, vanilla, syrup, etc)

tblIngredients
IngredientID (primary key)
IngredientName
(dark chocolate, milk chocolate, corn syrup, molasses, vanilla extract, pure
vanillin, etc, etc, etc)
IngredientTypeID (foreign key from tblIngredientTypes)

all ingredients go into one table; each ingredient is one record; each
record has an ingredient type assigned to it. you can easily manipulate and
analyze the ingredients by type anytime you need to, with queries and/or
reports.

If I want to keep the composition of a batch, should I have a batch
composition table that has all of data in the one table, or should I have a
separate table for each ingredient? For instance, a chocComposition table
that has BatchNumber, ChocolateID, Percent?

again, putting data into table names is big no-no (i.e. chocCompsition
table). suggest next two tables to handle your batches:

tblBatches
BatchID (primary key)
BatchName
(any other fields that describe the batch as a whole, such as a creation
date perhaps, or a "created by" date...whatever is needed, if anything.)

tblBatchIngredients
BatchID (foreign key from tblBatches, and half of combination primary key)
IngredientID (foreign key from tblIngredients, and other half of combo pk)
Percentage
(enter as many records as necessary to list each ingredient in each batch.)

table design (normalization) and relationships is the most
difficult-to-learn aspect of building a database. it's also the most
important, by far. recommend you read up on the subject; it's an investment
of time that will repay you a thousand-fold as you build this database. a
good book on the subject is Database Design for Mere Mortals by Michael
Hernandez; you can also google "table normalization", there are a number of
good resources on the internet.

hth

 

[Chris2]

I am creating tables for a food mfg. Various products are created in
batches that have ingredients. Sometimes the batches are mixed to create
new batches. Here is my problem: what is the most efficient way to design
my tables to track this?

As an example, here is a partial batch, I'll call Batch 1:

10% dark chocolate
90% milk chocolate

34% corn syrup
66% malt syrup

100% Vanilla extract
0% Vanillin

............other ingredients, etc.

Notice that they all add up to 100%.

Damon Heron,

I apologize, but "Batch 1" appears to add up to 300%. Portions of
Batch 1 add up to 100%, but how do I know which portions of Batch 1
are meant to add up to 100%?

Batch Table has BatchID, BatchName
Chocolate table has ChocolateID and chocName: dark, milk, semi-sweet, bitter
Syrup table has SyrupID, SyrupName: corn, malt, molasses, maple
Vanilla table has VanillaID, VName: Extract, pure vanillin, whole

beans

Having a separate table for each ingredient is inadviseable. It
will cause a world of hurt when it comes to designing Queries, and it
goes against good database design principles, anyway.

If I want to keep the composition of a batch, should I have a batch
composition table that has all of data in the one table, or should I have a
separate table for each ingredient? For instance, a chocComposition table
that has BatchNumber, ChocolateID, Percent?

Thanks for any input

CREATE TABLE Ingredients
(IngedientID AUTOINCREMENT
,IngredientName TEXT(255)
,CONSTRAINT pk_Ingredient PRIMARY KEY (IngredientID)
)

IngredientID IngredientName
1 Dark Chocolate
2 Milk Chocolate
3 Semi-Sweet Chocolate
4 Bitter Chocolate
5 Corn Syrup
6 Malt Syrup
7 Chocolate Drops
8 Molasses Syrup
9 Maple Syrup
10 Vanilla Extract
11 Pure Vanilla
12 Flour
13 Shortening
..
etc.

Type/Sub-Typing can be added in as desired.



CREATE TABLE Batches
(BatchID AUTOINCREMENT
,BatchName TEXT(255)
,CONSTRAINT pk_Batches PRIMARY KEY (BatchID)
)


BatchID BatchName
1 Troll-House Cookies
2 Humble Pie


CREATE TABLE BatchIngredients
(BatchIngredientID AUTOINCREMENT
,BatchID LONG
,IngredientID LONG
,Percentage DOUBLE
,CONSTRAINT pk_BatchIngredients
PRIMARY KEY (BatchIngredientID)
,CONSTRAINT fk_BatchIngredients_Batches
FOREIGN KEY (BatchID)
REFERENCES Batches (BatchID)
,CONSTRAINT fk_BatchIngredients_Ingredients
FOREIGN KEY (IngredientID)
REFERENCES Ingredients (IngredientID)
)

Not All Ingredients are shown, so the BatchIngredients don't add up
to 100%/BatchID.
The amounts are completely made up . . .

BatchIngredientID BatchID IngredientID Percentage
1 1 7 .10
2 1 10 .5
3 2 13 .10
4 2 12 .15


Now, here is where it gets tricky. Batches can be made of batches.
This requires a recursive relationship (bill-of-materials (BOM),
"parts explosion", adjacency list, nested set, etc.).

There are several ways to implement this, but one has come to the
forefront, at least IMO.

It's Joe Celko's "nested set".

The nested set discussion is middle-level complicated.

Here's an exmaple Access database that uses the basic nested-set, and
a query that displays the contents. The only thing lacking is,
INSERT, DELETE, and UPDATE examples:
http://www.mvps.org/access/queries/qry0023.htm

This example db can be supplemented with Joe Celkos March and April
1996 DBMS Magazine articles:

March: http://www.dbmsmag.com/9603d06.html
April: http://www.dbmsmag.com/9604d06.html

 

[Damon Heron]

Thanks to you all for responding. You have pointed me in the right
direction. I appreciate it!

Damon

 

[Chris2]

Chris2,
DDL, test data, links to Celko articles - great post.

Why, thank you!

Having separate subtables could be desireable though e.g. (thinking now
of my own store cupboard): Dark Chocolate 70% cocoa solids, Dark
Chocolate 85% cocoa solids, etc. Unless in the model these entities are
considered Batches made up by the chocolate manufacturer.

Well, a lot of specifics were missing to know for sure, I was
handing out a strictly generalized statement.