Top 10 Must Have Features in O/R Mapping Tools

Feature 4: Relationships and Life Cycle Management

The foundation of a relational database is that tables have relationships with other tables. Similarly, when you map objects to these tables, your objects also need to establish the same relationships with other mapped objects. Therefore, your O/R mapping tool must support this very important feature by letting you determine which relationships you want to keep in your objects. Below are the different types of relationships you must have:

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 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 to. 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 handle life cycle management in the generated code.

Feature 5: Object Inheritance

As you already know, a very important aspect of object-oriented programming is inheritance. However, relational databases do not automatically provide inheritance in the relational model. But, there are a number of patterns on how to map object inheritance to a relational database. And, a good O/R mapping tool must provide this capability.

Here are a few ways to map object inheritance to relational databases.

1. One table per object: This is the most popular and flexible pattern. 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 efficient for loading both base and derived objects because it requires a separate “load” 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 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, which 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 this 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 an insert or update operation, 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

The next most common thing that a database application does is to retrieve rows of data from one or more tables. The application does this by using SQL queries (SELECT statements). However, an object-oriented application wants to fetch a collection of objects and not rows. So, the O/R mapping tool must provide a way for you to create queries that return collections of objects.

Static queries are those that are defined at compile time and the only thing that changes at runtime for them are the parameter values. These queries can be precompiled and run very efficiently. So, the O/R mapping tool must allow you to define static queries as methods of your objects and specify whether these queries take any run-time parameters or not.

Dynamic queries on the other hand are those where either the query or its criteria is created at runtime. These queries cannot be precompiled and must run as “Dynamic SQL.” However, their benefit is that they allow for those situations in your application where you are performing ad hoc search operations and, based on the user’s input, determine what the query should look like. These queries also need to be provided as methods to your objects but with the flexibility that you can specify the “WHERE clause” and “ORDER BY clause” at run-time.

Feature 7: Stored Procedure Calls

Stored procedures have become very popular in high transaction environments because they allow you to put all your SQL inside the DBMS and in a compiled form. As a result, your SQL does not have to be compiled at runtime because that is a very expensive process. An O/R mapping tool must support two situations when it comes to stored procedures as described below:

1. Existing Stored Procedures: The first situation is when you already have custom stored procedures in the DBMS and you want to have your persistent objects call them. 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 be able to determine 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 did not generate stored procedures for all the SQL, it will be put inside your object source code as “dynamic SQL.”