Social Media Mini Clone – SQL Schema Design

Overview

A Social Media Mini Clone replicates the core features of platforms like Facebook, Twitter, or Instagram. It includes user profiles, posts, friendships/follows, likes, comments, and notifications. Building this schema gives students hands-on experience with complex relationships, self-joins, many-to-many tables, and scalable content structures.

Core Features

User registration and profile management
Posting text, images, or links
Following or befriending other users
Likes and comments on posts
Notifications for activities
Time tracking of all actions

Database Tables and Relationships

1. Users Table

Stores user profiles and account data.

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255),
    full_name VARCHAR(100),
    bio TEXT,
    profile_picture VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. Posts Table

Stores user-generated posts.

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    content TEXT,
    image_url VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

3. Followers Table

Tracks following relationships (user-to-user, many-to-many with self-join).

CREATE TABLE followers (
    follower_id INT,
    following_id INT,
    followed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    FOREIGN KEY (follower_id) REFERENCES users(user_id),
    FOREIGN KEY (following_id) REFERENCES users(user_id)
);

4. Likes Table

Tracks who liked which post.

CREATE TABLE likes (
    user_id INT,
    post_id INT,
    liked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

5. Comments Table

Stores comments on posts.

CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    comment_text TEXT,
    commented_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

6. Notifications Table

Stores notifications like new followers, likes, comments.

CREATE TABLE notifications (
    notification_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT, -- the receiver
    type ENUM('follow', 'like', 'comment') NOT NULL,
    from_user_id INT, -- the actor
    post_id INT, -- optional, nullable
    is_read BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (from_user_id) REFERENCES users(user_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

Sample Queries

Get Feed for a User (Posts by Followed Users)

SELECT p.post_id, p.content, u.username, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.user_id
JOIN followers f ON p.user_id = f.following_id
WHERE f.follower_id = 1
ORDER BY p.created_at DESC;

Like a Post

INSERT INTO likes (user_id, post_id)
VALUES (2, 5);

Comment on a Post

INSERT INTO comments (post_id, user_id, comment_text)
VALUES (5, 3, 'Nice post!');

Get Post with Total Likes and Comments

SELECT p.post_id, p.content,
       COUNT(DISTINCT l.user_id) AS like_count,
       COUNT(DISTINCT c.comment_id) AS comment_count
FROM posts p
LEFT JOIN likes l ON p.post_id = l.post_id
LEFT JOIN comments c ON p.post_id = c.post_id
WHERE p.post_id = 5
GROUP BY p.post_id;

Best Practices

Use self-joins to manage follower/following logic
Normalize likes/comments as many-to-many tables
Index foreign keys and high-read fields like created_at
Use triggers or backend logic for generating notifications
Secure user data by storing password hashes only