When to Use Table Inheritance

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.

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

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

TabPetDog Test Data

TabPetBird Test Data

See also

Table Inheritance Overview

Walkthrough: Creating Base and Derived Tables

How to: Use the UnitOfWork Class to Manage Database Transactions

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.