Say I've got a site with user profiles which have openly accessible pages (each profile has several pages each). Let me show the customers page view statistics (e.g. per page, for any certain period of time, etc.). What's a great way to store page sights?
This is what I believed:
Table Page Views ================ - Id (PK) - Profile Id (FK) - Page Id (FK) - Timestamp
I am afraid this solution will not scale. Suggestions?
Your intuition is correct, conntacting a database does not scale particularly well. You need to avoid a database transaction for every page request.
That noted, is scaling really your concern? If that's the case, and presuming a Web page (instead of intra), skip moving your personal and collect the hit data with Google Statistics or something like that similar. Then take that data and process it to create totals per profile.
However, if you are really hellbent on doing the work yourself, consider log parsing rather. If you're able to enumerate the Web addresses per profile, use that information, as well as your web server logs, to create hit totals. Tools for example Microsoft's Log Parser, which could process Lot of different formats, or *nix command line tools like sed and grep are the buddies here.
If enumeration's difficult change code to log the important information and procedure that log file.
With logs in position, generate results utilizing a batch process and place individuals results right into a database using MySQL's LOAD DATA.
Final note around the roll your personal approach I have suggested - this can scale a great deal better for those who have a clustered atmosphere than database transaction per request.
It is dependent on which type of reviews you need to offer.
If you wish to have the ability to say "this is actually the list of subscribers that seen your page between both of these dates", then you definitely must store all of the data you suggested.
Should you only have to have the ability to say "your page was seen X occasions between both of these dates", then you definitely just have a table having a page ID, date, and counter. Update the counter column on each page view having a single UPDATE query.
I guess you could have
personid(pk) activeProfileID(fk) -- the active profile to use. timestamp
profilePageID(pk) profileid(pk) pageid(pk) isActive