A Simple Exploration to Table Inheritance in AX 2012

Hi There!

On this post I would like to explore table inheritance in AX 2012. Microsoft has made a lot of changes to the table structure in this new release and it is important that we understand the core rules of this new concept.

In a nutshell, a table in AX 2012 can be extended (as a base table) to other tables. On this same thought, a table also can derive from another table.  So how does AX 2012 support this? Well, tables in AX 2012 have two new properties; (1) SupportInheritance property, and (2) the Extends property, which together control table inheritance.

So, when do we know we can use table inheritance?  First of all, we need to define which table will be the proposed base table, and the proposed derived table.  In addition, the following conditions (From a Microsoft Source) will tell us whether we can use inheritance between two tables:

1. There is no thought that there might be a 1-to-many or many-to-many relationship between the two tables.
2. An existing row in the proposed base table, and the corresponding row in the derived table, both refer to the same item in the real world.
3. Each row in the proposed base table has exactly one corresponding row in the derived table.
4. If one row is ever deleted from either table, the corresponding row must also be deleted.
5. The base table probably has at least two tables that derive from it.
1. The two derived tables have fields for different kinds of things.
2. The two derived tables refer to different variations of the general items that are tracked together in the base table. 
6. No item that is represented in a base table would ever be represented in more than one of its derived tables.
7. The derived table is not meant for performance tuning of the physical database, such as placing an image column in its own table.

Moving right along, let’s see an example of this. Let’s take a dealership that sales Cars and Trucks.  The dealership knows that both the cars and trucks have a Make and a Model. However, at the same time, the dealership wants to know a specific “thing” about the car and the truck that is not part of both.

In the following example, the dealership puts together a spreadsheet with two columns (one for the car and one for the truck). They identify that a Car and a Truck can be in one entity sharing the model and the make, but they need to also track how many passengers a car can sit in a car, and how much weight a can be loaded into a truck.

Vehicle – Car	Vehicle - Truck
Model	Model
Make	Make
Number of passengers	Maximum Weight

This is the perfect example to implement table inheritance as we can recommend to the dealership to create two separate tables for the two different conditions that they need to track (number of passengers and weight)

So, this is how table inheritance would work; The RecId system field has been added to each table as the primary key. Also, the system field InstanceRelationTypeId has been added to the base table, and its values refer to derived tables.

Car – Truck Table

Vehicle Make	Vehicle Model	InstantRelationTypeId	RecId
Toyota	Scion	50011	1234567890
Mac	Loader	50012	1234567891
Freightliner	Speed King	50012	1234567892
Ferrari	Diablo	50011	1234567893

Weights Table (Instant Relationship type 50012)

Weight	RecId
658 lbs	1234567891
1569852 lbs	1234567892

Passengers Table (Instant Relationship type 50011)

MaxPassengers	RecId
5	1234567890
2	1234567893

In the example above, the Car-Truck Table holds the common data for both cars and trucks’ make and models. The field InstanceRelationTypeId defines which table will be inherited from, and the RecId field defines the related record on the derived tables.

There is a lot more to learn about this topic, and I encourage you to visit partner source and/or the Microsoft resources about it.

This is all for now, but check my blog later this week to learn about creating services in AX 2012 and consuming them from an external C# application.