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.

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

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.

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

The Insert method looks like this. Please note that Update is very similar to Insert except that a unique EmployeeId 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; } }

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.