CODEWITHSUNDEEP
phpmysqlsqldatabase
ManuelSchneid3r
ManuelSchneid3r

Reputation: 16091

SQLTable with links to rows in an other table

I want to realize a database for recipes. One table should hold ingredents and its units and the other should hold recipes for dishes. But somehow I dont know how to realize it. One entry in the dishes table holds a name for the dish and several ids from the ingredents table with a factor (double) that tell how much units of that ingredent.

Now my question is, how can I get this entry for a dish to contain several entries with ingredentID and factor?

Upvotes: 2

Views: 59

Answers (3)

John Woo
John Woo

Reputation: 263723

Basically you can create three tables for this having many-to-many relationship

The list of all Recipes

CREATE TABLE RecipeList
(
    RecipeID INT, 
    RecipeName VARCHAR(50),
    -- .... other fields,
    CONSTRAINT tb_pk PRIMARY (RecipeID),
    CONSTRAINT tb_uq PRIMARY (RecipeName),
)

The list of all Ingredients

CREATE TABLE IngredientList
(
    IngredientID INT, 
    IngredientName VARCHAR(50),
    -- .... other fields,
    CONSTRAINT tb_pk1 PRIMARY (IngredientID),
    CONSTRAINT tb_uq1 PRIMARY (IngredientName),
)

and another table which holds the ingredients for each recipe

CREATE TABLE IngredientList
(
    IngredientID INT, 
    RecipeName INT,
    Amount INT,
    Units INT,
    InnerAmountInGrams DECIMAL(10,2),
    -- .... other fields,
    CONSTRAINT tb_pk3 PRIMARY KEY (IngredientID, RecipeName),
    CONSTRAINT tb_fk1 FOREIGN KEY (RecipeID) 
        REFERENCES  RecipeList(IngredientID),
    CONSTRAINT tb_fk2 FOREIGN KEY (RecipeName) 
        REFERENCES IngredientList(IngredientID),
)

Upvotes: 1

Randy
Randy

Reputation: 16677

you need at least one more table.

dish
-----
dish_id
name
other_things

dish_ingredient
----------------
dish_id
ingredient_id
amount
order?
mixing_instructions?
other_stuff

ingredient
-----------
ingredient_id
other_stuff

Upvotes: 1

ozahorulia
ozahorulia

Reputation: 10084

I'd suggest you to use 3 tables:

  1. Dishes (contains dish id, name and some general information)
  2. Ingredents (a list of all existing ingredents)
  3. Recipiec (table with 3 columns: dish id, ingredent id and number of this ingredent should be used for current dish).

So every dish will have a few rows: one row for each ingredent.

Upvotes: 5

Related Questions