Database Structure

Structuring the URL Shortener Model:

• The database structure was designed to keep things simple yet functional. The URL shortener model was configured to generate shortened codes dynamically. I wanted to allow each original URL to have multiple shortened versions, enabling flexibility in tracking and managing different campaigns or uses.
• The model also keeps track of the original URL and its title, which is useful for reference purposes, especially when redirecting users.

Tracking User Data:

• To enhance the functionality, I added a feature to track user interactions with the shortened URLs. This involved creating a user_data model that logs when and where a user clicks on a link. Although this data is currently accessible through a Rails admin dashboard, it lays the groundwork for future enhancements, such as detailed analytics and user engagement statistics.

URL Shortener Model:

• Structure: The shortened_urls table is the heart of the application, containing fields such as target_url, short_url, sanitize_url, and title. These fields ensure that each shortened URL is unique and easily retrievable.
• Dynamic URL Generation: The ShortenedUrl model employs a method to generate a unique short code using SecureRandom. This method recursively checks for duplicates to avoid conflicts, ensuring that each URL has a distinct short version.
• Sanitization and Title Extraction: Before storing the URL, the model sanitizes it by stripping unnecessary protocol and subdomain information. It then extracts and stores a simplified title for easy reference. This helps maintain a clean and consistent database while offering a user-friendly way to identify URLs.

User Statistics Tracking:

• Structure: The user_stats table is linked to the shortened_urls table via a foreign key. It stores data like the user's origin city and country, latitude, longitude, and the timezone. This detailed information is crucial for understanding user behavior and the geographical reach of the shortened URLs.
• Geocoding and Timezone Assignment: The UserStat model automatically assigns geographic coordinates based on the user's city and country. It then determines the user's timezone using the coordinates, thanks to integrated geocoding and timezone lookup gems. This setup enables future features like mapping user clicks and analyzing the data across different time zones.

erDiagram
    URL_SHORTENER {
        int id PK
        string original_url
        string short_url
        string title
        datetime created_at
    }

    USER_DATA {
        int id PK
        int url_shortener_id FK
        string origin_country
        string origin_city
        string timezone
        string latitude
        string longitude
        datetime created_at
    }

    URL_SHORTENER ||--o{ USER_DATA: has

Loading