SQL Server Optimization - how to store repeat data

Question

My project has an "Activity Monitor" that monitors the activity of a user.

Currently the way it works is, I have a method called LogActivity and it submits the data to the database. I call this method when a user has a new session start, when a user registers, when they update their profile, etc. Basically there are 3 or 4 different actions that get logged, so I'm storing it as so

ID | UserID | Activity | Date

The activity field has the same data put in every time

1. new account created
2. new session started
3. account updated
4. event posted
5. event voted
6. comment left
7. comment voted

since that could end up taking up a lot of data, would I be better off having a separate table with that info and joining it to the table? if so, do I just enter the ID of the new table manually to the "ActivityID" of the existing table?

What is the best way to achieve the desired outcome without filling up the database with redundant content?

Answer 1

The usual way to avoid repeated data is Normalisation.

BUT, often the requirement for logging is that it be fast, and adding Foreign keys to your logging table will slow down Inserts.

Disk space is cheap. I suggest you perform a back-of-the-envelope calculation to determine how much space you would use per month/year. It might not be as great as you think.

You could perhaps use a 3 (?) character acronymn for the Activity, to reduce space used.

Answer 2

It seems like it makes more sense to use the ENUM type for this and use the ENUM as the Activity, which saves space since this essentially stores an index into the table (see: http://dev.mysql.com/doc/refman/5.0/en/enum.html).

Are you currently storing these activities as strings in the database? With ENUMs, you also have the added benefit of being able to add different logging activities to the ENUM set fairly easily.