Database implementation in order to save user activity information

I am using Ruby on Rails 3 and MySQL.

In my project I would like to create an activity-stream "modu开发者_开发问答le" in order to save each user action information in a dedicated user table. That is, to create a database table for each user.

Is it a good approach to create a database table for each (new registered) user in my application?

No, it is not a good approach. Why would you create a separate tables with all the same fields? Just add user_id to your table and store all info for every user in there.

I do something similar, and it's not necessary to create a whole table for each user. For example, I have a table called "user_actions", and in it there is a column, "user_id".

The relationships are:

User has_many :user_actions

UserAction belongs_to :user

And you're done. Let the foreign-key relationship that comes naturally take care tying the specific action to a specific user.

Once you do that, you only need to decide:

1. Which actions cause an entry to be added?
2. How long should you retain the data (1 week, 6 months)?

For example, on my site, I keep a log of the last 5 things a user viewed, and present that list to them on a section of the page called "Recently viewed items" for convenience.

I also have a separate table called "admin_actions" that I use for security logging that keeps track of everything done under an admin account, and what admin account made what sort of change.

I guess the answer is that it depends on how many users there are. If it's not a small, defined number then I'd suggest that it's not a good idea to create one table per user.

I'd suggest a single table with one column being a unique identifier for the user. Make sure that whenever you're querying the table that you're using an index that has this column as the first column in the key. E.g. PRIMARY KEY(user_id, activity_time)

This should allow for fast and efficient reading of the rows.