NAV Team: NAV Design Pattern - Implementation of Surrogate Keys using the AutoIncrement Pattern

[Blog bot]

This week's pattern describes how to link tables in a generic way, so that satellite tables (such as a comment table) can be linked to multiple different master tables, each having its unique related comment lines. You can find all published patterns on the Dynamics Community Wiki site.


Meet the Pattern
This Pattern is meant to create generic & reusable links between tables. The goal is to have an easy generic way to link a generically designed sub table to a record on a main table which can be used for other links too.

To minimize the impact of customizations and to keep modules as generic and reusable as possible the idea of the Unique Record Identifier is:

• To create a generic and reusable link on a main table with minimum impact on the table.
• To create generic and reusable sub tables that effortless can be reused anywhere in the application.

Know the Pattern
Over years of development many things are repeated across different implementation and even inside the same application. A typical example could be adding comments to an area just as it is done in Microsoft Dynamics NAV multiple times. There can be reasons for doing this again and again, but not only does this need to be maintained and upgraded over the years, but all the implementations of comments also needs to be tested separately. If a standard and generic comment could be developed and a generic way of connecting it to a main table this could resolved. This is exactly what this pattern will resolve.

The following diagram shows the table structure for linking a Document Header and Line Table with a Document Comment Table.



The following diagram shows the table structure for linking a Master Data Table with a Master Data Comment Table.



A typical way of linking a table to master data or to a document has been to use the primary key of the table being linked to. This causes some issues as the linked table now is designed specifically for the main table and cannot be reused. In case of renames the linked table needs to be renamed too which is costly in processing. Code also needs to be added on the delete trigger of the table to ensure that the attached records get removed if needed. The following diagram shows a generic way of creating a Comment table and linking it in a generic way to the main table no matter what this table might be. The Unique Record Identifier on the main tables is an Integer with AutoIncrement set to Yes.





It is recommended using this pattern in all tables which need sub tables unless specific reasons exists for not doing this.


Use the Pattern
Step 1: Create a generic Unique Record Identifier in the main table

The pattern is implemented by adding a field called Unique Record Identifier in a table (Main Table) where links are needed to be established to. Set the Property Data Type to Integer, Editable to No & AutoIncrement to Yes.

Step 2: Create a generic linkable subtable.

Read more about the Unique Record Identifier on NAV Wiki...



Best regards,

Soren Klemmensen

The NAV Patterns team



Источник: http://feedproxy.google.com/~r/Micro...t-pattern.aspx

[Sancho]

что-то схлопотал я проблем с использованием RecordID как с типом данных... кажется вычисляемые поля не работали...
а если RecordID кидать в текст, то вычисляемые поля работают, но при переключении языка такая ерунда получается!
или что-то переделали, или там остались засадки.
хотя решение да, универсальное, как для коментов, так и для аттачментов