How to store login database?

Question

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

I thought of storing the log as follows:

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 

Which type would be best for action_type ? Since a new action_type may be added in the future, I thought ENUM would not be a good choice. So I decided to do it VARCHAR(..) , for example description .

Is this reasonable?

I will be happy to hear any comments / suggestions.

Answer 1

If you are concerned about adding additional types of actions, create a separate table to store your types of actions and attach it to the log table using 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 .....