Data Engineering Path · Data Modelling
INSTAGRAM CASE STUDY
Step 2: Entity Identification — Instagram
Detailed Entity Analysis
🔹 USERS — User profile and account details
Purpose: Store core user account data and profile configuration.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| user_id | INT (PK) | Unique auto-increment identifier |
| username | VARCHAR(30), UNIQUE | Unique handle for profile access (e.g. @john_doe) |
| VARCHAR(255), UNIQUE | Login and recovery email | |
| phone_number | VARCHAR(20), UNIQUE | Phone contact for account validation |
| password_hash | VARCHAR(255) | Hashed password credentials |
| full_name | VARCHAR(100) | Display name |
| bio | VARCHAR(150) | Profile biography summary |
| profile_picture_url | VARCHAR(512) | CDN link to profile avatar image |
| website_url | VARCHAR(255) | External link displayed on profile |
| is_private | BOOLEAN | Determines if content is followers-only |
| is_verified | BOOLEAN | Verifies public figure status (blue checkmark) |
| created_at | TIMESTAMP | Timestamp of account registration |
| updated_at | TIMESTAMP | Timestamp of last modification |
Business Rules:
usernamemust match regex pattern: lowercase alphanumeric characters, dots, and underscores only.biois capped at 150 characters to maintain profile screen consistency.- Private accounts must hide all content, follower counts, and tags from non-followers.
🔹 POSTS — Content uploads (image/video/carousel posts)
Purpose: Primary metadata container for standard posts.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| post_id | INT (PK) | Unique post identifier |
| user_id | INT (FK) | Author of the post |
| caption | VARCHAR(2200) | Post text including hashtags and user mentions |
| location_name | VARCHAR(100) | Tagged location display name |
| latitude | DECIMAL(9,6) | Geolocation coordinates |
| longitude | DECIMAL(9,6) | Geolocation coordinates |
| allow_comments | BOOLEAN | Toggle to disable comments |
| hide_likes | BOOLEAN | Toggle to hide public like counts |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last modification time |
🔹 POST_MEDIA — Media files/assets associated with a post (enables carousels)
Purpose: Store references to the actual media files (photos/videos) linked to a post. Supports carousel (multi-item) posts.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| media_id | INT (PK) | Unique media asset identifier |
| post_id | INT (FK) | Parent post reference |
| media_url | VARCHAR(512) | Cloud Storage/CDN link to optimized asset |
| media_type | ENUM | Type: IMAGE, VIDEO |
| position | INT | Display order index in carousel (0-9) |
| aspect_ratio | VARCHAR(10) | Asset dimensions (e.g. 1:1, 4:5, 16:9) |
| duration_sec | DECIMAL(5,2) | Video duration (NULL if image) |
Business Rules:
- A post can contain between 1 to 10 media items.
- Videos must be transcoded to H.264/AAC formats prior to ingestion.
🔹 POST_TAGS — User tags on photos/videos (including coordinate positions)
Purpose: Tracks users tagged inside specific media items of a post.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| tag_id | INT (PK) | Unique tag reference |
| media_id | INT (FK) | Specific media item target |
| user_id | INT (FK) | Tagged user |
| x_coord | DECIMAL(5,2) | X-axis percentage coordinate (0.00 to 100.00) |
| y_coord | DECIMAL(5,2) | Y-axis percentage coordinate (0.00 to 100.00) |
| created_at | TIMESTAMP | Timestamp of tag creation |
🔹 POST_LIKES — Likes on posts
Purpose: Tracks which users liked which posts.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| post_id | INT (FK) | Reference to post |
| user_id | INT (FK) | User who liked the post |
| created_at | TIMESTAMP | Time of action |
Business Rules:
- Composite primary key:
(post_id, user_id)to prevent duplicate likes.
🔹 COMMENTS — Post comments with support for nested/threaded replies
Purpose: Store user comments on posts.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| comment_id | INT (PK) | Unique identifier |
| post_id | INT (FK) | Target post |
| user_id | INT (FK) | Author of comment |
| parent_id | INT (FK, Null) | Self-referencing link for threaded replies (max 1 level depth) |
| content | VARCHAR(1000) | Comment body |
| created_at | TIMESTAMP | Timestamp |
🔹 COMMENT_LIKES — Likes on comments
Purpose: Stores likes specifically placed on comments.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| comment_id | INT (FK) | Target comment |
| user_id | INT (FK) | User who liked the comment |
| created_at | TIMESTAMP | Timestamp |
Business Rules:
- Composite primary key:
(comment_id, user_id).
🔹 FOLLOWS — Asymmetric user-to-user follow/social graph relationships
Purpose: Handles user-to-user follows.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| follower_id | INT (FK) | The user who initiates follow |
| followee_id | INT (FK) | The user being followed |
| is_pending | BOOLEAN | TRUE if follow request is awaiting approval (private account) |
| created_at | TIMESTAMP | Timestamp |
🔹 CLOSE_FRIENDS — User-curated close friends group for targeted story sharing
Purpose: Private, user-curated sub-list of followers.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| user_id | INT (FK) | Group owner |
| friend_id | INT (FK) | Friends added to close list |
| created_at | TIMESTAMP | Timestamp |
🔹 BLOCKS — User block list for safety and privacy
Purpose: Restrict interactions between two accounts.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| blocker_id | INT (FK) | User initiating the block |
| blocked_id | INT (FK) | User being blocked |
| created_at | TIMESTAMP | Timestamp |
🔹 STORIES — Ephemeral content disappearing after 24 hours
Purpose: Ephemeral content posts that auto-expire.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| story_id | INT (PK) | Unique story ID |
| user_id | INT (FK) | Story publisher |
| media_url | VARCHAR(512) | Media URL |
| media_type | ENUM | Type: IMAGE, VIDEO |
| is_close_friends_only | BOOLEAN | Toggle to restrict visibility |
| expires_at | TIMESTAMP | Set to created_at + 24 Hours |
| created_at | TIMESTAMP | Timestamp |
🔹 STORY_VIEWERS — Detailed log of who viewed a story (crucial for creators)
Purpose: Log views on stories.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| story_id | INT (FK) | Story being viewed |
| viewer_id | INT (FK) | User who viewed the story |
| viewed_at | TIMESTAMP | Timestamp |
🔹 STORY_REACTIONS — Quick emoji reactions and text replies to stories
Purpose: Story emoji reactions and quick comments.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| reaction_id | INT (PK) | Unique ID |
| story_id | INT (FK) | Reference to target story |
| user_id | INT (FK) | Reacting user |
| reaction_type | VARCHAR(10) | Emoji string or custom text message |
| created_at | TIMESTAMP | Timestamp |
🔹 REELS — Short-form video assets separate from standard posts
Purpose: Short-form, algorithmically driven video feed assets.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| reel_id | INT (PK) | Unique Reel ID |
| user_id | INT (FK) | Creator of Reel |
| video_url | VARCHAR(512) | High-throughput CDN stream url |
| caption | VARCHAR(2000) | Text caption |
| audio_track_name | VARCHAR(150) | Tagged music track name |
| views_count | BIGINT | Read-optimized views counter |
| created_at | TIMESTAMP | Timestamp |
🔹 REELS_LIKES — Likes on Reels
Purpose: Likes on Reels.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| reel_id | INT (FK) | Reels reference |
| user_id | INT (FK) | User reference |
| created_at | TIMESTAMP | Timestamp |
🔹 HASHTAGS — Taxonomy of global tags for discovery
Purpose: Lookup table for hashtag strings.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| hashtag_id | INT (PK) | Unique tag reference |
| tag_name | VARCHAR(150), UNIQUE | Case-insensitive tag string (e.g. datacamp) |
| created_at | TIMESTAMP | Track insertion time |
🔹 POST_HASHTAGS — Junction table associating posts with hashtags
Purpose: Associate posts with hashtags.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| post_id | INT (FK) | Linked post |
| hashtag_id | INT (FK) | Associated hashtag |
🔹 DIRECT_CONVERSATIONS — Messaging threads (1-to-1 or group chat channels)
Purpose: Messaging channels.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| conversation_id | INT (PK) | Unique thread identifier |
| is_group | BOOLEAN | Toggles multi-user channel controls |
| group_name | VARCHAR(100), Null | Group display name (nullable for 1-to-1 chats) |
| created_at | TIMESTAMP | Creation time |
🔹 CONVERSATION_MEMBERS — User-to-conversation mapping table
Purpose: User-to-conversation mapping.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| conversation_id | INT (FK) | Conversation reference |
| user_id | INT (FK) | Member reference |
| joined_at | TIMESTAMP | Timestamp user entered group |
🔹 DIRECT_MESSAGES — Direct messages with support for text, media sharing, and post forwarding
Purpose: Direct chat messages.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| message_id | INT (PK) | Unique message ID |
| conversation_id | INT (FK) | Active thread |
| sender_id | INT (FK) | Author |
| message_type | ENUM | TEXT, MEDIA, POST_SHARE |
| body_text | TEXT | Text contents |
| media_url | VARCHAR(512), Null | Linked file asset (if exists) |
| shared_post_id | INT (FK, Null) | Link to forwarded post ID |
| created_at | TIMESTAMP | Send timestamp |
🔹 SAVED_POSTS — User bookmarks and collection folders
Purpose: Bookmark and archive collections.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| save_id | INT (PK) | Unique identifier |
| user_id | INT (FK) | Saved owner |
| post_id | INT (FK) | Reference post |
| collection_name | VARCHAR(50) | Folder directory (e.g. Favorites) |
| created_at | TIMESTAMP | Saved timestamp |
🔹 NOTIFICATIONS — System-generated activity feeds and push notification logs
Purpose: Activity logs for feed updates and system notifications.
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| notification_id | INT (PK) | Unique log ID |
| recipient_id | INT (FK) | Target user |
| actor_id | INT (FK) | Triggering user (actor) |
| notification_type | ENUM | LIKE, COMMENT, FOLLOW, TAG, MENTION |
| post_id | INT (FK, Null) | Associated post target |
| comment_id | INT (FK, Null) | Associated comment target |
| is_read | BOOLEAN | Read state indicator |
| created_at | TIMESTAMP | Timestamp of action |
arrow_back Previous Topic
Problem Statement Analysis
Next Topic arrow_forward
Relationships Explained