Inheritance – Table Per Subclass Hierarchy
Author: Sebastijan Pistotnik, member of NConstruct Developers Team
Release date: February, 2008
Inheritance – Table Per Subclass Hierarchy
This article discusses:
- Problems in coding an OR mapping by not using polymorphism
- Using polymorphic associations and polymorphic queries
- Creating Table Per Subclass Hierarchy in NConstruct application builder
Contents:
Let us assume a task of writing a business application that handles purchase and
sales orders for a fictitious bicycle manufacturer. DB architect designs
a database similar to the Adventure Works database that can be found and
downloaded on the following URL:
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=8392
We will keep our focus only on three of the tables named SalesOrderDetails,
PurchaseOrderDetails and Product. The database tables look like
ones in the Figure 1.
Note
Adventure Works is a sample database. Table names, their relationships
and other properties are fictitious and not something that someone would actually
design in that way.
Figure 1
We will use an NConstruct builder and create a default mapping based on
Table Per Concrete Class strategy. The builder creates a separate entity
class for every existing table.
We start coding and with time our number of lines of code is getting larger and
larger. We start wondering how to improve our coding. Let us look at this concrete
class mapping and see what kind of problems we have.
1 Problems in coding when using type – based alternatives
We have a method for closing an order. We somehow manage to simplify the code to
the following state:
As you can see, we have two methods, since we have two concrete entity classes mapped
to two separate tables and each of them has a separate call to CloseOrder
method. Could we get rid of this second method?
A quick look at the above code would give a programmer a quick solution.
Why not
design this using if-then-else or case statement conditional logic.
The problem transforms into “How to handle type-based alternatives”?
Conditional variation is a fundamental theme in programs. If a program is designed using if-then-else or case statement conditional logic, then if
a new variation arises, it requires modification of the case logic - often in many
places . This approach makes it difficult to easily extend a program with new variations
because changes tend to be required in several places - wherever the conditional
logic exists.
Solution
When related alternatives or behaviors vary by type (class), assign responsibility
for the behavior using polymorphic operations to the types for which the behavior
varies.
Do not test for the type of an object and use conditional logic to perform varying
alternatives based on types.
We will introduce details (and code solution) to the implementation later
in the
article.
2 Problems in coding when not using polymorphic queries
The problems are appearing not only in methods, but also in query calls:
We could rather have just one method and one query :
Or let us look at a more “polymorphically” oriented example: Since the two tables
both refer to the same Product table, we could have:
In this case we would also like to have just one simple method call and process
orders polymorphically:
There are also other kinds of problems that appear. We introduced just a few of
them.
3 Possible Solution – Table Per Subclass Hiearchy
Now back to our database example. As you can see, those two tables have a lot of
common columns declared. So they are in a way duplicated, which is definitely something
that we should avoid. There are many approaches possible. We will take the approach
of joining those two tables into a single one called Orders. What we need
is the entire hierarchy to be mapped into a single table. We have to add a special
column to distinguish between persistent classes: the discriminator. This
is not a property of the persistent class; it is used internally by NHibernate.
The column name will be OrderType, and domain values will be strings
- in this case, “SALES” and “PURCHASE”. NHibernate automatically sets and retrieves the discriminator values. So the concrete subclass represented
by a particular row is identified by this value.
Let’s see how our SQL database script for creating an Orders table
looks like:
Note
We are going to simplify things a bit, not to create “all” or “same as existed in
Adventure Works database” many-to-one references.
There is one major problem: Columns for properties declared by subclasses must be
declared to be nullable. Since our subclasses each does not define several
non-nullable properties, the loss of NOT NULL constraints may
not be a serious problem from the point of view of data integrity . Another important
issue is normalization. We have created functional dependencies between
nonkey columns (Note: a nonkey is a field that does not serve
as a candidate, primary, alternate, or foreign
key. Its sole purpose is to represent a characteristic of the table's subject, and
its value is determined by the primary key), violating the third normal form. Details can be
found on the following URL:
http://en.wikipedia.org/wiki/Third_normal_form
When subclasses declare relatively few properties (particularly if the main difference
between subclasses is in their behavior), then violation might not be a problem.
Your goal is to minimize the number of nullable columns and to convince
your DBA that a denormalized schema will not create problems in
long term stability, maintainability, and the integrity of data. Note that the performance
may also be achieved by proper optimization of the SQL execution plans.
3.1.1 Solution to problem 1 – Polymorphic association
What we need is a so called polymorphic association. After mapping modified
tables, we have a ProductsEntity entity class that has an association to
an OrdersEntity entity class.
Note
This is a simplification though. Because if you carefully look at the table SalesOrderDetails
in AdventureWorks database you can see that it has a reference to a table
SpecialOfferProduct with a composed primary key.
This is a polymorphic association. At runtime, a product may reference
an
instance of
any of the subclass of OrderEntity. A polymorphic association
is an association that may refer instances of subclass of the class that was explicitly
specified in the mapping metadata. Now let’s suppose that a ProductEntity
entity class would be mapped as having a one-to-many association to OrdersEntity
entity class.
We may iterate over the collection and handle instances of SalesOrdersEntity
and PurchaseOrdersEntity polymorphically:
One of the common ways to apply polymorphism is in the context of what
Peter Coad calls the “Do It Myself” strategy or pattern (Coad,
OO 1995. Object Models:
Strategies, Patterns and Applications.):
“I (a software object) do those things that are normally done to the actual object
that I am an abstraction of.”
This is a classic object-oriented design style: Circle objects draw themselves,
Square objects draw themselves, Text objects spell-check themselves,
and so forth. Notice, that Circle and Square objects drawing themselves
are examples of polymorphism:
“When related alternatives vary by type, assign responsibility using polymorphic
operations to the types for which the behavior varies.”
Do It Myself and Polymorphism usually lead to the same choice.
3.1.2 Solution to Problem 2 - Polymorphic queries
We want to be able to write polymorphic queries (queries that return objects
of all classes that match the interface of the queried class) that refer to the
Order class, and have the query returns instances of its subclasses. We
can achieve this with an object oriented query language HQL:
This query returns objects of type OrdersEntity, which is an abstract class.
In this case, the concrete objects are of the subtypes of OrdersEntity:
PurchaseOrdersEntity and SalesOrdersEntity.
If only instances of a particular subclass are required, you may use:
We can also use a more complicated fetch: Suppose a many-to-one association
named
highestOrder exist between ProductsEntity and OrdersEntity.
This mapping strategy is a winner in terms of both performance and simplicity.
-
It is the best-performing way to represent polymorphism - both polymorphic and nonpolymorphic
queries perform well.
- Ad-hoc reporting is possible without complex joins and unions.
- There is only one insert for each entity in case of saving many of them, so scalability
is not compromised.
- Schema evolution is straightforward.
4 How to implement Table Per Subclass with NConstruct builder
The implementation is possible in the Objects
management step of a wizard. We select an OrdersEntity class and click on Inheritance
and it’s
Table Per Class hierarchy toolbar subitem shown in Figure
2.
Figure 2
The
following wizard, shown in Figure 3, opens.
Figure 3
4.1 Implementation with discriminator
column
We unselect ModifiedDate, OrderQty, Product, UnitPrice from nullable candidate
properties list. That way we move those properties that actually belong to both
of subclass entities into an abstract parent class. We select a discriminator column
and enter 2 for a number of subclasses to create. We get a situation as in the following Figure 4.
Figure 4
After clicking the Next button , we get the screen presented
in Figure
5.
Figure 5
We enter appropriate values for class names and discriminator
values and select
ReceivedQty and RejectedQty in PurchaseOrdersEntity properties
list, which we think they belong to.
Note
ReceivedQty probably makes no sense in sales
business context.
The entered values can be seen in Figure 6.
Figure 6
After clicking Next and Finish buttons, subclasses and their abstract base class should be successfully created.
After running the target application, the created two entities have their
two corresponding navigation items named by default as Sales Orders Subclass and Purchase Orders Subclass
(Figure 7).
Figure 7
A report query SQL that
NHibernate executes to get purchase
orders records is as follows:
However, the point here is that we can now create an abstract CloseOrder
method
and implement concrete implementation in individual concrete subclasses.
The generated class hierarchy introduced as UML is shown in
Figure 8.
Figure 8
4.2 Implementation with the formula SQL expression
Sometimes
, especially in legacy schemas, we do not have a freedom to include an extra discriminator
column in our entity table. In this case, we can apply a formula
to calculate a
discriminator value for each row (Figure 9).
formula = "case when ReceivedQty IS
NULL then 'SALES' else 'PURCHASE' end"
Note
We are simplifying and assuming here
things a bit, just to introduce an example.
Figure 9
The screen for entering the values of subclasses looks the same as the above in
Figure 6.
This mapping
relies on SQL CASE/WHEN expression to determine whether a particular row represents
a sales or a purchase order.
The result of the expression is a literal, SALES or PURCHASE, which in turn is declared on the
mappings like this:
At the end, only the SQL gets a little different than before, but the results
are the same.
Note
We do not need an additional OrderType column.
In case of executing
a query on top of a PurchaseOrdersDTO, we get the
SQL as follows:
Tip
The same as the above example could be achieved with entering:
Formula=”case
when OrderType = 'SALES' then 'SALES' else 'PURCHASE' end”
Return type in this example
must be String and “SALES” and “PURCHASE”
must be entered for discriminator values.
|
