Hibernate version: NHibernate 1.2.0.GA

I am trying to figure out how to get NHibernate working with our valid time temporal database. The database is an Oracle database and every table is a valid time table, therefore every table needs a VALID_FROM and VALID_TO column in it. There is no data, other than the surrogate key, that doesn't need to be versioned. By that I mean that the business value of this versioning is for reporting purposes between periods and being able to look at a previous state of the data. Even the name of an entity can change completely between versions, we only need to keep track of which name it used to possess.

I can see three solutions:

1) Applying the filter available in NHibernate. This is specifically enabled for temporal data, and works wonderfully for classes and one-to-many collections. It doesn't work for many-to-one relationships though, as described at http://forum.hibernate.org/viewtopic.ph ... poral+data. I completely agree with mgl, without this feature there is no way temporal data can be supported as part of Hibernate. (Or if it can, I'd like to know how this is done.)

2) Use the Workspace Manager Valid Time Support that is part of Oracle, described at http://download-west.oracle.com/docs/cd ... ong_vt.htm. This is a possible solution but I do not know how well it will work with Hibernate. From the issues that user530397 has at http://forums.oracle.com/forums/thread. ... ID=1551355 I suspect this would be a non-trivial implementation.

3) Create a table and table_version for each table in the database, as VampBoy discusses at http://forum.hibernate.org/viewtopic.ph ... poral+data. Since every table in our system would need this and has no other data that will not be versioned, this is a poor solution for us. We'd have a table with nothing but unique surrogate keys and all the other data in the table_version table.

The best solution I can see is the implementation of many-to-one filters in Hibernate. Option two, I believe, will be fraught with issues. Option three almost isn't really an option at all, we'd be changing our data model solely for the purpose of making it work correctly with Hibernate. I'd rather drop Hibernate than double the number of tables in our database.

I would be very appreciative of any suggestions people have. I've been working on this issue for some time now and can't find a way to do this that wouldn't cause many potential issues.

