Database Design for Facebook: A Social Network Database Example

Подписаться 56 тыс.

Просмотров 46 тыс.

50% 1

📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
Are you interested in designing a database for a social network, like Facebook?
Facebook is a popular website and designing a database for a site like Facebook is a common exercise, whether you're in a database course at college or want to practice your skills.
In this video, you'll start with a series of basic features of the system, and one-by-one we'll create a database design, adding to the design at each step.
The final design will meet the requirements of the features we have identified.
You can then expand on the design, and of course modify it if you want different requirements.
A couple of links mentioned in this video:
- Falsehoods Programmers Believe About Names: www.kalzumeus.com/2010/06/17/...
- Image of final database design: dbshostedfiles.s3.us-west-2.a...
Timestamps:
00:00 Start
00:52 Features
01:39 Feature 1 - create profile
04:08 Feature 2 - add other profiles
05:41 Feature 3 - add posts
07:09 Feature 4 - see posts
07:31 Feature 5 - likes and comments on posts
08:49 Final design
09:51 Sample queries

Наука

Опубликовано:

26 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 103

@-0-__-0- 2 года назад

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

@DatabaseStar 2 года назад

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

@tylercondon3453 2 года назад

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

@DatabaseStar 2 года назад

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

@lenguyenminhtan6499 2 года назад

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

@DatabaseStar 2 года назад

Glad it was helpful!

@noanswerrs Год назад

awesome explaining, thank you !

@DatabaseStar Год назад

You're welcome!

@iremozen7172 2 года назад

many thanks. it is a great tutorial!!

@DatabaseStar 2 года назад

Glad it was helpful!

@skywalker0823 2 года назад

Very good, helps me a lot, many thanks!

@DatabaseStar 2 года назад

Glad it was helpful!

@Delicatamente 2 года назад

Great! Ty!

@DatabaseStar 2 года назад

Happy it was helpful!

@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 :)

@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.

@tana_dior 4 месяца назад

Just Perfect

@DatabaseStar 4 месяца назад

Thanks!

@gauravsrivastava17 Месяц назад

🔥

@DatabaseStar Месяц назад

Thanks!

@fredoscott2346 2 года назад

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

@DatabaseStar 2 года назад

That’s a good idea!

@fredoscott2346 2 года назад

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

@roshedulalamraju7936 2 года назад

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 2 года назад

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.

@user-cd3nn5bc6u Год назад

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)

@burakkaraduman3814 2 года назад

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

@DatabaseStar 2 года назад

Thanks! I used Google Slides and Lucidchart

@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 11 месяцев назад

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.

@sphinxz_gaming 2 года назад

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 2 года назад

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/

@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!

@MinhNguyen-ul4tk 7 месяцев назад

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 7 месяцев назад

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.

@Incnoun 4 месяца назад

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 4 месяца назад

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.

@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!

@apurvsingh5541 2 года назад

Which database should one prefer?

@DatabaseStar 2 года назад

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.

@makuruwandotcom 2 года назад

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

@DatabaseStar 2 года назад

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

@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.

@ShawnRay 7 месяцев назад

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 7 месяцев назад

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

@ShawnRay 7 месяцев назад

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

@t3ntube357 2 года назад

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 2 года назад

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

@t3ntube357 2 года назад

@@DatabaseStar ♥

@basheeral-momani2032 2 года назад

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

@DatabaseStar 2 года назад

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

@basheeral-momani2032 2 года назад

@@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 2 года назад

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

@_johncameronfernandez 2 года назад

@@user-tz6nn8iw9m really?

@_johncameronfernandez 2 года назад

@@user-tz6nn8iw9m really?

@DsamaJa 7 месяцев назад

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 7 месяцев назад

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.

@ker6503 3 месяца назад

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 3 месяца назад

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.

@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.

@temiwilliams688 2 года назад

Hey, what's the name of this database structure

@DatabaseStar 2 года назад

I don’t know if it has a name

@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.

@AA-oh3tw 4 месяца назад

how would we do dislike posts?

@DatabaseStar 4 месяца назад

You could enhance the post_like table to add a column to indicate whether the post was liked or disliked (perhaps a boolean or INT column)

@federicobau8651 5 месяцев назад

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 5 месяцев назад

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 5 месяцев назад

@@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

@pstha4537 Год назад

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

@DatabaseStar Год назад

That’s a good idea

@totsubo2000 2 года назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GmCF_vLPZmg.html

@DatabaseStar 2 года назад

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.