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].

For example: - To find an employee “John”:- 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.

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.

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.

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 fig, 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.

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 fig .

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 fig, 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].

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.

Comparison

Performance: For optimal performance the solution should allow to retrieve an object with one database access without any join operations. Database accesses should fetch a minimum number of pages to economize on I/O bandwidth.

Maintainability: For optimal maintainability, aggregated types, that are aggregated in more than one object type, should be mapped to one set of tables instead of being strayed identically across many different spots in the physical data model. Normalization should be used at the data model level to ease maintenance and ad hoc queries.

Consistency of the database: Aggregation implies that the aggregated object’s life cycle is coupled with the aggregating object’s life cycle. This has to guaranteed by either the database or application code

Performance: Foreign Key Aggregation needs a join operation or at least two database accesses where Single Table Aggregation needs a single database operation. If accessing aggregated objects is a statistical rare case this is acceptable. If the aggregated objects are always retrieved together with the aggregating object, you have to have a second look at performance here.

Maintenance: Factoring out objects like the AddressTypes into tables of their own makes them easier to maintain and hence makes the mapping more flexible.

Consistency of the database: Aggregated objects are not automatically deleted on deletion of the aggregating objects. To perform this task you have to provide and maintain application kernel code or database triggers. This is also an implementation issue. You have to chose one of these two options.

Ad-hoc queries: Factoring out aggregated objects into separate tables allows easy querying these tables with ad-hoc queries.

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