CSC/ECE 517 Fall 2009/wiki3 12 Patterns for ORM

Overview

Object-relational mapping is not trivial. Apart from being based on Software Engineering principles like coupling, cohesion and polymorphism, the object paradigm focuses on building applications out of objects that have both data and behavior. However, the relational paradigm is based on mathematical principles and focuses primarily on storing data. If only data objects were to be mapped to the relational database the procedure would have been fairly straightforward. With the growing complexities of object models, concepts like aggregation, inheritance, polymorphism, association between classes, and data type (smarted than SQL data types) have to be mapped to relational table structure. The table below highlights the fundamental mismatch between object oriented data model and relational data storage.

Object Oriented Data Model	Relational Database Management System
Class	Table
Object	Row
Entity	Key
State	Data
Behavior	Transactions
Inheritance	Data Relationships

Inheritance Mapping

Inheritance in Object Oriented Design makes it difficult to map between object and relation databases. In this pattern, there is mapper class for each domain class that will save and load data for the domain class [8]. Mapper class allows both abstract and concrete classes to handle their own O/R mapping[8].

                          
   Figure 1: Domain Class                                            Figure 2: Mapper Class

For example: - To find an employee “John”. Figure 1 represents the Employee Domain class and Figure 2 represents the corresponding Mapper Class. Application calls find () method on concrete mapper object and instantiate a new Executive object. The find () will pass this executive object and database record to load () of Executive. This in turn will call the load () of Superclass. When all loads return, find returns the filled object. Mappers loading and saving the domain objects is determined by the inheritance mapping. This inheritance hierarchy is represented in the database as follows:-

• Single Table Inheritance
• Class Table Inheritance
• Concrete Table Inheritance

Choosing a mapping scheme depends on speed requirement, whether database is shared and type of RDBMS.

Single Table Inheritance

Inheritance Hierarchy of all classes is represented as single table. The columns of this table will have fields of all classes in the hierarchy. Advantage of this Inheritance is that there is one table and requires no join while retrieving data. But it could lead to large confusing table and name conflicts.

                          
   Figure 3: Domain Class                                            Figure 4: Single Table Inheritance

Class Table Inheritance

One table for each class is constructed. The advantage of this table inheritance is that there is clear relationship between class and table. There is high normalization. But multiple tables requires join and when fields move up and down in hierarchy, it requires changes in the tables.

                         
      Figure 5: Domain Class                           Figure 6: Class Table Inheritance

Concrete Table Inheritance

In this hierarchy there is one table per concrete class. The table are independent, requires no join and there is no unused fields.

                         
      Figure 6: Domain Class                           Figure 7: Concrete Table Inheritance

The strengths and weaknesses of each approach are compared below:

Factors to Consider	One table per hierarchy	One table per concrete class	One table per class
Ad hoc reporting	Simple	Medium	Medium/Difficult
Ease of implementation	Simple	Medium	Difficult
Ease of data access	Simple	Simple	Medium/Simple
Coupling	Very High	High	Low
Speed of data access	Fast	Fast	Medium/Fast
Support for polymorphism	Medium	Low	High

Association Mapping

The Foreign key is used to maintain relationships in relational databases. A row in one table can relate to a row in another table using foreign key. Therefore to implement relationships, a key of one table must include in the other table [4].

One-to-one mapping

If one record in Table A corresponds to exactly one row in Table B, this relationship is called One – to-one relationships [10]. In Figure 6, the address attribute of an employee class forms a one-to-one relation with the address class. In database, this relationship is stored by creating one-to- one mapping between these two objects by storing the id of Address instance in the Employee table instance is written [11]. When the employee is read form database, the address instance is linked to Employee. Here, the mapping is from Employee to Address.

                              
                               Figure 6: One-to-one mapping

One-to-many mapping

If each record in Table A corresponds to many records in Table B , but each record in Table B links to only one record in Table A, this relationship is called One-to-many relationships [10]. In fig, a Order may have zero to many OrderItems. To represent in relational database, the Owner object’s OID can be inserted into OrderItem table [9]. Here, OID is Foreign Key. This is shown in Figure 8.

                  
                              Figure 8 : One-to-many mapping

Many-to-many mapping

If each record in Table A may links to many records in Table B and vice-versa [10], it’s called Many-to-Many relationships. In Figure 9, let’s consider that an employee can work more than one department and department can contain more than one employee.
The Many-to-Many relationships is represented in term of association table. A separate table called association table is created that contains the foreign keys of participating tables [9].

                         
                              Figure 9 : Many-to-many mapping

Aggregation Mapping

Aggregation is a specialized version of the association relationship. Aggregations may be mapped to relational data model either by integrating all objects' attributes into a single table or using foreign keys.

Single table aggregation

In this pattern the a table is created with the attributes of the Aggregating objects. The attributes of the Aggregating object is simple added to this table.

Foreign key aggregation

To achieve this create a table each for the aggregating and aggregated object. Create a foreign key reference between the two table by inserting a synthetic object identity into the aggregated objects type and reference it in the aggregating objects table.

Differences between the two approaches has been tabulated below:

Single Table Aggregation	Foreign Key Aggregation
Only one table needs to be accessed to retrieve an aggregating object with all its aggregated objects. On the other hand, the fields for aggregated objects’ attributes are likely to increase the number of pages retrieved with each database access, resulting in a possible waste of I/O bandwidth.	Needs a join operation or at least two database accesses where Single Table Aggregation needs a single database operation.
If the same object is aggregated in more that on one object, a change in the aggregated object would require a change in a lot of database table. This pattern thus has poor maintainability.	Since the aggregated object has it's own table, the pattern is more maintainable and flexible.
Aggregated objects are automatically deleted on deletion of the aggregating objects.	Aggregated objects are not automatically deleted on deletion of the aggregating objects.
Ad-hoc queries are very hard to formulate.	Factoring out aggregated objects allows easy querying these tables with ad-hoc queries.

Type Conversion

There are situations when the values in the database do not map directly to object data types. Take the Boolean data type for example. Boolean values "true" and "false" may be saved as "T" and "F" respectively in the database. Type conversion should be facilitated without any data loss[2]. The figure below shows type conversion mapping.

References

[1] http://www.objectarchitects.de/ObjectArchitects/orpatterns/
[2] http://subs.emis.de/LNI/Proceedings/Proceedings48/GI.Band.48-6.pdf
[3] http://www.joeyoder.com/Research/objectmappings/Persista.pdf
[4] http://www.ibm.com/developerworks/library/ws-mapping-to-rdb/
[5] http://www.dparsons.co.uk/mindthegap.pdf
[6] http://proceedings.informingscience.org/InSITE2007/IISITv4p767-779Jusi281.pdf
[7] http://www.sis.pitt.edu/~gray/INFSCI1025/references/AmblerORMMapping101Article.pdf
[8] http://www.brettdaniel.com/files/2006/inheritancemapping.ppt
[9] http://www.objectarchitects.de/ObjectArchitects/orpatterns/index.htm?MappingObjects2Tables/mapping_object.htm
[10] http://databases.about.com/cs/tutorials/a/accessgup7.htm
[11] http://www.oracle.com/technology/products/ias/toplink/doc/1013/main/_html/relmapun005.htm
[12] http://docs.jboss.org/hibernate/core/3.3/reference/en/html/inheritance.html
[13] http://wiki.eclipse.org/Introduction_to_Mappings_(ELUG)