Throughout its history, AX has evolved into the complex and robust system that it is today, growing since its first versions that focused on inventory and storage, to focus on the accounting modules and sales processes.
As we grew the system also became more complex, due to the need to store and process large amounts of information.
As a result of the previous version of Microsoft, it was deemed necessary to take any action to help a sustained growth of a system that is vital to thousands of companies around the world.
It was decided to standardize the database from the AX 2012 version.
Recall that in practice the system contains the data dictionary, which in turn contains a number of objects that attempt to replicate as closely as possible the structure of the data that exists on the server. Each table that exists in the system is an SQL table, for example the customer table.
To understand the concept
Standardizing the database means to minimize redundancy and dependency, which means dividing large tables into smaller, less redundant ones, defining certain relationships between them. The aim is to isolate data so that additions, deletions, and modifications in a field can be made into a single table and spread through the rest using the defined relationships.
Some advantages of this are a quicker data update time, a lower risk of database locking, greater flexibility in the functionality of a solution, less redundancy it forces the relations between corresponding data tables and in theory could divide a table in several sub tables distributed in several servers, which does not apply to AX 2012.
On the other hand, the standardization of data makes this strategy difficult to build indices to a point that can be extremely complex, due to the existence of tables and sub tables that inherit attributes and properties from the previous ones. In the worst case, a person who is developing and does not know what he or she is doing can mess up something that is already running very easily.
Therefore, the first step should always be to study the relationships between existing tables:
If we want to intervene with the data of the sales process, we have to understand the relationship between SalesTable, Custable, and CustTrans, analyzing the behavior of those in the process of billing and making sure to not intervene with the standard process that already exists, except when necessary.
- A strategy to make modifications in these tables is to duplicate one of them in order to observe the behavior of the data.
- Another strategy is to create an additional intermediate table, carefully constructing the indices and corresponding relationships to manage the necessary information.
- And of course you can also intervene with the standard tables, being the most risky option but also the best, due to optimal query results obtained in the standard process that already exists.
In the following entries we will talk about best practices, common errors and some tips that will help us with the work of the normalized database.