I need to create a table that is similar to a many-to-many mapping for "resources" and "systems". Each resource will be mapped to 0 or more systems on which it can run. Each system will be mapped to 0 or more resources that are configured to run on it.

The twist to this is that I would like to map the state of each resource on each system. So, I want a table like this:



State is a UserType, not sure if that will matter much or not. Anyway, I've been looking at ternary maps, but they do not seem to be exactly what I want. They want to add another column (idx), I guess because they think there could be multiple states for the resource/system pair.

Should I just make a domain object that represents this map? What is the best practice for this type of mapping?

One other key point, which I neglected, is that I want to do this with one table. The ternary mapping seems to need two tables to pull this off, but I don't want to maintain two separate tables.

Looks like this is in the FAQ: http://www.hibernate.org/118.html#A10.

Ok, the composite elements solution looked good until I read the fine print, which says, "properties of composite elements cannot be used in a query." With the example table I gave above it sounds like I can't ask the following question:



This sounds like a pretty severe limitation. In SQL I could to this as:



I'm sure there is a good reason why Hibernate can't do this, but is there a work around for this situation?

What I'd like is a table that has these three columns (resource_id, system_id, state) and that I can query on any column or combination of columns.

I think it's doable since Hibernate 2.1beta1.

composite elements may be queried in 2.1