MySQL & Database Design

Social Media Database Design

13 min Lesson 36 of 40

Social Media Database Design

Social media platforms require complex database designs to handle user interactions, content creation, social connections, and real-time notifications. In this lesson, we'll design a complete social media database that supports posts, comments, likes, follows, and messaging.

Core Social Media Features

A typical social media platform needs:

User Management:
- User profiles with bio and avatar
- Account privacy settings
- User verification/badges

Social Interactions:
- Follow/unfollow relationships
- Posts with text, images, videos
- Comments and nested replies
- Likes and reactions
- Shares/retweets

Communication:
- Private messaging
- Group conversations
- Notifications system
- Activity feed generation

Complete Social Media Schema

Let's build the database structure:

-- Users table
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    cover_photo_url VARCHAR(500),
    website VARCHAR(255),
    location VARCHAR(100),
    is_verified BOOLEAN DEFAULT FALSE,
    is_private BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    date_of_birth DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_verified (is_verified),
    FULLTEXT idx_search (username, full_name, bio)
) ENGINE=InnoDB;

-- Follows/Connections
CREATE TABLE follows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    follower_id BIGINT UNSIGNED NOT NULL COMMENT 'User who follows',
    following_id BIGINT UNSIGNED NOT NULL COMMENT 'User being followed',
    status ENUM('pending', 'accepted', 'blocked') DEFAULT 'accepted',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_follow (follower_id, following_id),
    INDEX idx_follower (follower_id),
    INDEX idx_following (following_id),
    INDEX idx_status (status),
    CHECK (follower_id != following_id)
) ENGINE=InnoDB;

-- Posts
CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    media_type ENUM('none', 'image', 'video', 'link') DEFAULT 'none',
    media_url VARCHAR(500),
    link_preview_title VARCHAR(255),
    link_preview_description TEXT,
    link_preview_image VARCHAR(500),
    is_public BOOLEAN DEFAULT TRUE,
    allow_comments BOOLEAN DEFAULT TRUE,
    location VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at),
    INDEX idx_public (is_public, created_at),
    FULLTEXT idx_content (content)
) ENGINE=InnoDB;

-- Post media (multiple images/videos per post)
CREATE TABLE post_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    media_type ENUM('image', 'video') NOT NULL,
    media_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500),
    display_order INT DEFAULT 0,
    width INT UNSIGNED,
    height INT UNSIGNED,
    duration INT UNSIGNED COMMENT 'Video duration in seconds',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    INDEX idx_post (post_id)
) ENGINE=InnoDB;

-- Comments
CREATE TABLE comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    parent_comment_id BIGINT UNSIGNED NULL COMMENT 'For nested replies',
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE,
    INDEX idx_post (post_id, created_at),
    INDEX idx_user (user_id),
    INDEX idx_parent (parent_comment_id),
    FULLTEXT idx_content (content)
) ENGINE=InnoDB;

-- Likes (polymorphic - can like posts or comments)
CREATE TABLE likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    likeable_type ENUM('post', 'comment') NOT NULL,
    likeable_id BIGINT UNSIGNED NOT NULL,
    reaction_type ENUM('like', 'love', 'haha', 'wow', 'sad', 'angry') DEFAULT 'like',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_like (user_id, likeable_type, likeable_id),
    INDEX idx_likeable (likeable_type, likeable_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- Shares/Retweets
CREATE TABLE shares (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL COMMENT 'User who shared',
    original_post_id BIGINT UNSIGNED NOT NULL,
    caption TEXT COMMENT 'Optional comment when sharing',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (original_post_id) REFERENCES posts(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_post (original_post_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Hashtags
CREATE TABLE hashtags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    post_count INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_trending (post_count, created_at)
) ENGINE=InnoDB;

-- Post hashtags (many-to-many)
CREATE TABLE post_hashtags (
    post_id BIGINT UNSIGNED NOT NULL,
    hashtag_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, hashtag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (hashtag_id) REFERENCES hashtags(id) ON DELETE CASCADE,
    INDEX idx_hashtag (hashtag_id)
) ENGINE=InnoDB;

-- Conversations (for direct messages)
CREATE TABLE conversations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    is_group BOOLEAN DEFAULT FALSE,
    group_name VARCHAR(100),
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_group (is_group)
) ENGINE=InnoDB;

-- Conversation participants
CREATE TABLE conversation_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    left_at TIMESTAMP NULL,
    last_read_at TIMESTAMP NULL,
    is_muted BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_participant (conversation_id, user_id),
    INDEX idx_user (user_id),
    INDEX idx_active (conversation_id, left_at)
) ENGINE=InnoDB;

-- Messages
CREATE TABLE messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    sender_id BIGINT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    media_url VARCHAR(500),
    media_type ENUM('none', 'image', 'video', 'file') DEFAULT 'none',
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_conversation (conversation_id, created_at),
    INDEX idx_sender (sender_id)
) ENGINE=InnoDB;

