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

Association Table Mapping

We know that objects can handle multivalued fields by using say collections as field values but the same cannot be said about relational databases since they are constrained to single valued fields only [11]. For a one-to-many association this can be done using Foreign Key Mapping by using a foreign key for the single-valued end of the association but not for many-to-many association as there is no single-valued end to hold the foreign key [11]. To solve this problem, we can use Association Table Mapping to create an extra table to record this type of a relationship and then map the multivalued field to this link table as shown in the figure below [11].

Embedded Value

Many small objects that make sense in an object oriented system do not make sense as tables in a database, for example, money objects and date ranges [12]. It may be easier to just store an object as a table but that would mean a table of money values in this case which will be a bad design. To solve this problem, an Embedded Value maps the values of an object to fields in the record of the object's owner as seen in the figure below [12]. We have an employment object with links to a date range object and a money object and in the resulting table the fields in those objects map to fields in the employment table rather than make new records themselves [12].

Serialized LOB

Object models often contain complicated graphs of small objects where much of the information in these structures is in the links between them [13]. To understand this with an example consider the figure below which represents an organization hierarchy for customers. With an object model its is easy to represent organizational hierarchies by adding methods that allow you to get ancestors, siblings, descendants, and other common relationships [13]. But this is not a simple task in case of relational databases since several joins may be reqired to represent all the above relationships which can cause a big performance hit [13]. The solution to this is to use a form of persistent serialization called single large object or LOB where a whole graph of objects is written out in a table instead of persisting individual objects as table rows related to each other.

Comparisons of the Patterns

Concrete Table Inheritance and Class Table Inheritance are both similar in a way that both are based on mapping an entire class to a table. However, the main difference between them is that the former does not do this for an abstract class while the latter maps all the classes to tables. In some cases Class Table Inheritance may be preferred over the other as it may be achieved more easily and also helps to solve the problem of object-relational mismatch.

The concept of using a foreign key is common to several mapping patterns such as Foreign Key Mapping, Recursive Relationships Mapping and Association Table Mapping. However all these patterns differ from each other in many ways as we have seen before. Foreign Key Mapping is a more generalized mapping technique to represent several different relationships between entities. Recursive Relationships Mapping is more specific as it deals with a class or a table which is involved with both ends of the relationship. Association Table Mapping also addresses the specific issues of mapping multivalued fields of objects to single valued fields of a table.

Several patterns such as Lazy Initialization, Embedded Value and Serialized LOB may not be as frequently used as say the Foreign Key Mapping pattern but they solve specific problems very effectively.

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
[11] http://martinfowler.com/eaaCatalog/associationTableMapping.html
[12] http://martinfowler.com/eaaCatalog/embeddedValue.html
[13] http://martinfowler.com/eaaCatalog/serializedLOB.html