I'm developing a webapp with users and roles, and I need to keep track of any changes that are made, so that I know when and who has changed a user role.
I have my user table:
CREATE TABLE user( user_id, name );
And my user_role table (one user can have many roles):
CREATE TABLE user_role( user_id, role_id );
I have 2 potential approaches to manage this:
Add some attributes to the
user_roletable so it would be something like
CREATE TABLE user_role( user_id, role_id, change_date, changed_by, ..... );
With this approach, every time a user role is changed, a new entry is created.
Having a new table:
CREATE TABLE user_role_historic( user_id, role_id, change_date, changed_by .... );
I know (think) both approaches might work, but I can't decide which one is better. Can someone help me?