recipe db

[John O'Conner]

Hi all,

I want to create a recipe db. I've created two tables:

RECIPE_TBL:
recipeID (autonumber)
Name (text)
Category (text)
Instructions (memo)

INGREDIENTS_TBL:
ingredientID (autonumber)
recipeID (number)
Quantity (text)
Measurement (text)
Ingredient (text)

Obviously a recipe has 1 or more ingredients. I thought this was best
represented in another table...but is there a better way?

Also, if how do you represent multiple lines of ingredients in a form?
For viewing, I thought a simple list box would work, but what about for
entering new recipes? What component will allow me to enter in 1 or more
ingredients, then allow another ingredient if necessary, without taking
lots of space in the form?

Thanks,
John O'Conner

 

[Pavel Romashkin]

RECIPE_TBL:
recipeID (autonumber)
Name (text)
Category (text)
Instructions (memo)

INGREDIENTS_TBL:
ingredientID (autonumber)
Ingredient (text)

Junction_TBL:
recipeID
ingredientID
IngredientAmount
UnitsOfMeasure

Make a junction table that will have recipeID and ingredientID and its
PK will be the combination of the two. Add IngredientAmount and
UnitsOfMeasure to the junction table, as it is specific to the
ingredient and recipe, not to ingredient itself. Create relationships
between the junction table and data tables PKs and enforce referential
integrity. Remove RecipeID, IngredientAmount and UnitsOfMeasure from
Ingredients table.
To make this structure accessible on a form:
1) Make a form bound to RECIPE_TBL.
2) Make a subform bound to a query such as this (air SQL, please test):
SELECT Ingredients _TBL.Ingredient AS Ingredient, Junction_TBL.recipeID
AS recipe_ID, Junction_TBL.IngredientAmount AS IngredientAmount,
Junction_TBL.UnitsOfMeasure AS UnitsOfMeasure FROM Ingredients_TBL INNER
JOIN junction_TBL ON Ingredients_TBL.ingredientID = junction_TBL.ingredientID
3) Link parent/child fields of parent form and subform on recipeID.
This should do it.
Pavel