Step 3: Relationships Explained — Instagram

Relationship Map Overview

The Instagram data model features 20+ distinct relationships designed to scale. It maps the asymmetric social graph, post/media carousels, comments, ephemeral stories, discovery hashtags, and the direct messaging engine.

1. Social Graph & Privacy Relationships

users ↔ users via follows (M:N — Asymmetric)

Unlike Facebook's bidirectional friend model, Instagram relies on a unidirectional follow system.

users.user_id (follower) ←→ follows.follower_id
users.user_id (followee) ←→ follows.followee_id

Cardinality: Many-to-Many. A user can follow many users, and be followed by many users. Privacy Rules: If the followee's account is_private = TRUE, the follow relation is inserted with is_pending = TRUE until approved.

Query Example — Finding mutual followers (followers who follow each other):

SELECT f1.followee_id AS mutual_user_id, u.username
FROM follows f1
JOIN follows f2 ON f1.followee_id = f2.follower_id AND f1.follower_id = f2.followee_id
JOIN users u ON f1.followee_id = u.user_id
WHERE f1.follower_id = :user_id AND f1.is_pending = FALSE AND f2.is_pending = FALSE;

users ↔ users via close_friends (1:N)

A sub-list of followers designated for close-friends-only stories.

users.user_id ←→ close_friends.user_id

users ↔ users via blocks (1:N)

Blocks restrict messaging, tagging, commenting, and viewing profiles.

users.user_id (blocker) ←→ blocks.blocker_id
users.user_id (blocked) ←→ blocks.blocked_id

2. Content & Feed Relationships

users → posts (1:N)

A single user can author multiple posts.

users.user_id ←→ posts.user_id

posts → post_media (1:N)

Supports Instagram's Carousel feature. A post must have at least 1 media item, and up to 10.

posts.post_id ←→ post_media.post_id

Cascade Rule: Deleting a post must cascade-delete all references in post_media.

post_media ↔ users via post_tags (M:N)

Users tagged in photos/videos with precise coordinate positions.

post_media.media_id ←→ post_tags.media_id
users.user_id ←→ post_tags.user_id

posts ↔ hashtags (M:N via post_hashtags)

Enables tag-based content categorization.

posts.post_id ←→ post_hashtags.post_id
hashtags.hashtag_id ←→ post_hashtags.hashtag_id

3. Engagement & Interaction Relationships

posts ↔ users via post_likes (M:N)

Tracks who liked a post.

posts.post_id ←→ post_likes.post_id
users.user_id ←→ post_likes.user_id

posts ↔ users via comments (M:N)

Enables user discussions on posts.

posts.post_id ←→ comments.post_id
users.user_id ←→ comments.user_id

comments ↔ comments (Self-Referencing Threading — 1:N)

Enables threaded comment replies.

comments.comment_id ←→ comments.parent_id

Constraint: Max reply nesting level is 1 (replies cannot have nested sub-replies) to fit mobile screens.

4. Ephemeral Stories Relationships

users → stories (1:N)

Users publish stories that vanish after 24 hours.

users.user_id ←→ stories.user_id

stories ↔ users via story_viewers (M:N)

Tracks every unique view of a story.

stories.story_id ←→ story_viewers.story_id
users.user_id ←→ story_viewers.viewer_id

5. Direct Messaging Relationships

users ↔ direct_conversations (M:N via conversation_members)

Groups or 1-to-1 rooms mapping users together.

direct_conversations.conversation_id ←→ conversation_members.conversation_id
users.user_id ←→ conversation_members.user_id

direct_conversations → direct_messages (1:N)

All messages sent within a messaging thread.

direct_conversations.conversation_id ←→ direct_messages.conversation_id

Relationship Summary Table

This content is reserved for Premium Members.