I'm trying to map a one-to-one and one-to-many (or many-to-one) relationship between two entities, through an intermediate table, and it doesn't look like Hibernate supports this mapping without using an intermediate entity to join the two.

Here's my schema:

CREATE TABLE cap_sets (
cap_set_id serial primary key
);

CREATE TABLE cap (
cap_id serial primary key,
cap_set_id integer references cap_sets ON DELETE CASCADE,
cap_type varchar(20) not null,
target_type varchar(20) not null,
period varchar(50) not null,
value numeric(16,6)
CHECK (period IN ('daily', 'running')),
CHECK (cap_type IN ('budget', 'quantity')),
CHECK (target_type IN ('account', 'order', 'order_sheet')),
UNIQUE (cap_set_id, cap_type, period)
);
ALTER TABLE account ADD COLUMN cap_set_id integer references cap_sets;
ALTER TABLE order_sheet ADD COLUMN cap_set_id references cap(cap_set_id);
ALTER TABLE common_order ADD COLUMN cap_set_id integer references cap_sets;

So, we have Accounts, OrderSheets, and Orders with a one-to-many relationship with Caps. However, I need the UNIQUE constraint in the cap table (with the cap_set_id) to enforce that there never exists more than one cap of a particular type on a particular "capped" entity (one of Account, Order, or OrderSheet). I could do this using a single cap table and an any mapping, but then I'd lose referential integrity.

I'm looking for an object model that looks like:
public abstract class Cap {//...}
public class OrderCap extends Cap {
public Order getOrder();
// ...
}
public class Account extends Cap {
public Account getAccount();
// ...
}

and possibly methods like Account.getCaps(), Order.getCaps(), etc., although I really don't need navigability in that direction.

Does Hibernate support this mapping without introducing an intermediate CapSet entity to handle the mapping to cap_sets from the other tables? None of the permutations of <join>, inverse, many-to-one, and one-to-many mappings seems to handle the foreign keys being in the end tables instead of the mapping table.

Thanks!


Hibernate version: 3.2.1
Name and version of the database you are using:
PostgreSQL 8.2