How to Design a Database for Instagram

[@DatabaseStar]

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

[@joshchinwendu]

There not much of this on KZbin. Thanks for this tutorial

[@DatabaseStar]

You're welcome, glad you find it useful!

[@t_sizzler_2032]

I just completed a database class in my university. I'm trying now to create my own web based projects and this definitely helps me. Thank you!!!

[@DatabaseStar]

Awesome! Glad to hear it’s helpful

[@cesaredecal2230]

Love these db design videos, keep it up!

[@DatabaseStar]

Thanks! Do you have any suggested topics or areas you'd like to see a database desgn video for?

[@mostinho7]

Thanks for making these! Would love to see videos on normalization, performance, and other advanced topics such as transactions, locking etc

[@DatabaseStar]

Glad it was helpful! Sure, I can make videos on those topics.

[@sagarghare9829]

Amazing! I am following and learning from your videos nowdays :) Love from India

[@DatabaseStar]

Thanks! Glad to hear it.

[@021_dhruvaggarwal2]

Very helpful for interviews, Thanks

[@DatabaseStar]

Glad it was helpful!

[@shabekpsr]

Very good explanation

[@DatabaseStar]

Thanks!

[@sourajyotighosh4075]

Please, correct me if I am wrong. At 6:14, don't you think app_user (ID (PK)) references post_media_user_tag.user_id (FK)? I believe you may have misplaced the pointer when drawing it.

[@DatabaseStar]

Yes, you're right! The line should be pointing to post_media_user.user_id and not the field it points to in the diagram. Thanks for letting me know!

[@vusamavila]

this is very good, learned a lot and it was easy to follow and understand the design

[@DatabaseStar]

Thanks! I’m glad you found it useful.

[@Nuninecko]

Thanks and congrats for your style of teaching, in my opinion one of if not the best, specially on real-life examples. I would be very curious to see how you approach the real-life example of a simpler ERP (Enterprise Resource Planning) system.

[@DatabaseStar]

Thanks, I’m glad you like it! Sure I don’t know a lot about ERP but I’ll look into it.

[@IkechukwuOdokoro]

Great video. Thank you so much for sharing. What tool did you use for creating this sample, please?

[@DatabaseStar]

Thanks! I use LucidChart for this diagram.

[@aedrynngravitt2290]

The Relationship to the app_user to the post_media_user_tag wouldnt the app_user ID be linked to the user_id on the post_media_user_tag table for that FK? Just wanted to verify. Thanks for all the info!

[@DatabaseStar]

Good point! Yes, it should be linked to the post_media_user_tag.user_id, and not the post_media_user_tag.post_media_id. Thanks for pointing it out!

[@thebulletkin8393]

Thanks ever so much for this! I've been working on a database structure for some uni coursework and couldn't determine how the relationships between a user and the userFollower table would work. This definitely helps. One quick question though, what tool did you use to create this? I've been using vision but this tool looks far more intuitive =.

[@DatabaseStar]

Glad it was helpful! I used a tool called LucidChart for creating this diagram.

[@CTde110]

Can you make a follow up video on what indexes can be added to improve performance when loading new posts for people you follow and when listing posts by a user

[@DatabaseStar]

Yeah, good idea!

[@japan9169]

Thanks so much for this. I would really appreciate if you can send a link to the exported version of the database design so that I can easily navigate and study it properly

[@DatabaseStar]

No problem! Here's the link to an image of the design: dbshostedfiles.s3.us-west-2.amazonaws.com/dbs/erd_instagram.png I'll add it to the description too.

[@japan9169]

@@DatabaseStar thanks a lot

[@vigneshreddy9262]

Need more designs like this. What are these called I had searched for - Database Design - Database modelling But still only a few were found please upload any one application database design on weekly or 2weeks.

[@DatabaseStar]

Glad you like these kinds of videos! I think it would be called “database design” and yes I haven’t seen many others on KZbin. I can create more of them - I have a bunch planned

[@naveenchandpandey7940]

Just a query I have. where are you capturing the actual reaction of the users Like/Dislike? in the reaction table I can see only user_id and post_id

[@DatabaseStar]

Good question. For this example, I only captured Likes, so there's only one reaction to capture. If we want to capture Dislikes, we could add a new column to that reaction table. If there is only Like and Dislike, perhaps a boolean or tinyint column called is_like, which could be 1 or 0.

[@juanadearco6356]

Can you do a database design for an ecommerce website like shein?

[@DatabaseStar]

Good idea, I’ve got a video planned soon on that topic.

[@VishiVish01]

Wonderful video!

[@DatabaseStar]

Thanks!

[@pransabroy574]

Thank You

[@DatabaseStar]

You’re welcome

[@henriqueguerino7108]

Hey! I'm a data science student and currently I'm working on a project where I need to design a database for an education company (I work there). Your videos are helping a lot, but I have a question - We work with B2B and B2C and sell basically the same things for both, but have different treatments and procedures for each type. But there is this specific case in which we can treat them equally and not have to split the selling procedure for B2B and B2C. I have a table of B2B clients and another one for B2C clients. If I give PKs of the same type for them (something like a9b9-9ab9-a99b-9b9a), can I create another table where I just put them all in there and relate the selling tables with it? Or is there a better solution?

[@DatabaseStar]

I think I understand the question: you want to have a single table with all clients (both B2B and B2C clients)? If so, then yes, you can do that, and it's probably the easiest solution. I have created this video here which may help. It's about having similar records (in your case, B2B and B2C clients) and whether you should have the same or different tables: kzbin.info/www/bejne/hpiTcneqrNlmaM0

