I have a database that I've called "food" and I'm a bit stumped on how to store the ingredients for a recipe. Should I create a separate table for the ingredients or just have the ingredients insert into the Recipe table as a comma-delimited list? Different recipes will not be using the "same ingredients" in that, they won't be sharing a value such as onion. If 10 ten different recipes have onions, then onion will be in the database 10 times (is that smart?).

A few tables from my database

2 Answers 2


I think ingredients should go in a master table and another table to map ingredient to recipe. The following is the basic idea:

Create table recipe
    recipe_id int not null,
    recipe_name varchar2(50),
    constraint pk_recipe primary key (recipe_id)
Create table ingredient
    ingredient_id int not null,
    ingredient_name varchar2(50),
    constraint pk_ingredient primary key (ingredient_id)
Create table food_ingredient
    fk_recipe int not null,
    fk_ingredient int not null,
    measure number,
    unit_of_measurement varchar2(10)

Please note that the script above might have some errors due to syntax.

The advantage of this approach:

  1. Finding food or recipe with similar ingredients
  2. Finding food or recipe with a specific ingredient
  3. Convert measurement, well you should incorporate some more tables to store unit of measurement and conversions. You've got the idea

Hopefully this would help.

  • Thanks Isaac. I was thinking something along these lines. Just to make sure I'm certain, unit_of_measurement would be things like Tablespoons, teaspoons, etc. correct?
    – user2406
    Commented Jul 13, 2011 at 10:48
  • nevermind.....:)
    – user2406
    Commented Jul 13, 2011 at 10:48

I would make a food table. This would have things like 'onions', 'beef', whatever. There would also be a foodID.

So something like:

(   FoodID int IDENTITY(1,1) NOT NULL,
    Food varchar(50),
    Description varchar(200),
 (  FoodID ASC))

From there I would make mapping table that would look something like this:

FoodID int,
RecipeID int)

So then when you want to get all of your foods for your recipe just join the recipe to the mapping table to the food table.

You should also put Foreign keys on from the mapping table to the other tables.

Maybe even a unique index on the mapping table so you couldn't have the same food to the same recipe more than once.

Kind of high level, but that is how I would do it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.