다음을 통해 공유

Dynamics AX Development and Customization Best Practices Tables Design

  • 아티클

As we have discuss in previous article the basics of AX Dev best practices Conceptualization

Today we are going to discuss table design in detail:

Table design

Table design best practices that apply to data types, table designs, and key and index definitions.

Let's take a peek.

Surrogate keys, Natural keys and foreign key relations are the new concepts in 2012 tables. EDTs no longer support relations defined on them and Microsoft wants developers to avoid using Field fixed and related field fixed relations and instead use foreign key relations.

Before going into detail of best practices first we need to understand the keys:
**
**

Surrogate Key:

  • A single column index on a table that uniquely identifies each record
  • Also referred to as the Primary key, RecId index or the PrimaryIndex
  • No business meaning
  • System generated in Microsoft Dynamics AX 2012

Note: On new tables, the PrimaryIndex property will be set to SurrogateKey by default. Existing tables will NOT automatically have their PrimaryIndex property set to SurrogateKey.

Natural Key:

  • Defines a unique index that can be used instead of the SurrogateKey on look-up forms Optional Property
  • User-friendly index with business meaning
  • Also known as Alternate Key

Index properties

On table indexes, there is a new AlternateKey property. When set to Yes, this property allows for an index to be specified in the PrimaryIndex and NaturalKey properties on a table. The AlternateKey property can only be set to Yes on indexes with the AllowDuplicates property set to No since both the PrimaryIndex and NaturalKey properties require indexes that are unique.

 

  • Additionally on table indexes, a property called IncludedColumn has been added. A field with the IncludedColumn property set to Yes is added to a non-clustered index to improve the performance of a query by covering all of the fields that are referenced in a query using this index including the key and non-key fields. To set the IncludedColumn property to Yes, more than one field must exist on the index.

 

Relation properties

  • On a relation there is a property RelationshipType which can take 2 values. Composition and Association
  • In a Composition relationship, the parent table OWNS the child table. No records can be created in the child table without having a corresponding header in the parent table. For example, a sales line cannot exist without a sales header. A department can exist without an employee and an employee can be deleted without deleting the department, this relationship is an Association.

Foreign Keys: Parent/Child tables

  • Child table is one that has a foreign key column. Parent table is one that supplies the value for the foreign key column. Normal, Field fixed and Related field fixed table relations should be avoided going forward.

Best Practice:

Use a surrogate key as the primary key and the foreign key. Avoid using a natural key as the foreign key, because the natural key is attached to the business, and the value for it may change over time as required by the needs of the business. If a natural key is referenced, a change in its value would require updating all references. Surrogate keys are not attached to the business, and even if the natural key changes, the references to the surrogate key do not need to be updated.

Time zones and date effectivity:

You can use the UtcDateTime field to store date and time values. It has support for different time zones. Date fields should not be used unless they represent a value that is independent of the time zone. For example, use a date field for a birth date or an anniversary.

If the table has validFrom and validTo attributes, the table should be marked as a valid time state table. To mark the table as a valid time state table, set the ValidTimeStateFieldType property to UTCDateTime or Date. The Date field and utcDateTime field then implement date effectivity correctly.

Data type:

Use extended data types (EDTs) to create new fields, or to define parameters or variables in code. Avoid using base data types such as int, str, and str30, which have the following Disadvantages:

    • If you reference the data type in code in multiple places, the cost of updating code is high.
    • It is easy to introduce inconsistency in usage. For example, you could use str instead of str30.

In case of country-specific or region-specific functionality, verify that the country/region context is set correctly in all places, such as fields and EDTs. Enforce constraints in the database schema. When constraints are defined in code, it is easier to miss enforcing a constraint and therefore end up with inconsistent data.

Design the schema without influence from the UI. In the case of a complex or fully normalized schema, we recommend that you create views for use in the UI and reporting.

For example, by default, Microsoft Dynamics AX ships with views for dimensions and addresses. In some scenarios denormalization might also be needed. If denormalization is required, keep the following guidelines in mind:

    • Factor infrastructure and performance limitations into your design.
    • If denormalization is required, your design will include redundant data. Ensure that all redundant data is kept in sync.