Dynamics Community Forum Thread Details

Table Relations and indexes

Hi,

If we have an existing table with multiple fields. (TableX)

Now there was a task to create a table (TableY) with one string field only (Field1)

Field1 is added as an index with allow duplicate No.

First question: should we change the primary index on TableY to be this field1 or leave it surrogate key and why?

Second question: In general when should we leave primary key property to be surrogate key as is (default value) and when we should change the primary index property value to be equal to the allow duplicate = No index?

Then the task said, add that Field1 to TableX as relation.

Way1:
Add field1 to TableX as a string, then add relation on TableX (TableX.Field1 = TableY.Field1)

Way2:
Add Field1 to TableX as recId ( of course we will set replacementKey on TableY to be Field1 index)
then add relation on TableX (TableX.Field1RecId = TableY.RecId)
And on the form for TableX, we'll add it as a reference group

Third question: which way is better for this scenario? and why?
Forth question: In general when should we put relation based on the string field and when we should put it based on RecId? and why?

Categories:

Dynamics 365 Finance

Hi,

Thanks for your questions. I will try to help with my thoughts.

This is a choice you can make yourself. In the earlier versions, the group tables had a primary index on an identification column of a string type. In another release, for possible storage and/or performance reasons, the surrogate key was introduced where a reference record ID will be stored in a related table. If you look at some newer tables, then the index is changed to a string type field. There is no good or wrong here.
While there is no good or wrong, in case there is no unique index, the surrogate key is required to have at least a unique identifier for the records. Also in case a table has multiple fields that is part of the primary key, it can be useful to have the surrogate key.
This is in fact the same question as your question 1 and 2. In case there is one primary key field, use your own preference. The data model in table X will be easier with a string field, but both will work. In case there are multiple fields part of the primary key, way 2 will be better.
This all depends on the choices in questions 1 until 3

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL