How to Design a Database for Instagram

📝 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...
Instagram, the photo and video-based social network, is a good example to use as an exercise in database design.
In this video, you’ll learn how to design a database as though you were creating the next Instagram. We’ll look at a few features that Instagram has, understand what they do, and create a database diagram that shows the tables and fields that could be used. We’ll see how all of these tables relate to each other and adjust the design as we go.
If you want to learn more about database design and practice your skills, this is the video for you.
Timestamps:
00:00 What we’re doing
00:45 Users
01:14 Posts
02:17 Followers
03:04 Filters and effects
04:34 Location and caption
05:36 Tags
06:11 Comments and reactions
07:27 Stories
08:26 What else can we add?
ERD used in this video: dbshostedfiles.s3.us-west-2.a...

[@joshchinwendu]

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

[@DatabaseStar]

You're welcome, glad you find it useful!

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

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

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

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

[@shabekpsr]

Very good explanation

[@DatabaseStar]

Thanks!

[@VishiVish01]

Wonderful video!

[@DatabaseStar]

Thanks!

[@Collins01]

Great video 👍

[@DatabaseStar]

Thanks!

[@mohamedsafwat1454]

great video

[@DatabaseStar]

Thanks!

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

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

[@user-rz1fi1st9s]

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.

[@lungelomkhize5816]

Mann; Thanks a Ton;

[@DatabaseStar]

You're welcome!

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

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

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

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

[@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) ?

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

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

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

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

[@AdiTrioka]

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

[@DatabaseStar]

Thanks! Sure, I can do that.

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

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

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

[@apnacloud1]

which tool u are using for drawing ER diagram

[@DatabaseStar]

I’m using LucidChart

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

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

[@sajjadhossainshuvo6975]

What app/ide did you use here to design?

[@DatabaseStar]

I used LucidChart.

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

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

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

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