Database Design for Facebook: A Social Network Database Example

[@DatabaseStar]

Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?

[@-0-__-0-]

Oh my god this is a life saving video. Youve saved me so much time. Got yourself another subscriber.

[@DatabaseStar]

That’s great to hear! I’m glad the video was helpful.

[@tylercondon3453]

Very nice video. Never really made sense how to implement the friend relationship until now.

[@DatabaseStar]

Thanks! Glad you liked it and helped you understand that kind of relationship.

[@pavankumarv9218]

Big tech companies does not use relational database for friendships, they use their own graph databases, why because, as the relationship between users keep growing, the number of rows also kepp growing, which results redundancy, so they use graph databases, where each user is a node , their relationship between other user is indicated by edge

[@azureauurora]

while watching this video I am writing my own database for my portfolio website and also I need help for the table which I don't understand quite well at the 5:00 add other profiles additional question: where can i contact you?

[@DatabaseStar]

Sure, what do you need help with for this user_profile table? I've got a contact form on my website here: www.databasestar.com/contact/

[@architech5940]

Excellent video series!

[@DatabaseStar]

Thanks!

[@nicholassmith6412]

With step 3, what are the pros and cons of the two options you mentioned: separate tables for different types of posts vs one table with several attributes?

[@DatabaseStar]

Having it in one table means that any queries to find "all types of posts" would be easier (e.g. a news feed display or a profile display). But it does mean there may be empty fields, if there is no media for a text post for example. Having separate tables means you can have more descriptive field names (e.g. photo_location inside a photos table, instead of a generic media_location field inside the posts table), making the design easier to understand. I'm not sure which would perform better with a lot of rows. Indexes on a single large table could work well. It depends on the types of queries being run, I think.

[@nicholassmith6412]

@@DatabaseStar thank you!

[@roshedulalamraju7936]

I am currently using Mysql. Can you please suggest me which database will be best for this? I am concerned about the scalability and also speed.

[@DatabaseStar]

I think that any database including MySQL should be able to handle a reasonable level of speed and scalability. Once you start approaching the size of Facebook you’ll know more about how to improve these.

[@ShawnRay]

For the friendship, why not have a table with “Owner” | User_Profile and “Friends” | List of User_Profile? Or is that not how list work?

[@DatabaseStar]

What do you mean by owner | user_profile and friends | list of user_profile? Are you referring to two tables?

[@ShawnRay]

I think I’m missing some fundamentals. I’ll watch more videos. Can a cell only contain 1 object? This is basically my question but upon some googling that is a terrible thing to do

[@kishanvaishnav2812]

Having more than one values in a single column violates 1NF rule. This design would make searching for mutual friends a very costly

[@Incnoun]

What would happen if we had billions of posts, and want to see the posts of a certain profile, wouldn't it take ages to get them? What is the solution?

[@DatabaseStar]

Good question. I get this one occasionally. Generally, databases are pretty good at handling large amounts of data (millions or tens of millions of rows). There are many optimisation techniques you can use. Indexes is one of them, and they work remarkably well. If you're getting to the "billions of rows" level, there are many other things you can do to improve performance, but that's a Facebook-level problem, where you have the budget and skills to look into it.

[@javadmoeinihadizadeh5598]

thank you very much Ben, but I think extracting a list of data (posts for example) through database each time a user load a page is time consuming, isn't it?

[@javadmoeinihadizadeh5598]

I mean, I think in your suggested model you provide a single table on database for all posts, isnt there another model to store users posts (for example) and read them from database easier and faster? thanks.

[@DatabaseStar]

Good point. There are probably some things the app could do that could help performance, such as storing some posts in memory or only loading a small amount at a time.

[@javadmoeinihadizadeh5598]

@@DatabaseStar good advice ! thanks Ben,

[@ikemkrueger]

For that you use caches like Redis.

[@MinhNguyen-ul4tk]

Hi, I am new to database design. I just want to ask about scalability. I mean in this database design, you use a table to store videos /image/text. I read some documents saying that with SQL, there is a limited amount of data that could store in each table. What if the size of videos/image/text is too large?

[@DatabaseStar]

Good question. In this design (and in general), I suggest storing the link or URL of the image or video, and the actual image or video file elsewhere on the server. This is to save space in the database and to leave the file stored in the most appropriate place. So the social network website would allow you to upload a video - for example "my_video.mp4". The application would store the database on a server somewhere (for example, "s3.mynetwork.com/my_video.mp4", and the database would store that location in the table.

[@marufbelete]

great video, I have a question tho in the relation between user_profile and post_like should't the association be one-to-one instead of one-to-many since a user can make only one like for a given post

[@DatabaseStar]

Thanks! That's a good question. Yes, a user can only make one like for a post, but a user can like many posts. So, a user_profile can have many post_likes, but a post_like can only have one user_profile. You could also create a Unique Constraint on the post_like table to ensure the post_id and profile_id is unique (to prevent duplicates)

[@fredoscott2346]

Would be interesting to see how you would create the table setup for groups

[@DatabaseStar]

That’s a good idea!

[@fredoscott2346]

@@DatabaseStar I thought that would be a little more complicated ;)