[@henriqueguerino7108]

@@DatabaseStar Thanks a lot, I'll watch it

[@kellecierion]

what is the name of this software that you are designing tables?

[@DatabaseStar]

It's called LucidChart. I can do a demo of it in a future video.

[@ludovice.m1834]

Really great content. I am grateful for it. What's the tool you are using to draw the map ?

[@DatabaseStar]

Thanks! It's called LucidChart.

[@mohamedsafwat1454]

great video

[@DatabaseStar]

Thanks!

[@Collins01]

Great video 👍

[@DatabaseStar]

Thanks!

[@apnacloud1]

which tool u are using for drawing ER diagram

[@DatabaseStar]

I’m using LucidChart

[@sajjadhossainshuvo6975]

What app/ide did you use here to design?

[@DatabaseStar]

I used LucidChart.

[@shashanksingh4708]

people can also like/react to comments , should this be added in the same likes table ?Perhaps we can add a field indicating whether its a post or a comment ...... or maybe a different table , or just add likes property to a comment ? What would you suggest ?

[@DatabaseStar]

That's true. I'd probably want to think about it a bit more, but my initial thoughts would be to add another table like the "reaction" table but it would sit between the app_user and comment table, to capture likes for comments.

[@patrycjamichaek8794]

Hi, I love this video! I have one question, how this design could be implemented in OLAP? Should I enter fake news and then create tabular model? How does it work?

[@DatabaseStar]

Good question. I'm not sure on the specifics, but you would somehow translate this into a Fact and Dimension design, depending on what you want to report on.

[@viq234]

Is crows foot still used, I'm only learnt uml

[@DatabaseStar]

As far as I know it's still used, but there are a few different types depending on which software you use.

[@akshatjainbafna]

I am using MongoDB. So What approach should I use to Social Media 1. Make Different collections(similar to tables) for likes, comments etc containing the referenceField(similar to foreign key) of Posts collection & User Collection for the user who interacted. 2. Making a List in the collections with a user_id, post_id and any interaction he made with the post. 3. Making a Entire Collection for a user where all his interactions will be stored. Ex. {_id, post_id, liked(if he liked), (list of comments that this particular user made(if any)), time of comment(if any comment made)} As MongoDB is Dynamic Database what should be right criteria for designing database

[@akshatjainbafna]

For Performance, Optimization and OLAP

[@DatabaseStar]

Hi Akshat, good question. I don't know a lot about MongoDB and these concepts, so can't really advise on this

[@akshatjainbafna]

@@DatabaseStar okay, thanks 😁

[@AdiTrioka]

Can you create one for Twitter app? Awesome videos btw, love it.

[@DatabaseStar]

Thanks! Sure, I can do that.

[@lungelomkhize5816]

Mann; Thanks a Ton;

[@DatabaseStar]

You're welcome!

[@tristanthompson7607]

What does FK and PK stand for?

[@kishorrathva4097]

They stand for Foreign Key and Private Key Respectively

[@DatabaseStar]

That's right, thanks Kishor!

[@kishorrathva4097]

@@DatabaseStar I was looking for reddit data base design actually and found this one 😅,I read article that reddit uses only one table !! if that's true its insane😃it would be interesting if you make video on the same.

[@240ping.24fps]

Is this schema design scalable? The followers table could easily get to a million records just with a few thousand users. In that case how well can indexing work?

[@DatabaseStar]

It would be scalable up to a certain point, I think. Modern relational databases can handle millions of records without issues, especially with indexing on commonly filtered fields. At some point, once your application and user base is large enough, you can investigate alternatives to improve any performance issues you get. This could be implementing features in the database, or migrating to a different technology.

[@samatzhussipov1139]

Great modeling! Can you make it in practice?)

[@DatabaseStar]

Yeah you can make it in practice! The next steps would be to create the database and then develop the application - both are not small steps though!

[@wangping7557]

Can I get the scheme?

[@DatabaseStar]

As in the SQL code to create the database? I didn’t create any code but you’re welcome to write some.

[@coderlady_]

Hi, I wonder (for a mobile app), or in general, do I need to separate a user entity from profil entity? what's advisable to merge them or separate them? even if they are the same but there are a lot of information about the user/profil, and for my project the user can only create 1 profil. Thank you

[@DatabaseStar]

Good question! If a user can only ever have one profile, then you probably don't need to separate them. You could have all of the information in one table or entity. The main reason for having a separate profile to a user entity is if a user can have multiple profiles, or if a profile can be managed by multiple users (e.g. a business that has two owners, and each owner can manage the business profile).

[@coderlady_]

@@DatabaseStar doesn't matter if the user table is huge (profile has lots of parameters) ?

[@abhijit2614]

Great video - got a lot of value out of this! Could you please consider doing a video on access controls? Post visibility is quite the feature on social media platforms. If you consider doing one, it would be nice to learn how do you support FB visibility settings such as, "this post can be seen by friends of friends".

[@DatabaseStar]

Thanks! Sure, I can do a video on that topic, that's a good idea.

[@javierrenteria3195]

where can i download the diagram that you made on this video? it's possible? thanks

[@DatabaseStar]

Sure, I've just added it here: dbshostedfiles.s3.us-west-2.amazonaws.com/dbs/erd_instagram.png I'll add it to the description too.

[@javierrenteria3195]

@@DatabaseStar thaaaaaanks!!!

[@dipenbambhaniya5288]

Hello sir, Why you didn't use Neo4j. It will be very used full in Social media apps right

[@DatabaseStar]

Good point, but I have no experience with Neo4j