-- Notifications
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL COMMENT 'Recipient',
    actor_id BIGINT UNSIGNED NOT NULL COMMENT 'User who triggered',
    notification_type ENUM(
        'follow', 'like', 'comment', 'share',
        'mention', 'message', 'tag'
    ) NOT NULL,
    entity_type ENUM('post', 'comment', 'message') NULL,
    entity_id BIGINT UNSIGNED NULL,
    content TEXT,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, is_read, created_at),
    INDEX idx_actor (actor_id),
    INDEX idx_type (notification_type)
) ENGINE=InnoDB;

-- User blocks
CREATE TABLE blocks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blocker_id BIGINT UNSIGNED NOT NULL,
    blocked_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_block (blocker_id, blocked_id),
    INDEX idx_blocker (blocker_id),
    INDEX idx_blocked (blocked_id)
) ENGINE=InnoDB;

-- Saved posts (bookmarks)
CREATE TABLE saved_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    post_id BIGINT UNSIGNED NOT NULL,
    collection_name VARCHAR(100) DEFAULT 'default',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    UNIQUE KEY unique_saved (user_id, post_id),
    INDEX idx_user (user_id, collection_name)
) ENGINE=InnoDB;

-- Reports (for content moderation)
CREATE TABLE reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reporter_id BIGINT UNSIGNED NOT NULL,
    reported_type ENUM('user', 'post', 'comment') NOT NULL,
    reported_id BIGINT UNSIGNED NOT NULL,
    reason ENUM(
        'spam', 'harassment', 'hate_speech',
        'violence', 'false_information', 'other'
    ) NOT NULL,
    description TEXT,
    status ENUM('pending', 'reviewing', 'resolved', 'dismissed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP NULL,
    FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_reported (reported_type, reported_id)
) ENGINE=InnoDB;

Design Pattern: The likes table uses a polymorphic design where likeable_type and likeable_id allow likes on both posts and comments without duplicate tables.

Key Social Media Queries

Essential queries for social media functionality:

-- Get user feed (posts from followed users)
SELECT
    p.id,
    p.content,
    p.created_at,
    u.username,
    u.full_name,
    u.avatar_url,
    COUNT(DISTINCT l.id) AS like_count,
    COUNT(DISTINCT c.id) AS comment_count,
    COUNT(DISTINCT s.id) AS share_count,
    EXISTS(
        SELECT 1 FROM likes
        WHERE user_id = 1 AND likeable_type = 'post' AND likeable_id = p.id
    ) AS is_liked_by_me
FROM posts p
JOIN users u ON p.user_id = u.id
JOIN follows f ON p.user_id = f.following_id
LEFT JOIN likes l ON p.id = l.likeable_id AND l.likeable_type = 'post'
LEFT JOIN comments c ON p.id = c.post_id
LEFT JOIN shares s ON p.id = s.original_post_id
WHERE f.follower_id = 1
    AND f.status = 'accepted'
    AND p.is_public = TRUE
GROUP BY p.id, p.content, p.created_at, u.username, u.full_name, u.avatar_url
ORDER BY p.created_at DESC
LIMIT 20;

-- Get followers count and following count
SELECT
    (SELECT COUNT(*) FROM follows WHERE following_id = 1 AND status = 'accepted') AS followers,
    (SELECT COUNT(*) FROM follows WHERE follower_id = 1 AND status = 'accepted') AS following;

-- Get post details with all interactions
SELECT
    p.*,
    u.username,
    u.full_name,
    u.avatar_url,
    COUNT(DISTINCT l.id) AS like_count,
    COUNT(DISTINCT c.id) AS comment_count,
    COUNT(DISTINCT s.id) AS share_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.likeable_id AND l.likeable_type = 'post'
