For an instructor lead, in-depth look at learning SQL click below.
Structured Query Language (SQL) is a highly popular database language because it is not only robust and versatile, but also very easy to learn. In this blog post, I would like to share with you how to create a recipe sharing platform by using SQL.
Database Design
Before we get to the code, it’s essential to understand the layout of our database. We need at least three tables: users, recipes, and reviews.
- Users table will store information about our users like their ID, name, and email.
- Recipes table will keep recipes details shared by our users, like recipe ID, user ID (who shared it), name of the dish, ingredients, and directions.
- Reviews table will contain feedbacks given by users for each recipe including review ID, recipe ID, user ID (who reviewed), rating, and comment.
Now, let’s start creating our tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE Users ( UserID INT PRIMARY KEY, Name VARCHAR(100), Email VARCHAR(100) ); CREATE TABLE Recipes ( RecipeID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES Users(UserID), DishName VARCHAR(100), Ingredients TEXT, Directions TEXT ); CREATE TABLE Reviews ( ReviewID INT PRIMARY KEY, RecipeID INT FOREIGN KEY REFERENCES Recipes(RecipeID), UserID INT FOREIGN KEY REFERENCES Users(UserID), Rating INT, Comment TEXT ); |
Basic SQL Operations
Let’s move to the CRUD (Create, Read, Update, Delete) operations:
Adding a new recipe
|
1 2 3 4 |
INSERT INTO Recipes (RecipeID, UserID, DishName, Ingredients, Directions) VALUES (1, 1, 'Spaghetti Bolognese', 'Spaghetti, Minced Meat, Tomato Sauce', 'Cook and combine all ingredients') |
Showing all recipes from a specific user (UserID = 2)
|
1 2 3 |
SELECT * FROM Recipes WHERE UserID = 2; |
Updating a recipe
|
1 2 3 4 5 |
UPDATE Recipes SET Ingredients = 'Spaghetti, Minced Meat, Tomato Sauce, Parmesan' WHERE RecipeID = 1 |
Deleting a recipe
|
1 2 3 4 |
DELETE FROM Recipes WHERE RecipeID = 1; |
Conclusion
SQL is a powerful tool for managing and manipulating databases. With a basic understanding of SQL, you can effectively build, manage and operate a recipe-sharing platform. Keep practicing, keep learning, and remember – the kitchen of data management is always open!