Is there a description of the following with advantages/disadvantages anywhere:

ClassTable One table per class in the inheritance structure. Inherited properties are stored in the parent class. SingleTable Maps all fields of all classes of an inheritance structure into a single table. ConcreteTable One table for each concrete class in the inheritance hierarchy. Inherited properties are duplicated in the descendant tables.

Having suffered from locking problems due to the single table at the top of the hierarchy in DO 3 , I want to make sure I pick the correct schema for DO 4.

This thread was imported from our support forum. The original discussion may contain more detailed answer.

asked Sep 29 '09 at 17:58

Tony's gravatar image


One Answer:

General note: you can read about pros and cons of these ways of hierarchy mapping everywhere in the internet: they're absolutely common.

Brief decision map:

  • ClassTable is ideal for deep inheritance hierarchies and queries returning base classes. Currently our query engine is optimized mainly for this case; moreover, we optimize upcoming prefetches for it. If there is no inheritance at all, I recommend you to use this schema as well (it is default) - just to get the better one with high probability in case of future changes in your model; it gives no any disadvantages if there is just a single sealed class in hierarchy. On the other hand, this case implies joins + a single base table for the whole hierarchy.

  • SingleTable is preferable for tiny inheritance hierarchies, or for hierarchies where there is a set of abstract classes and a non-abstract single leaf.

  • ConcreteTable is ideal when you always query for exact types of objects stored there. I.e. you query not for Animal, but for Dog (which is a leaf type in hierarchy). In this case there are no JOINs. On the other hand, if you query for Animal here, there will be UNION in query. Moreover, each index will be "split" by actual types. So if you'd return animals ordered by Age, a set of indexes (IX_Age @ each table) will be processed instead of a single one for ClassTable, and then results will be united. This isn't complex (cost is ~ constant per each row), but anyway...

Few more general recommendations:

  • Think about physical structure of what you'll get from the point of indexes and operations in queries you'll get.

  • If you're never planning to query for potential base type of some object, store it in its own hierarchy. E.g. this must be true for Sex object. Of course you can inherit it from e.g. abstract NameValueBase. But if you will never query for NameValueBase (or this isn't actually strongly necessary), don't put [Hierarchy] attribute on this type. This will ensure its ancestors will reside in their own hierarchies (sets of tables), which is always better if you don't want to query for their bases. So any dictionary entry-like object is perfect candidate for being stored in its own hierarchy.

  • A type (or base type) with huge count of instances (or instances of ancestors) is another good candidate for being stored in its own hierarchy - mixing this huge hierarchy with some tiny hierarchy will simply slow down access to the last one (likely, except ConcreteTable case + leaf-only queries). Choosing correct InheritanceSchema is quite important here as well.

Let me show a bad case. Imagine there is:

  • [Hierarchy(InheritanceSchema=ConcreteTable)] NameValueBase (string Name (indexed), string Value)

  • Star: NameValueBase // Large number of instances

  • ... 1000 more similar types

  • Sex: NameValueBase // Small number of instances

And you write: "from v in NameValueBase where v.Name=="Male" select v".

RDBMS will seek for Male in ~ 1000 indexes (of 1000 tables), some of which are pretty large (e.g. for Star). Bad, especially taking into account the expected result.

answered Sep 29 '09 at 20:09

Alex%20Yakunin's gravatar image

Alex Yakunin

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA