I'll add some comments on JOINs vs UNION a bit later - I need to know what kind of hierarchy it is.

Preliminary comment: according to you, BusinessEntity and Workstate tables are going to be empty in ConcreteTable case. This indicates ConcreteTable (with UNION) is, likely, a preferable option, since query optimizer will take this fact into account.

On the other hand, I'd consider an option with moving [HierarchyRoot] closer to leafs first.

well, 1. as i told you before i have 4 level of hierarchy max and leaf objects can be max 7 to 8. like leave , Permission , officialPermission etc will inherit from Workstate. 2. i may rarely use base types. 3. i will never create "Workstate" or "businessentity" objects directly.

1. as i have understood , we cannot have two hierarchy roots, i have already one defined on BusinessEntity, and you want me to define on "WorkState" also,in this case, new keys and ids will be generated for "Workstate" and it will not be part of BusinessEntity, but i have some done much logic on BusinessEntity , i have to repeat similar code for all hierarchy roots.

Like for eg, in databinding expression i am type casting object to BusinessEntity n it works but now how i will decide whether is should typecast to Businessentity or Workstate,,,basically i dont understand how moving [HierarchyRoot] closer to leafs will help?

pls advise. thanks

Moving [HierarchyRoot] closer to leafs splits single hierarchy to multiple ones, and thus reduces the number of JOINs / UNIONs - some of them can be absolutely unnecessary in your case.

You need a single hierarchy root only if you're going to:

• Use Query.All<BusinessEntity>()
• Use [Field] BusinessEntity SomeBusinessEntity.

If you don't need these features, you should consider splitting the hierarchy into multiple ones. Obviously, casting and inherited logic will work anyway; you'll still be able to compare Entity.Keys and entities, up/down cast their references and so on.

Moreover, if keys there will be of the same type (e.g. long), and will share the same key generator (by default that's true), all the keys in these hierarchies will be unique anyway (i.e. as if they'd be in case of single hierarchy).

In particular, this will allow you to implement common persistent interfaces (e.g. IBusinessEntity) in any parts of such hierarchies.

as i told you before i have 4 levels of hierarchy max and leaf objects can be max 7 to 8.

My question was about count of instances, but not about depth of inheritance. How many instances are expected there?

As you say that keys will be unique irrespective of heirarchies then i might give a try to ConcreteTable approach, i have already started with ClassTable approach, biliving that Union will take more time then Joins with conditions i have, but i wanted to try ConcTable approach... 2. I may have 7 to 8 instances. thanks han

I may have 7 to 8 instances

If so, it doesn't matter much what kind of inheritance mapping you'll use. SQL Server will handle this fully in-memory.

sorry i got you wrong, if i look old customers then employee workstate it may reach 200,000 records. and like this i have two more heirarachies which can reach close to this number.

200 K records isn't a big number as well: even if record size is 1K, it's just 200 MB, i.e. the whole set will be cached in RAM, and thus joins must be pretty fast.

So my recommendation in this case is: don't fear joins too much in, if you don't expect 100-1000 times more data. I'd try to avoid some them, but only if this don't significantly affect on design. I.e. if I'd really need an ability to query for some base type, I'd keep it as hierarchy root.