Recipe DB Design

[Guest]

I'm designing a Recipe database. I have one table called Recipe (with the
name, yield, times, comments, etc) and it has Ingredient1, Quantity1,
Ingredient2, Quantity2, etc. I also have an Ingredient Table with Ingredient
Name and Unit of Measure, since the NUMBER of cups of flour will change with
every recipe and I can store the NUMBER of those units with each recipe
(right?). What I'm trying to do is create the most COMPACT way of coming up
with a list of TOTAL ingredients based on the TOTAL of several recipes
selected.

 

[Rick B]

I believe there is at least one sample recipe database template on the
microsoft website. Doe is not meet your needs?

 

[Roger Carlson]

Storing valuese like Ingredient1, Quantity1, Ingredient2, Quantity2, etc is VERY BAD database design. It is nearly impossible to query the database as you describe.

Actually, you want 3 tables: Recipe, Ingredient, and RecipeIngredient (for lack of a better name).
The idea is this:
Each Recipe can have one or more Ingredients.
but
Each Ingredient can be used in one or more Recipes

This is a classic Many-to-Many relationship. So here's what you want:

Recipe Rec_Ing Ingredient
====== ========= ==========
RecipeID-------< RecID |---- IngredientID
RecipeName IngID >-----| IngredientName
.... Quantity Units
... ...

(...) stands for "other fields"

To see how to implement this design in a form (for data entry), see my website (www.rogersaccesslibrary.com) for this sample: "ImplementingM2MRelationship.mdb"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L