3 Relational Data Model Examples

The foundation of any app is a solid database structure. You have the idea for an app, and the user stories mapped out. But before you write a single line of code, you should dedicate some time to think through what data tables are necessary to support the app, and how they relate to one another. I cannot emphasize how important taking the time to plan our your data structure is. While you can make changes down the line, it can (and very likely will) be a giant headache. It pays dividends to take the time upfront, just trust me on this one.

This blog is very example-driven, with the hopes of giving you some tangible examples of data modeling in action. I find that using real examples helps solidify my learning. So let’s start small with a simple to do list app. In this app, users will be able to track to-do list tasks. What data tables will we need to track the data in our app?

First we will need a Users table to track all of the users in our app. Each user will have a single entry or row in the data table with their respective information:

Next we will need a table to track the to do list tasks. It might include the following columns:

The next step is to determine how these tables will relate. I always find it helpful to take a step back and speak in logically human terms to make your relationships — don’t get caught up thinking about the table or any code. Think back to our app concept — a user will be able to create tasks to add to their to do list. Each single user will have many tasks. Each single task will belong to a single user. So we need a way to link a user to their respective tasks in the Tasks table. To do this, we can add what is called a foreign key to the Tasks table:

A foreign key is a column in one table, that refers to a primary key in another table. A primary key is a column in a table that uniquely identifies one specific record, or row, in that table. For example, in the Users table, the primary key (ID), uniquely identifies each individual user in our table. So when we reference the user_id in the Tasks table, it is a foreign key.

A common way to visualize data tables and relationships is to draw an entity relationship diagram or ERD. An ERD is a type of structural diagram used in database design. An ERD visualizes two important pieces of information: the major entities or models within the scope of the app (or whatever system you are building), and the relationships among these entities.

There are several tools out there to help you make ERDs. I like to use Draw.IO (it’s free and has VS Code integration), but anything will work — even the back of a napkin. The important thing is that you actually draw it out.

Here’s how the ERD of our to do list app might look:

Arrows are used to show the relationship between the two models. Note how the arrow has a special end. This is called “crows feet” and is used whenever a model has a one-to-many relationship. In this case since a single user has many tasks, we use an arrow with the crows feet end.

So there you have it — a simple example of how you may design a database to support a to do list app. The remainder of this blog includes several ERD examples ranging in complexity. If you’re looking for some practice, take a few minutes to draw up your own ERD before looking at my possible solution.

Example: Social Media App

App Overview: A social media app where users can post messages, reply to previous posts, and favorite posts.

ERD:

The breakdown: Based on the brief overview, there are several possible solutions to make an ERD for this app. Here’s how I decide to map the data:

• Each user has many posts, replies, and favorites.
• Each post belongs to a single user user.
• Each post also has many replies and many favorites.
• Each reply belongs to both a user and a post.
• Each favorite belongs to both a user and a post.

Example: Workout Studio App

App Overview: A workout studio created an app to allow members to register for classes. The studio has a variety of class types and several different instructors.

ERD:

The breakdown: Once again, there are several possible solutions, but here’s how my model breaks down:

• Each Member has many signups.
• Each signup belongs to a single Member and a single scheduled class. (e.g., Member #1 is signed up for scheduled class #211, “Spin (60 mins) with Brian on Wednesday August 25, 2021 from 2–3pm”)
• The Class Schedule represents the overall schedule for the studio. It includes the unique combinations of classes, instructors, and date/time.
• Each Class, can appear on the class schedule numerous times (e.g., “Bikram Yoga (40 mins)” is scheduled on Mondays, Wednesdays and Fridays at 8am and 6pm.)
• Each Class also belongs to a single type (e.g., “Bikram Yoga (40 mins)” belongs to “Yoga”)
• Each type (e.g., yoga, pilates, spin), has many classes.
• Each instructor, has many scheduled classes.

Hope this helps! Feel free to comment with any variations to the example solutions or any examples of your own.

References:

https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/