Implementing a data history / versioning solution for a Hibernate-based application (with a twist).

Question

First the basic facts: Java webapp, Spring, Hibernate, MySQL.

The situation is that I have a complex object model of e.g. a Car. It's composed of many objects (Engine, Tires, ...) with many-to-one and one-to-many relationships between them.

Now there are many cars, and every now and then someone inspects a car and creates a Report of the inspection. Report refers to the many parts that car, displaying their properties etc.

So far the system hasn't supported the ability to update the car's and its parts' properties once they have been fed into the system. Meaning that if a color of the chassis or number of tires were changed, the old reports would reflect this change which is not what we want.

Well, now this feature has been requested. The cars and their parts need to be modifiable and a version history has to be created. The old reports need to refer to the old versions of the parts and their values.

I've been looking at "Slowly changing dimensions" and it seems that the versioning of the car and its parts could be done with the Type 6 approach.

The thing (the twist) that I'm having trouble figuring out (probably due to my limited Hibernate experience) is this:

How can I assemble my Report instances with Hibernate so that they refer to the correct versions of each part of the car? The reports have a date and each version of the car parts would have date ranges when they were the valid, so I guess I could do it with some complex HQL/SQL. But is there any easier, more automatic way to do it with Hibernate?

Answer 1

MySQL supports triggers. Setup a trigger so that whenever a row is changed, the trigger copies the row into a "archive" table, along with a timestamp. That way, all previous data versions are maintained, which the reports can be run against.

Answer 2

You can take a look at JBoss envers for versioning your objects. I'm not sure that it is suitable for your usecase, but give it a look.

Answer 3

I've used the approach you suggest (Type 6) with Hibernate and it worked fine for me. The queries for the reports did get a bit more complicated but not that much, since the same clause was needed for all queries (e.g. ' and :reportTime >= x.startTime and (:reportTime < x.endTime or x.endTime is null)').

I created an interface and a base class (interface was only needed for temporal sub classes whose parent was not temporal) for entities that supported this approach with 2 properties (e.g. startTime and endTime), and a base class for DAOs working with temporal entities that had some functionality often needed. Things that I put in this base DAO:

1. Preventing changes to instances whose startTime had passed (except setting the endTime to a future time)
2. Automatically closing (i.e. filling the endTime) the previous instance if a new instance was added (e.g. oldInstance.endTime = newInstance.startTime)
3. Adding the standard clause to select the current entities at time of query to HQL queries.
4. Dealing with duplicates if for some reason two valid instances/versions were found at a moment in time (I ordered my queries by 'startTime desc' and took only the first returned)

The only part where I found it really messy was working with plain SQL queries also running against the database, where the extra clauses were needed on joined tables or in sub-selects.