What is O/R mapping?

If you’re developing an object oriented application that requires a relational database, you’ll need to develop persistence objects that are modeled against your database design and know how to interact with different tables in the database. You can either develop these objects by hand or use an O/R mapping tool to map and generate them quickly. Hopefully, after reading this article, you’ll be convinced that developing by-hand is a bad idea in most situations.

CREATE TABLE t_employees (
      employee_id int IDENTITY (1, 1) NOT NULL,
      name nvarchar (40) NOT NULL,
      birth_date datetime NULL,
      photo image NULL,
      reports_to int NULL,
)

// Domain object class for t_employees table
public class Employee
      {
             Int32               _employeeId;
             String             _name;
             DateTime           _birthDate;
             Byte[]             _photo;
             Int32              _reportsTo;
             ArrayList          _subordinates;
             Employee           _supervisor;
             Int32 EmployeeId {
                get { return _employeeId;} set { _employeeId = value; }
             }
            String Name {
                get { return _name; } set { _name = value; }
             }
            DateTime BirthDate {
                get { return _birthDate; } set { _birthDate = value; }
                   }
            Byte[] Photo {
                get { return _photo; } set { _photo = value; }
                   }
            Int32 ReportsTo {
                get { return _reportsTo; } set { _reportsTo = value; }
                    }
            ArrayList Subordinates { 
                get { return _subordinates; } set { _subordinates = value; }
            }
            Employee Supervisor { 
                get { return _employee; } set { _employee = value; }
             }
      }
 // Persistence object for Employee class
public interface IEmployeeFactory
      {
            void Load(Employee object, int nDepth);
            void Insert(Employee object);
            void Update(Employee object);
            void Delete(Employee object);            
            // Query methods
            ArrayList FindSomeEmployees();
            // Relationship methods
            void LoadSupervisor ( Employee emp);
            void LoadSubordinates(Employee emp, int nDepth);
      }

Below is an example of how a client application will use this code:

public class NorthwindApp
    {
        static void Main (string[] args) {
            Employee emp = new Employee();
            EmployeeFactory empFactory = new EmployeeFactory();
            // Let's load a employee from Northwind database.
            emp.EmployeeId = 10045;
            empFactory.load(emp);
            // empList is a collection of Employee objects             
            ArrayList empList = empFactory.FindSomeEmployees();
            // subList is a collection of Employee's subordinates objects             
            ArrayList subList = empFactory.LoadSubordinates(emp, 1);
            // supervisor is Employee's supervisor object
            Employee supervisor = empFactory.LoadSupervisor(emp);
         }
    }

Feature 1: Flexible object mapping

Everything in O/R mapping starts with mapping your objects to your relational tables. Here are some specific features in this area that you should know:

1. Tables & views mapping: The tool should let you map objects to both tables and views in
   your relational database. Mapping to views is important because many real-life applications prefer to use views instead of tables.
    
2. Multi-table mapping: The tool should let you map an object not only to a single table but
   also to multiple tables and specify a join between these tables. If your application needs to fetch list of rows that span multiple tables (a common occurrence in web applications), you’ll need this feature.
    
3. Naming convention: The tool should let you use a different naming convention in objects and their attributes than in relational databases. If your database table is named t_employees, your object may need to be named Employee.
    
4. Attribute mapping: There are a number of features that the tool should support:

1. Primary key: Your object must distinguish the primary key from other columns. It should also let you use a single-column or multi-column primary key.
    
2. Auto generated columns: Some columns are auto generated (IDENTITY or SEQUENCE) and your object must have code to handle fetching the generated values after an insert.
    
3. Read-only columns: Some columns are not meant to be set by the client but instead their values are system generated (e.g. creation_dtime column using getDate() function in SQL Server). Your object must have appropriate code to fetch these system-generated values.
    
4. Required columns: Your object must do data validation for required columns at the time of insert or update operations. This is much more efficient than wasting a trip to the database just to get an error message back.
    
5. Validation: In most cases, you have defined various constraints on your database columns. It would be nice to have the same validations done in your persistence objects so you can save an unnecessary trip to the database just to receive an error message.
    
6. Formula Fields: There are many situations where when you fetch data from the database, you use a regular expression rather than a column (e.g. Annual Salary object attribute might be a formula field monthly_salary * 12).
    
7. Data type mapping: Sometime, you want to map one data type from the database to another data type in your object. For example, a datetime type might be converted into a string. Your object must have the logic to do this automatically in both directions (read and write).

Feature 2: Use your existing domain objects

Feature 3: Transactional operations (CRUD)

