CSC/ECE 517 Fall 2009/wiki3 12 sn

Patterns for Mapping Objects to Relational Databases

Introduction

Mapping Objects to Relational Databases is also referred to as Object-relational mapping or ORM. It is a programming technique for converting data between incompatible type systems in relational databases and object-oriented programming languages which creates a virtual object database that can be used from within the programming language [1]. The term "mapping" is used to refer to how objects and their relationships are mapped to the tables and relationships between them in a database [2]. The following figure shows a high-level depiction of ORM [8]:

Fundamentals of Mapping

To understand mapping we must start with the class whose objects need to be mapped. An attribute of such a class will then be mapped to a single or multiple columns of a relational database table. The simplest form of mapping is the mapping of an attribute to a single column and both the entities being mapped have the same data type. For example, a date attribute is mapped to a date type column [2].

Patterns in ORM

There are several patterns or techniques used to implement ORM in different situations. Let us see some of them in detail.

Concrete Table Inheritance

In this technique, each "concrete" class is mapped to its own table such that both the attributes implemented by the class and its inherited attributes are mapped to the table. A class is called concrete when objects are instantiated from it and is not just an abstract class used for inheritance [2]. Following is an example to explain this technique. There are tables corresponding to each of the Footballer and Cricketer classes because they are concrete but not Player because it is abstract. Each table has its own primary key and to add a new Bowler class a corresponding table is created with all of the attributes required by the Bowler objects [3].

Class Table Inheritance

In this technique, one table is created for every class with one column for every business attribute and any necessary identification information [2]. The following figure illustrates this pattern. Here as opposed to the earlier pattern, we have created one table for every class including the abstract class Player [4]. This pattern is important as it solves the problem of object-relational mismatch since relational databases don't support inheritance so links across the inheritance structure are possible by creating separate tables [4].

Foreign Key Mapping

In any object oriented system, objects are connected to each other via object references which provide for different interactions between these objects in the system so if these objects were to be mapped to a database then its important to map the references as well [5]. To accomplish this a Foreign Key Mapping is used which maps an object reference to a foreign key in the database and thus maintains the relationships in the relational databases [2,5]. A foreign key is a data attribute that appears in one table and may be coincidental with the key of another table [2]. Several relationships may exist between two tables: one-to-one, one-to-many, many-to-one or many-to-many. A one-to-many relationship between Artist and Album is shown in the figure below where the artistId in Albums is the foreign key from the Artists table and connects the two tables together and can be used to retrive the required data by using sql joins [5].

Identity Field

In an object oriented system, individual objects can be easily differentiated as the object system ensures the correct identity with the help of say, raw memory locations [6]. But this is not the case with relational databases and so to be able to differentiate between different rows of a table we need a key which is called a primary key. This is required in order to tie the database to the in-memory object system so that both read and writes are correctly done [6]. So to achieve this, the identity field pattern stores the primary key of the relational database table in the object's fields [6]. An example can be seen in the figure below [6].

Lazy Initialization

The idea behind lazy intialization or lazy load is as follows: when designing a system it would be a good idea to handle loading all related objects when loading a particular object. This way all the other required objects are already available when they need to be used and do not have to be individually loaded each time. This can makes things easier at times but can cause performance issues if the system ends up loading a large number of objects when only a few of them will actually be used eventually [2]. To solve this problem, a Lazy Load interrupts this loading process for the moment setting a null value in the object structure to indiacate object not loaded so that if the data is needed it can be loaded only when it is used [7].

Recursive Relationships Mapping

A recursive relationship is when the same entity which may be a class or a table is involved with both ends of the relationship [2]. For example, in the figure below, Employee class maps to itself such that an employee reports to a manager who is also an object of class Employee [9]. This is achieved using the Recursive Relationships Mapping pattern by use of foreign keys such that a one-to-many relationship between Manager and Employee can be created using a managerId column which refers to an employeeId of another row of the Employee table.

Single Table Inheritance

We know that relational databases do not support inheritance so when representing such an inheritance structure in the form of relational databases, we have to take care of the associations between the various classes of the hierarchy. This can ultimately lead to several tables with foreign key associations and which in turn will result in sql queries with several joins. This can be a performance issue. To solve this problem, Single Table Inheritance maps all fields of all classes of an inheritance structure into a single table as seen in the figure below [10].

References

[1] http://en.wikipedia.org/wiki/Object-relational_database
[2] http://www.agiledata.org/essays/mappingObjects.html
[3] http://martinfowler.com/eaaCatalog/concreteTableInheritance.html
[4] http://martinfowler.com/eaaCatalog/classTableInheritance.html
[5] http://martinfowler.com/eaaCatalog/foreignKeyMapping.html
[6] http://martinfowler.com/eaaCatalog/identityField.html
[7] http://martinfowler.com/eaaCatalog/lazyLoad.html
[8] www.adobe.com/newsletters/edge/october2008/articles/article2/images/fig2.jpg
[9] farm1.static.flickr.com/179/372852690_fb5eb37fde.jpg
[10] http://martinfowler.com/eaaCatalog/singleTableInheritance.html