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.
