1. Database Tables
1.1 URL Table
Stores the mapping between long URLs and short URLs.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
id |
BIGINT (AUTO_INCREMENT) | PRIMARY KEY | Unique ID for internal use. |
short_url |
VARCHAR(10) | UNIQUE, NOT NULL | Shortened URL identifier. |
long_url |
TEXT | NOT NULL | Original long URL. |
created_at |
TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | URL creation timestamp. |
expiry_date |
TIMESTAMP | NULL | Expiry time of the short URL. |
user_id |
BIGINT | FOREIGN KEY | User who created the URL (optional). |
clicks |
INT | DEFAULT 0 | Number of times the short URL was accessed. |
Indexes:
-
short_url(to quickly look up short URLs). -
expiry_date(to remove expired URLs).
1.2 Click Analytics Table
Stores analytics for each short URL.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
id |
BIGINT (AUTO_INCREMENT) | PRIMARY KEY | Unique ID. |
short_url |
VARCHAR(10) | FOREIGN KEY | Associated short URL. |
click_time |
TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Time of access. |
ip_address |
VARCHAR(45) | NOT NULL | User IP address. |
user_agent |
TEXT | NOT NULL | Browser and device details. |
referrer |
VARCHAR(255) | NULL | Source website of the click. |
geo_location |
VARCHAR(100) | NULL | Country/Region of visitor. |
Indexes:
-
short_url(to track visits per URL). -
click_time(for time-based reports).
1.3 Users Table (For authenticated users)
If we support user accounts, we need a Users table.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
id |
BIGINT (AUTO_INCREMENT) | PRIMARY KEY | Unique user ID. |
username |
VARCHAR(50) | UNIQUE, NOT NULL | User’s name. |
email |
VARCHAR(100) | UNIQUE, NOT NULL | Email for login. |
password |
VARCHAR(255) | NOT NULL | Hashed password. |
created_at |
TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Account creation time. |
Indexes:
-
email(for login authentication).
1.4 Blacklist Table (For blocking malicious URLs)
Prevents shortening of spam, phishing, or malware URLs.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
id |
BIGINT (AUTO_INCREMENT) | PRIMARY KEY | Unique blacklist entry ID. |
url_pattern |
TEXT | UNIQUE, NOT NULL | Blocked URL or domain pattern. |
added_at |
TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | When it was blacklisted. |
Indexes:
-
url_pattern(for fast lookup of banned URLs).
2. Database Relationships
-
One-to-Many: A user can have multiple short URLs.
-
One-to-Many: A short URL can have multiple clicks (analytics).
3. Query Examples
Shorten a URL (Insert into DB)
Retrieve Original URL for Redirection
Update Click Count
Insert Click Analytics
Get Click Analytics for a Short URL
4. Scaling Considerations
Sharding – Distribute URLs across multiple databases (e.g., by hash of short_url).
Caching – Store frequently accessed URLs in Redis or Memcached.
Partitioning – Split click analytics based on date ranges.
Replication – Use read replicas for analytics queries.
