Hello. 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?
+1
A:
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?
Chad Birch
2010-04-05 16:14:49
For the first table, let's call it 'Today'. Isn't better to create two columns URL ID and number of clicks?
Francesc
2010-04-05 16:17:31
Depends what you mean by "better". It'll have less rows, but if you do it that way, you won't have access to statistics like "number of clicks in the last hour". But like I said, it all depends on what stats you want to have access to.
Chad Birch
2010-04-05 16:20:23