Linking ingredients with food menu

  • Thread starter Thread starter Clement DeCastro
  • Start date Start date
C

Clement DeCastro

I'm trying to create a table(s) and I'm not sure on how to go about doing
this. I have to design a food menu database (thanks for the responses I got
for my first question). For example: An item such as small garden salad.
This is how I was asked to set it up. I have to have a menu id number as
well as all of its ingredients.

Menu Item=Small Garden Salad Menu_ID=S0001
Recipe Item=Cucumbers Menu_ID=S0011
Recipe Item=Tomatoes Menu_ID=S0019
Recipe Item=Lettuce Menu_ID=S0005
Recipe Item=Ranch Dressing Menu_ID=S0030

Then I have to break down the ingredients of the ranch dressing much like
the same way I broke down the Small Garden Salad.

Then I have to create a form and all the end user has to do is select the
Menu Item from a drop down menu list and the a picture of the menu item as
well as all the ingredients and the procedure on how to make the item will
pop up. What I need to know is should I have a separate table for each Menu
Item (there is about 75 in all)? Should I make a separate table for the
ranch dressing ingredients or can I include that with the Small garden
salad?

Any help is appreciated. Thanks.
 
create the tables as follows:

tblMenuItems
MID (primary key)
MName (name of menu item)
MDescription (and/or any other data you need to include
about each menu item)

tblFoodItems
FID (primary key)
FName (name of food item)
FDescription (and/or any other data...)

tblIngredients
IngID (primary key)
IngName (name of ingredient)
IngDescription (and/or any other data...)

these are your "parent" tables. list all the pertinent
data in each table - DON'T make multiple tables for
different menu items, etc.

now create the following tables:

tblMenuItemFoods
MF_MID (foreign key from tblMenuItems)
MF_FID (foreign key from tblFoodItems)
use the two fields to create a combination primary key for
this table.
add any other fields you need, but do NOT add any field
that is already in one of the parent tables (you don't
want to duplicate data in multiple tables).
list all the food items included in each each menu item in
this table.

tblFoodIngredients
FI_FID (foreign key from tblFoodItems)
FI_IngID (foreign key from tblIngredients)
use the two fields to create a combination primary key for
this table.
add any other fields you need, but do NOT...(see above).
list all the ingredients in each food item in this table.

these last two tables are called linking tables. they
serve as links between the "parent" tables, establishing a
one-to-many relationship with each. (of course you can
name the tables and fields whatever you want, just be
clear about what table and field holds what data.)

suggest you get a book or take a class on "data modeling",
to learn how to normalize your data, segregate it into
tables and set up appropriate table relationships. this
process is not unique to Access, but is used in building
any relational database.

hth

ps. if you would like a model of the above in Access 2000
or Access 97, you can email me and i'll send it to you.
just include the text of this post to remind me what i
wrote. ttaccess1 at yahoo dot com
 
Please e-mail me the model of this menu at (e-mail address removed) or
(e-mail address removed)

Thank you very much
 
Back
Top