Is my database rational correct? one Primary key to Multiple foreign keys?

Question

I have an idea that I want to execute and am very rusty on my database design when it comes to the relationships between tables. I want to be able to type in 3 ingredients into 3 <input type="text"> fields and search for all the recipes with those ingredients.

I have 3 tables...

• ingredients
• recipe
• menu

the columns that are bold are my primary keys and i want the columns in italics to be the foreign keys.

Example - iName is the primary key to iName1 iName2 and iName3.

---

INGREDIENTS

iName - iType

RECIPE

mName - iName1 - iName2 - iName3 - method

MENU

mName - mDiscription - mAllergy

---

Are the relationships between my tables efficient enough for what I want to do? and what would the join query be if I want to for example

SELECT mName, mDiscripton, mAllergy
FROM menu
WHERE iName1 = input etc etc

I already have a one table version of the database and the query I'm using works fine; I just want to redesign my database and modify my query to suit.

Answer 1

Your design is missing one table to be efficient; remove iName1 to iName3 from your recipe table and add a mapping table "recipe_ingredients" with foreign keys for recipe and one ingredient. You will then have one to many rows in that table for every recipe (look at my example). But: You can easily search for all recipes where an ingredient is used. And you can use 10000 ingredients for one recipe if necessary.

How is the table menu related to recipes? Does a menu always consist of one recipe? Otherwise also add a mapping table for that relation.

SELECT mName, mDiscription, mAllergy 
    FROM menu
        JOIN recipe ON recipe.mName = menu.mName
        JOIN recipe_ingredients ON recipe_ingredients.mName = recipe.mName
    WHERE recipe_ingredients.iName = 'whatever'

Answer 2

Please don't use 1..n colums to save a n..m connection between two tables.

As you can use one ingredient in different recipes, you will need a matching table. Those are often called table1_2_table2, or in your case recipe2ingredient. In there you store the primary keys from both tables (in most cases as a combined primary key on that table, as it often makes sense)

The same should go for menu and recipe, since you could want to use a recipe in several menus.

So

Table 1 - Menu    (ID, Name, ... whatever columns you need on top of that)
Table 2 - Recipe (ID, Name, ...whatever columns you could desire)
Table 3 - Menu2Recipe( IdMenu, IdRecipe)
Table 4 - Ingredient (ID, Name, measurement unit)
Table 5 - Ingredient2Recipe( IdRecipe, IdIngredient, Amount )