Concepts - Entity inheritance and relational models

This section describes the phenomenon called 'entity inheritance' and its connection with relational models and the physical data model. It presents two typical entity scenarios and offers insight in how these scenarios are represented in a physical model. Mapping an entity hierarchy in LLBLGen Pro is discussed by describing the two most common ways of mapping entity hierarchies onto tables / views and which are also the two inheritance types which are supported by LLBLGen Pro.

The approach of this section will answer the questions: "why would you use it, and if you want to use it, how would you implement it", to make it more understandable why entity inheritance can help you with your project and also for which inheritance can be helpful and thus for which situations you also might want to consider another approach.

Abstract entity models and entity inheritance

In abstract entity models, entities can derive from another entity, for example to specialize the definition of that other entity. Typically the derived entity is called a subtype and the entity derived from is called a supertype.

LLBLGen Pro uses this same terminology: an entity which has subtypes, is called a supertype, and an entity which is a derived entity is called a subtype. In the following sections, two hierarchy types are described which are typical for many situations. The hierarchy types are illustrated with screenshots from such a model in the LLBLGen Pro designer.

Hierarchy creation for proper entity relationship modeling

First an example of a hierarchy which is constructed to utilize proper entity relationship modeling:

This model is a model view on a larger model (the other part is described in the next sub-section), which illustrates three entity types in an inheritance hierarchy (Employee, Manager and BoardMember) and different relationships per entity. The diagram further shows a relationship between Employee and Department which illustrates the semantic "Employee works for Department" relationship.

Manager and BoardMember also have relationships: Manager has a second relationship with Department (next to the one it inherits from Employee): "Manager manages Department". BoardMember has a relationship with CompanyCar, to illustrate the semantic relationship "BoardMember has a CompanyCar".

To use the last mentioned relationship, "BoardMember has a CompanyCar", as an example: this relationship is defined on the entity BoardMember because only BoardMembers are allowed to have a company car, in this particular domain. Would the relationship be placed on Employee, every employee would, in theory, be able to have a related CompanyCar entity, and thus a company car.

There are two main ways to construct a physical representation in tables from a hierarchy like this example:

1. All entities in the inheritance hierarchy are mapped onto the same table/view, which contains all fields/attributes of all entities in the hierarchy.
2. Every entity is mapped onto its own table/view which contains only the fields of the particular entity, and which has a pk-pk relationship with its supertype (if any).

The first way, which is called in LLBLGen Pro Target Per Entity Hierarchy and which is discussed more in the next subsection, will define the aforementioned relationship "BoardMember has a CompanyCar" on the same table / view as in which normal employees are stored. This can be a problem, as it's then up to program logic to limit the insertion of employee data so a normal employee can't have a company car.

A better approach in this situation, is the second option, where for each entity in the hierarchy a table/view is created and on the BoardMember table the relationship to CompanyCar is defined. This second way is called TargetPerEntity in LLBLGen Pro.

Physical representation in the relational data model

The typical hierarchy mentioned above is realized in your datamodel with one table / view per entity. This means that for the hierarchy above you'll get an Employee table / view, a Manager table / view and a BoardMember table / view.

The Employee table is the leading table (root of the hierarchy), where you define the primary key for the entity to uniquely identify an entity instance in the database, in this case Id is chosen to be the PK. Manager and BoardMember inherit this  field, to identify the rows stored in these tables, though they're not new PK values, but have a foreign key constraint defined to the pk of the supertype's table.

This means that the table Manager is mapped on has a foreign key constraint to the table Employee is mapped on, between the primary key field of Manager to the primary key field of Employee. BoardMember has a foreign key constraint to Manager, between the primary key of BoardMember and the primary key of Manager. This way, referential integrity rules in the database make sure your data stored in the database is correct, also for derived entities. 

Entities

