Posted on 2010-01-04
What type of database design do you have to use for something like google analytics? where there is a massive amount of data collection on a daily basis, but with a ton of breakdowns per hit, and the ability to use mysql GROUP functions, as well as manual search of the data?
I basically need to come up with a normalization technique that will allow me to store aggregate information (like total hits, unique hits) for each user (say I have admin can view everything, Katie can only view rows with "Katie's" ID), without needing to run a full table scan on the original data.
For example, I might want the total hits for all rows where the browser was Firefox, and also where the screen size was 1024x768 - year to date.