1

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!

1

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
| improve this answer | |
  • I would add that recipes are likely to have more than one user, so consider a userRecipe table that can link multiple users to one recipe. I'd call the Ingredients_list table the RecipeIngredients and the directions_list table RecipeDirections. Note that this design could get more complicated if needed or wanted. You could consider having only one direction (say "sift the flour") and have it shared between several recipes. Probably not necessary, but something to consider. – paulbarbin Feb 23 '16 at 21:24
  • Thanks a lot. I figured I was almost there, and this brought me all the way. It's been a bit since my database management class, I guess I have some reviewing today. – Graham Bewley Feb 24 '16 at 22:12
0

I don't think a db schema should have a "list" table. The table itself creates that list when you have a reference key. You already see the fault in your current

  • ingredients_list
    • ingredients_list_id
    • ingredient_id
    • ingredient_id
    • ...

A row should always be bounded. If it is not than it should be a column.

  • ingredients
    • ingredient_Id
    • name
    • recipe_id
    • order (if you care)

Then you can do

Select * 
from ingredients 
where recipe_id = 1
order by order

You can see how now there's the list that you wanted for that recipe.

TLDR; your table is your list already. Don't create a table to maintain lists.

| improve this answer | |
0

You're pretty close but not quiet there. You'll want to leverage the power of a relational database model to make clean and easy JOINs, and to do that you'll want some of those columns to be rows and utilize linking tables to associate your data between tables. You'll also want to add Foreign Keys between those linking tables to insure data integrity.

To start we'll need to make your primary information tables (based on your post):

CREATE TABLE dbo.Users
(
     userID INT IDENTITY(1,1)
    ,userEmail NVARCHAR(100)
    ,userPassword NVARCHAR(100)
    ,CONSTRAINT PK_UserID PRIMARY KEY (userID)
)
GO
CREATE TABLE dbo.Recipes
(
    recipeID INT IDENTITY(1,1)
    ,recipeName NVARCHAR(100)
    ,recipeDescription NVARCHAR(500)
    ,CONSTRAINT PK_Recipes PRIMARY KEY (recipeID)
)
GO

First deviation from your initial design, I dropped the IngredientID in favor of simply making the ingredient the PK as the Ingredient names should be static and unique. This has a secondary benefit of allowing us to return the Ingredient name directly in our queries without that extra JOIN but still enforcing the Ingredient names via FK for data integrity purposes.

CREATE TABLE dbo.Ingredients
(
    ingredient NVARCHAR(100)    
    ,CONSTRAINT PK_Ingredients PRIMARY KEY (ingredient)
)
GO

Second larger deviation, you'll want to avoid adding columns in situations where a row achieves the same goal. To facilitate this change we need to add a directionOrder column so we know what order the directions go in for a particular recipe.

CREATE TABLE dbo.Directions
(
    directionID INT IDENTITY(1,1)
    ,recipeID INT
    ,directionDetails NVARCHAR(500)
    ,directionOrder INT
    ,CONSTRAINT PK_Directions PRIMARY KEY (directionID)
    ,CONSTRAINT FK_Directions_Recipes FOREIGN KEY (recipeID) REFERENCES dbo.Recipes (recipeID)
)
GO

Now that we have the basic data tables setup we can move onto the linking tables. This is where the relational aspect of a good design really shines as you'll be able to retrieve all of the above data using some very simple queries.

First up UserRecipes, basically same as what you have with a bit more robustness built in:

GO
CREATE TABLE dbo.UserRecipes
(
    userRecipeID INT IDENTITY(1,1)
    ,userID INT
    ,recipeID INT
    ,CONSTRAINT PK_userRecipeID PRIMARY KEY (userRecipeID)
    ,CONSTRAINT FK_UserRecipes_Users FOREIGN KEY (userID) REFERENCES dbo.Users (userID)
    ,CONSTRAINT FK_UserRecipes_Recipes FOREIGN KEY (recipeID) REFERENCES dbo.Recipes (recipeID)
)
GO