In LLBLGen Pro you can define the same hierarchy as defined above, by simply making Manager a subtype of Employee and BoardMember a subtype of Manager. You can also let LLBLGen Pro try to find these hierarchies for you, by selecting the Construct Target-per-Entity Hierarchies option in the context menu, when you right-click the Entity Model-, Entities- or any group node in the Project Explorer.

In general, O/R mapper frameworks which support TargetPerEntity inheritance (like the LLBLGen Pro runtime framework, NHibernate and Entity Framework), when you save a new entity which is a subtype and which is represented in the database by multiple tables, like for example the BoardMember entity, the entity will get a record in all the tables of the hierarchy: Employee, Manager and BoardMember.

Updating such an entity will update its rows in the tables where changed fields are located. So if you change BoardMember.Name, an update statement will be issued on the Employee table. Fetching a BoardMember will cause the O/R mapper framework to use INNER JOINs between Employee, Manager and BoardMember.

It's important to note that you can't re-use a record in a supertype table for a different subtype instance. For example if you store 'shared' information in the Employee table record, you can't share that record with different Manager instances for example. See also Limitations and Pitfalls later in this section.

Hierarchy creation for proper entity field availability modeling

Following is an example of a hierarchy which is constructed to have different field availability in the different entities in the hierarchy:

This model view is viewing another part of the bigger entity model of which another part was shown in the previous subsection. At first it looks like the same type of hierarchy as the previous example however there's a small, but important, difference: the relationship presented in this example is defined on the root of the hierarchy. This means that this hierarchy specifies the specialization of an entity for the purpose of adding different fields to the entity, without polluting the supertype with these fields.

In this example, the supertype CompanyCar, which has a relationship with BoardMember shown in the previous section, is specialized with a HasTowingHook field in the FamilyCar entity. The SportsCar entity has an extra field as well: an IsCabrio field. Would you not use inheritance, you had to add these fields to the CompanyCar entity and set them to NULL accordingly. This is a bit inconvenient, because a sportscar obviously never has a towing hook, except for some 'edge case' ferrari's ;)

In the two main ways to construct a physical representation in tables, you can opt for both supported inheritance types for this hierarchy, however it's more efficient to use the first hierarchy type: Target Per Entity Hierarchy, because you work on a single table and you don't need foreign keys to preserve referential integrity with related entities defined on subtypes: the relationships with subtypes are defined in the root of the hierarchy, CompanyCar.

Physical representation in the relational data model and supported discriminator field types

The typical hierarchy mentioned above is projected onto a relational model by simply flattening the hierarchy and store all fields of all the entities in the hierarchy in a single table (or view). Every field of the root entity is defined as mandatory (not nullable), while every field of a subtype is defined as nullable. To be able to determine what the entity type is of a given row in the table / view, a discriminator column is used with a value which represents the type.

This column can be of any type, as long as it ends up as a non-nullable System.Byte/Int16/Int32/Int64/Guid/Decimal/Boolean or System.String type in LLBLGen Pro. An example for the discriminator column for the hierarchy in this subsection could be the column called 'CarType' of type integer, which for example contains 1 for CompanyCar instances, 2 for FamilyCar instances and 3 for SportsCar instances. Not all discriminator field types are supported by all supported O/R mapper frameworks. Unless stated otherwise in the O/R mapper documentation, you can assume all types supported by the designer are supported as discriminator field type by the O/R mapper.

General inheritance mapping background information

Inheritance is an important part of most modern O/R mapper frameworks. Because O/R mapping is a generic technique with a wide variety of detailed descriptions, it can be confusing what each mapping strategy means and how it compares to another O/R mapper's way of mapping inheritance.

There are in general 4 ways to map entity / class hierarchies (inheritance hierarchies) onto a set of tables / views:

