With Hibernate, can I mix table-per-subclass and a <map> within the same table?

Question

I'm attempting to map an object model for "Promotions"; i.e., a customer enters a promo code during signup.

Different promo codes might have different types of "benefits", meaning what we are giving to the customer. For example: promo code XYZ123 will give the customer free minutes in his or her account, while a different promo code will offer various discounts off of the various subscription plans that the user can choose from when signing up.

In my domain model, I've modeled the different types of benefits as subclasses with an abstract superclass which has the common properties. I've chosen to model the different types as subclasses since some types of benefits will need different properties.

public abstract class Benefit {
 //getters/setters for common attributes
}

public class FreeMinutesBenefit extends Benefit {
 public int getFreeMinutes() {...}
 public void setFreeMinutes(int minutes} {...}
}

public class PriceDiscountBenefit extends Benefit {
 public Map<Plan, BigDecimal> getDiscountMap() {...}
 public void setDiscountMap(Map<Plan, BigDecimal> map) {...}
}

Rough SQL schema:

-- Parent table, maps promotion to benefits
create table Promo_Benefit (
 map_id integer auto-generated PRIMARY KEY,
 promo_id integer references PROMOTION(promo_id),
 type_id integer references BENEFIT_TYPES(type_id)
);

create table BenefitDetails_FreeMinutes (
 map_id integer PRIMARY KEY,
 minutes integer not null,
 FOREIGN KEY (map_id) references Promo_Benefit(map_id)
);

create table BenefitDetails_PriceDiscount (
 map_id integer references Promo_Benefit(map_id),
 plan_id integer references Plans(plan_id),
 reduced_price numeric not null,
 PRIMARY KEY (map_id, plan_id)
 FOREIGN KEY (map_id) references Promo_Benefit(map_id)
);

I'm able to successfully map the basic properties of each subclass in my Hibernate mapping files, but I'm having trouble figuring out how to map the association between the PriceDiscountBenefit subclass and the Plan class.

I imagine this is because the primary key of the subclass table (BenefitDetails_PriceDiscount) isn't simply the map_id column - in other words, multiple rows in this subclass table will form a single PriceDiscountBenefit entity. From what I can see, it seems as if table-per-subclass support in Hibernate is meant for instances where a single row in the subclass table maps to a single row in the parent table - and mapping a <map> should refer to a second table which contains the keys/values.

Am I mapping this completely wrong? I can't tell if I'm having trouble because I'm butchering the table-per-subclass pattern.

Answer 1

Table-per-subclass strategy means you will have a table for each class in your hierarchy (including abstract classes, if any) and they will all have shared primary key. In your case that means you would have a separate table for each of Benefit, FreeMinutesBenefit and PriceDiscountBenefit classes. Your table names are a bit confusing and table structure is somewhat inconsistent with table-per-subclass.

Assuming that map_id is the shared primary key, BenefitDetails_PriceDiscount table must define it as such. plan_id belongs to a different ('mapping) table that will hold a map between PriceDiscountBenefit, Plan` and the decimal value. In other words:

create table BenefitDetails_PriceDiscount (
        map_id integer PRIMARY KEY,
        ... /* any other attributes, perhaps? */
        FOREIGN KEY (map_id) references Promo_Benefit(map_id)
);

create table BenefitDetails_PriceDiscount_Map (
        map_id integer references Promo_Benefit(map_id),
        plan_id integer references Plans(plan_id),
        reduced_price numeric not null,
        PRIMARY KEY (map_id, plan_id)
        FOREIGN KEY (map_id) references Promo_Benefit(map_id)
);