[@basheeral-momani2032]

I think for this type of system we should use document oriented db

[@DatabaseStar]

Oh, that’s a good option. What advantage would that have over a relational database?

[@basheeral-momani2032]

@@DatabaseStar every post is one doc, therefore and comments likes in one place, no joins no multi relations over many tables imagine we have 200M DailyActiveUsers

[@user-tz6nn8iw9m]

@@DatabaseStar a graph database like nebula graph, neo4j, tigergraph

[@_johncameronfernandez]

@@user-tz6nn8iw9m really?

[@_johncameronfernandez]

@@user-tz6nn8iw9m really?

[@DsamaJa]

Hi Database Star, your post_like table will grow exponentially on large user base. There are chances of table got blew up. How will you handle in such situations. Thanks

[@DatabaseStar]

Good question. Even if it has millions of rows, the database will be able to handle it. Indexes can be added to ensure fast retrieval of data.

[@lenguyenminhtan6499]

Great tutorial! Thanks for this video. It's so helpful and helps me a lot.

[@DatabaseStar]

Glad it was helpful!

[@ker6503]

Hi, I am new to a database design. The post_like ❤ table and post_comment 💬 table connected the same way to user_profile 👤 table and user_post 📋 table, but for post likes there can be only one like per post for a user and for post comments there can be many comments per user. How come? 😮

[@DatabaseStar]

Good question. It’s because I have assumed a person can only like a post once, but they can leave multiple comments. I don’t think there would be a need to like a post more than once.

[@Odyssey392]

How to make data enter from multiple devices into a single database? What is the best software for this? Very good video!

[@DatabaseStar]

You can use any relational database software to do this, such as Oracle, SQL Server, MySQL, or Postgres.

[@Odyssey392]

@@DatabaseStar thanks!

[@StepwaveMusic]

But how would this work when you social media app grows to a million people? Databases will become so large that this is not sufficient for querying, and you need graphs in some way. That's what I've always been wondering, how do the large social media apps manage that?

[@DatabaseStar]

Modern databases are built to handle millions of records in tables, so a database design like this running on Oracle or Postgres or something should be OK. However, once a company starts getting to this size, they will likely have some great developers who will look at design and performance issues and may look at ways to optimise it if there are concerns.

[@madrussian23]

This was so helpful. Thank you so much! My question is does MySQL (relational DB) have any advantages over MongoDB (document DB) for a social network? (Of course that depends on the complexity of features because I know that Mongo would be better for one-to-many relationships but SQL provides better support for joins and is better at many-to-many relationships. But if we're just talking about the simple features like in the video without finding mutual friends where the data would have to be highly interconnected, which would probably require a graph database, which database would you rather use a MySQL or MongoDB for a project like this and which one would scale better for millions of users? Thank you so much in advance, I'm just really curious about this

[@DatabaseStar]

Good question. My expertise is in relational databases and I know almost nothing about document DBs. However, I think that a relational database is good enough for a simple social network. Once you start getting more complex features, or getting towards millions of users, then you may need to analyse how the website performs and may need to redesign parts of it. Or, maybe a document DB like Mongo is a better fit overall - I'm not sure!

[@madrussian23]

@@DatabaseStar Thank you so much for your response

[@spicynoodle7419]

I would definitely use patterns like Event Sourcing and CQRS for this so I could have one source of truth with multiple types of database. For example, Mongo could be the primary DB for the front-end but for analysis and friend recommendations I would use a graph DB.

[@slyrax0737]

what is that software you are using ?

[@DatabaseStar]

I'm using LucidChart.

[@DANNYEL20122]

What is the name of this tool you used?

[@DatabaseStar]

I use a tool called LucidChart.

[@nicholassmith6412]

So useful! Your videos are very insightful and are really helping me. Thank you

[@DatabaseStar]

Glad you like it and that the videos are helping you. Are there any topics or concepts you want to learn more about?

[@nicholassmith6412]

@@DatabaseStar Perhaps knowing when it's best to use one vs multiple tables, and indexing (although I can already see you have a video on that, which is on my to-watch list ;) )

[@nicholassmith6412]

@@DatabaseStar also just had a thought: a video of writing the SQL for one of your db design videos would help demonstrate it in practice :)

[@aditikaushik68]

Awesome video

[@DatabaseStar]

Thanks!

[@burakkaraduman3814]

Hey sir great tutorial! Which program or website did you use in this video?

