In the previous part I discussed Table-per-Hierarchy Inheritance, in this part I'll go through Table-per-Type Inheritance model. There is a video demonstrating this model if you wish to watch. Here I'll update my previous sample with new entities that demonstrate the Table-per-Type Inheritance model. For the differences between 2 models and definitions return to 1st part. The updated sample can be downloaded from here.

How to define a model with Table-per-Type Inheritance:

At the beginning I'll explore the database design for this subject, and explain why Table-per-Type must be used to model the inheritance:

As you notice in the Database diagram. It is typically one-to-one relationships between Department table and other tables. Each department holds its own data on its own table. For example Engineering Department saves its specific data on DeptEngineering table, and at the same time the common data about the department such as Department Name and Administrator are stored in Department table. We are talking about inheritance here, aren't you able to visualize that?!

So from the above Schema, Table-per-Hierarchy Inheritance model will not fit, as every table must have its one separate entity. And here Table-per-Type Inheritance model can exactly fit.

So the Diagram contains 4 tables, each table will represent a separate entity. But Department table/entity will be considered the Base Type for all other 3 entities. And of course it is an Abstract Entity as it is not possible to have a Department with no specialities.

Updating and Refreshing existing Data Model:

As I'm going to update the previous sample, I'll use Visual Studio.Net 2008 SP1 to update the data model and add the new desired entities. To update your model from the database follow these steps:

1. Right Click on the model canvas and select Update Model from Database from the context menu.
2. From Update Wizard dialog window and under Add tab expand Tables node and select (check) Department, DeptBusiness, DeptEngineering and DeptMusic tables. Click Finish.
    

This is how the model will look like after update.
 

Apply Inheritance:

As you noticed there is an association created between Department and Person entities. This is out of our scope right now, so just ignore it.

By default EF didn't recognize the I wish to apply Inheritance, so It will require me to do some modifications to apply inheritance. Also EF generates associations between the newly added entities as you can see in the diagram. As long as we are going to apply Inheritance, associations between Department tables are no longer valid. To apply inheritance follow the following steps:

1. Delete Associations between Department tables this should include Department/DeptEngineering, Department/DeptBusiness and Department/DeptMusic.
2. Right Click on Department Entity and select Add -> Inheritance from the context menu.
3. From Add Inheritance dialog window, confirm that the base entity is Department. Then select DeptBusiness as derived entity.
   
4. Repeat step 3 to select DeptEngineering and DeptMusic as the derived entity.

After you finish the diagram should be closer to this

Define Mappings:

It is much simpler here -in my opinion-. Before we just start mapping and define which exactly will need mapping, you'll notice that the Keys in DeptXxx tables had been removed after defining inheritance. Yes that make sense in fact; because the main Key is in the base entity Department which is DepartmentID. And Now I have some unused properties now which are BusinessDeptID in DeptBusiness Entitiy, EngineeringDeptID in DeptEngineering Entity and DeptMusicID in DeptMusic Entity.

1. Delete the following properties
   • BusinessDeptID in DeptBusiness Entity.
   • EngineeringDeptID in DeptEngineering Entity.
   • DeptMusicID in DeptMusic Entity.
2. Right Click on DeptBusiness Entity and select Table Mapping from the context menu.
   
3. On the Mapping Details pane and under Column Mappings. For BusinessDeptID Column select DepartmentID Property as mapping property.
   
4. Repeat steps 2-3 to define mapping for EngineeringDeptID Column on DeptEngineering Table and DeptMusicID Column on DeptMusic Table.

Define An Abstract Class:

To set an Department Entity as Abstract:

1. Right Click on the Department Entity, select Properties form context menu.
2. From Properties window and Under Code Generation group, set Abstract property to True. Click Ok on the confirmation message that appears.

Conclusion:

Again, it is clearly that the database schema is the key choice between using Table-per-Hierarchy Inheritance or Table-per-Type Inheritance. In my opinion, one-to-one relationships give more abstraction and normalization to the database. So unnecessary fields are not exist in the base table. However I would go with Table-per-Hierarchy model for small projects and simple schemas where one table would hold max of 3 to 4 different types. This is my own opinion and it is not following a best practice or not even considered one.

You can download the updated sample here.