Hibernate version: 3

We have three important objects that are very frequently read, but only infrequently linked to each other.

IncomingCar <0..1-1> Repair <1-0..1> OutgoingCar

IncomingCar and OutgoingCar are both subtypes of Car and share a bunch of properties.
The Repair object works as a join-table between IncomingCar and OutgoingCar. Occasionally the OutgoingCar changes after the Repair link has already been established (it is relinked to another Repair).

For performance reasons we would like to map the three objects into a single database table (i.e. denormalize the db model). Unfortunately we want all three objects have their exclusive primary key (because the keys must be stable, remember that we'll relink them later).

IMHO the obvious component mapping is not appropriate here, because the relationship between the objects is a simple association and not a composition. Furthermore we need three primary keys for it.

Any chance to map this with Hibernate ?

I dont see how putting all three of those objects into one table can be a performance enhancer.

IMHO, the obvious starting design would be to put IncomingCar and OutgoingCar into the same table, and then have a separate Repair table, and maybe a status table (IN/OUT), and several relation tables using the IDs to link them all together. If this design isn't good enough performance-wise, then maybe the app architecture should be examined, as this is standard for many large sites.

If you need to stick with the "same table" design, give some more background so I can see better what you are trying to do.

d

Thx for the reply, Dan,

IncomingCar <0..1-1> Repair <1-0..1> OutgoingCar

I need bidirectional associations between IncomingCar and Repair, and between OutgoingCar and Repair. These relations are frequently traversed in the program, so it should be fast (by eager fetching all three records in one select).
The repair is no more than a join table between IncomingCar and OutgoingCar.
IncomingCar and OutgoingCar can't exist without a Repair, and there's no Repair without at least an IncomingCar or an OutgoingCar, but one of them can be null.

This would be no problem with SQL, as it's easy to map these entities to three tables, but it's impossible to map this with Hibernate, because IncomingCar and OutgoingCar have a common baseclass (Car) and thus we have two polymorphic bidirectional 1:1 associations to the same table.
Hibernate can't join both objects with one select (don't ask me why, it just doesn't work, although this would be no problem in SQL. I always get two select statements instead of one).
We 'solved' this issue by defining two unidirectional associations between IncomingCar and Repair, and two additional unidirectional associations between OutgoingCar and Repair, but that's stupid, although it worked (with some problems, but at least Hibernate joined the tables with one select statement).

So the obvious idea was to put all three objects into one table.

I see more of what you are doing now.

If you want to get Hibernate to grab all three in one query, I think you can add "outer-join='true'" to the XML mapping file, and hibernate will fetch the associated properties when it fetches the main object. Did this not work?

If that does work, then you get your single select hapiness, and can pick whatever object model works best. I've had a lot of frustration with hibernate's polymorphism -- so much so, that after a year of running it in production, I just refactored all of my classes to remove all polymorphic classes from my hibernate model. What I replaced it with, mapped to your situation, was something like this:

- Car object
- IncomingJob object that contains a many-to-one mapping to a Car
- OutgoingJob object that contains a many-to-one mapping to a Car
- Repair object that contains a mapping between IncomingJob and OutgoingJob.

(That repair object mapping could be one-to-one or many-to-one depending on the business rules.)

So, now you are at 4 tables. BUT, if you map all of the associations as outer-join='true' I think it will pull them all in one SQL statement.

let me know how it works out...

No, that doesn't work.

Hibernate just can't fetch it with one SELECT under the following circumstances:
* Polymorphism
* Two 1:1 associations to objects that are mapped in the same table
* The 1:1 associations aren't mandatory

It's no problem to fetch these things with SQL, but Hibernate just doesn't do it.

I think I'll have to go with JDBC.

I agree that Hibernate's polymorphism support is very limited (not because of a bad implementation, but because the proxying thing just can't handle it, this it a theoretical limitation).

No, that doesn't work.

Hibernate just can't fetch it with one SELECT under the following circumstances:
* Polymorphism
* Two bidirectional 1:1 associations to objects that are mapped in the same table
* The 1:1 associations aren't mandatory

It's no problem to fetch these things with SQL, but Hibernate just doesn't do it.

I think I'll have to go with JDBC.

I agree that Hibernate's polymorphism support is very limited (not because of a bad implementation, but because the proxying thing just can't handle it, this it a theoretical limitation).