// ============================================ // Instagram Data Model — Complete DBML Schema // 22 Entities | FAANG Interview Level // ============================================

// ---- ENUMS ----

Enum media_type { IMAGE VIDEO }

Enum post_type { IMAGE VIDEO CAROUSEL }

Enum notification_type { LIKE COMMENT FOLLOW TAG MENTION }

Enum message_type { TEXT MEDIA POST_SHARE }

Enum follow_status { PENDING ACCEPTED BLOCKED }

// ---- TABLES ----

Table users { user_id int [pk, increment] username varchar(30) [unique, not null] email varchar(255) [unique, not null] phone_number varchar(20) [unique] password_hash varchar(255) [not null] full_name varchar(100) [not null] bio varchar(150) profile_picture_url varchar(512) website_url varchar(255) is_private boolean [default: false] is_verified boolean [default: false] created_at timestamp [default: now()] updated_at timestamp

Note: 'Core profile details. Private/public status controls access permission.' }

Table posts { post_id int [pk, increment] user_id int [ref: > users.user_id, not null] caption varchar(2200) location_name varchar(100) latitude decimal(9,6) longitude decimal(9,6) allow_comments boolean [default: true] hide_likes boolean [default: false] created_at timestamp [default: now()] updated_at timestamp

Note: 'Primary posts collection.' }

Table post_media { media_id int [pk, increment] post_id int [ref: > posts.post_id, not null] media_url varchar(512) [not null] media_type media_type [not null] position int [default: 0] aspect_ratio varchar(10) duration_sec decimal(5,2)

Note: 'Individual image or video assets associated with a post (enables carousel structure).' }

Table post_tags { tag_id int [pk, increment] media_id int [ref: > post_media.media_id, not null] user_id int [ref: > users.user_id, not null] x_coord decimal(5,2) [not null] y_coord decimal(5,2) [not null] created_at timestamp [default: now()]

Note: 'Users tagged on pictures at specific coordinates.' }

Table post_likes { post_id int [ref: > posts.post_id, not null] user_id int [ref: > users.user_id, not null] created_at timestamp [default: now()]

indexes { (post_id, user_id) [pk] } }

Table comments { comment_id int [pk, increment] post_id int [ref: > posts.post_id, not null] user_id int [ref: > users.user_id, not null] parent_id int [ref: > comments.comment_id, null] content varchar(1000) [not null] created_at timestamp [default: now()]

Note: 'Post comments. parent_id references another comment_id for 1-level nested replies.' }

Table comment_likes { comment_id int [ref: > comments.comment_id, not null] user_id int [ref: > users.user_id, not null] created_at timestamp [default: now()]

indexes { (comment_id, user_id) [pk] } }

Table follows { follower_id int [ref: > users.user_id, not null] followee_id int [ref: > users.user_id, not null] is_pending boolean [default: false] created_at timestamp [default: now()]

indexes { (follower_id, followee_id) [pk] } }

Table close_friends { user_id int [ref: > users.user_id, not null] friend_id int [ref: > users.user_id, not null] created_at timestamp [default: now()]

indexes { (user_id, friend_id) [pk] } }

Table blocks { blocker_id int [ref: > users.user_id, not null] blocked_id int [ref: > users.user_id, not null] created_at timestamp [default: now()]

indexes { (blocker_id, blocked_id) [pk] } }

Table stories { story_id int [pk, increment] user_id int [ref: > users.user_id, not null] media_url varchar(512) [not null] media_type media_type [not null] is_close_friends_only boolean [default: false] expires_at timestamp [not null] created_at timestamp [default: now()] }

Table story_viewers { story_id int [ref: > stories.story_id, not null] viewer_id int [ref: > users.user_id, not null] viewed_at timestamp [default: now()]

indexes { (story_id, viewer_id) [pk] } }

Table story_reactions { reaction_id int [pk, increment] story_id int [ref: > stories.story_id, not null] user_id int [ref: > users.user_id, not null] reaction_type varchar(10) created_at timestamp [default: now()] }

Table reels { reel_id int [pk, increment] user_id int [ref: > users.user_id, not null] video_url varchar(512) [not null] caption varchar(2000) audio_track_name varchar(150) views_count bigint [default: 0] created_at timestamp [default: now()] }

Table reels_likes { reel_id int [ref: > reels.reel_id, not null] user_id int [ref: > users.user_id, not null] created_at timestamp [default: now()]

indexes { (reel_id, user_id) [pk] } }

Table hashtags { hashtag_id int [pk, increment] tag_name varchar(150) [unique, not null] created_at timestamp [default: now()] }

Table post_hashtags { post_id int [ref: > posts.post_id, not null] hashtag_id int [ref: > hashtags.hashtag_id, not null]

indexes { (post_id, hashtag_id) [pk] } }

Table direct_conversations { conversation_id int [pk, increment] is_group boolean [default: false] group_name varchar(100) created_at timestamp [default: now()] }

Table conversation_members { conversation_id int [ref: > direct_conversations.conversation_id, not null] user_id int [ref: > users.user_id, not null] joined_at timestamp [default: now()]

indexes { (conversation_id, user_id) [pk] } }

Table direct_messages { message_id int [pk, increment] conversation_id int [ref: > direct_conversations.conversation_id, not null] sender_id int [ref: > users.user_id, not null] message_type message_type [default: 'TEXT'] body_text text media_url varchar(512) shared_post_id int [ref: > posts.post_id] created_at timestamp [default: now()] }

Table saved_posts { save_id int [pk, increment] user_id int [ref: > users.user_id, not null] post_id int [ref: > posts.post_id, not null] collection_name varchar(50) [default: 'All Posts'] created_at timestamp [default: now()]

indexes { (user_id, post_id) [unique] } }

Table notifications { notification_id int [pk, increment] recipient_id int [ref: > users.user_id, not null] actor_id int [ref: > users.user_id, not null] notification_type notification_type [not null] post_id int [ref: > posts.post_id] comment_id int [ref: > comments.comment_id] is_read boolean [default: false] created_at timestamp [default: now()] }

// ---- TABLE GROUPS ----

TableGroup user_social_domain { users follows close_friends blocks }

TableGroup feed_post_domain { posts post_media post_tags post_likes comments comment_likes saved_posts }

TableGroup discovery_domain { hashtags post_hashtags reels reels_likes }

TableGroup story_domain { stories story_viewers story_reactions }

TableGroup messaging_notification_domain { direct_conversations conversation_members direct_messages notifications }

This content is reserved for Premium Members.