User Tracking - Custom PHP / MySQL

Question

I am launching a local directory site (I think yelp / yell.com, etc.) and must provide analytical data to the enterprises listed on the site.

I need to track the following:

1) The number of visitors on certain pages (i.e. Jim widgets viewed 65 times)

2) The number of times a user clicks a link (i.e.: 25 users clicked to visit your site)

I can do this by simply adding one to the corresponding number each time an action occurs.

What I would like to do is divide this into date ranges, for example, last 30 days, last 12 months, all the time.

How to store this data in a database? I need a theory, not a code! If someone can explain the best way to store this information, I would be extremely grateful.

For example, do I use one table for dates, one for pages / links and another for user data (click links / visited pages)? The only solution I have so far is to add a new row to the database every time one of these actions happens, which will not scale very well.

Thanks to everyone who can help.

Answer 1

I would not reinvent the wheel and use already available solutions such as Piwik . He can really read your regular blogs to provide all the information you requested.

If for some reason you still need a special solution, I would not save the tracking data in ranges, but rather use the exact time and url data for each individual page call (which your regular weblog suggests). Cumulative data should be generated on the fly in your logical partition, for example. via SQL view:

SELECT count(url),url FROM calllog WHERE calldate > NOW()-30days