Union and Vertical Mapping of Classes (Continued)

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")