0

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:

  1. Add some attributes to the user_role table 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.

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

  • Sort of off topic. You don't specify the SQL server product you are using, but modern versions of MS SQL server have built in functionality to directly track changes to data. (Besides which the traditional approach is to use an audit trigger.) – Peter M Aug 21 '17 at 11:12
  • there is the "hibernate envers" module that does that for you. – Walfrat Aug 23 '17 at 11:18
  • @PeterM Triggers tend to be the last place a developer looks when tracking a support issue so these should be used sparingly. – Robbie Dee Aug 23 '17 at 12:04
2

I'd go with the second one if you're doing it that way; the ideas "user" and "user role change history" are two different things. It'll make it easier to differentiate between the actions "check what roles a user has" and "check when a user role was changed and by whom."

  • 1
    The second approach also allows to place the historic data into a separate database which is optimized for this kind of data which never get deleted... – Timothy Truckle Aug 20 '17 at 10:54
  • I would use triggers to store the changes to make sure all changes made with all applications, including database tools, are written to the audit trail. – Kwebble Aug 22 '17 at 20:07
  • One step at a time ;) – ac1dh0n3ycl0ud Aug 22 '17 at 20:48
0

In general it depends on how you want to access the data. For this particular case I think you look at the data in two different ways. One what is the current role and what was the user's role at this time and the queries are used in different ways e.g. the security authorisation always looks at the current one whilst the history is a separate report.

Keeping the data in one table is useful if every query uses a time ie the query is always get me the data as at this time. For more detailed use see temporal databases e.g. the answer here

0

The example provided by your second approach seems flaw : usually fields like changed_by and changed_date are in the main table. And the history of changes is in another dedicated table.

This is because you may need to know the last modification date without hitting the history table (which you want to avoid when not necessary).

Also note that there is an hibernate module to do the stuff for you : http://hibernate.org/orm/envers/

-1

Solution 2 is better, because the history is stored independently from the audited table, so it is retained even if the original record is deleted. However, it would even be better to expand it to cover inserts and deletes as well. So add an action field to indicate Insert, Update, Delete:

CREATE TABLE user_role_history ( user_id, role_id, change_date, changed_by, action .... );

  • 1
    explanation of why you think that solution 2 is better seems to be missing, could you please edit the answer to expand on that? – gnat Aug 21 '17 at 12:29
  • Since the table only seems to hold ID's to other tables there will only be inserts and deletes. – Kwebble Aug 22 '17 at 20:08
  • I was referring to inserts/deletes on the original table. Not the history table. – Alex Schievink Aug 23 '17 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.