Object — Relational Mapping Patterns

Avijeet Dash and Satyabrata Dash

We will discuss Object-Relational Mapping Patterns over a few articles. It will give an insight into issues concerning persistence layer design, domain layer —persistence layer relationships. In this article we will focus on the object to tables mapping and the inherent impedance mismatch between them. It shows the interesting link between a domain object model and database schema.

Objects and relational tables are fundamentally different because of their different data structures. Relational databases are table oriented: visualizing data as a collection of one-dimensional tables, which that can be manipulated through by relational calculus. Objects include collection data structures such as lists and maps, use inheritance and direct links (having the reference of another object) between objects. On top of this we have the fact that Objects are created and modified in memory whereas the database operations are disk operations. what happens in memory with what is happening on disk. Since the database is usually a separate process, we have to do this with as few remote calls as possible.

Let’s quickly re-visit the various class relationships. Typically the static relationships between object types are of 3 kinds

Inheritance is the easily and widely understood relationship, which is to have a parent-child relationship where the child inherits all the properties and behavior from the parent. Usually all programming languages support this relationship implementation. This is also known as the generalization-specialization relationship. One thing to notice here is in the table relationships parent-child term is often used to mean a different kind of relationship not inheritance (which is actually a 1:n association/aggregation relationship).

Association and Aggregation is the generally less understood relationship, because at some point the logical and physical relationship between the modeled objects makes the difference. For example: A customer having multiple orders and each order having multiple order items is association. And a circle object having points is composition and having style objects is aggregation. As you can see, the relationship between order and order item objects is a logical containment. And the relationship between circle and point objects is a physical containment where points are physically part of the circle. Style objects though physically part of circle is an aggregation relationship because other objects like polygon or rectangle might use them. Though order items also make up an order and any other order object doesn’t use them, it’s more a ‘has-a’ rather a ‘part-of’ relationship. At some point its up to the modeler’s discretion to define the relationship, which doesn’t matter, as the implementation is pretty much same for association and aggregation.

For our discussion we will take association/aggregation relationships as below

Object-Table Mapping Pattern Catalog

 

This is a one to one relationship, which could be mapped to a single table. For example: Employee (aggregating object) having addresses (aggregated object). The number of addresses that an employee can have is pretty much fixed.

 

 

Foreign Key Aggregation

The same relationship could be split in 2 tables with a dependent table for the aggregated object type.

 

 

Preferred Approach

Using Single Table Aggregation is simple and if the relationship more static in nature (where the aggregated object properties doesn’t change), it’s a surely the preferred approach.

To map a one-to-many object relationship in tables, a parent-child relationship is used where the child table holds a unique primary key along with the primary key of the parent table as foreign key.

For example: The Order-Order items association, where for each order many order items exists.

 

 Association Table

To map many to many associations in relational tables, ideally a new table is introduced with the object identifiers from both the main tables.

For example: employee to department relationship. Each employee may work for many departments and each department may have many employees.

 

For understanding the possible o-r object mapping possibilities in case of inheritance, lets look at an example where Party is the parent with Employee and Customer as the children. 

Only one table could be used to store information on all the objects including the parent and all child classes.

 

Tables only for sub classes, no table for parent class.

 

 In this approach tables are designed for all the classes in the tree.

In the next article, we will discuss some of the behavioral patterns on Object-Relational mapping patterns catalog.

http://www.objectarchitects.de/ObjectArchitects/orpatterns/

http://www.j2eeolympus.com/J2EE/JDBC/ObjectRelationalMapping.jsp

http://www.ambysoft.com/mappingObjects.html