1. Complete hierarchy mapped onto a single table. In LLBLGen Pro this is called TargetPerEntityHierarchy. This is the inheritance type which is very easy to implement and therefore supported by most O/R mappers. All supported O/R mappers support this inheritance hierarchy type.
2. Every type in a hierarchy mapped onto its own table, no discriminator column. In LLBLGen Pro this is called TargetPerEntity. This inheritance type is hard to implement, most O/R mappers fall back to option 3. Not all supported O/R mappers support this inheritance hierarchy type (e.g. Linq to Sql doesn't).
3. Every type in a hierarchy mapped onto its own table, with discriminator field in root type. Similar to option 2, and because LLBLGen Pro doesn't need a discriminator field for option 2, this type is supported but the discriminator field is ignored / not required. In the LLBLGen Pro designer, you can't specify the discriminator field for this hierarchy, so option 2 is preferred. 
4. Every type in a hierarchy mapped onto its own table, where all fields from the supertype are present in each entity's table. The O/R mappers which do support this mapping call it Target per concrete entity. As it has severe limitations, and is not efficient (both for saves/updates and for data storage) this setup is not supported by LLBLGen Pro.

Abstract entities

It can be that you've defined a hierarchy, for example the Employee, Manager, BoardMember hierarchy, and you don't want the developers of your team to use every type in that hierarchy, for example because one or more types in the hierarchy, like Employee, is defined just to define fields for the rest of the hierarchy, not to be a real entity.

LLBLGen Pro lets you specify if an entity is abstract or not, you do that in the entity editor. Entities which have subtypes and which don't have a supertype which isn't abstract, can be made abstract. An abstract entity means that you can have instances of that entity in multi-entity polymorphic fetches but you can't instantiate an entity instance in code by using its constructor.

Don't confuse an abstract entity with an abstract base class: an abstract base class is a code construct and which can be defined/specified using code generation settings for the particular framework. So if you want all your entity classes to derive from a given abstract base class, you don't have to define an abstract entity to mimic that base class, just specify the base class as the preferred base class in the code generation settings for the particular framework.

Limitations and pitfalls

Entity inheritance is a powerful instrument and can bring you a lot of advantages when working with plain relational data. It also comes with a warning label, as it does require you to think through your physical datamodel before proceeding, and can have performance limitations.

Pitfalls with inheritance

• Don't share data in records belonging to supertypes with different subtype instances. This is already mentioned, but it's important to mention it here again in this list. A multi-table entity is owner of the records saved for that entity instance in all the tables the entity is mapped to.
• Deep hierarchies of TargetPerEntity can cause performance problems. When you create deep hierarchies of type TargetPerEntity, so the leafs in your hierarchy are mapped onto a lot of tables, you have to be aware of the fact that when you fetch entities of that hierarchy, the O/R mapper will create INNER JOINs to be able to pull derived entity instances as well. This can lead to a performance impact when your tables are very large (large amount of records)
• Don't map instance variations as hierarchies. While this is a grey area, it's important to note that if you have variations in instances based on semantical interpretation of the data and there aren't likely to be very many instances of a given 'type', it might be better to not create these subtypes. An example can be if you have a 'Department' entity and you create a 'Marketing' subtype of that Department entity and from that a MarketingEurope, MarketingUSA, MarketingAsia etc. subtypes. These last subtypes are actually instances of Marketing, not real subtypes.
• Avoid defining subtypes for entities which can have more than one semantic type. A typical example of this pitfall is the Person - Employee and Person - Customer hierarchy. If you create an Employee instance, and you also allow an Employee to be a Customer, it gives a problem as you can't cast to a type which is a sibling in the same hierarchy. In such a situation, use a role defining field to help semantical interpretation of the 'Person' entity and avoid inheritance, so choose has-a over is-a.
• Mixing of inheritance hierarchy types is supported by the LLBLGen Pro designer, but few O/R mapper frameworks support it (NHibernate and Entity Framework) and it's not very common to use this inheritance setup. If you need more performance in a sub-part of an inheritance hierarchy of type TargetPerEntity you could opt for using TargetPerEntityHierarchy in that sub-part but in general it's then perhaps better to choose has-a over is-a, and use association (relationships) instead of subtypes.