Most Effificient Way to Store Ingredients in a Food Database

Question

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

Answer 1

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.

Answer 2

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

So something like:

CREATE TABLE FOOD
(   FoodID int IDENTITY(1,1) NOT NULL,
    Food varchar(50),
    Description varchar(200),
CONSTRAINT [PK_FOOD] PRIMARY KEY CLUSTERED 
 (  FoodID ASC))

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

CREATE TABLE FoodRecipeMap(
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.