Object-Relational Transparency II: the Designs

I was left feeling a little dissatisfied with this morning's post about the dangers of object-relational transparency. Although I felt I had communicated the high-level point I was trying to make, without the designs, it feels somewhat abstract.

In this instance, I was trying to map an an entity, Agreement, to different versions. Most of these versions would be effective for a particular timeframe (one month to twelve months), but some would be entirely superceded by the next version.

In the database, it seemed sensible to map this structure as follows:

create table AGREEMENT_VERSION
(
AGREEMENT_ID number not null,
EFFECTIVE_DT date,
-- Other Stuff here
)

The first version would have an effective-date in line with the agreement start, while subsequent versions would take on later effective dates. When a later version's effective-date is the same as a previous one's, the previous version's effective-date would be nullified, indicating that it was never effective.

Simple enough, so on to the object model. I wanted to be able to quickly run through the versions in effective-date order to find the version effective for a given date. Hibernate supports sorted collections via SortedSet and SortedMap. Trying to map the versions into a SortedSet is where it all starts to fall apart.

Set items are unique, requiring me to have a sort order across all items. For superceded versions with no effective date, there's no obvious sort order. I toyed with some silly ideas including using the hash code, but none felt quite right. I was trying to impose an arbitrary order on unordered things in order to support order for the ordered things, just because both happened to be stored within a single table.

After chatting with a few colleagues, partly design discussion and partly venting, I was struck, as I said this morning, with the realization that I was trying to force-fit my data model design onto the object model. Hibernate supports where clauses on collections, which allowed me to put the superceded versions into one collection and the effective versions in another:

<set name="effectiveVersions" table="AGREEMENT_VERSION" sort="natural" where="EFFECTIVE_DT is not null">...</set>
<bag name="archivedVersions" table="AGREEMENT_VERSION" where="EFFECTIVE_DT is null">...</bag>

Et voila; the object model makes sense, the data model makes sense, and the two meet happily inside a Hibernate mapping file. How sweet.