recipe db

J

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top