Have you ever wondered how to design a database if you wanted to build the next Facebook?
In this video, I'll explain how I would design a database if I was building a
social media site like Facebook, showing you the design as I go. You could use this
as inspiration for your own project or just something you may be curious about.
Let's get into it.
So, you probably know what Facebook is already,
but it's a social network or a website that allows you to create a profile, connect with other
profiles by adding them as your friends, and share content such as text posts, photos, and videos.
While Facebook is a large and complicated system behind-the-scenes,
for the purpose of this video and this exercise,
we'll list out some of its features and aim to design a database that could handle them.
We'll pretend we're building a website that does something like what Facebook does.
We won't design or develop the website in this video. We'll just design the database.
So what kind of features would exist in a new social network that was like Facebook?
Here's a list we can use. It's a list of the basic features of Facebook, but not everything.
So, what could we do?
Sign up and create a profile Add other profiles as friends
Add posts that contain text, photos, or videos See posts that our friends have added
Like and add comments to posts that others have added
How can we create a database design that implements these features?
We can look at them one at a time, creating a design, and making some assumptions along the way.
I've described the steps to do this in detail in some of my courses inside Database Star Academy,
which you can find out more about using the link in the description.
Let's go through this process now and design a database.
Our first requirement is for users to be able to sign up and create a profile.
How would we create a database to allow them to do that?
We'll start with a blank page. I use LucidChart to draw all of my database diagrams,
but you can use pen and paper or Google Drawings or PowerPoint, or any other tool.
So, in order to create a profile, we can assume that a user needs to enter some information.
Some of this information could be required, such as an email address,
a password, and a name. Others may be optional, like country and date of birth.
Let's create our first table for storing user information. We could call it something simple and
obvious, like "user". However, user is a reserved word in many databases and we try to avoid
reserved words for table names as it can cause issues running SQL and working with the tables.
So, what can we call it? We could call it user_profile or user_account or something similar.
Let's call it user_profile, that seems good enough.
In the user_profile table, we add several attributes, which are our columns.
The attributes would be those we discussed earlier: email address,
password, name, country, and date of birth.
What do we do about the name? If you're in a country or culture
where a first name and a last name are normal, it might make sense to add two
columns and have them both mandatory. However, this is not always the case.
There's a great article called "Fallacies Programmers Believe about Names",
which I'll link to in the description below, that has a list of points that are not true
about names but seem true. For example, not everyone has a traditional last name,
not everyone has a name with at least three characters, and so on.
So, I would still suggest having two fields, and it's up to you what you call them, but I'll call
them given_name and surname. If we develop this application, we would have to determine
which name to show where if one is not filled, but for now, we could just have the two fields.
What about a primary key? It's a good idea to have a unique way of identifying this record.
There are a few ways to do it, but I recommend creating a new column to store a unique ID.
We'll call it "id".
How about a username? Usernames are common in many websites that allow users to log on.
We could add one here, but Facebook doesn't need one to log on as far as I know,
and we don't need one for our application. So let's ignore it.
The next feature was the ability to add other profiles as friends.
We can assume that the functionality to search for profiles and find profiles will exist on
the application, and we just need to allow the friends to be stored in the database.
How do we do this? A friendship is a relationship between two profiles.
There's a concept called a self join, which allows you to join a record to another record of
the same table. This is useful for hierarchical relationships, such as employees and managers
where managers are also employees, or product categories and subcategories.
However, using a self join relationship will only allow a single friend.
We want users to be able to have many friends.
This is actually a many-to-many relationship. To accomplish
this, we add a joining table to our database.
We can add the table here, and call it by the relationship between the two profiles.
We'll call it "friendship". If we have other relationship types in the future,
we can rename the table, but for now I think friendship is OK.
We add two columns: one for each side of the friendship. We'll call the columns profile_request
and profile_accept, as we can assume that one person will request friendship of another.
You might be wondering, what if a user declines the request?
Perhaps we don't add the record to the table. Or what if the other person requests a friendship?
We could add a table constraint, or an application feature, to prevent this from happening.
For now, let's move on to the next feature.
The next feature is the ability to add posts that contain text, photos, or videos.
A post is owned by a profile or a user,
so we can create a table for posts and link it to the user_profile table.
Our new table could be user_post, as the word post may be a reserved word.
We add in an id column for the primary key. We also add in the profile ID as a foreign key,
and we can call it owner_profile_id, to indicate it's the profile that owns the post.
What about different types of posts? There are a few ways we can do this.
We could have separate tables for the different types of posts:
a text_post, a video_post, and an image_post table.
Or we could have one table with several attributes. Or maybe another way.
There are pros and cons to each approach.
This is one way we can do it. We'll use a single user_post table. We can
add a text field to capture any text for the post, for written posts for example.
We can also add a media field, which can contain a photo or a video. This
could be a text value that refers to the URL on the server of the video or photo.
It's possible in some databases to store files inside the database,
inside a BLOB for example. There are pros and cons to this, but to keep it simple,
we can assume that the file will be stored on the server and this would be a reference to it.
We can also store the date and time that the post was created,
so it can be shown in the application or analysed in the data.
The next feature is the ability to see posts from others.
We can assume this is a news feed or timeline feature,
where you can see posts that others have made.
This is something that is likely built into the application,
where it queries the database to find posts and show them on screen.
We don't need to make database changes for this, so let's move to the next feature.
The last feature is the ability to like or comment on other posts.
What is a like? It's an indication that you like a post that someone else has put up.
For likes, we need to store a few things. We store the person who liked it,
and the post that was liked. A user can do this on many posts, but only once per post.
So, in our database design, we have a new table to store this information.
We want to store the post ID that was liked, the user_profile ID that liked the post.
This is a separate table so we can store multiple likes for a post. If
we just added a user_profile_liked_id to the user_post table, we could only
store one ID in that column. In this new table, we can store as many as we want.
We can also store a date and time of the like, so we can see when it was liked.
What about comments?
A comment is a text comment that someone makes on another post.
You can do this many times on the same post, or on many posts.
We would create a new table for this. We'll call it post_comment.
We want to store the post ID that is being commented on, the user profile ID who makes
the comment, the comment_text itself, and the date and time the comment was made.
Here's the final design of our database,
taking into account the features we mentioned earlier in the video.
It allows for profiles to be created for users, users to be friends with others,
users to add posts, like and comment on posts.
What about data types? The data types for these columns will depend on your database.
Generally, there are some recommended data types for each attribute,
situation, and the amount of data you're storing. For example,
the comment_text column in the comment table may be an NVARCHAR column with 1000 characters.
What about the other features that are available on Facebook and other social networks,
such as liking comments, nested comments, groups, or photo albums? We haven't included them here to
keep this design simple. But you can expand on this diagram if you want. That would be
a good way to practice your database design skills - adding new features to the database.
We've got our database design.
Let's take a look at a couple of sample queries that the application may use to find information.
This query finds the details of all of your friends. We have the friendship table
and the user_profile table, and show the records here from the user_profile table.
We've got this extra part of the query here because of how the friendship table is structured.
It's possible for a profile ID to exist in either of the two friendship columns,
so we need to cater for that, otherwise we would omit some records.
Here's another query that shows all of the comments on a specific post of yours. We've
got the post_comments table and the user_profile table, and we are filtering on a specific post ID.
This query here shows all of the text posts you have added.
We filter based on the user_profile_id. We also filter where the media is NULL,
assuming that if this field is null there are no images or videos and therefore it is a text post.
And that brings us to the end of this video on a database
design for a Facebook-style social network.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which step from this tutorial was the most helpful? Was it
how we looked at each of the features one by one,
or how there are several options for adding a feature to a database, or something else?
Thanks for watching.