When to Use Table Inheritance

Article
05/18/2015

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

When you consider the use of inheritance between two tables, one table is the proposed base table, and the other is the proposed derived table. You should consider the use of inheritance between two tables when all the following conditions are true:

There is no thought that there might be a 1-to-many or many-to-many relationship between the two tables, if they were considered in isolation from all other tables.
The row in the proposed base table, and the corresponding row in the derived table, both refer to the same item in the real world. The two rows refer to different attributes about the item.
Each row in the proposed base table has exactly one corresponding row in the derived table. If one row is ever deleted from either table, the corresponding row must also be deleted.
The base table probably has at least two tables that derive from it. The two derived tables have fields for different kinds of attributes for different kinds of things. The two derived tables refer to different variations of the general items that are tracked together in the base table.
No item that is represented in a base table would ever be represented in more than one of its derived tables.

Note

In Microsoft Dynamics AX the system prevents any individual row in a base table from being associated to more than one derived table (at any one level in the inheritance hierarchy). This is an implementation choice that was made by the Microsoft Dynamics AX product team.
The derived table is not meant for performance tuning of the physical database, such as placing an image column in its own table.

Note

In cases where a table is not involved in any inheritance relationship, there might be a small performance penalty if you set its SupportsInheritance property to Yes.

Example Data to Illustrate Table Inheritance

This section uses example data to illustrate an effective use of table inheritance.

Same Field Names are a Clue

A pet store has several pets for sale, including dogs and birds. The store uses Microsoft Dynamics AX to track its pet inventory. The store owner envisions a database that has tables named TabPetDog and TabPetBird. The store owner creates a list of all the fields to track for each dog, and for each bird. The following table displays the two lists.

Pet dog properties	Pet bird properties
BirthDate Name NumberOfTeeth	BirthDate Name BeakColor

BirthDate

Name

NumberOfTeeth

BirthDate

Name

BeakColor

The store owner notices that the lists for dogs and birds start with the same two properties, BirthDate and Name. This overlap is a clue that there might be a third table that is less specific than TabPetDog and TabPetBird. The store owner realizes he needs to add the TabPet table. The two overlapping fields should be moved to TabPet.

The store owner settles on a design that has one base table named TabPet, and two derived tables that are named TabPetDog and TabPetBird that each extend TabPet.

Test Data for Three Tables

This section shows test data for the three tables TabPet, TabPetDog, and TabPetBird.

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. Its value 50011 refers to TabPetDog, and 50012 refers to TabPetBird.

TabPet Test Data

PetBirthDate	PetName	InstanceRelationTypeId	RecId
1988-07-20	Spot	50011	1234567890
2008-02-29	Tweety	50012	1234567891
2005-03-21	Polly	50012	1234567892
2005-04-22	Lassie	50011	1234567893
2004-05-23	Rex	50011	1234567894

Each value in the TabPet.RecId column must match a RecId value in one of the tables that derives from TabPet.

TabPetDog Test Data

NumberOfTeeth	RecId
42	1234567890
41	1234567893
38	1234567894

TabPetBird Test Data

BeakColor	RecId
gray	1234567891
red	1234567892