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:
- The “active flag” approach
- 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 Book
s of a particular traceId
by their modificationDate
s 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.