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.

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

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

@DatabaseStar

2 жыл бұрын

Glad it was helpful!

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.

awesome explaining, thank you !

@DatabaseStar

Жыл бұрын

You're welcome!

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

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)

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 ;)

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!

Very good, helps me a lot, many thanks!

@DatabaseStar

2 жыл бұрын

Glad it was helpful!

many thanks. it is a great tutorial!!

@DatabaseStar

2 жыл бұрын

Glad it was helpful!

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.

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/

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.

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

@DatabaseStar

2 жыл бұрын

Thanks! I used Google Slides and Lucidchart

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.

Great! Ty!

@DatabaseStar

2 жыл бұрын

Happy it was helpful!

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.

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.

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.

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

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.

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.

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!

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

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.

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 ♥

🔥

@DatabaseStar

Ай бұрын

Thanks!

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?

Just Perfect

@DatabaseStar

4 ай бұрын

Thanks!

what is that software you are using ?

@DatabaseStar

Жыл бұрын

I'm using LucidChart.

Hey, what's the name of this database structure

@DatabaseStar

2 жыл бұрын

I don’t know if it has a name

What is the name of this tool you used?

@DatabaseStar

Жыл бұрын

I use a tool called LucidChart.

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)

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.

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

4 ай бұрын

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

4 ай бұрын

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

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

@DatabaseStar

Жыл бұрын

That’s a good idea

where is whatsup

@DatabaseStar

3 ай бұрын

I have another video for messaging which may cover this

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: kzread.info/dash/bejne/eaF3qMGvfLPAnco.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.

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

@DatabaseStar

7 ай бұрын

Thanks, I think!

This is cluuuuuuuutch

@DatabaseStar

2 жыл бұрын

Thanks!