Table IDs and ordering
In Incentives, columns that are selected as IDs make up the primary key. Here are some considerations:
The database automatically creates a clustered index based on these ID columns.
Ideally, only one column is selected as the unique identifier.
Narrow primary keys are important because the more columns we need, the more database resources are required to sort and store the underlying clustered index.
When tables are sourced, this can affect calculations, data stores, Presenter reports and Web Form data.
When more than one column is necessary for row uniqueness, the ordering of the columns is important.
The best order depends on how the data will be joined to in Incentives.
Incentives data tables
For Incentives data tables, calculations typically join to transaction IDs. For this reason, transaction ID should be the leading column. In Incentives table creation wizard, this means the topmost ID column. Subsequent ID columns should be created based on how often they are joined to.
For example, suppose a Data table has {TransactionID, Payee, TransactionDate} as its primary key columns. TransactionID is the first ID column created. To decide if Payee or TransactionDate should be the 2nd ID column, pick the one that will be used more often to join to this Data table. The theory supporting this technique is that the database will choose a 'clustered index seek' over a 'clustered index scan'.
Incentives custom tables
For Incentives custom tables, use the same approach as for data tables. ID columns should be created in the order of most used to least used.
Incentives structural tables
For Incentives structural tables, only one ID column is allowed therefore the ordering of multiple ID columns does not apply.