|
|
|
|
|
|
Inheritance in O/R Mapping
by Kashif Butt - Alachisoft in Development
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:
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 the one to one inheritance. The single-table inheritance is a topic for another article. And, for our mapping exercise, we'll use an example.
Consider that Employee is a base class having attributes EmployeeId, Title, Hire date. whereas Engineer(having attributes: job level, enterprise) and Manager(having attributes: budget, dept) 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, 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.
Author: Kashif Butt works for Alachisoft, a leading software company providing O/R Mapping and Clustered Object Caching solutions for .NET. You can reach him at kashif@alachisoft.com or visit Alachisoft at http://www.alachisoft.com/.
Submit an article
|
|
|
|
|
| Developpers! |
|
Submit
your software to hundreds of software sites and archives within
minutes. more...
|
Firefox users add the
1000apps search plugin
|
|
