axdaily: Surrogate keys in AX 2012

[Blog bot]

Источник: http://axdaily.blogspot.com/2011/05/...n-ax-2012.html
==============

Data modeling enhancements were prioritized really high in AX 2012. I already mentioned one earlier – table inheritance, but there are a lot of other important features about data modeling.

One such feature is the support of surrogate keys. Surrogate keys recommended themselves as a very good solution for complex databases because of their beneficial properties, like immutability, robustness to redesign and performance. And now this best practice is coming to AX. It is the recommendation for AX 2012 to use surrogate keys for all new tables, unless there is a good reason not to do so.

Actually, previous versions of AX already have surrogates – RecIds. And it was possible to introduce surrogate key just by setting CreateRecIdIndex table property to Yes. But there is a problem with the inconvenience of working with surrogates in the UI. Imagine InventTable using surrogate key and all other tables that have relations to InventTable using its surrogate key as a foreign key. Then on any form where ItemId should be displayed (let’s say on the SalesTable) an extra join to InventTable will be needed to fetch the ItemId. And what if user can change the item? Then even more custom logic is needed to resolve the entered ItemId to the corresponding surrogate key value and write it to the SalesLine.

This inconvenience actually was a showstopper. But not anymore. AX 2012 got kernel support for surrogate key substitution. And not only in forms, but in Axd document services and even in the debugger.

Let me show an example of how it works:
UnitOfMeasure table uses surrogate key as a primary key. However, the user-friendly identifier of the UnitOfMeasure table records is the UnitOfMeasureSymbol field. This field is a part of the SymbolIdx index, which is specified as a replacement key on the UnitOfMeasure table.


This setup basically means that surrogate key values (RecIds) of the UnitOfMeasure table records will be automatically substituted in the UI with the replacement key values (UnitOfMeasureSymbols).
UnitOfMeasureConversion table has a relation to the UnitOfMeasue table through the surrogate key.


As you can see, on the UnitOfMeasureConversion form there are only UnitOfMeasureConversion datasources. And you can notice new ReferenceGroup control which is bound to the FromUnitOfMeasure field (which contains surrogate values).


And in the UI it will look just like UnitOfMeasureSymbol field is shown instead. And it can also be modified and that will be handled automatically, without a single line of code.


Actually ReferenceGroup control has much more interesting capabilities and thus deserves a separate post.


Источник: http://axdaily.blogspot.com/2011/05/...n-ax-2012.html