Most Effificient Way to Store Ingredients in a Food Database

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?).

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:

• Finding food or recipe with similar ingredients

• Finding food or recipe with a specific ingredient

• 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.

StackExchange Database Administrators Q#3808, answer score: 7