CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

Data Model for Profile Page Views

Say I have a site with user profiles that have publicly accessible pages (each profile has several pages each). I'd like to show the users page view statistics (e.g. per page, for a certain time period, etc.). What's a good way to store page views?

Here's what I was thinking:

Table Page Views
================
- Id (PK)
- Profile Id (FK)
- Page Id (FK)
- Timestamp

I'm afraid this solution won't scale. Suggestions?

Upvotes: 2

Views: 328

Answers (3)

orangepips
orangepips

Reputation: 9961

Your intuition is correct, writing to a database doesn't scale particularly well. You want to avoid a database transaction for each page request.

That noted, is scaling really your concern? If so, and assuming a Internet site (as opposed to intra), skip rolling your own and collect the hit data with Google Analytics or something similar. Then take that data and process it to generate totals per profile.

However, if you're really hellbent on doing it yourself, consider log parsing instead. If you can enumerate the URLs per profile, use that information, and your web server logs, to generate hit totals. Tools such as Microsoft's Log Parser, which can process A LOT of different formats, or *nix command line tools like sed and grep are your friends here.

If enumeration's not possible change code to log the information you need and process that log file.

With logs in place, generate results using a batch process and insert those results into a database using MySQL's LOAD DATA.

Final note on the roll your own approach I've recommended - this will scale a lot better if you have a clustered environment than database transaction per request.

Upvotes: 1

daemyo
daemyo

Reputation: 1

I suppose you can have

tblPerson

personid(pk)
activeProfileID(fk)    -- the active profile to use.
timestamp

tblPage

pageid(pk)
data

tblPersonProfile

profileID(pk)
timestamp

tblProfilePages

profilePageID(pk)
profileid(pk)
pageid(pk)
isActive

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

It depends on what kind of reports you want to make available.

If you want to be able to say "this is the list of people that viewed your page between these two dates", then you must store all the data you proposed.

If you only need to be able to say "your page was viewed X times between these two dates", then you only need a table with a page ID, date, and counter. Update the counter column on each page view with a single UPDATE query.

Upvotes: 0

Related Questions