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