Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
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 Jul 13 '11 at 10:48
    
nevermind.....:) –  user2406 Jul 13 '11 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:

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.