Posted by: Kashif Bashir; Alachisoft; www.alachisoft.com

Object oriented applications usually have inheritance as an important part of their design, including in their domain objects. However, the corresponding data model has no built-in mechanism for specifying inheritance. Therefore, you must map your domain objects to your relational database intelligently.

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:

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

UML diagram of class hierarchy.

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

Data model showing one-to-one relationships

In this data model, Employee class is directly mapped to 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. The Engineer and Manager actually use the EmployeeId from their base class Employee to store in t_engineer and t_manager tables.

How Does 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 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     } }

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;     } }

 

he 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 the 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.