Mysql – Help with database schema design

MySQLschema

I'm working on an Android application which utilizes a number of MySQL databases.

The design is as such:

  • Users have many recipes.
  • Recipes have many ingredients
  • Recipes have many directions

I understand how to implement joins, but I'm struggling with the schema I should be using to get the desired results. This is where I am so far:

  • users
    • user_id
    • user_email
    • user_password
  • recipes
    • recipe_id
    • ingredients_list_id
    • directions_list_id
  • user_recipes
    • user_id
    • recipe_id
  • ingredients_list
    • ingredients_list_id
    • ingredient_id
    • ingredient_id
    • ingredient_id
    • …
  • directions_list
    • directions_id
    • direction
    • direction
    • direction
    • …
  • ingredients
    • ingredient_id
    • ingredient_name

Note: The reason I'm using a separate database for ingredients is that I want to have a shared set of ingredients between all users, this way I can have an "ingredient of the week" with id 8067 or whatever.

I think I'm getting lost around the ingredient_list and directions_list part of the schema. It appears as though it may work, but I'm afraid I'm either over-complicating the problem or missing some crucial/more efficient join(s).

If this is the wrong place to ask this question, please let me know, so I can post elsewhere. Any help would be greatly appreciated!

Best Answer

I would suggest some reading on normalization and many-to-many relationships.

One problem you're probably running into is figuring out how many fields to include with your current ingredients and directions lists. Ingredients_list with ingredients_list_id, ingredient1, ingredient2, and ingredient3 will be insufficient for any recipe needing more than three ingredients. Bumping up the column count to N doesn't work either, when Forrest's Fancy Fruitcake needs N+1 ingredients.

Following the pattern shown in the many-to-many link, you could link ingredients back to the recipe id, where each row in ingredients_list contains one recipe_id and one ingredient_id. Then (simplifying to ignore joins) getting all ingredients for a recipe would look like SELECT [ingredient_id] FROM [ingredient_list] WHERE [recipe_id] = 1234.

Overall, I might change the schema to something like this:

  • users
    • user_id
    • user_email
    • user_password
  • recipes
    • recipe_id
    • user_id
    • recipe_name
  • ingredients_list
    • recipe_id
    • ingredient_id
    • ingredient_qty
  • directions_list
    • recipe_id
    • direction_text
    • direction_number
  • ingredients
    • ingredient_id
    • ingredient_name
Related Question