For an instructor lead, in-depth look at learning SQL click below.
In this blog, we’ll delve into how SQL can be used to design a personal health and fitness goal setting and tracking application. Most fitness applications are data-driven, meaning they need to effectively store, retrieve, and manipulate data – common tasks where SQL shines.
Database Design
To begin with, we need to create a database to store our information. We will have tables for users, goals, activities, and activity logs. ‘Users’ hold each user’s information, ‘goals’ for each user’s set goals, ‘activities’ for the different types of exercises, and ‘activity logs’ is used to record when a user completes an exercise.
Creating the Database
|
1 2 3 |
CREATE DATABASE FitnessApp; |
Creating Tables
Next, let’s create the tables mentioned above using SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
USE FitnessApp; CREATE TABLE Users( UserID INT PRIMARY KEY, UserName NVARCHAR(100), UserEmail NVARCHAR(100) ); CREATE TABLE Goals( GoalID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES Users(UserID), Goal VARCHAR(255), TargetDate DATE ); CREATE TABLE Activities( ActivityID INT PRIMARY KEY, ActivityName NVARCHAR(100), CaloriesBurnedPerMinute INT ); CREATE TABLE ActivityLogs( LogID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES Users(UserID), ActivityID INT FOREIGN KEY REFERENCES Activities(ActivityID), ActivityStartDateAndTime DATETIME, DurationMinutes INT ); |
Setting and Retrieving Goals
With our tables set up, we can now create, view, and track our fitness goals. For example, to add a new goal and retrieve it:
|
1 2 3 4 5 |
INSERT INTO Goals(UserID, Goal, TargetDate) VALUES(1, 'Run 5 miles', '2022-12-31'); SELECT * FROM Goals WHERE UserID = 1; |
The above SQL statements add a new goal for the user with UserID 1, to run 5 miles by the end of the year 2022. Then, it retrieves the set goals for the same user.
Logging & Tracking Activities
Whenever a user performs an activity, we log it in the ‘ActivityLogs’ table. The following queries show how to insert the log and calculate the total calories burned:
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO ActivityLogs(UserID, ActivityID, ActivityStartDateAndTime, DurationMinutes) VALUES(1, 2, GETDATE(), 30); SELECT SUM(a.CaloriesBurnedPerMinute * l.DurationMinutes) as TotalCaloriesBurned FROM ActivityLogs l JOIN Activities a on a.ActivityID = l.ActivityID WHERE l.UserID = 1; |
The above SQL statements log 30 minutes of an activity for the user with UserID 1, then it calculates the total calories burned by the same user by multiplying the calories burned per minute by the number of minutes the activity was performed.
Conclusion
The above examples illustrated the core of using SQL for building a personal health andfit ness goal tracking application. But remember, real world applications will involve more complex queries and would need to consider security, performance, and efficiency. Happy coding!