[@DatabaseStar]

Thanks! I used Google Slides and Lucidchart

[@noanswerrs]

awesome explaining, thank you !

[@DatabaseStar]

You're welcome!

[@devanshusinha5833]

Hi Ben, loving your videos, I have been on a binge for the past few days. I have a video request / idea you may find interesting: Most SQL tutorials / articles online have typical database design tutorials like how to do social media, or ecommerce, but seldom have I seen db designs for things like Trello. How would one go about it? We would have to keep a track of the order of the elements in the card, allow for user generated custom fields within the cards. Was wondering if you could shed some light on it.

[@DatabaseStar]

Glad you like my videos! That's a good point - I don't see many designs for applications like that. I can create a video on this in the future.

[@temiwilliams688]

Hey, what's the name of this database structure

[@DatabaseStar]

I don’t know if it has a name

[@makuruwandotcom]

Awesome, can you please share a link to download the tool you are using to draw the sketches please

[@DatabaseStar]

Sure, it's called LucidChart. You can find it at lucidchart.com

[@skywalker0823]

Very good, helps me a lot, many thanks!

[@DatabaseStar]

Glad it was helpful!

[@iremozen7172]

many thanks. it is a great tutorial!!

[@DatabaseStar]

Glad it was helpful!

[@t3ntube357]

Very nice video, I have a question sir, when designing a database I found myself most of the time using user_id as a foreign key inside most of the other tables, is that okay?

[@DatabaseStar]

Thanks! Yeah that is ok, I often do the same thing.

[@t3ntube357]

@@DatabaseStar ♥

[@apurvsingh5541]

Which database should one prefer?

[@DatabaseStar]

It depends on what you want to use it for. Postgres and MySQL are popular for personal projects but also large systems too. Oracle and sql server are popular at large companies.

[@tana_dior]

Just Perfect

[@DatabaseStar]

Thanks!

[@kuroisan2698]

this one looks like my schema but I found an issue How the frontend can get the likes number for multiple posts I mean imagine a post that has 2 milion likes should the frontend get the likes array and get the length of it I think that is wrong I thought about making a field in the posts schema just for likes number and if he wants the users who liked the post he can fetch them

[@DatabaseStar]

You can query the database the get the count of likes for a post if all you need to do is show the number. This may be quicker than getting all of the likes and getting the length of an array. You could have a number in the posts table for the number of likes. There is a risk that this would be out of date with the count of records, but you could treat this as an "approximate" number of likes.

[@Delicatamente]

Great! Ty!

[@DatabaseStar]

Happy it was helpful!

[@gauravsrivastava17]

🔥

[@DatabaseStar]

Thanks!

[@nasry-hp2re]

where is whatsup

[@DatabaseStar]

I have another video for messaging which may cover this

[@federicobau8651]

Cool by i found weird that you area changing name because "they could be reserved words".. in mysql user or post is NOT a reserved word. What i think you are trying to say is that "this are common names use in MySQL databases (or anything really) and in order to try to avoid collision we try to name it something else" but the problem with this is that you are making assumption for no reasons. also user_post or user_profile could be easily find. Normally if you create a test / learning db you would have only this table, and in case you have a running db and u actually have a name that is taken. well still a no problem, mysql would throw an error then , only then if that is the case you renamed it .. Sorry i found it funny and unecessary :D

[@DatabaseStar]

Thanks for the feedback! The word USER in MySQL, Postgres, and other databases indicates a user account on the database, and is part of the CREATE USER and ALTER USER commands. I try to avoid words that are part of existing commands so they don't cause unexpected errors. I agree that when you start creating a database you only have one or a few tables, and then you can address any conflicts. Thanks for sharing!

[@federicobau8651]

@@DatabaseStar True for user , i though of it later. Still aint a bit issue u can always Yo refer in code as with apostroph -> ' anyway nit a big deal too to call a table with a less generic name

[@totsubo2000]

Be warned, if you present this solution at a system design interview the interview will follow up with this question "how well will this scale to 1 billion users"? and "How do ensure availability?" And of course this is where this solution falls down. The design presented here won't scale. This youtuber explains the reasons: kzbin.info/www/bejne/fZ6md5KsgbWNo8k

[@DatabaseStar]

Thanks for the feedback Jean-Christian! Yes I imagine once you start thinking about scaling to 1 billion users your design (and many things) would need to change.

[@pstha4537]

u should actually create it and fill out the data and show us how it done.

[@DatabaseStar]

That’s a good idea

[@jocelyntuble1003]

This is almighty God pastor apollo carreon quiboloy heirs spiritual ministry intelligence groups inc.

[@DatabaseStar]

Thanks, I think!

[@andregant9980]

This is cluuuuuuuutch

[@DatabaseStar]

Thanks!