NHibernate table per hierarchy mapping issue

Question

We have the following table structure:

tbl_A 
   A_ID INT PK 
   Type_ID INT 

tbl_EA 
   EA_ID INT PK 
   A_ID FK to tbl_A 
   C_ID INT (reference to other table based on Type_ID from tbl_A) 

tbl_C 
   C_ID INT PK 

So, we're thinking tbl_A is abstract class, and each type is a subclass, discriminator based on Type_ID. But then we run into problems handling tbl_EA. It has a "fake" discriminator, in that you join to tbl_A in order to determine the type. This is b/c having a Type_ID column in tbl_EA is redundant, you have to join on tbl_A anyway to get that data (there are more columns to the tables, but not needed for this). Then, we have to somehow map the relationship b/w tbl_C and tbl_EA.

SQL to get all EA items for a C entity: select * from tbl_EA where C_ID = AND A_ID IN (select A_ID from tbl_A where type_id = ).

Any ideas on how to set up mapping for these tables, and corresponding relationships? I feel like answer may lie in the formula of a discriminator, but can't figure it out.

Answer 1

You can't have the discriminator do a join.

Is A just a list of types, or an actual entity? Are A and EA one-to-one?

Generally, it's best to start with your desired object model and work from there, even if your table structure is fixed.