Designing the database
In this section, we will look at how to design the database to allow us to store information for the fitness tracking application. The following screenshot shows a mockup of the application:
Figure 1.1 – Screenshot of the sample application
Looking at these functionalities, we will look at designing a database structure that will look like the following entity relationship diagram:
Entity relationship diagram
An entity relationship diagram shows the relationships of entity sets stored in a database.
Figure 1.2 – Entity relationship diagram of our fitness application
Let’s drill further into each table to understand the data that they contain:
|
Table Name |
Description |
|
Users |
This table contains user information for login purposes. Passwords will be stored as a hash, not plaintext. |
|
Images |
This table contains images of exercises that users want to do. This table will store all the exercise images that the user uploads. |
|
Exercises |
This table contains the name of the exercise that the user wants to do. Users will define what kind of exercise they want to do. |
|
Sets |
This table contains the number of sets of each exercise that the user wants to do. |
|
Workouts |
This table contains the workouts that the user wants to do. Users define a workout as a combination of exercises with the number of sets that they want to do. |
The trade-off we are making to store images in the database is to simplify the design; in reality, this might not be suitable for bigger images and production. Now that we have defined the database structure and understand what kind of data it will store, we need to look at how to implement it. One of the major criteria that we want to focus on is to completely separate writing SQL from the code; this way, we have a clear separation between the two, which will allow higher maintainability.