CODEWITHSUNDEEP
mysql
Francesc
Francesc

Reputation: 1349

MySQL - Table format for Click stats

I'm currently developing an URL shortening service. I want to allow users to see the stats for their URLs. How has to be the table. First, it has to be the url ID, but then, how I can sort the clicks per day?

Upvotes: 1

Views: 215

Answers (1)

Chad Birch
Chad Birch

Reputation: 74528

It really depends on what stats you want to be able to display. In the absolute most general case, you could have two columns: URL ID, and a timestamp of when someone used that URL, insert one row every time someone uses a URL through your service. This will generate a lot of rows, but you'll be able to get any statistics that you want.

I doubt that you need to-the-second statistics forever though, so I'd suggest setting up a scheduled job to run once a day or so, and "roll up" the statistics for the day into a second table. The second table could have 3 columns: URL ID, date, and number of clicks. Every day, go through the first table that contains every click, figure out how many clicks there were for each URL, and insert a "summary" row into the second table. Then delete all the individual click-rows from the first table.

Make sense?

Upvotes: 1

Related Questions