Design a URL Shortener

Problem Statement

Design a simple URL shortener system (like bit.ly). A user gives a long URL like: https://www.google.com/maps/place/some/very/long/address The system returns a short URL like: https://short.ly/abc123 Write your design as plain text or pseudo-code covering: 1. Database schema — what tables and columns do you need? 2. How would you generate the short code? (explain your approach) 3. How does redirect work? (user clicks short URL → what happens?) 4. What happens if the same long URL is submitted twice? 5. One thing that could go wrong at scale and how you'd handle it

Sample Data

No code required. Write your design as text.
You can use pseudo-code, bullet points, or SQL for the schema.
Focus on explaining your thinking clearly.

Expected Output

1. Schema: urls table with id, short_code, long_url, created_at, click_count
2. Short code: hash, random string, or base62 encoding
3. Redirect: look up short_code → return 301/302 redirect to long_url
4. Deduplication: check if long_url already exists before inserting
5. Any valid scale concern: DB load, collisions, caching