Concepts - Entity inheritance and relational models
Preface
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 most common ways of mapping entity hierarchies onto tables / views and which of these 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.
Supertypes / subtypes and NIAM terminology
In relational 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 a (simplified) NIAM / ORM (object role modelling:
http://www.orm.net). To help the readers who aren't familiar with NIAM and / or ORM, the following brief description
should help understand the presented diagrams.
NIAM/ORM diagrams are based on
sentences, which are readable in the diagram, and which are called 'facts'. Entities are represented by oval objects with
a solid border. Entity attributes (fields) are represented by oval objects with a dashed border. Relations between entities or entities and attributes are
represented by an '--[ | ]--' object, as illustrated in the following examples. A supertype/subtype hierarchy is represented
by an arrow, starting at the subtype, pointing to the supertype.
One to many relation, which is mandatory on both elements
One to one relation, between an entity (mandatory) and an attribute
Hierarchy creation for proper entity relation modelling
First an example of a hierarchy which is constructed to utilize proper entity relation modelling:
This model is a simplified NIAM/ORM model, which is a bit larger than shown here (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 relations per entity. The common attributes for all
entities in the hierarchy are defined with relations to Employee, which is the
root of the hierarchy. The diagram further shows a relation between
Employee with Department which illustrates the semantic "Employee works for Department" relation. Manager and BoardMember also have relations, which are
specific for their type: Manager has also a relation with Department, but for the semantic relation "Manager manages Department". BoardMember has a relation
with CompanyCar, to illustrate the semantic relation "BoardMember has a CompanyCar". These relations are with other entities.
To use the last mentioned relation, BoardMember has a CompanyCar, as an example: this relation is defined on the entity "BoardMember" because only BoardMembers
are allowed to have a company car, in this situation. Would the relation 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:
- One table, which contains all fields/attributes of all entities in the hierarchy.
- Every entity its own table, which contain only the fields of the particular entity
(Of course, for 'table' you can say 'view')
The first way, which is called in LLBLGen Pro
TargetPerEntityHierarchy and which is discussed more in the next subsection, will define the aforementioned
relation "BoardMember has a CompanyCar" on the same table / view as where 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 relation to CompanyCar is defined. This second way is called
TargetPerEntity in LLBLGen Pro.
Physical representation in the data model and LLBLGen Pro entities
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, where you define the primary key for
the entity to uniquely identify an entity instance in the database. As
ID is a perfect candidate for this (1:1 relation between entity Employee and
attribute) this will become the primary key. Manager and BoardMember get this same field, to identity the rows stored in these tables, though they're not
new PK values, but have a foreign key constraint defined to the ID of the supertype, so Manager.ID has a foreign key constraint to Employee.ID and
BoardMember has a foreign key constraint to Manager.ID. This way, referential integrity rules in the database make sure your data stored in the database
is correct, also for derived entities. Often O/R mappers require a discriminator column in the root table/view so they can determine what the type is
of the data the root table/view is holding. In LLBLGen Pro you don't need to define a discriminator column on the root table,
LLBLGen Pro can find out by itself what the type is of the data stored in the tables / views which make up a hierarchy.
 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 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 LLBLGen Pro
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 modelling
Following is an example of a hierarchy which is constructed to have different field availability in the different entities in the hierarchy:
This model is part of the bigger NIAM/ORM model presented 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 relations presented in this example are between an entity and an attribute. This means that
this hierarchy specifies the specialisation 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 relation with BoardMember, is specialized with a DrawingHook attribute in the FamilyCar entity.
The SportsCar entity has an extra attribute as well: a Cabrio attribute. Would you not have inheritance, you had to add these attributes to the CompanyCar
entity and set them to NULL accordingly. This is a bit inconvenient, because a sportscar obviously never has a drawinghook (ok, some people are that crazy).
In the two main ways to construct a physical representation in tables, you can opt for both ways for this hierarchy, however it's more efficient to use the
first 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 relations with subtypes are defined in the root of the hierarchy, CompanyCar.
Physical representation in the data model and LLBLGen Pro entities
The typical hierarchy mentioned above is realized by simply
flattening the hierarchy and store all attributes of all the entities in the hierarchy
in a single table. Every attribute of the
root entity is defined as mandatory (not nullable), while every attribute of a subtype is defined 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 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 int, which for example contains '1' for
CompanyCar instances, '2' for FamilyCar instances and '3' for SportsCar instances.
Entities
In LLBLGen Pro you can define the same hierarchy by creating subtypes of the CompanyCar entity (or subtypes of subtypes in that hierarchy). As soon as an
entity which isn't in a hierarchy, is made root of a hierarchy of type
TargetPerEntityHierarchy, all fields which are nullable and not part of
a relation are unmapped in the root entity, and can be mapped manually in subtypes. You can of course remap them in the root entity if you want / need to, however
typically nullable fields in the root entity are used for fields in subtypes so to save you some work, LLBLGen Pro unmaps them for you first.
A dialog helps you defining subtypes and specifying discriminator columns.
New entities which are in a TargetPerEntityHierarchy hierarchy don't have to get their Discriminator field set to a value, this is done for you by
LLBLGen Pro.
Comparison of inheritance mapping with competing O/R mappers
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:
- 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.
- 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.
- Every type in a hierarchy mapped onto its own table, with discriminator column in root type. Similar to option 2, and because LLBLGen Pro
doesn't need a discriminator column for option 2, this type is supported but the discriminator column is ignored / not required.
- 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 attributes 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.
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. LLBLGen Pro's inheritance implementation,
while powerful, also has some limitations which most likely won't affect you in your work, but are worth mentioning.
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 runtime libraries
will create INNER JOINs to be able to pull derived entity instances as well. This can lead to some performance degrease 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. In NIAM/ORM you can define multiple inheritance, however in
.NET you can't. (In theory, you can with interfaces, but LLBLGen Pro doesn't support interface based entity inheritance). 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, you're in trouble, 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.
Limitations of the LLBLGen Pro entity inheritance implementation.
- Mixing of hierarchy types isn't supported. If you have an entity hierarchy of type TargetPerEntityHierarchy, you can't make the root of
that hierarchy a subtype of an entity to form a TargetPerEntity hierarchy and vice versa.
- Multiple inheritance isn't supported. While .NET doesn't support multiple implementation inheritance, it does support multiple type
inheritance, through interfaces. Because it's not that common (actually rather rare) to have multiple entity inheritance, LLBLGen Pro doesn't
support multiple entity inheritance.
