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

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

Rate This
  • Comments 3

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

Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post
  • Looks like the dumb link tracking in outlook web access has struck again ... this is the real link for the Dynamics Community Wiki.

    community.dynamics.com/.../105.nav-design-patterns-repository.aspx

  • Any problems in changing the standar Ledger Entries tables into using the Autoincrement property? I know about the "link" between the Ledger Entry number (GL Entry and Customer Ledger Entry), but as this is not really of importance I would guess that AutoIncrement would be very much faster than FIndLast?

    Thanks

  • The largest problem with AutoIncrement is that it needs a round trip to the database so it would probably end up taking longer that a buffered set of inserts. This is one of the reasons DynamicsCRM uses a GUID field for it's unique ID.

    But for your specific example the major problem will probably be that the G/L register records won't work anymore as they use a range of records numbers. One possible solution would be to turn the reference around; ie store the G/L register number on the G/L entry. There is also the link between the G/L and the inventory that seems to assume that the last G/L entry in the table was posted for this value entry; the jobs also do something similar.  

    The method I used was to save the G/L,C/L,V/L and related entries into a set of temp tables. Once it was time to commit the transaction the G/L would be locked and the entries renumbered to match the top of the G/L as they were streamed out to the database. I got over 90% overlap with pure G/L transactions with the Classic client, ie: the G/L was locked for only the last 10% of the transaction.

Page 1 of 1 (3 items)