MySQL: Best way to save user history

[Jeff_H]

My site-in-development, if you will, will consist of a large number of pages. When the user visits a page and takes a certain action, the page should be marked "1" in a mysql table, along with the user id # and the action taken.

There are currently two tables:

TABLE USERS
includes the user id #, username, password, etc

TABLE PAGES
includes the page id #, other info about the page

When a user visits page#x a mysql query will be made to determine if the visitor has already taken the certain action on the page.

Keeping in mind that this website should be scalable, and that the queries will be made often, what configuation would be better for, say, 5000 users and 30000 pages:

- a table for each user with fields (pageid#, action) = possible 30000 rows
- a table with fields (pageid#, action, userid#) = possible 150000000
- or is there a better way entirely to do this?

[coopster]

Have you considered using cookies at all?

[mattur]

I'm working on a similar thing. I think for max speed there's a couple of factors to focus upon:

From a normalisation POV then prob. the standard table structure would be three tables: Users, Pages, and User-Pages i.e. a many-to-many relationship between users and pages.

Once the visitor is in the Users table, and assuming all the pages are already in the Pages table, then for each page visit you just have to append a row to User-Pages with the UserID (from a cookie) and PageID and Action - which I think should be reasonably quick.

Another thing I'm thinking about is handling bots: since they don't accept cookies when a bot visits multiple pages it will get a unique UserID each time. I'm not sure of the best way to handle this, though maybe checking the UserAgent string for bot and spider could avoid this.