Building a biking app using SQL Server

An overview of the user research and testing that led us to define critical data to store and develop our app, INCycle, on SQL Server

PART I — BACKGROUND

Welcome to the Biking Capital of the Midwest

Bloomington is not only home to one of the premier cycling events in the nation (the Little 500), an award-winning bike tour (The Hilly Hundred), and the location of an Oscar-winning film on biking (Breaking Away), but is also ranked 1st in the Midwest for the most bicycle commuters per capita in the Midwest.

No other part of the Midwest has access to the diversity of riding options that southern Indiana has, from its topography to its trails. Whether it’s road or mountain biking (MTB), Bloomington embodies the terrain diversity that is essential for a well-rounded cycling experience.

The Challenge

Despite its rich culture, Bloomington riders don’t have efficient, real-time GPS navigation available to them. This is especially vital when considering the challenges of intensive cycle training, the variety of terrains and landscapes available, and the mixture of biker types (Road, Gravel, MTB, Leisure — which includes busy students who just want to get to their classes as fast as possible). The INCycle app aims to solve this problem by designing a navigation interface with route options that vary between different categories of rides, such as the quickest, most scenic, or training-specific routes, and tracking their achievements.

PART II — RESEARCH

Research & User Experience

We began our research with a comparative analysis by collecting and comparing observations and key features from existing cycling apps. Most apps did not feature customizable routes or pre-built custom routes in Bloomington. They also do not distinguish cross-training across various terrains in MTB trails.

Navigation Habits

To gain insights of the key motivations and preferences of people who use navigation apps, we asked 20 cyclists about their habits. Out of the 20, 8 were leisure riders, 7 were road cyclists and training to compete in the Little 500, and 5 were mountain bikers. Our goal was to ask users about how they interact with navigation apps when cycling. Our key findings were as follows:

1. Do not like to check for directions. “I don’t like having to stop frequently to check for directions, so I usually add a stop in Google Maps if I need to go somewhere else nearby.” Although most users liked to pull over to check for directions if they need to add a stop, they were concerned about spending too much time doing it and would prefer to know the route beforehand to avoid it altogether.
2. Rut of familiarity. “I take the same bike route to class everyday. I’m not sure if it’s the fastest or the best-looking, but it’s familiar so I do that. It gets boring sometimes.” Users, especially leisure riders, ride for either convenience or speed to get there on time. They have a set route they have gotten used to, but are not aware of faster, more scenic, available routes.
3. Offline maps. “I don’t like to use data while using navigation apps.” Users expressed frustration with navigation apps that cannot save their route as soon as they lost internet.
4. Google Maps is primarily used but lacks in some features. “I like using Google Maps because it shows me a fast route. But you can’t use it for any MTB trails in Brown County, you’d have to look that up yourself.” 80% of users preferred Google Maps for their rides because they find it reliable, easy to use, able to add stops. However, they disliked the lack of customization, and MTB are not able to use Google Maps to see their trails.
5. Feeling accomplished and recording data. “When I’m training for the Little 500, I always have my Fitbit on me. I like tracking what I’ve done and getting small awards for it.” Users like the idea of gamifying their experience, as Strava accomplishes.
6. Preview difficulty. Cyclists like to know about the elevation, distance, and duration before they begin their ride. Elevation profile was rated as the most important for cyclists in order to know what hills they will encounter during their ride.

Key Features

Taking our User Persona into account, we began to brainstorm and prioritize potential app features into three categories: Need to have, nice to have, and not needed. We identified the following features that the site should have according to our user’s needs:

1. Pick a route type. Upon opening the application, a dashboard appears with recommended routes; however, members still have the option of selecting or creating a route to pedal to their desired destination. In addition, members will also have the option of selecting their desired route difficulty based on the surrounding conditions and geographies.
2. Map it out. After selecting the type of route and difficulty, members will be able to map out a starting and ending point for the route. Recommendations for stops, such as restaurants, cafes, or sights and landmarks, will automatically pop up once the route has been selected. Members will have the ability to select stops along the route if they desire, and the app’s mapping function will guide them there.
3. Feedback. Within the application, there will also be a feedback tab for members to provide information on the application’s functionality as well as additional information about routes for each region.
4. Customize routes and ratings. Members will be able to “like” routes as well as stops, all of which can be stored in a personalized favorites folder for future use. Members will be directed to rate the route on a scale from 1–5, 5 being the most enjoyable. The rating feature will help other users in selecting routes they wish to ride.
5. Gamefication. By running queries based on user’s data on how many miles and time they’ve traveled, we plan to implement a feature to “award” the user for their accomplishments, strengthening their connection to the app and motivating them to stay with the app for more rewards.

PART III — DESIGN

Low-fi prototyping and user flow

Upon creating the user flow, we began to brainstorm and visualize our ideas into an early paper prototype. We began our user testing with this prototype and tweaked our iteration based off the user feedback we received. One of the biggest changes we made was on the profile screen, in which the user is able to see their awards and statistics all-in-one (users did not like to swipe between each because it adds an extra step to click). We also added time and distance tracking for each route so the user can immediately see the data.

High-fidelity prototype

We used Adobe XD to prototype the app design for INCycle, following the guidelines as defined by the UX navigation principles in this research study.

Click here to interact with our prototype.

PART IV — DEVELOPMENT

Database Design

Defining Data with Conceptual and Logical ER Diagram

While considering all of our features, we had to consider what sort of tables would be feasible to create in SQL Server, and what sort of data we needed to extract from the user moving forward. Pulling map data by connecting with Google Maps and integrating available online information of Bloomington trails using local resources, we distinguished the type of data needed to be included in the ER diagram and what attributes should be included in each table.

Data Dictionary

After creating the conceptual design, we added cardinal relationships to each table to connect each table. Using this as a framework, we created a data dictionary for each attribute, clarifying the data type, data validation, and primary keys for every table that we expected to include in the database. A sample is below, but you can find the entire Excel sheet here.

And then: the fun part. Creating the database! You can access part of our code here.

Running Complex Queries

To take it even further, we ran queries to be able to find users with X amount of miles have been ridden, and gamefied their experience by creating rewards for each type of accomplishment. We also wrote queries to rank users to be able to share and compete with friends, an additional social component we hope to release in future iterations.

Here’s one example: There are several different awards users can achieve that will be displayed on the profile. For beginning users, there are three main awards: when a user has completed an easy trail, they will receive the N00b award. When a user has completed a moderately difficult trail, they will upgrade to the Racer award. When a user has completed a master trail, they will upgrade to the bike ninja award. We needed to figure out which new riders that have joined within the last 6 months have received which beginner awards.

Sample of our queries to derive awards

PART V — REFLECTION

Blueprint the user experience and database necessities before jumping into the code.

Our initial steps before development, from drawing out the insights to user testing helped outline what we needed to create. The ER diagram and data dictionary were especially vital tools in planning, without which would have been much tougher to develop. Organization and thinking ahead to how this could translate in code, even at the earliest stages, is essential to create a successful database.

Learning how to work with complex queries.

This was my first time using SQL for a big project and building something from scratch. We realized how powerful SQL Server could be once we got deeper into the development, especially when we could started to add potential gamefication or exclusive membership features and run a multitude to queries to award users, extracting data from their rides.