Union and Vertical Mapping of Classes
Meet the challenge of impedance mismatch with object to relational mapping. Part 2 looks at union and vertical mapping as best practice approaches
by Richard Jensen

Posted May 5, 2004

Editor's Note: This is Part 2 of a two-part article on best practices for object to relational mapping. Part 1, "Mapping Classes to Relational Tables" (Java Pro Online, April 28, 2004), provided an overview of the necessity to overcome the challenge of impedance mismatch between object-oriented and relational data and of using the horizontal mapping approach. Part 2 looks at the union and vertical mapping approaches and provides examples of both.

While horizontal mapping requires duplicating columns representing base class attributes in the database tables to which leaf classes map, union mapping (sometimes called filtered mapping or typed mapping) offers a way to eliminate this redundancy. Union mapping allows multiple classes within an inheritance hierarchy to share a common table and thus reuse columns representing the common data. The term "union mapping" refers to the fact that the attributes in the table represent the union of the object attributes for objects that are mapped to that table.

For the Employee hierarchy within the previous object model (see"Mapping Classes to Relational Tables," Java Pro Online, April 28, 2004), a union mapping stores all of the concrete classes in one table by sharing the name, dob, hiredate, and ssn columns (see Table 1). Supporting the PartTime and FullTime classes requires adding a payrate column, and supporting the Exempt and Manager classes requires adding a salary column.

The complication with a union inheritance mapping is that the data layer has no way of knowing how to instantiate certain closely related classes. For example, with the sample table structure described previously, there is no way of knowing whether the NonExempt data (those with a NULL salary column) is for a full or part-time employee, or whether the Exempt data (those with a NULL payrate column) is for a manager.

To determine the type of object to which a row will map, a union inheritance mapping must introduce a distinguishing "class type code" value. The added "code" column illustrates this approach, where all rows in the table with a value of F in the code column map to a FullTime instance, and the P code column values map to a PartTime instance (see Table 2).

Two Union Tables
There are times when it makes sense to partition a set of related classes across more than one union table. For example, the Employee hierarchy could be mapped to two tables by storing the NonExempt types in one union table and the Exempt types in another union table. Figure 1 illustrates this mapping approach.

With union mapping, there is some extra complexity associated in determining how to map each row to a particular class. However, this approach produces very compact database schemes, with all the data associated with a certain column in one table, as opposed to spreading similar data across multiple tables as in the vertical inheritance mapping. There are several benefits of a union inheritance mapping scheme, including more efficient queries at the superclass level and more elegant database schema, that is, fewer overall tables to manage.

Again, however, there are limitations to the union inheritance. It's a more complex coding effort, particularly for a hand-coded data mapping layer, and the time required to implement features such as type codes may be significant. And there may not be a match for underlying table structures: for legacy tables, union mapping may not fit the existing table layouts. In addition, it may not be possible to add the type code column to facilitate the mapping.

These snippets demonstrate the SQL portion of the mapping code for creating, reading, updating, removing, and retrieving persistent data (with the example object model mapped to a single database table). To create the database row corresponding to a new PartTime employee object, we insert one row into the CodedEmp table:

insert into CodedEmp name, code, 
  dob, hiredate, ssn, payrate
  values ("Elijah Banks", 'P', 
  "03/21/85", "07/01/2003",
  "901-23-4567", 8.90)

Since no value is given for salary, it will be NULL. To instantiate a FullTime employee object, we need only to read from one table:

select name, dob, hiredate, ssn, 
  payrate from CodedEmp
  where name = 'Sarah Jackson' 
  and code in ('F')

Using the type code guarantees that we will only get back a row that corresponds to a FullTime employee. Similarly, we can update an Exempt employee by writing to one table:

update CodedEmp set code = 'E', 
  set dob="06/30/1962",
  set hiredate="06/15/1997", 
  set ssn="567-89-0123",
  set salary = 52000.00
  where name = 'Nicky Colson'

To remove an object:

delete from CodeEmp where name = 
  'Susan Jekel' and code in (
  'M')

Using the type code guarantees that we will only remove data for a Manager.

To find employees hired since 01/01/1998, we need to perform the query on only one table:

select name, dob, code, 
  hiredate, ssn, payrate, salary
  from CodedEmp 
  where hiredate >= "01/01/1998"
  and code in (
  'P', 'F', 'E', 'M')