LEFT JOIN comments c ON p.id = c.post_id
LEFT JOIN shares s ON p.id = s.original_post_id
WHERE p.id = 100
GROUP BY p.id;

-- Get comments with nested replies
WITH RECURSIVE comment_tree AS (
    -- Top-level comments
    SELECT
        c.id,
        c.post_id,
        c.user_id,
        c.parent_comment_id,
        c.content,
        c.created_at,
        u.username,
        u.avatar_url,
        0 AS depth,
        CAST(c.id AS CHAR(500)) AS path
    FROM comments c
    JOIN users u ON c.user_id = u.id
    WHERE c.post_id = 100 AND c.parent_comment_id IS NULL

    UNION ALL

    -- Nested replies
    SELECT
        c.id,
        c.post_id,
        c.user_id,
        c.parent_comment_id,
        c.content,
        c.created_at,
        u.username,
        u.avatar_url,
        ct.depth + 1,
        CONCAT(ct.path, '->', c.id)
    FROM comments c
    JOIN users u ON c.user_id = u.id
    JOIN comment_tree ct ON c.parent_comment_id = ct.id
    WHERE ct.depth < 5
)
SELECT * FROM comment_tree
ORDER BY path;

-- Get trending hashtags
SELECT
    h.name,
    h.post_count,
    COUNT(DISTINCT ph.post_id) AS recent_posts
FROM hashtags h
LEFT JOIN post_hashtags ph ON h.id = ph.hashtag_id
    AND ph.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY h.id, h.name, h.post_count
ORDER BY recent_posts DESC, h.post_count DESC
LIMIT 10;

-- Get unread notifications
SELECT
    n.id,
    n.notification_type,
    n.content,
    n.created_at,
    u.username AS actor_username,
    u.avatar_url AS actor_avatar
FROM notifications n
JOIN users u ON n.actor_id = u.id
WHERE n.user_id = 1
    AND n.is_read = FALSE
ORDER BY n.created_at DESC
LIMIT 50;

-- Get conversation list with last message
SELECT
    c.id,
    c.is_group,
    c.group_name,
    c.updated_at,
    m.content AS last_message,
    m.created_at AS last_message_at,
    sender.username AS last_sender,
    COUNT(DISTINCT CASE
        WHEN m2.created_at > cp.last_read_at THEN m2.id
    END) AS unread_count
FROM conversations c
JOIN conversation_participants cp ON c.id = cp.conversation_id
LEFT JOIN messages m ON c.id = m.conversation_id
    AND m.id = (
        SELECT id FROM messages
        WHERE conversation_id = c.id
        ORDER BY created_at DESC LIMIT 1
    )
LEFT JOIN users sender ON m.sender_id = sender.id
LEFT JOIN messages m2 ON c.id = m2.conversation_id
WHERE cp.user_id = 1 AND cp.left_at IS NULL
GROUP BY c.id, c.is_group, c.group_name, c.updated_at,
         m.content, m.created_at, sender.username
ORDER BY c.updated_at DESC;

Performance Tip: For the feed query, consider using a materialized view or caching layer as this is the most frequently executed query in social media apps.

Follow System Implementation

Handling follow relationships with privacy:

-- Follow a user (auto-accept for public accounts)
INSERT INTO follows (follower_id, following_id, status)
SELECT 1, 2,
    CASE
        WHEN is_private = TRUE THEN 'pending'
        ELSE 'accepted'
    END
FROM users WHERE id = 2;

-- Accept follow request
UPDATE follows
SET status = 'accepted'
WHERE follower_id = 1 AND following_id = 2 AND status = 'pending';

-- Check if user A follows user B
SELECT EXISTS(
    SELECT 1 FROM follows
    WHERE follower_id = 1 AND following_id = 2 AND status = 'accepted'
) AS is_following;

-- Get mutual followers (friends)
SELECT DISTINCT u.*
FROM users u
JOIN follows f1 ON u.id = f1.following_id
JOIN follows f2 ON u.id = f2.follower_id
WHERE f1.follower_id = 1
    AND f2.following_id = 1
    AND f1.status = 'accepted'
    AND f2.status = 'accepted';

Real-Time Activity Feed

Generating personalized activity feeds:

