In this chapter, we will design and build the initial data structures for our fleet management system. The goal is to illustrate, by example, the key parts of creating data structures in Dynamics AX 2012. We will be introducing ideas you may not yet be familiar with and refreshing the concepts you may be already using. The following are the topics we will cover in this chapter:
- Designing data structures for our solution
- Creating labels
- Creating the setup tables
- Creating extended data types
- Creating enumerated types
- Creating tables that support inheritance (vehicles of different types)
- Referential integrity—delete actions and validation
The solution we are going to create is a fleet management system for Contoso. We will be working with the Microsoft Dynamics AX 2012 R3 Demonstration Virtual Machine, available from PartnerSource and CustomerSource.
For the initial part of the solution, we require the following data to be stored:
- Vehicle data (Vehicles are of different types, and the attributes against each vehicle vary. They should be grouped into vehicle types for display and reporting purposes.)
- Vehicle service data
- Vehicle service schedule data
- Vehicle grouping data
Vehicle data is the main table, and we have a big design choice to make—to add fields for all attribute values for all vehicle types or to use inheritance.
Inheritance will allow us to define a base vehicle table, and then a table for each special case where we can add the additional fields. The problem with choosing this method is that, once it has been implemented, we are locked into this architecture without the need for a lot of rework—much like adding a basement garage to a house after it is built.
Another solution could be to add a child table to store the attribute values by vehicle where the possible attributes for that type of vehicle are stored in an attribute-type table. This is a nice solution as it allows the attributes to be changed through configuration and not in code. The downside is that it is difficult to handle the vehicle types in code as we are dealing with types not known at the time of design. Other problems are reporting and a potentially cumbersome interface, as it has to handle attribute values of different types.
Of course, we could also have one large table that holds all fields for all vehicle types, and use the UI logic to hide fields that aren't relevant. This is okay, but only really suits the scenario where we need some additional information for a special case; for example, a sales order return requiring additional information.
In our case, each vehicle type is not a special case. Vehicles such as cars and trucks share some base attributes, but are also distinct entity types with different information held against them.
We will not create a separate table per vehicle type; instead we will present a single view to the user. This means duplication of table handling logic, validation, relations, and so on as each table is distinct. In this case, we will use table inheritance.