I am currently creating a unique tracking tool tailored for marketing campaigns. This specialized tool acts as a mediator between advertisements and landing pages, responsible for recording all user data including user-agent information, IP addresses, clicks on landing pages, and geocoding details such as country and ISP associated with the users' IPs.
However, I am facing some design challenges:
- The volume of traffic on these campaigns is extremely high, potentially resulting in millions of rows being inserted daily. Since this system caters to multiple users, storing all data in a single table could lead to chaos. Perhaps dividing the data into separate tables, one per user, might alleviate this issue, but I am uncertain about this approach.
- The data storage process must be executed swiftly (within milliseconds), leading me to believe that NodeJS would outperform PHP due to its speed and resource efficiency. I want to avoid server crashes caused by insufficient RAM.
- For statistical analysis purposes, I require data grouping. While there may be one row for each user visiting a landing page, it is imperative to aggregate this information accurately in order to reflect the total number of impressions on said landing page. These queries need to be conducted quickly given the large dataset involved.
- To geocode IP addresses effectively, precise details such as Country, ISP, and connection type are necessary. However, incorporating an API service for real-time data retrieval could potentially slow down the saving process. It needs to be done instantaneously without delaying execution.
Following the data-saving procedure, the system should promptly redirect to the landing page to prevent missing out on potential leads. Time sensitivity is crucial.
My primary objective revolves around finding optimal solutions for:
- Effectively managing extensive databases
- Rapidly capturing user data (in milliseconds)
- If feasible, expediting ip geocoding while not hindering operations
- Enhancing schema and query efficiency for generating statistics
Do you have any recommendations? Thank you in advance.