Recipe DB Design

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
I believe there is at least one sample recipe database template on the
microsoft website. Doe is not meet your needs?
 
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
 
Back
Top