Since the single query returns multiple types, there must be a mechanism to instantiate the correct class type from the row data.

Vertical Inheritance Mapping
Another mapping strategy is to create tables at each level of the hierarchy. Such tables contain only the columns for the attributes defined at that level in the hierarchy. To relate the multiple tables required to instantiate an instance of a class requires a join of the appropriate tables through shared key values. A vertical table mapping for our example might look the one you see in Figure 2. Note that to relate the EmployeePart to the PersonPart requires that they share a common value.

We've used the name column for this example. Also, notice the introduction of three tables—PartTimePart, FullTimePart, and ManagerPart—to perform a full vertical mapping of the concrete classes in our model (see Figure 3). Without them we cannot tell whether objects should be instances of PartTime, FullTime, Exempt, or Manager. Another approach would introduce some form of type code column in the NonExemptPart and ExemptPart tables to distinguish the object types.

The primary benefit of a vertical inheritance mapping is flexibility; this approach provides greatest flexibility for dealing with arbitrarily complex legacy data. In addition, by mixing the various mapping approaches, almost any legacy schema can be accommodated. There are also significant drawbacks to the vertical inheritance mapping, including results in complex data models, generally slow performance as multiple queries are required to retrieve an object, and possible impracticality in partitioning the object model into a complete vertical hierarchy without duplicating primary/foreign key information several times.

These snippets demonstrate the SQL portion of the mapping code for creating, reading, updating, removing, and retrieving persistent data (with the example object model mapped vertically to the database tables). To create the database row corresponding to a new PartTime employee object, we insert one row into the PersonPart, EmployeePart, NonExemptPart, and PartTimePart tables:

insert into PersonPart name, dob
  values ("Elijah Banks", 
  "03/21/85")
insert into EmployeePart name, 
  hiredate, ssn values (
  "Elijah Banks", "07/01/2003", 
  "901-23-4567")
insert into NonExemptPart name, 
  payrate values (
  "Elijah Banks", 8.90)
insert into PartTimePart name 
  values ("Elijah Banks")

To instantiate a FullTime employee object, we need to read from four tables and join them on the common key value:

select p.name, p.dob, 
  e.hiredate, e.ssn, ne.payrate 
  from PersonPart p, 
  EmployeePart e, NonExemptPart 
  ne,
  FullTimePart ft
  where name = 'Sarah Jackson'
  and p.name = e.name and 
  p.name = ne.name
  and p.name = ft.name

Similarly we can update an Exempt employee by writing to multiple tables:

update PersonPart set dob=
  "06/30/1962"
  where name = 'Nicky Colson'
update EmployeePart set 
  hiredate="06/15/1997",
  set ssn="567-89-0123"
  where name = 'Nicky Colson'
update NonExemptPart 
  set salary = 52000.00
  where name = 'Nicky Colson'

To delete an object:

delete from ManagerPart where 
  name = 'Susan Jekel'
delete from ExemptPart where 
  name = 'Susan Jekel'
delete from EmployeePart where 
  name = 'Susan Jekel'
delete from PersonPart where 
  name = 'Susan Jekel'

To find employees hired since 01/01/1998, we need to perform the query (through joins) for each of the concrete classes and return the combined results (see Listing 1).

The design of the O/R mapping layer is crucial for system development where efficient data access is a prime concern. Database optimization and object-oriented design often have conflicting requirements. O/R mapping flexibility offers developers the ability to make the appropriate trade-offs and still achieve performance objectives. It is also possible to use combinations of the different mapping strategies in the same application, or to use hybrids of the three strategies discussed here.

When considering different mapping options, remember the primary benefits of each: the horizontal inheritance is easiest to code, the union inheritance provides best query performance, and the vertical inheritance provides the most flexible legacy mapping.

A well-designed object model and carefully chosen mapping strategy provide a robust foundation for enterprise applications. However, after these high-level decisions are made, developers encounter another challenge. As shown by the examples discussed here, hand coding the data access layer is time consuming. The resulting code can be difficult to maintain, especially when changes occur—as they inevitably will—to the data model and the database schema. An O/R mapping tool that automatically generates the data access layer can pay for itself in productivity savings alone. Look for a tool that offers flexibility for choosing an optimal mapping strategy.

About the Author
Richard Jensen is a senior architect at Persistence Software.