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
.....