Course contentsShow
← System Design
Lesson 1554 of 1,91938. Real-World System: PastebinFree lesson

Paste Metadata in Relational Database

Design schema for paste ID, creation time, expiration, language, privacy settings, and ownership metadata.

Paste Metadata in Relational Database

You'll learn how to design a relational database schema to store all the metadata about each paste without including the actual content.

The Core Idea

When a user creates a paste, you need to track information about that paste—when it was created, who owns it, when it expires, etc.—separate from the paste content itself (which lives in object storage). This metadata is perfect for a relational database because it's structured, fixed-size, and frequently queried for lookups, user dashboards, and access control.

Think of it like a library catalog: the card (metadata) tells you the title, author, publish date, and location, while the actual book (content) sits on a shelf elsewhere.

Schema Design

Your pastes table should include:

  • paste_id (VARCHAR/CHAR, primary key): The unique short identifier (e.g., "abc123xyz")
  • user_id (BIGINT, nullable, indexed): Owner reference; null for anonymous pastes
  • created_at (TIMESTAMP): When the paste was created
  • expires_at (TIMESTAMP, nullable, indexed): When to delete; null means never
  • language (VARCHAR): Syntax highlighting hint (e.g., "python", "json", "plain")
  • is_private (BOOLEAN): Whether access requires authentication
  • is_encrypted (BOOLEAN): Whether content is client-side encrypted
  • size_bytes (INTEGER): Content size for quota tracking
  • view_count (INTEGER, default 0): Analytics metric

Index on expires_at to efficiently find expired pastes. Index on user_id for user dashboard queries. The paste_id serves as the foreign key linking metadata to the object in storage.

Key Takeaway:
Store paste metadata (ID, timestamps, ownership, privacy settings, language) in a relational database for fast structured queries, while keeping the actual paste content in object storage referenced by the paste ID.