Object-Relational Mapping - Taking the Horror Out of Data Access

Object-Relational Mapping – Taking the Horror Out of Data Access

1. Horror stories and the object data divide
Let’s face it, the world is getting more and more OO, but we are still pretty happy with our relational databases. In fact the RDBMS is the foundation upon which basically all of our data driven applications reside. Having said this we are pretty aware of the fact that the relational data models we deploy are limited. Sometimes they aren’t much more than a bunch of tables that are increasingly cursed due to limitations and design flaws.

To develop great applications, we need a solid foundation based on a great data model, a data model that should matches real life as closely as possible, the so called domain model. How many database schemas actually resemble the real-world problem? Data access has become a time-consuming horror story that can take the fun out of any development project. But it doesn’t have to be this way.

2. Benefits of OO in stateless business methods
Even though relational theory is great for storing and retrieving large amounts of structured data, it hardly makes for readable business methods. The reason being that the relational data model contains very little information (despite what the name implies) about the relationships and intended use of the data. It has a low level of abstraction in the same way as C compared to VisualBasic. A properly implemented object-oriented data model on the other hand gives you the full picture and allows business methods to contain more straightforward, readable, code. Comparing two code snippets proves our point. The first code snippet interfaces the relational data model through SQL, the second interfaces the object-oriented data model through OO-notation (in this example we use the API of an O/R DAL generated by Pragmatier Data Tier Builder, se reference at the end of this article):

Public Sub GiveRaise(ByVal ConnStr As String, _
                     ByVal EmployeeID As Long, _
                     ByVal RaisePercent As Double)

Dim strSQLFetchSalary As String
Dim strSQLUpdateSalary As String
Dim Salary As Long
Dim rs As ADODB.Recordset
Dim conn As New ADODB.Connection

conn.Open ConnStr

strSQLFetchSalary = "Select Salary From " & _
                    "Employees Where emp_id = " & _
                    EmployeeID

rs.Open strSQLFetchSalary, conn

If Not (rs.BOF Or rs.EOF) Then
   Salary = rs("Salary")
End If

rs.Close

Salary = Salary + (Salary * RaisePercent)

strSQLUpdateSalary = "Update Employees Set (Salary) = " & _
                      Salary & " Where emp_id = " & _
                      EmployeeID

conn.Execute strSQLUpdateSalary

conn.Close

End Sub

fig 1. Business methods using SQL.

Public Sub GiveRaise(ByVal EmployeeID As Long, _
                     ByVal RaisePercent As Double)

Dim ObjectFactory As New EmployeeServer.CComponent
Dim Employee As Employee

Set Employee = ObjectFactory.GetEmployee(EmployeeID)

Employee.Salary = Employee.Salary + (Employee.Salary * RaisePercent)

End Sub

fig 2. The same business methods calling persistent objects in an O/R DAL.

3. Introducing an O/R DAL
Obviously there is a problem if we want to use OO in our business methods yet store the data in an RDBMS. We have to map the object-oriented data model to a relational data model, basically mapping objects to tables. To do this properly we need to introduce an object-to-relational mapping data access layer, a so-called O/R DAL. All data access should be done by calling the persistent data objects in this layer, narrowing any maintenance efforts such as database refactoring or upgrades to a single part of the application.

The answer to why we choose to implement an object-to-relational mapping data access layer (O/R DAL) is obvious when we present the exciting features it will bring us. What it basically spell is faster, simpler and more maintainable development:

–The persistent data objects that we have created are strongly typed, saving us from costly and embarrassing type mismatches and improving the accuracy of our code.
–The components expose themselves beautifully through Intellisense, eliminating typos and problems with remembering property names.
–And then for the greatest feature of all… you can navigate the object-oriented data model by using the OO-dot syntax, such as OrderItem.Order.Customer.BillingAddress. Beautiful and sensational! Even your client can be convinced that this refers to the billing address of the customer whose order item is being processed.

fig 3. Navigate through the data structure using the OO-dot and Intellisense. No more worries about spelling, vocabulary or dialect.

Even though we access the data at a higher level by navigating through beautiful objects, the data is still available in a relational form allowing you to plug straight into the db to perform number crunching or data mining. Not to mention the fact that you can keep SQL-Server and your existing DBA without retraining.