1. COM+/MTS: Microsoft Transaction Server (MTS) manages all transactions of an application. Your objects do not start, commit, or rollback a transaction. They only return success or failure from their methods and MTS figures out when to do “BeginTrans”, “Commit”, or “Rollback”. Additionally, all your factory objects are stateless so MTS can do object pooling on them. This is a specific design pattern that your O/R mapping tool must understand and generate your persistence objects to comply with it. Most common applications for this environment are ASP.NET applications and .NET Web Services.
2. Stand-alone: This is the environment where your application manages all the transactions itself. It needs to know where to go “BeginTrans”, “Commit”, and “Rollback”. And, your O/R mapping tool needs to be aware of this environment and generate code to comply with it. Most common situations for this are Windows Forms based client/server applications that directly talk to the database server.

Feature 4: Relationships and life cycle management

1. One-to-one relationship: In this, your object must contain a reference to exactly one other object and must handle load and save scenarios for it.
2. Many-to-one relationship: This is very similar to one-to-one where your object must contain a reference to exactly one other object and must handle load and save scenarios.
3. One-to-many relationship: In this, your object must contain a collection of the related objects and must handle loading them with load and also adding and removing them with save operations.
4. Many-to-many relationship: This is the most complex relationship and involves a bridge table in the database to establish the relationship. There are two different situations for the bridge table as described below:

1. Bridge table with only primary key: In this situation, the bridge table contains only the primary key (which is actually composed of multiple foreign keys). So, your object need not have any bridge table attributes and only needs to keep a collection of the related objects (similar to one-to-many). In fact, the public interface of your object is usually identical to one-to-many but the underlying code is different because of the bridge table.
2. Bridge table with additional columns: This is the most complex situation because the bridge table has additional useful columns that your object must cater for. Your object needs to load a collection of composite objects containing both the bridge table and the related table information.
   The life cycle management feature must include the ability to load a primary (or parent) object and through this object load all the related objects (meaning all the different relationships). You should also be able to add newly created related objects or remove existing related objects from the relationships. And, when you save the primary object, it must save all the relationship information along with it (as one transaction). A good O/R mapping tool would let you define all types of relationships and also handle life cycle management in the generated code.

Feature 5: Object inheritance

1. One table per object: This is the most popular and flexible pattern. In this, each object is mapped to its own table in the database. And, there is a one-to-one relationship between every base object and its derived object. The foreign key of this relationship is kept in the derived object. It is the most flexible because without changing the structure of any existing tables, we can keep adding to the inheritance hierarchy. However, it is not the most efficient for loading both base and derived objects because a separate “load” is done for each object.
2. One table for all objects: In this pattern, the base object and all the derived objects are represented in one table in the database. This table contains columns representing attributes from all the objects. It is the most efficient for loading and saving data but is very limited because adding a new object to the inheritance requires changing the structure of an existing table in the database that is highly undesirable.
   Keeping this in mind, the O/R mapping tool must support at least the “One table per object” approach and if it can also support the second approach that is icing on the cake. The generated code for base and derived classes should handle the following situations:
    
   1. Insert and update operations: The derived class must first ask the base class to do Insert or Update and then do its own. But both the base and derived class operations must be performed in one transaction.
   2. Delete operation: Unlike the insert and update operations, the delete operation is performed first on the derived object and then on the base object. However, both must be done in one transaction.
   3. Load operation: The load operation in the derived class must also call load on the base class and both of these should be done in one transaction.

Feature 6: Static and dynamic queries

Feature 7: Stored procedure calls

1. Existing Stored Procedures: First is when you already have custom stored procedures in the DBMS and you want to have your persistence objects call them. In this situation, the O/R mapping tool must allow you to define methods in your objects that can call stored procedures. It must also support different parameter types (in, out, in/out) and also whether the stored procedure returns a Recordset or not. If the stored procedure returns a Recordset then the object must return this data to its client.
2. Generate Stored Procedures: The second situation is where all the SQL (minus the dynamic queries) that is going to be generated as a result of your object-relational mapping is put inside the DBMS as stored procedures and your objects code is generated so it calls these stored procedures. If you didn’t generate stored procedures for all the SQL, it would be put inside your object source code as “dynamic SQL”.

Feature 8: Object caching

Feature 9: Customization of generated code

Feature 10: Template customization

Conclusion

You should seriously consider using an O/R mapping tool as it will save you a lot of development and testing time. And, when it comes to evaluating which tool is best for you, you should know what to look for. I hope this article helps you gain a better understanding of O/R mapping.