Welcome back! In the last lesson, you learned how to set up a basic Flask application and connect it to a SQLite database using SQLAlchemy. You also organized your code with blueprints. Now, we are ready to take the next step: designing the models that will represent our recipes, ingredients, and reviews in the database.

Models are templates for your data. They define what kind of information your app will store and how different pieces of data relate to each other. In our cooking app, models will help us keep track of recipes, the ingredients they use, and the reviews users leave.

By the end of this lesson, you will understand how to design these models and how to represent their relationships in code using SQLAlchemy.

Before we dive in, let’s quickly remind ourselves of what we did in the previous lesson:

• We created a Flask app.
• We set up a SQLite database and connected it to our app using SQLAlchemy.
• We organized our code using blueprints.

This means our app is ready to store and manage data. Now, we need to decide what data to store and how to organize it. That’s where models come in.

Let’s start by thinking about the real-world things our app needs to keep track of:

• Recipes (like "Spaghetti Carbonara")
• Ingredients (like "eggs," "bacon," "spaghetti")
• Reviews (users rating and commenting on recipes)

To organize these, we use something called an Entity-Relationship (ER) diagram. This is a simple way to draw out how different types of data relate to each other.

Here’s a basic version for our app:

• A Recipe can have many Ingredients (and an ingredient can be used in many recipes).
• A Recipe can have many Reviews (but each review belongs to one recipe).

This diagram helps us plan how to structure our database tables and how they connect.

Before we move on, let’s clarify two important concepts you’ll see in our models: primary keys and foreign keys.

A primary key is a unique identifier for each row in a database table. It makes sure that every record can be found and referenced easily. In our models, the id field is the primary key for each table.

For example:

This means every recipe, ingredient, or review will have its own unique id.

A foreign key is a field in one table that links to the primary key of another table. It creates a relationship between two tables.

For example, in the Review model:

Here, recipe_id is a foreign key. It points to the id of a recipe, showing which recipe the review belongs to.

Foreign keys help keep data connected and consistent. For instance, a review must always be linked to a valid recipe.

• Primary keys guarantee that each record is unique and can be referenced.
• Foreign keys connect related data across tables, enforcing relationships and data integrity.

Understanding these keys is essential for designing reliable databases and building relationships between your models.

Now, let’s turn our ER diagram into actual code using SQLAlchemy. We’ll do this step by step.

First, we need to create a db object. This is the main way we interact with the database in SQLAlchemy.

This line creates a db object that we will use to define our models.

Recipes and ingredients have a many-to-many relationship. This means a recipe can have many ingredients, and an ingredient can be used in many recipes. In SQLAlchemy, we use a helper table for this.

• recipe_ingredient is a table that links recipes and ingredients.
• Each row connects one recipe to one ingredient.

Now, let’s define the Ingredient model.

• id is a unique number for each ingredient.
• name is the ingredient’s name.
• recipes is a list of recipes that use this ingredient. The back_populates argument links this relationship to the ingredients relationship in the Recipe model, so changes on one side are reflected on the other.

Next, let’s define the Recipe model.

• id is a unique number for each recipe.
• name is the recipe’s name.
• ingredients is a list of ingredients used in the recipe.
• steps will store the instructions for making the recipe (we’ll talk more about this in the next section).

Finally, let’s define the Review model.

• id is a unique number for each review.
• recipe_id links the review to a specific recipe, and if the recipe is deleted, the review will be deleted as well (ondelete='CASCADE').
• rating is the score given to the recipe.

You might wonder how to store the steps for making a recipe. In some databases, you can store arrays or lists directly, but with SQLite (and many relational databases), this is not easy.

So, we use a simple trick: store all the steps as one long string, with each step separated by a newline character ("\n").

For example, if a recipe has these steps:

1. Boil water.
2. Add pasta.
3. Cook for 10 minutes.

We store it like this:

This way, we can save all the steps in one text field. Later, when we need to show the steps, we can split the string by "\n" to get each step back.

This approach is simple and works well for our needs.

In this lesson, you learned how to:

• Plan your data using an ER diagram.
• Turn that plan into SQLAlchemy models for recipes, ingredients, and reviews.
• Handle many-to-many and one-to-many relationships in your models.
• Store recipe steps as a single string separated by newlines.

You now have the foundation to build and use these models in your cooking app. In the next practice exercises, you’ll get hands-on experience creating and working with these models in the CodeSignal IDE. This will help you get comfortable with designing and using database models in Flask.