Database Schema for URL Shortener
What you'll learn: How to design the core database tables that power a URL shortening service, storing mappings, metadata, and user associations efficiently.
The Core Tables
A URL shortener needs at least one primary table, but a production system typically uses two or three core tables for flexibility and performance.
URLs Table (Primary)
This is your central table mapping short codes to destinations:
urls
- id (primary key, bigint)
- short_code (unique, indexed, varchar(7))
- original_url (text, indexed)
- created_at (timestamp)
- expires_at (timestamp, nullable, indexed)
- user_id (foreign key, nullable, indexed)
- click_count (integer, default 0)
The short_code is your Base62-encoded identifier from the previous lesson. Indexing it ensures lightning-fast lookups during redirects. The original_url may also be indexed to detect if a user is shortening the same URL again.
Users Table (Optional)
If supporting registered users:
users
- id (primary key)
- email (unique)
- created_at (timestamp)
Analytics/Metadata Table (Optional)
For tracking clicks without bloating the main table:
url_metadata
- id (primary key)
- url_id (foreign key)
- custom_alias (varchar, nullable)
- description (text, nullable)
- tags (jsonb or separate table)
Design Decisions
Expiration: The indexed expires_at field enables efficient cleanup jobs that periodically delete or recycle expired links.
Partitioning: For massive scale, partition the urls table by creation date (range partitioning) or hash the short_code to distribute load.
Denormalization: Storing click_count directly trades write amplification for read speed—acceptable since reads vastly outnumber writes.
Key Takeaway: A URL shortener's schema centers on a fast-lookup urls table with unique short codes, timestamps for expiration, and optional user/metadata associations, optimized for read-heavy workloads through strategic indexing.