How to store Log in database?

Question

My application let users to send files to each other. Regular users can edit their contacts, change password, etc. In addition, admin users can add/remove users and view the log of what happened. My question is how to store this log in MySQL database ?

I thought to store the log like this:

log_id   time   user_id   action_type                      description
------   ----   -------   ----------------   ----------------------------------------
   1     ....      4      User added         Added new user: alex
   2     ....      1      Contact added      Added contact Paul to group Family 
   3     ....      1      User removed       Removed user: gabrielle 
   4     ....      3      Files sent         Sent files 3,5,7,14 to contacts 2,4,8
   5     ....      8      Group added        Added new group: Family 
   6     ....      8      Password changed   
   7     ....      8      First Name changed Changed First Name from Michael to Misha       

What type would be the best for action_type ? Since new action_types may be added in future, I thought that ENUM won't be a good choice. So I thought to make it VARCHAR(..), like description.

Is this seems reasonable ?

I will be happy to hear any comments / suggestions.

Answer 1

If you're concerned about adding additional action types, make a separate table to store your action types and and join it to your logs table with a foreign key:

logs table:

log_id   time   user_id   action_type_id     description
------   ----   -------   ----------------   -----------------------------------
   1     ....      4            1            Added new user: alex
   2     ....      1            2            Added contact Paul to group Family 
...

action_types table:

id    name
---   ---------------
1     User added
2     Contact added
.....