Inheritance in O/R Mapping

Introduction

Domain objects in an application represent the core data that is used by the application, and therefore these objects are usually persisted in some data source. If the data source is a relational DBMS, then domain objects have to be mapped to a relational model which looks different from an object model. Therefore, there are many things that you must keep in mind when mapping these domain objects to a relational database. This article focuses on mapping inheritance in your domain objects to your relational databases.

Mapping Inheritance

As you know, inheritance represents an "IS-A" relationship between two classes, where the derived class inherits all the characteristics of the base class. Incidentally, there is no direct inheritance relationship defined in the relational model. Therefore, when you map inheritance from your object model to your data model, you have two different ways in which you can map inheritance to a relational database. They are:

1. Vertical inheritance mapping: In this approach, each class in the inheritance hierarchy maps to its own table in the database, and all the tables in the database have a one-to-one relationship with each other.
2. Single-table inheritance mapping: In this approach, all classes in the inheritance hierarchy map to the same single table in the database, and this table contains columns for all the classes. It also contains a type column to indicate the type for each row.

In this article, we'll only discuss one to one inheritance. Single-table inheritance is a topic for another article. And, for our mapping exercise, we'll use the object model shown below as an example.

Employee is a base class whereas Engineer and Manager are derived classes. An Engineer is an Employee and so is a Manager. Therefore, both Engineer and Manager inherit all the attributes and methods of an Employee. The code skeleton of domain object classes for this model should look something like this:

public class Employee {

    public Employee() {}

    // Properties for Employee. Implement your 'get' & 'set' here.
    public String  EmployeeId { get {;} set {;}}
    public String  Title { get {;} set {;}}
    public DateTime  HireDate { get {;} set {;}}
}

public class Engineer : Employee {
    // Some of the private data members
    public Engineer () {}

    // Properties for Engineer object
    public String  JobLevel { get {return _jobLevel;} set {_jobLevel = value;}}
    public String  Expertise { get {return _expertise;} set {_expertise = value;}}
    public ArrayList  GetEmployeeByTitle(String  strTitle);
}

public class Manager : Employee {

    public Manager () {}

    // Properties for Manager object
    public float  Budget { get {return _budget;} set {_budget= value;}}
    public String  Dept { get {return _dept;} set {_dept = value;}}
}

Vertical Inheritance Mapping

The simplest and most flexible inheritance mapping is where each class in the inheritance hierarchy (base or derived) is mapped to its own table in the database. And, each derived class table in the database has a one-to-one relationship with the base class table along with an existence dependency (meaning, the derived class table cannot have a row unless there is a corresponding row in the base class table). The main benefit of this mapping is that it is very flexible, and allows you to keep adding more derived classes without impacting any of the existing code in your application and also any existing tables in the database that are most likely holding a lot of valuable data. However, the drawback is that when you want to load a derived class many level down the hierarchy, the load involves a join of multiple tables (although the join is on primary keys), and is therefore a little slower. Additionally, when you do any insert, update, or delete operation, you end up making multiple database calls, one for each level of the inheritance hierarchy. Below is the data model for the above-mentioned object model:

In this data model, the Employee class is directly mapped to the t_employee table, Engineer is mapped to t_engineer, and Manager is mapped to t_manager. And, t_engineer and t_manager both have a one-to-one relationship with t_employee, along with an existence dependency. Each attribute of Employee has a corresponding column in t_employee with which it is mapped, and the same is true for Engineer and Manager. However, notice that Engineer or Manager does not have its own EmployeeId attribute, but t_engineer and t_manager both have their own employee_id column. Engineer and Manager actually use the EmployeeId from their base class Employee to store in the t_engineer and t_manager tables.

How Does the Code Look?

In vertical inheritance mapping, the code in the base class is totally unaware of the fact that there are derived classes, and there is nothing special about it to mention here. This is also true to object oriented design principles. However, the code in derived classes is aware of inheritance, and has to tackle one to one mapping as described below.

// Employee persistence
public class EmployeeFactory : IEmployeeFactory
{
    // Standard transactional methods for single-row operations
    void Load(Employee emp) { /* standard code to load from database */ }
    void Insert(Employee emp) { /* standard code to insert into database */ }  
    void Update(Employee emp) { /* standard code to update in database */ }  
    void Delete(Employee emp) { /* standard code to delete from database */ }  
}

