Creating a Recipe Database

Question

I am in the process of structuring a SQL database for a recipe website that I am playing around with. I am new to the many-to-many style and am not confident in what I have so far. Inevitably, what I would like the end result to do is to have a solid set up where users can:

1. Add a recipe to the database

2. Sort by prep-time, cook-time, cuisine, category, food category and course

3. Ability to generate a shopping list from a weekly meal plan by adding the total amount of ingredients from the recipes selected. (This is the most important!)

I will challenge myself to work out how to add the ingredients from the recipes to generate a shopping list, but the question for this post is "How should I structure my tables and connect everything?"

This is what I have so far in my SQL database:

If you think you have the answer please structure your tables similar to mine. I am a visual learner and it would be much appreciated. Also, if you could explain what I may or may not be missing would be greatly appreciated.

- Recipe(table)
 - recipe_id (primary key)
 - recipe_name
 - recipe_description
 - course
 - food_category
 - cuisine
 - prep_time
 - cook_time

- Ingredients(table)
 - ingredient_id (primary key)
 - ingredient_name
 - recipe_id (foreign key)

- Quantity(table)
 - quantity_id (primary key)
 - recipe_id (foreign key)
 - ingredient_id (foreign key)
 - ingredient_quantity
 - ingredient_measurement

- Recipe Steps(table)
 - step_id (primary key)
 - step_description
 - recipe_id (foreign key)

- Join(table)*
 - join_id (primary key)
 - recipes_id(foreign key)
 - ingredients_id(foreign key)
 - quantity

// I do not know if I need this 'join' table since 'quantity' seems to be similar

Thank you in advance!

Answer 1

As I'm not 100% sure what you want. I assume you're asking what might be the best database and table structure for your project. As I'm not completely sure what the scope of the project entails, I can only provide the following based on assumptions of field names and assumptions of project structure.

Database and table structure

       Recipe(table)

• recipe_id int primary_key auto_increment
• course_id int foreign_key
• food_category_id int foreign_key
• recipe_name varchar
• recipe_description varchar
• cuisine not sure
• prep_time time

• cook_time time
  
  Food Category(table)

• food_category_id int primary_key auto_increment

• food_category_name varchar
  
  Course(table)

• course_id int primary_key auto_increment

• course_name varchar

• other course properties maybe, such as time of day?
  
  Ingredients(table)

• ingredient_id int primary_key auto_increment

• ingredient_name varchar

• other ingredient properties maybe?
  
  Quantity(table)

• quantity_id int primary_key auto_increment

• recipe_id int foreign_key
• ingredient_id int foreign_key
• ingredient_measurement_id int foreign_key

• ingredient_quantity float
  
  Measurements(table)

• measurement_id int primary_key auto_increment

• measurement_name varchar
  
  Recipe Steps(table)

• step_id int primary_key auto_increment

• recipe_id int foreign_id
• step_number int
• step_description varchar

The Join table is useless as you'd be joining the tables in your SELECT query i.e. the following queries

---

Example queries

Getting recipe details - Recipe query
Assuming a search parameter has been entered - i.e. chocolate

SELECT
        r.recipe_id AS recipeID,
        r.recipe_name AS recipeName,
        r.recipe_description AS recipeDescription,
        r.cuisine AS cuisine,
        r.prep_time AS prepTime,
        r.cook_time AS cookTime,
        c.course_name AS course,
        f.food_category_name AS foodCategory
    FROM Recipe AS r
    JOIN course AS c ON c.course_id = r.course_id
    JOIN food_category AS fc ON fc.food_category_id = r.food_category_id
    WHERE r.recipe_name LIKE '%chocolate%'
        AND c.course_id = 1 -- i.e. breakfast as course_name
    ORDER BY r.prep_time -- for example

Assuming the user has selected a recipe (with ID = 123) in the list of recipes that showed up with "chocolate" in the name

Listing ingredients (using recipeID from user-selected recipe)

SELECT
        i.ingredient_name AS ingredientName,
        q.ingredient_quantity AS ingredientQuantity,
        m.measurement_name AS measurementName
    FROM ingredients AS i
    JOIN quantity AS q ON q.ingredient_id = i.ingredient_id
    JOIN measurements AS m ON m.measurement_id = q.ingredient_measurement_id
    JOIN recipe AS r ON r.recipe_id = q.recipe_id
    WHERE r.recipe_id = 123
    ORDER BY i.ingredient_name ASC

Listing steps (using recipeID from user-selected recipe)

SELECT
        s.step_number AS stepNo,
        s.step_description AS stepDescription
    FROM steps AS s
    JOIN recipe AS r ON r.recipe_id = s.recipe_id
    WHERE r.recipe_id = 123
    ORDER BY s.step_number ASC

---

P.S. the more control you have over the content in the database (i.e. the course names - your initial structure does not involve any control over this content) the better and easier it will be for you in the end. For example: If a user creates a recipe with the course "Breakfast" and another creates a recipe with the course "Brekfast" and you try to organize your recipes by course "breakfast" you're going to run into some fun issues and you're going to have to either forget about that method of organization or manipulate your database to accommodate for something that should have been done initially