0

I have a requirement to implement change management in a database. By change management I do NOT mean database schema or code. I mean data. If for example a SQL database contains a user table with 2 users. Changing the username of user 1 is a "change". Setting the status of user 2 is a "change". Performing these actions would have resulted in two changes to the data in the user table. I need to be able to log these changes (easy) but then be able to rollback to a previous point in time so either test a previous configuration or rollback a bad one. I'm banging my head against a wall wrt to the database design and how to support this - particularly if I introduce linked tables such as Role and UserRole!

Any help or thoughts would be greatly appreciated.

Thanks

user1474992
  • 739
  • 3
  • 7
  • 18

1 Answers1

1

There are a few solutions to this.

The most natural for traditional database design is to introduce the concept of "valid from/until" for each record. For your example table of users, it would be something like:

UserID  UserName Status ValidFrom  ValidUntil ...
============================
1       Bob          1    1/1/2017    2/4/2017
1       Simon        1    2/4/2017    null
2       Mary         1    6/7/2016    7/7/2017
2       Mary         2    7/7/2017    null

Your primary key would be composite - UserID, ValidFrom. That means that all your foreign keys would require this composite to link.

By applying "valid_from/valid_until" to all your tables, you can see what the data status was at any point in time.

For instance, if you have a "roles" table:

RoleID  RoleName   Active ValidFrom  ValidUntil ...
============================
1       Admin          1    1/1/2017    2/4/2017
1       Administrator  1    2/4/2017    null

And a table to map users to roles...

UserID  RoleID .  Status ValidFrom  ValidUntil ...
============================
1       1            1    1/1/2017    2/4/2017
1       1            0    2/4/2017    null

By looking for the user data and the role data for the date you're interested in, you can gather the application state at that point in time. You do need to have "status" flags, rather than deleting records (e.g. in my example, user 1's member ship of role 1 was suspended on 2/4).

It's not particularly straightforward; in practice, when I've used this, I've only used "valid_from/until" on tables where time was important.

An alternative is to address this at the application level - increasingly common with microservices, and eventual consistency. Event sourcing and CQRS are good starting points. However, this will require much more than a database schema.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • I can see this solution working if I only had one table but if I have dependencies such as the UserRole then how would this approach work for all dependencies down the chain? – user1474992 Jul 13 '17 at 12:00
  • @user1474992 - I've updated the question to demonstrate. – Neville Kuyt Jul 13 '17 at 12:10