Social Media Platform Schema

High-performance schema supporting users, posts, comments, follows, and likes with optimized indexes.

SQL DDL Script

schema.sql

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  bio TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  comment_text TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE post_likes (
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, post_id)
);

CREATE TABLE follows (
  follower_id INT REFERENCES users(id) ON DELETE CASCADE,
  following_id INT REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, following_id)
);

Architectural Explanation

A standard relational layout for social graph platforms. Many-to-many relations like follows and post_likes utilize composite primary keys (two columns combined) to enforce uniqueness naturally.

Design Best Practices

Enforce unique constraints on composite keys to prevent duplicate likes or follows.
Index the created_at column on posts to allow fast feed sorting.
Consider using UUIDs instead of auto-incrementing integers for user and post IDs to enhance privacy and facilitate distributed database generation.

Test this Schema in the Playground

Copy the DDL above and execute it in our SQL console to practice writing queries against this layout.

Open SQL Playground