MySQL - Table format for Click stats

I'm currently developing an URL shortening service. I want to allow users to see开发者_如何学Python 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?

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?