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 is based on: "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.
 Note: |
Keep in mind that not all supported O/R mapper frameworks do support both inheritance hierarchies. The LLBLGen Pro designer supports mixing of both types in a single inheritance hierarchy, yet that's not always supported by all supported O/R mapper frameworks. Please consult the support documentation shipped with LLBLGen Pro for the particular O/R mapper framework you're using. |
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.
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 relation "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:
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.
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.
|
Note:
|
| Don't use surrogate keys on the subtype tables, it's important the PK of the subtype tables has the foreign key to the supertype's PK. |
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 Entities context menu when you right-click
the Entities 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.
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.
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 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.
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:
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.
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.