Finally, we'll adjust your RecipeIngredients table to a similar structure as the Directions, turning those columns into rows instead:

CREATE TABLE dbo.RecipeIngredients
(
    recipeIngredientID INT IDENTITY(1,1)
    ,recipeID INT
    ,ingredient NVARCHAR(100)
    ,CONSTRAINT PK_RecipeIngredients PRIMARY KEY (recipeIngredientID)
    ,CONSTRAINT FK_RecipeIngredients_Recipes FOREIGN KEY (recipeID) REFERENCES dbo.Recipes (recipeID)
    ,CONSTRAINT FK_RecipeIngredients_Ingredients FOREIGN KEY (ingredient) REFERENCES dbo.Ingredients (ingredient)
)
GO

Now that you have those tables setup you can easily retrieve data using something resembling these queries:

--Recipes For a User
SELECT R.recipeName
       ,R.recipeDescription
FROM dbo.UserRecipes UR
        INNER JOIN dbo.Recipes R
            ON R.recipeID = UR.recipeID
WHERE UR.userID = @UserID
ORDER BY R.recipeName ASC

--Ingredients for a Recipe
SELECT R.recipeName
      ,RI.ingredient
FROM dbo.Recipes R
        INNER JOIN dbo.RecipeIngredients RI
            ON RI.recipeID = R.recipeID
WHERE R.recipeID = @RecipeID
ORDER BY RI.ingredient ASC

--Directions for a Recipe
SELECT R.recipeName      
      ,D.directionOrder
      ,D.directionDetails      
FROM dbo.Recipes R      
        INNER JOIN dbo.Directions D
            ON D.recipeID = R.recipeID
WHERE R.recipeID = @RecipeID
ORDER BY D.directionOrder ASC

That should give you a strong base to build the rest of your application on. If you want to get really fancy you can add another table that relates a Recipe's Ingredients to specific Directions like this:

CREATE TABLE dbo.DirectionRecipeIngredients
(
    recipeDirectionIngredientID INT IDENTITY(1,1)
    ,directionID INT 
    ,recipeIngredientID INT
    ,CONSTRAINT PK_DirectionRecipeIngredients PRIMARY KEY (recipeDirectionIngredientID)
    ,CONSTRAINT FK_DirectionRecipeIngredients_RecipeDirections FOREIGN KEY (directionID) REFERENCES dbo.Directions (directionID)
    ,CONSTRAINT FK_DirectionRecipeIngredients_RecipeIngredients FOREIGN KEY (recipeIngredientID) REFERENCES dbo.RecipeIngredients (recipeIngredientID)
)
GO

You can then retrieve all directions for a recipe along with the ingredients that go along with that specific direction using a query like the this:

SELECT R.recipeName                  
       ,D.directionOrder
       ,D.directionDetails            
       ,RI.ingredient
FROM dbo.Recipes R
    INNER JOIN dbo.Directions D
        ON D.recipeID = R.recipeID
    LEFT OUTER JOIN dbo.DirectionRecipeIngredients RDRI
        ON RDRI.DirectionID = D.directionID
    LEFT OUTER JOIN dbo.RecipeIngredients RI
        ON RI.recipeIngredientID = RDRI.recipeIngredientID
WHERE R.recipeID = @RecipeID
ORDER BY D.directionOrder,RI.ingredient ASC

Optional: Depending on how generic your directions are you could take a different approach that has the distinct directions in their own table with a linking table connecting them to your recipes. This would allow you reuse directions and search recipes for particular directions a bit easier as they will be uniform based on the contents of this new Directions table. Would also allow the creation of an interface that would let you assemble a recipe from preset options. Not sure if such an idea is of any value but I thought I would lay it out there. You would have to modify some of the Linking tables to facilitate it's existence, but it would be minor change to those.

| improve this answer | |
  • This is super detailed and exactly what I was hoping for, thanks! – Graham Bewley Feb 24 '16 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.