Instagram is a unique social network and a great topic for a database design exercise.
In this video, we’ll design a database for an Instagram-style app,
looking at the functionality we want and following a step-by-step process.
Welcome to the Database Star YouTube channel - the place for developers
looking to improve their database and SQL skills.
Let’s get started.
If you’re not familiar with Instagram, it’s an app that’s used to share photos and
videos with other people. In this video, we’ll pretend we’re designing an app like Instagram,
and create database tables that can be used to represent the functionality inside this app.
This is not Instagram’s real database. They likely have something a lot more complicated to handle
their system and everything it does. This is just an exercise with some of the functionality.
We’ll start with a simple table for users, which are people that use the app.
We should capture an ID to use as the primary key.
What else? Let’s add in a first name and last name. Instagram also allows us to
set a profile name which is different from our real name, so we’ll add a profile name field.
We may also want to capture a date the user signed up,
so we can see our user growth over time. Let’s add that in.
Let’s move onto posts, which is where a user adds a photo or a video to the app.
We’ll create a new table called post,
which can have an ID and a user ID for the user that posted it.
We can add a date and time that the post was made.
What about the actual photo or video? We could record this in a few ways,
such as the actual video or photo, or a link to the photo or video on the server.
But, in Instagram, you can add multiple photos or videos to a post.
This means we’ll need a separate table to store each photo or video in a post.
Let’s create this table. We’ll call it post_media, which is the media, such as photo or video,
included in a post. We’ll add an ID, the post it relates to, and a media file field.
We can work out later how this field is populated.
We should also add the position that the photo or video is in. This means whether
it’s shown as the first item, second item, or another place when it shows on the app.
The next feature we can add is the concept of followers.
This is where you, as a user, can follow other users, to see the posts they create.
You can follow many users.
So this is a many-to-many concept between the user and another user. If you want to learn
more about many-to-many relationships such as why they are used, check out this video.
We create a table here, which we can call follower. We use this to capture users that
follow other users. We can do this by adding the user ID of the user who is following, and the user
that is followed. We just have these two columns here. When a user follows someone, a record is
added to this table, and calculations can be done on this to see how many followers a user has.
Instagram has a concept of applying preset filters to photos and videos,
which change the way they look. How can we capture this in our database?
We can create a new table to record the filters, which could contain an ID and a name. We also need
to capture what is done as part of the filter. Now, my photo editing knowledge is close to zero,
so I have no idea how this is done. So for this design we could add a field,
called filter details, which could be used to capture whatever is needed. This could be expanded
to refer to a config file, or perhaps some JSON data, or something else that the app needs.
How is it related to our existing tables?
We apply a filter to a single photo or video. So we add a filter ID to the post media table.
When we post photos or videos, we can also apply effects,
such as changing the brightness or contrast. They are similar to filters but many can be applied.
We’ll capture the list of them in an effects table. We’ll add an effect ID and a name.
This table is related to a post media table. However, a photo or video can have multiple
effects, and an effect can be for multiple photos. So this is a many-to-many relationship.
We add a new table to capture this, and add in the effect ID and the post media ID.
We can also add a scale, which is where you specify the amount of the effect to add,
which is a number from 0 to 100.
So we’ve captured some modifications to the photo or video so far.
Another feature that Instagram has is tagging a location of the photo,
such as a sports stadium or a city. How can we do this?
We could have a list of locations to select from. Or we could integrate
with some kind of Google Maps API where we provide a location or search for one.
For our purposes, let’s say we want to capture the longitude and latitude of where the photo
or video applies. We can assume we can match this using some API to a more readable location.
So let’s add two fields for this into the table: one for latitude and one for longitude.
Some databases may have a data type for this information to be in one field,
but for now we can assume they are two different fields.
Another feature we can add is the ability to add a caption to a post,
which is a short description of what has been added.
We can do this by adding a new field to the post table.
There is only one caption per post, so it’s a simple change here.
On Instagram, you can tag photos by putting someone’s username on the photo,
which means they get notified of it. Tagging the user of a photo of them is a common use for this.
To capture this in our database we can relate the post media to a user.
We can tag multiple users, so this would be a many-to-many relationship, and we add a table for
this. We capture the post media ID, and the user ID that was tagged. We can also capture the X and
Y coordinates of where the tag was added, because Instagram displays the tag on the actual photo.
There is also the functionality for other users to add comments to a post.
We can capture this by adding a comment table, with an ID. We add the date and time the comment
was made, so they can be shown in an order. We also add the user who made the comment,
which is an ID of the user. We add the post the comment is for.
We’ll also need the comment that was made as a separate field.
What about replies to comments? This can be done by specifying the other comment that this comment
is in response to. This is a concept called a self-join, which is where you relate a record
to another record of the same type. We add this to our table as a new column, called something
like comment_replied_to_id, and add a join. If you want to know more about self joins,
check out this other video to see more about why they are used and an example.
Instagram allows users to like posts that other users add. We can capture this as a table between
users and posts, because a user can like many posts, and a post can be liked by many users.
Liking in Instagram is boolean - it’s either a yes or a no. We can
indicate that a user likes a post by adding a record in this table.
Now, what about Instagram Stories, one of their most popular features?
It’s a different post type to regular posts, but a similar concept.
Does it fit into our existing model? I think it almost does.
A story has one photo or video, can have effects and filters,
and is posted by a user. There are no comments allowed, but users can reply with messages.
Let’s expand our existing table to allow it to handle stories.
We could add a post_type to this table, to indicate whether it’s regular or a story.
We should also add a separate table to store the list of post types, and use a
relationship to this table. This could allow us to add other things like Reels if we want to.
We’ve already got the date and time and the user, so everything else fits.
We’ll need to build some validation in the app, to ensure that a story has only one photo or video,
and that comments are not made, but the concept of a story can be added to the database in this way.
We’ve got a pretty decent design so far for an Instagram-style app.
How would you add to this? There are many other Instagram features that we have missed,
such as messaging other users, business accounts, advertising, shopping, links, and more.
As an exercise, you could take this design we have and add enhancements to it,
to include other features.
But the design we have will capture a good amount of Instagram features.
We’ve got a few many to many relationships, we’ve created some tables and edited them
as we learned more, and seen that our design captures different types of posts.
If you liked this example of a database design,
then watch my other video on designing a social networking site like Facebook here.
You may also be interested in my other video on 7 database design mistakes to avoid.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.