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)

INSERT INTO url (short_url, long_url, expiry_date, user_id)
VALUES ('abc123', 'https://www.example.com/long-url', '2025-12-31', 1);

 

Retrieve Original URL for Redirection
 
SELECT long_url FROM url WHERE short_url = 'abc123' AND (expiry_date IS NULL OR expiry_date > NOW());

Update Click Count
 
UPDATE url SET clicks = clicks + 1 WHERE short_url = 'abc123';

Insert Click Analytics
 
INSERT INTO click_analytics (short_url, ip_address, user_agent, referrer, geo_location)
VALUES ('abc123', '192.168.1.1', 'Mozilla/5.0', 'https://facebook.com', 'USA');

Get Click Analytics for a Short URL
 
SELECT COUNT(*) as total_clicks, geo_location, referrer
FROM click_analytics WHERE short_url = 'abc123'
GROUP BY geo_location, referrer;

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.

PreviousNext