-- Combined activity feed (posts + shares)
(
    SELECT
        'post' AS activity_type,
        p.id,
        p.user_id,
        u.username,
        p.content,
        p.created_at
    FROM posts p
    JOIN users u ON p.user_id = u.id
    JOIN follows f ON p.user_id = f.following_id
    WHERE f.follower_id = 1 AND f.status = 'accepted'
)
UNION ALL
(
    SELECT
        'share' AS activity_type,
        s.id,
        s.user_id,
        u.username,
        s.caption AS content,
        s.created_at
    FROM shares s
    JOIN users u ON s.user_id = u.id
    JOIN follows f ON s.user_id = f.following_id
    WHERE f.follower_id = 1 AND f.status = 'accepted'
)
ORDER BY created_at DESC
LIMIT 50;

Scalability Challenge: Feed generation becomes slow with many followers. Consider using a push-based feed where posts are pre-computed and stored in a denormalized feed table or Redis.

Direct Messaging System

Complete messaging implementation:

-- Create a conversation between two users
INSERT INTO conversations (is_group, created_by) VALUES (FALSE, 1);
SET @conv_id = LAST_INSERT_ID();

INSERT INTO conversation_participants (conversation_id, user_id)
VALUES (@conv_id, 1), (@conv_id, 2);

-- Send a message
INSERT INTO messages (conversation_id, sender_id, content)
VALUES (@conv_id, 1, 'Hello, how are you?');

-- Mark messages as read
UPDATE conversation_participants
SET last_read_at = NOW()
WHERE conversation_id = @conv_id AND user_id = 1;

-- Get messages in a conversation
SELECT
    m.id,
    m.content,
    m.created_at,
    u.username,
    u.avatar_url,
    m.sender_id = 1 AS is_mine
FROM messages m
JOIN users u ON m.sender_id = u.id
WHERE m.conversation_id = @conv_id
    AND m.is_deleted = FALSE
ORDER BY m.created_at ASC;

Scalability Strategies

Feed Generation:
- Use push model for users with <1000 followers
- Use pull model for celebrities with millions of followers
- Implement fan-out on write for active users
- Cache feeds in Redis with TTL

Database Sharding:
- Shard users by user_id ranges
- Keep social graph in dedicated cluster
- Use consistent hashing for messages
- Replicate for read-heavy operations

Performance Optimization:
- Paginate all feeds with cursor-based pagination
- Denormalize engagement counts (like_count, comment_count)
- Use message queues for notifications
- Archive old posts after 1 year
- Implement rate limiting per user

Practice Exercise:

Task: Add a stories feature (24-hour temporary posts).

Requirements:

Create a stories table with expiration
Track who viewed each story
Auto-delete stories after 24 hours
Show stories from followed users only

Solution:

CREATE TABLE stories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    media_type ENUM('image', 'video') NOT NULL,
    media_url VARCHAR(500) NOT NULL,
    duration INT DEFAULT 15 COMMENT 'Display duration in seconds',
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_active (user_id, expires_at)
) ENGINE=InnoDB;

CREATE TABLE story_views (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    story_id BIGINT UNSIGNED NOT NULL,
    viewer_id BIGINT UNSIGNED NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (story_id) REFERENCES stories(id) ON DELETE CASCADE,
    FOREIGN KEY (viewer_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_view (story_id, viewer_id),
    INDEX idx_story (story_id)
) ENGINE=InnoDB;

-- Get active stories from followed users
SELECT
    s.id,
    s.media_url,
    s.duration,
    u.username,
    u.avatar_url,
    COUNT(sv.id) AS view_count,
    EXISTS(
        SELECT 1 FROM story_views
        WHERE story_id = s.id AND viewer_id = 1
    ) AS viewed_by_me
FROM stories s
JOIN users u ON s.user_id = u.id
JOIN follows f ON s.user_id = f.following_id
LEFT JOIN story_views sv ON s.id = sv.story_id
WHERE f.follower_id = 1
    AND s.expires_at > NOW()
GROUP BY s.id
ORDER BY viewed_by_me ASC, s.created_at DESC;

Summary

In this lesson, you learned:

How to design a complete social media database
Implementing follow relationships with privacy controls
Building a polymorphic likes system
Creating threaded comments with nested replies
Implementing direct messaging and notifications
Generating personalized activity feeds
Scalability strategies for social platforms

Next Up: In the next lesson, we'll design a blog and CMS database with categories, tags, and multi-language support!