With vertical mapping each class has its own table containing only its fields.
Advantages of vertical mapping are:
- Tables are normalized as they only contain columns for the fields of the class they are for and rows are only present for instances of that class
- New subclasses can be added without changing existing tables
- A discriminator column is not required. This may help when mapping an inheritance hierarchy to existing tables.
Disadvantages of vertical mapping are:
- Joins are used to fetch fields from possible subclasses in a single query. If the type of the instance being fetched is known then only its tables are joined, otherwise, all possible subclass tables must be included.
- Creating, updating, or deleting an instance requires multiple INSERT, UPDATE or DELETE statements (one for each table involved)
To enable vertical mapping for a subclass change its mapping strategy in the Forward Mapping wizard to vertical. This change should be made for each child class in order to be mapped vertically. Setting for the ‘Dog’ class looks like this:
The corresponding tables for the sample class hierarchy, with vertical mapping are shown below:
Note that there is a discriminator column in the ‘pet’ table. It can be disabled the same way as it is for ‘flat mapping’ - by selecting the {no} option in the Discriminator Value combo box for the base class.
The SQL to fetch all of the pets for a vertical inheritance mapping without a discriminator column is shown below. All tables are pulled in with OUTER joins and the null/not-null status of each of their primary keys is used to determine the type of the row. Any number of instances of different types can be retrieved with a single query. Some O/R mapping tools even run an extra query for each row returned to discover its true type (N+1 queries) or run a separate query against each possible table and merge the results in memory:
| SQL |
 Copy Code |
|
SELECT a.pet_id, b.pet_id, c.pet_id, d.pet_id, e.pet_id,
a.nme, a.voa_version, b.lives_left, c.best_friend,
d.cats_eaten, e.lngth
FROM pet a
LEFT JOIN cat AS b ON (a.pet_id = b.pet_id)
LEFT JOIN dog AS c ON (a.pet_id = c.pet_id)
LEFT JOIN rottweiler AS d ON (c.pet_id = d.pet_id)
LEFT JOIN wiener_dog AS e ON (c.pet_id = e.pet_id) |
If a discriminator column is used the SQL is as follows:
| SQL |
Copy Code |
|
SELECT a.pet_id, a.voa_class, a.nme, a.voa_version,
b.lives_left, c.best_friend, d.cats_eaten, e.lngth
FROM pet a
LEFT JOIN cat AS b ON (a.pet_id = b.pet_id)
LEFT JOIN dog AS c ON (a.pet_id = c.pet_id)
LEFT JOIN rottweiler AS d ON (c.pet_id = d.pet_id)
LEFT JOIN wiener_dog AS e ON (c.pet_id = e.pet_id) |
If all the instances returned are known to be of a given subclass, OpenAccess ORM will optimize the joins as shown in this example:
|
OQL |
Copy Code |
|
SELECT * FROM DogExtent |
Note that an INNER join is used to pickup the base class fields:
| SQL |
Copy Code |
|
SELECT a.pet_id, a.pet_id, c.pet_id, d.pet_id,
a.best_friend, b.nme, b.voa_version, c.cats_eaten,
d.lngth
FROM dog a
INNER JOIN pet AS b ON (a.pet_id = b.pet_id)
LEFT JOIN rottweiler AS c ON (a.pet_id = c.pet_id)
LEFT JOIN wiener_dog AS d ON (a.pet_id = d.pet_id) |
If the subclass is a leaf class then all the joins will be INNER joins:
| OQL |
Copy Code |
|
SELECT * FROM RottweilerExtent |
|
SQL |
Copy Code |
|
SELECT a.pet_id, a.cats_eaten, b.best_friend, c.nme, c.voa_version
FROM rottweiler a
INNER JOIN dog AS b ON (a.pet_id = b.pet_id)
INNER JOIN pet AS c ON (a.pet_id = c.pet_id) |