2015-11-08

How to display user-friendly entity history

Most systems need data audit/versioning. This usually means that for every audited table FOO there is table FOO_AUD, which contains the current and all previous values of records from FOO. FOO_AUD can be populated using Hibernate Envers or simply by triggers on table FOO.

The question is, how to present historical/audit data to the user? Suppose we have a bug tracking system with tables ISSUE and ISSUE_AUD. Obviously we could display the results of the following query:
select * from issue_aud where issue_id = 10;

 rev  | revtype | issue_id |    issue_title    | issue_status | issue_assignee
------+---------+----------+-------------------+--------------+----------------
 1000 |       0 |       10 | Bar always fails! | Open         |
 1011 |       1 |       10 | Bar always fails! | In Progress  | Developer A
 1037 |       1 |       10 | Bar always fails! | Resolved     | Reporter B
 1057 |       1 |       10 | Bar always fails! | Closed       |
but that's not readable and user-friendly. Ideally, we would like to present data in the following format:
 rev  |    property    | prev_value  |    curr_value
------+----------------+-------------+-------------------
 1000 | issue_status   |             | Open
 1000 | issue_title    |             | Bar always fails!
 .... | ...........    | ..........  | .................
In this post I would like to show you how this can be done using SQL query only.