Monday, April 23, 2012

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.

6 comments:

  1. Thanks for you, for a very clear explination about table inheritance in AX2012 which I ever understood even after reading many released documnents. This made me to get the clear picture of table inheritence. I would wish you to continue your posting futher and will be waiting for that.. great work!!!

    ReplyDelete
    Replies
    1. Hi!

      You are very welcome, PrasanKumar. Thank you so much for reading my blog and I'm glad that it was helpful to you.

      Check my blog later this week for a post about how creating services : )

      Have a great day and thanks again!

      Delete
  2. I think this blog post is more powerfully simple than AX but I guess Microsoft has probably copyrighted the slogan ;)

    ReplyDelete
  3. Is there a way i can move data from the Parent table to the Child? (Considering that the Parent table is not abstract?)

    ReplyDelete

Thank you for your thoughts. Your comment will appear in my blog shortly after review.

Have a great day!