To make it clearer, I'll illustrate the different approaches I've identified and how our project differs from that. (Appologies for the table formats, I couldn't get them to look better.)

From what I can tell most people implement valid time temporal databases with hibernate by doing something like:

Schema 1A

Person
ID PersonName
-- --------------
1 Sally
2 Bob

PersonHistory
ID ValidFrom ValidTo FkEmployerId Salary
-- ----------- -------- --------------- -------
1 2007/6/1 2007/9/1 1 40,000
1 2007/9/2 2007/10/1 2 45,000
2 2007/6/1 9999/12/31 1 22,000

Employer
ID EmployerName
-- ----------------
1 Ms Jones
2 Mr Xavier

EmployerHistory
ID ValidFrom ValidTo EmployerAddress
-- ----------- -------- ----------------
1 2007/6/1 9999/12/31 Smith St
2 2007/6/1 9999/12/31 Hopps Rd



However, our schema, if we implemented it in a similar fashion, would look like:

Schema 1B

Person
ID
--
1
2

PersonHistory
ID ValidFrom ValidTo FkEmployerId PersonName Salary
-- ----------- -------- --------------- --------------- -------
1 2007/6/1 2007/9/1 1 Sally 40,000
1 2007/9/2 2007/10/1 2 Sally 45,000
2 2007/6/1 9999/12/31 1 Bob 22,000

Employer
ID
--
1
2

EmployerHistory
ID ValidFrom ValidTo EmployerName EmployerAddress
-- ----------- -------- ---------------- ----------------
1 2007/6/1 9999/12/31 Ms Jones Smith St
2 2007/6/1 9999/12/31 Mr Xavier Hopps Rd



By that I mean the Person table would have nothing but the surrogate ID in it. And our entire database is like this! (Obviously, this isn't the best example because one normally *would* record the PersonName/EmployerName in the People/Employer table, but nowhere in our schema would we do something similar). So, our database is like:

Schema 2

Person
ID ValidFrom ValidTo FkEmployerId PersonName Salary
-- ----------- -------- --------------- --------------- -------
1 2007/6/1 2007/9/1 1 Sally 40,000
1 2007/9/2 2007/10/1 2 Sally 45,000
2 2007/6/1 9999/12/31 1 Bob 22,000

Employer
ID ValidFrom ValidTo EmployerName EmployerAddress
-- ----------- -------- ---------------- ----------------
1 2007/6/1 9999/12/31 Ms Jones Smith St
2 2007/6/1 9999/12/31 Mr Xavier Hopps Rd



-------------------------------------------------

Hibernate

There isn't a problem with the schema 1 type of temporal database, we use filters. However, with ours - schema 2 - we need filters on the Many-to-One relationships, but Hibernate doesn't implement the filter on such a relationship. And we have no way, from what I have found, to integrate this schema 2 type of database with Hibernate.

Schema 1A mapping



Since the Many-to-One of Employer maps to a unique ID, Hibernate has no problems. We can then do something like: aPerson.Employer.Details.EmployerAddress

Schema 2 mapping



We'd want to be able to do something like: aPerson.Employer.EmployerAddress, but of course it doesn't work because there is no filter being applied to the Many-to-One relationship.

Right now I am having a look at the NHibernate code to see if I can implement the change displayed at http://forum.hibernate.org/viewtopic.php?t=976233, and wouldn't mind contributing it to the next version if it gets accepted, though I have never contributed anything to a open source project, and I'm not sure how to go about it.

All I have to say is that I feel for you, lol.

I'll throw in my 2c and you can take it for what its worth.

I'll address options 2 and 3 as option 1 I thought I wanted at one point and now I'm not convinced I would.

Anyways, option 2 Oracle Workspace Manager. That was our first attempt at our time slicing and from my experience a few years ago it wasn't ready for prime time. IIRC, you have to enable versioning on your db tables which will actually spawn off extra tables under the covers. You end up with further restrictions as to what you have now, i.e. you can only use 27 character table names as the spawned tables tack on 3 characters or so.

In addition we had to have our DBAs on multiple occasions troll system tables manually as we ended up blocked with not being able to fully enable versioning but then we couldn't drop our tables because versioning was half enabled.


Option 3, which we settled on and are still using and expanding on. Some of our tables actually have just what you describe. A surrogate key and nothing else. This allows us to maintain our referential across our multiple versions and is relatively transparent when coding against and we wouldn't have been able to accomplish what we did w/o leveraging Hibernate's filtering framework. I understand not wanting to completely disrupt your data model as we were very particular on what we wanted to version.

I'm curious, in your case what would you use as your FKs to maintain your referential integrity?

Hi VampBoy

I'll give you a little bit of the project history to explain why it is the way it is.

Until I turned up, for the last ten plus years or so the system was merely a pretend temporal database. Every quarter the database was copied and all the data was put into a new set of tables with a name corresponding to a new quarter. So "20071_TableA" would become "20072_TableA" and then "20073_TableA", etc. There wasn't any need to relate any previous period with the current one, and so everything kind of worked. There were no duplicate surrogate keys because we duplicated the whole god darn database!

I turned up and within a couple of days screamed "yuck!" without fully realising, until last week, the implications of my protest. I'd never used a temporal database, and only now realise what is going on and how many issues they create. If it weren't for the fact that we need to do reporting across quarters and for other minor reasons (data duplication, maintenance) I'd almost suggest going back to the previous model.

So possibly we don't even need to version every bit of data, but everyone thinks we do because that's kind of what we've been doing for the last ten plus years. (And maybe that's where the issue lies, not with Hibernate or Oracle, but with us.)

Referential integrity? What's that? Haha. That's just the thing, as far as I understand we couldn't do it at the database level. We'd have to put in checks elsewhere. Hm... the more I think about this the more I think there might be something wrong with our approach. i.e. Letting history cloud the judgement.

Thanks a lot for your suggestions, especially the stuff on the Oracle solution. I was somewhat suspicious of it to begin with and even though I'm sure it is more stable now I think it might be wise to stay away.

Okay. Done.

Turns out we were versioning everything because "that's just what one does" and I've managed to convince people that there are a whole pile of situations where we don't need to do it, nor even want to.

So option 3 is fine. Sure, we'll have whole tables that have nothing but unique surrogate IDs in it, but also tables that aren't versioned at all.

Everything is good. Hibernate will do as I ask and we don't need to try to use some Oracle specific solution.

Now it's just about getting down to everyday development.

Our original project started as a replacement for the same thing you describe. Every month the bean counters would essentially clone the whole data structure and move forward. It works (mind you in a twisted way)....until you need to correlate the data somehow.

Hopefully you don't have all of the requirements we have based on where the date bounds are as we do. It works pretty well once you get the kinks worked out.