Skip to main content

Table pick list columns

Consider the following best practices when handling table pick list columns.

Parent-child relationship with structural tables

  • When Incentives data and custom tables include pick-listed columns, they establish a parent-child relationship.

  • The structural table acts as the parent, and the database links the two tables through a foreign key.

  • Maintaining data integrity is crucial in data modelling, and incorporating pick-listed columns is one method to ensure this.

Performance considerations

Inserting and updating records 

  • Insertion and updating of records can suffer performance drawbacks since the foreign key must be checked for data validity.

  • If a staging layer already performs these checks in some integration solutions, pick-listed columns might be redundant.

Optimization opportunity 

  • If import times are critical and pick-listed columns are redundant, consider converting these fields to text fields.

  • This change eliminates the ability to drill into the structural table for further data joins/retrieval within a calculation, which is a form of nested join and is not recommended as per the Calculation section.

Impact on page load times 

  • Presenter reports and Web Forms

    • Pick-listed columns directly impact the page load times of editable Presenter reports and Web Forms, especially when rendered as an editable field or part of a row form.

    • Page load times degrade as the number of selectable items in a drop-down list increases.

  • Example - PayeeID:

    • For fields like PayeeID, avoid using a pick list to the Payee table in tables sourced by Presenter reports and Web Forms.

    • Typically, these are hidden columns, either as Presenter report defaults or controlled via JavaScript.