Auditing & Approval of a Database Entity

In this post we address a problem in the web programming context. Imagine our customer wants us to develop a web application that provides a standard CRUD user interface for a very important, very special, mission-critical entity: a Book.

class Book {
  String title
  String isbn
  Date releaseDate
  List<String> authors
}

Ok, that’s quiet easy. Start your favorite IDE with your web framework of choise and use its scaffolding capability to generate the CRUD controller and the CRUD views.
Now, suppose our customer wants a book to be traceable. That is, when creating, updating, or deleting a book, the corresponding editor and the modification date must be accessible in addition to the modification itself.

There are several approaches to achieve this goal. We discuss the following ones in more detail:

  1. The “active flag” approach
  2. The “history table” approach

The “active flag” approach

In this approach we extend the Book entity by the following attributes:

class Book {
  String title
  String isbn
  Date releaseDate
  List<String> authors

  boolean deleted

  String editor
  Date modificationDate
  long traceId
  boolean active
  long versionNumber
}

First, we add the deleted flag to indicate that the given book was deleted.
Without this flag, we would not know whether the latest version should be displayed or not.

In addition to the required editor and the modification date, we add the traceId attribute.
It represents a constant identifier that is generated by the initial version of a Book instance and passed to all its following versions.
In this way, we can trace modifications of a Book instance.

For example, when sorting all Books of a particular traceId by their modificationDates in descending order, we get the latest version at the top and the first version at the bottom.

Basically, we are finished now.
The four new attributes fully implement our customer’s requirements (apart from the missing implementation logics).
However, it is common practice to add an active flag and a versionNumber attribute to ease the writing of database queries and to increase the query performance.
The active flag indicates that a given book instance is the latest version and is not marked as deleted.
The versionNumber attribute represents the version number of the given book instance. It is not necessary for the approach to work properly. However, it could be useful for the user in order to know how often the book has already be modified.

The “history table” approach

In this approach we extend the Book entity by the following attributes:

class Book {
  String title
  String isbn
  Date releaseDate
  List<String> authors

  boolean deleted

  String editor // a user which could have also been deleted
  Date modificationDate
  // removed traceId
  // removed active
  long versionNumber
  OldBook predecessor
}

The attribute predecessor of a book refers to the book’s previous version in the new table OldBook. This table contains all previous versions of all books and thus represents the books’ history. It has the same structure as the Book table.

class OldBook {
  String title
  String isbn
  Date releaseDate
  List<String> authors
  
  boolean deleted

  String editor // a user which could have also been deleted
  Date modificationDate
  long versionNumber
  OldBook predecessor
}

This approach works as follows. Everytime a book is being modified, its attributes (before the change) are stored in a new OldBook instance. Afterwards, the modification is applied to the book and its predecessor is set to the new OldBook instance.

The main difference to the previous approach is the separation of the current version and all its old versions. This approach requires a less complex query to list (a subset of) all current books. Furthermore, it does not require to index an attribute to perform this query in a fast way. In contrast, the previous approach needs to index the active attribute for an efficient execution. Finally, it is a modular approach. If you want to introduce versioning for your existing book table, you do not need to change your queries to list all current books. You only need to add the attributes, necessary for versioning, to the Book table and the OldBook table.

Moreover, we directly represent the order of modifications by using the foreign key predecessor in the OldBook table. However, you can stick to the traceId  attribute, if you like.

Leave a Reply

Your email address will not be published. Required fields are marked *