Database Structure
The system uses the following tables:
admin_users Table
CREATE TABLE admin_users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role ENUM('admin', 'user') DEFAULT 'user',
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20),
verification_token VARCHAR(32),
is_verified BOOLEAN DEFAULT FALSE,
reset_token VARCHAR(32),
reset_token_expiry DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
profile_updated_at DATETIME
);
short_urls Table
CREATE TABLE short_urls (
id INT AUTO_INCREMENT PRIMARY KEY,
short_code VARCHAR(10) NOT NULL UNIQUE,
long_url TEXT NOT NULL,
user_id INT,
custom_name VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES admin_users(id)
);
url_visits Table
CREATE TABLE url_visits (
id INT AUTO_INCREMENT PRIMARY KEY,
short_code VARCHAR(10) NOT NULL,
ip_address VARCHAR(45),
extra_info TEXT,
device_info TEXT,
device_type VARCHAR(20),
visit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (short_code) REFERENCES short_urls(short_code)
);
blogs Table
CREATE TABLE blogs (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
author INT,
image VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (author) REFERENCES admin_users(id)
);