Database Design & Schema

Difficulty: Intermediate

Question

Design a database schema for a social media platform. When would you choose SQL vs NoSQL?

Answer

SQL (Relational): Structured data with strong relationships and ACID transactions. Best for e-commerce, banking, CMS. Examples: PostgreSQL, MySQL

NoSQL: Flexible schema, horizontal scaling. Best for real-time apps, product catalogs, IoT data. Types: - Document: MongoDB (flexible JSON-like documents) - Key-Value: Redis (caching, sessions) - Column-family: Cassandra (time-series, analytics) - Graph: Neo4j (social networks, recommendations)

Schema design principles: 1. Identify entities and relationships 2. Normalize to reduce redundancy (3NF) 3. Denormalize selectively for read performance 4. Add indexes on frequently queried columns 5. Consider access patterns before choosing SQL vs NoSQL

Code examples

Social Media Schema (SQL)

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  bio TEXT,
  avatar_url VARCHAR(500),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Posts table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  media_url VARCHAR(500),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Followers (many-to-many self-join)
CREATE TABLE followers (
  follower_id INT REFERENCES users(id),
  following_id INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (follower_id, following_id)
);

-- Likes
CREATE TABLE likes (
  user_id INT REFERENCES users(id),
  post_id INT REFERENCES posts(id),
  created_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (user_id, post_id)
);

-- Indexes for common queries
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_followers_following ON followers(following_id);

This normalized schema prevents data duplication. Indexes are added on columns used in WHERE clauses and JOINs. The followers table uses a composite primary key to prevent duplicate follows.

Same Data in NoSQL (MongoDB)

// User document (MongoDB)
{
  "_id": "user_123",
  "username": "alice",
  "email": "alice@example.com",
  "bio": "Developer",
  "followers_count": 1500,
  "following_count": 200,
  "posts_count": 45
}

// Post document - denormalized
{
  "_id": "post_456",
  "author": {
    "id": "user_123",
    "username": "alice",
    "avatar": "https://..."
  },
  "content": "Hello world!",
  "likes_count": 42,
  "liked_by": ["user_789", "user_101"],
  "comments": [
    {
      "user_id": "user_789",
      "username": "bob",
      "text": "Great post!",
      "created_at": "2025-01-15T10:30:00Z"
    }
  ],
  "created_at": "2025-01-15T09:00:00Z"
}

// Trade-off: Fast reads (no JOINs needed)
// but updating username requires updating
// every post and comment by that user

NoSQL embeds related data for fast reads but creates update complexity. Best when read frequency far exceeds write frequency.

Indexing Strategy

-- B-Tree index (default) - good for equality and range
CREATE INDEX idx_users_email ON users(email);

-- Composite index - order matters!
CREATE INDEX idx_posts_user_date
  ON posts(user_id, created_at DESC);
-- Supports: WHERE user_id = X ORDER BY created_at
-- Does NOT efficiently support: WHERE created_at > X

-- Partial index - smaller, faster
CREATE INDEX idx_active_users
  ON users(email) WHERE is_active = true;

-- Full-text search index
CREATE INDEX idx_posts_content
  ON posts USING GIN(to_tsvector('english', content));

-- Query using full-text search
SELECT * FROM posts
WHERE to_tsvector('english', content) @@
      to_tsquery('english', 'typescript & react');

-- EXPLAIN ANALYZE to check query performance
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

Indexes speed up reads but slow down writes. Use EXPLAIN ANALYZE to verify queries use indexes. Composite index column order must match query patterns.

Key points

• PostgreSQL supports JSON columns, full-text search, and NoSQL-like features - it is the most versatile choice
• Always add indexes on foreign keys and columns used in WHERE/ORDER BY clauses
• Normalization prevents data anomalies; denormalization improves read speed - most real systems use both

Concepts covered

SQL vs NoSQL, Normalization, Denormalization, Indexing, Schema Design