// Engineer persistence
public class EngineerFactory : EmployeeFactory, IEngineerFactory
{
    // Standard single-row transactional methods and a query method
    void Load(Engineer emp) { /* standard code to load from database */ }
    void Insert(Engineer emp) { /* standard code to insert into database */ }  
    void Update(Engineer emp) { /* standard code to update in database */ }  
    void Delete(Engineer emp) { /* standard code to delete from database */ }  
    ArrayList FindEngineersByExpertise(string expertise) { /* query code here */ }
}

// Manager persistence
public interface IManagerFactory : IEmployeeFactory 
{
    // one to one mapping specific code here for all standard methods
}

Code for CRUD Operations

Let's see how one of the derived classes implements the CRUD methods. Below is the Engineer class code:

public void Load(Engineer eng){
    try {
        // eng.EmployeeId is passed by the client. Call base to load it first
        // which fetches data and populates only the base class attributes.
        base. Load(eng); 
        // Now load the Engineer BUT use the same EmployeeId as Employee
        _LoadFromDb(eng);
    }
    catch (Exception e){
        // handle exception here
    }
}

The Insert method looks like this. Please note that Update is very similar to Insert except that a unique EmployeeId is not generated.

public void Insert(Engineer eng){
    try {
        // Code below tracks who does BeginTransaction and Commit in inheritance
        // hierarchy. _txnCount is in top-level base class.
        if (_txnCount == 0) {
            BeginTransaction();
        }
        _txnCount++;

        // Call base to insert Employee and generate a unique EmployeeId
        // If your base class has another base class, it will call its insert
        base. Insert(eng); 
        // Now insert the Engineer BUT use the same EmployeeId as Employee
        _InsertIntoDb(eng);
        if (_txnCount == 1)
            Commit();
        _txnCount--;
    }
    catch (Exception e){
        Rollback(); // Rollback the current transaction 
        throw e; 
    }
}

The Delete method is very similar to Insert and Update except for a couple of things. First, you have to know whether your database has specified cascaded delete. If yes, then you only need to call the Delete in the base class and your derived object's corresponding row will automatically be deleted. If not, then you must first delete the derived table's row and then call Delete in the base class. And, you must do all of this as part of one transaction (similar to the example above on Insert).

Code for Queries

Queries in base classes are not aware of class inheritance, and therefore no special logic is required there. However, queries in a derived class with database mapping must always do a join between the base class table and the derived class table. This way, all the columns can be fetched and mapped to the attributes of both base and derived classes. Below is a query method for the Engineer class:

// Return an ArrayList of Engineer objects
public ArrayList GetEngineersByExpertise(string expertise){ 


    Engineer eng; 
    ArrayList objList = new ArrayList();
  
      try {
        string sql = "SELECT x.employee_id, x.title, x.hire_date,
                             y.expertise, y.job_level 
                      FROM t_employee x INNER JOIN t_engineer y
                                ON x.employee_id = y.employee_id 
                      WHERE y.expertise = @expertise";

        SqlConnection cn = new SqlConnection("Database=myDatabase; uid=sa; pwd=");
        cn.Open(); 
        SqlCommand cm = new SqlCommand(sql, cn);
  
        cm.Parameters.Add("@expertise", SqlDbType.VarChar, 0, "expertise"); 
        cm.Parameters["@expertise"].Value = expertise; 
        SqlDataReader dr = cm.ExecuteReader();
  
             while (dr.Read()){ 
                   eng = new Engineer(); 
                   eng.EmployeeId = dr["employee_id"]; 
                   eng.Title      = dr["title"]; 
                   eng.HireDate   = dr["hire_date"]; 
                   eng.Expertise  = dr["expertise"]; 
                   eng.JobLevel   = dr["job_level"]; 

            objList.Add(eng); 

        }
        dr.Close();
        cm.Dispose(); 
    } 
       catch (Exception e){
        throw e; 
    } 
     return objList; 
}

Conclusion

In this article, I have discussed one of the basic techniques of mapping class hierarchy to relational databases. You can either choose to do all of this by hand, or use one of the O/R mapping tools available in the market that will generate all this mapped code for you based on your data model.